TSQL-Tasks: января 2016

Система управления базами данных Microsoft SQL Server обладает компонентом интеллектуального анализа данных (Data Mining), который предоставляет набор алгоритмов, позволяющих анализировать табличные и многомерные данные. Этот анализ позволяет предсказывать будущее поведение данных, прогнозировать значения столбцов, искать ассоциации, находить ключевые значения одних столбцов, влияющих на значения других столбцов.

Рассмотрим в данной статье анализ роста прибыли от продажи некоторых абстрактных продуктов. Природа этих продуктов и единица измерения прибыли сейчас не имеют значения. Важно то, что такие данные имеют естественную временную ось. С течением времени прибыль по каждому продукту накапливается с разной скоростью, изменение ее объема может происходить по разным законам в зависимости от типа продукта. Если продуктов сотни или тысячи, а история наблюдений охватывает годы, то прогнозирование будущей прибыли по каждому такому продукту может стать непростой задачей. Компонент Data Mining позволяет во многом автоматизировать поиск закономерностей в изменении данных и прогнозирование значений атрибутов. Поскольку мы рассматриваем задачу с временной осью, то будет полезно воспользоваться алгоритмом временных рядов. Но сначала создадим структуру данных, а также сами данные, в которые будут внедрены определенные закономерности.

Создадим и наполним таблицу продуктов. Для простоты рассмотрим поведение одного продукта, так как создание модели прогнозирования не зависит от числа элементов.

if object_id ( N'dbo.Products', N'U' ) is null

begin

create table dbo.Products

(

iProductId smallint identity ( 1, 1 ) not null,

vcName varchar ( 100 ) not null,

constraint PK_Products_iProductId primary key clustered ( iProductId asc ) on [primary],

constraint AK_Products_vcName unique nonclustered ( vcName asc ) on [primary]

) on [primary]

end

if not exists

(

select *

from dbo.Products

where vcName = 'Продукт_1'

)

begin

insert into dbo.Products ( vcName )

values ( 'Продукт_1' )

end

go

Теперь создадим таблицу с историей доходов по продуктам:

if object_id ( N'dbo.DayIncome', N'U' ) is null

begin

create table dbo.DayIncome

(

iRowId int identity ( 1, 1 ) not null,

dtDate date not null,

iProductId smallint not null,

fIncome float not null,

constraint PK_DayIncome_dtDate_iProductId_iRowId primary key clustered ( dtDate asc, iProductId asc, iRowId asc ),

constraint FK_DayIncome_iProductId foreign key ( iProductId ) references dbo.Products ( iProductId ) on update cascade on delete no action

) on [primary]

end

go

Заполним таблицу с историей прибыли от продуктов на каждый день 2015-ого года. При этом считаем, что особенностью продукта Продукт_1 является то, что с начала каждого квартала прибыль ничтожна, в районе тысячи, и с каждым днем в первые три недели растет как парабола:

set nocount on

declare @iQuarter int = 1

declare @dtStart date = datefromparts ( 2015, @iQuarter * 3 - 2, 1 ), @dtEnd date, @dtCur date, @iProductId smallint = ( select iProductId from dbo.Products where vcName = 'Продукт_1' )

set @dtEnd = dateadd ( day, 19, @dtStart )

set @dtCur = @dtStart

while @dtCur <= @dtEnd

begin

insert into dbo.DayIncome

(

dtDate,

iProductId,

fIncome

)

values

(

@dtCur,

@iProductId,

1000 + power ( 300 * day ( @dtCur ), 2 ) +

100 * round ( rand (), 4 ) * case when cast ( ( 10 * round ( rand (), 1 ) ) as int ) % 2 = 0 then 1 else -1 end

)

set @dtCur = dateadd ( day, 1, @dtCur )

end

set nocount off

Затем в последующие 10 недель объем за каждый день колеблется на одном уровне, отклоняясь выше или ниже на величину не превышающую 20 % от некоторого среднего значения:

set nocount on

declare @iQuarter int = 1

declare @dtStart date = dateadd ( day, 20, datefromparts ( 2015, @iQuarter * 3 - 2, 1 ) ), @dtEnd date, @dtCur date, @iProductId smallint = ( select iProductId from dbo.Products where vcName = 'Продукт_1' )

set @dtEnd = dateadd ( day, 50, @dtStart )

set @dtCur = @dtStart

while @dtCur <= @dtEnd

begin

insert into dbo.DayIncome

(

dtDate,

iProductId,

fIncome

)

values

(

@dtCur,

@iProductId,

36000000 + 7200000 * round ( rand (), 4 ) * case when cast ( ( 10 * round ( rand (), 1 ) ) as int ) % 2 = 0 then 1 else -1 end

)

set @dtCur = dateadd ( day, 1, @dtCur )

end

set nocount off

go

Последние 3 недели квартала ознаменовываются тем, что наблюдается резкий скачок в каждодневной прибыли, которая нарастает по экспоненте:

set nocount on

declare @iQuarter int = 1

declare @dtStart date = dateadd ( day, 71, datefromparts ( 2015, @iQuarter * 3 - 2, 1 ) ), @dtEnd date, @dtCur date,

@iProductId smallint = ( select iProductId from dbo.Products where vcName = 'Продукт_1' )

set @dtEnd = dateadd ( day, -1, dateadd ( quarter, 1, datefromparts ( 2015, @iQuarter * 3 - 2, 1 ) ) )

set @dtCur = @dtStart

while @dtCur <= @dtEnd

begin

insert into dbo.DayIncome

(

dtDate,

iProductId,

fIncome

)

values

(

@dtCur,

@iProductId,

36000000 + 3600000 * round ( rand (), 4 ) * case when cast ( ( 10 * round ( rand (), 1 ) ) as int ) % 2 = 0 then 1 else -1 end +

20000 * cast ( 1000 + power ( 2 * ( 1 - 0.01 * ( datediff ( day, @dtStart, @dtCur ) + 1 ) ), ( datediff ( day, @dtStart, @dtCur ) + 1 ) ) as float )

)

set @dtCur = dateadd ( day, 1, @dtCur )

end

set nocount off

go

Прогнав эти скрипты для четырех кварталов 2015-ого года и создав сводную таблицу и диаграмму в Excel на основе таблицы dbo.DayIncome, можно увидеть следующее представление данных:

Перед нами стоит задача спрогнозировать поведение объема дневных прибылей продуктов на первое полугодие 2016-ого года. Конечно, внедренные закономерности легко обнаружить в приведенном выше рисунке. Однако в реальности графики могут быть более сложными и не очевидными для невооруженного глаза, да и самих продуктов может быть очень много. Но для всех подобных ситуаций работают одни и те же принципы для распознавания поведения данных.

Прежде чем заняться построением модели прогнозирования требуется создать базу данных Analysis Services. Для этого выполним код на языке XML/A:

<DatabaseID>ProductModels</DatabaseID>

</Object>

<Database

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"

xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"

xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"

xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"

xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"

xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300"

xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"

<ID>ProductModels</ID>

<Name>ProductModels</Name>

<ImpersonationMode>Default</ImpersonationMode>

</DataSourceImpersonationInfo>

</Database>

</ObjectDefinition>

</Alter>

</Batch>

Теперь в новой базе данных необходимо создать источник данных, чтобы база данных могла подключаться к привычным таблицам SQL Server. Для создания источника данных создадим хранимую процедуру для Analysis Services на основе clr-сборки. Сначала требуется создать проект Visual C# типа библиотека классов. К нему требуется добавить ссылки для подключения пространств имен Microsoft.AnalysisServices и Microsoft.AnalysisServices.AdomdServer. Эти ссылки добавляются через файлы Microsoft.AnalysisServices.dll и msmgdsrv.dll, которые находятся соответственно в каталогах C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies и C:\Program Files\Microsoft Analysis Services\AS OLEDB\120 (если у вас установлен MS SQL 2014).

Сборка реализуется в виде класса, в котором есть 2 метода для создания и удаления источников данных:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.AnalysisServices.AdomdServer;

using Microsoft.AnalysisServices;

namespace DatabaseOper

{

public class DatabaseOper

{

[SafeToPrepare(true)]

public void CreateDataSource(string DataSourceName, string ConnectionString,

string sImpersonationMode, string Account, string Password)

{

if (Context.ExecuteForPrepare)

{

return;

}

Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();

svr.Connect("*");

Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);

DataSource ds = db.DataSources.Add(DataSourceName, DataSourceName);

ds.ConnectionString = ConnectionString;

bool FoundMode = false;

foreach (ImpersonationMode mode in Enum.GetValues(typeof(ImpersonationMode)))

{

if (mode.ToString() == sImpersonationMode)

{

FoundMode = true;

ds.ImpersonationInfo = new ImpersonationInfo(mode, Account, Password);

break;

}

if (!FoundMode)

{

throw (new SystemException("Недопустимый режим олицетворения."));

}

ds.Update();

}

[SafeToPrepare(true)]

public void DropDataSource(string DataSourceName)

{

if (Context.ExecuteForPrepare)

{

return;

}

Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();

svr.Connect("*");

Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);

DataSource ds = db.DataSources.FindByName(DataSourceName);

if (ds != null)

{

ds.Drop();

}

}

Думаю в этом коде все понятно. Он повторяет все те действия, которые мы делаем в графическом конструкторе Business Intelligence Development Studio. Сборка иллюстрирует возможности объектной модели AdoMd.Net. C ее помощью можно выполнять административные задачи сервера Analysis Services, запускать код на языках MDX и DMX, выполнять операции с многомерными объектами.

Развернем сборку в базе данных. Это можно сделать с помощью обозревателя объектов в Management Studio:

После развертывания сборки в редакторе запросов dmx можно запустить процедуру создания источника данных:

call DatabaseOper.CreateDataSource

(

'DataStructure',

'Provider=SQLNCLI11.1;Data Source=1-ПК;Integrated Security=SSPI;Initial Catalog=Sales',

'ImpersonateServiceAccount',

'',

)

Теперь можно приступить к проектированию объектов моделирования. Создадим структуру добычи данных:

create mining structure ProductAnalysis

(

dtDate date key time,

iProductId long key,

fIncome double continuous

);

В метаданных можно проверить, что структура создана:

select *

from $system.dmschema_mining_structures

where structure_name = 'ProductAnalysis'

;

К структуре Data Mining добавим модель, использующую алгоритм временных рядов:

alter mining structure ProductAnalysis

add mining model ProductsDynamic

(

dtDate,

iProductId,

fIncome predict

) using microsoft_time_series

(

auto_detect_periodicity = 0.99,

forecast_method = 'arima',

minimum_series_value = 1000,

maximum_series_value = 350000000

)

with drillthrough

;

Свойства созданной модели можно просмотреть таким запросом:

select *

from $system.dmschema_mining_models

where model_name = 'ProductsDynamic'

;

Теперь требуется произвести этап обучения модели. Структуру необходимо наполнить данными за 2015-ый год, в этот момент модель применит алгоритм и создаст паттерны:

insert into mining structure ProductAnalysis ( dtDate, iProductId, fIncome )

openquery

(

DataStructure,

select dtDate, iProductId, fIncome

from dbo.DayIncome

)

;

После окончания обучения остается только запустить прогнозирующий запрос. Для просмотра прогноза на ближайшие 6 месяцев по первому продукту можно выполнить такой запрос:

select flattened PredictTimeSeries ( fIncome, 180 )

from ProductsDynamic

where iProductId = 1

;

Для возможности запуска таких запросов на стороне реляционного сервера создадим связанный сервер:

use master

if not exists

(

select *

from sys.servers

where name = 'DataMining'

)

begin

exec master.dbo.sp_addlinkedserver @server = N'DataMining', @srvproduct = N'MSOLAP', @provider = N'MSOLAP', @datasrc = N'1-ПК', @catalog = N'DataMining'

end

exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'DataMining', @useself = N'True', @locallogin = null, @rmtuser = null, @rmtpassword = null

exec master.dbo.sp_serveroption @server = N'DataMining', @optname = N'collation compatible', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'DataMining', @optname = N'data access', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'DataMining', @optname = N'rpc', @optvalue = N'true'

exec master.dbo.sp_serveroption @server = N'DataMining', @optname = N'rpc out', @optvalue = N'true'

Создадим такое представление чтобы построить на его основе сводную таблицу в Excel:

if object_id ( N'dbo.GetProductForeCast', N'V' ) is null

begin

exec sp_executesql N'create view dbo.GetProductForeCast as select i = 1'

end

alter view dbo.GetProductForeCast

select dtDate = [Expression.$TIME], fIncome = [Expression.fIncome]

from openquery

(

DataMining,

select flattened PredictTimeSeries ( fIncome, 185 )

from ProductsDynamic

where iProductId = 1

)

Создадим сводную таблицу на новом листе Excel:

Результат не впечатляет. Для улучшения качества прогноза требуется передать модели дополнительные данные, которые есть у нас: мы понимаем, что в распределении данных есть квартальная цикличность. Поэтому необходимо воспользоваться параметром periodicity_hint модели:

drop mining model ProductsDynamic;

alter mining structure ProductAnalysis

add mining model ProductsDynamic

(

dtDate,

iProductId,

fIncome predict

) using microsoft_time_series

(

auto_detect_periodicity = 0.99,

forecast_method = 'arima',

periodicity_hint = "{ 92 }",

minimum_series_value = 1000,

maximum_series_value = 350000000

)

with drillthrough

;

delete from mining structure ProductAnalysis

;

insert into mining structure ProductAnalysis ( dtDate, iProductId, fIncome )

openquery

(

DataStructure,

select dtDate, iProductId, fIncome

from dbo.DayIncome

)

;

Теперь результат предсказания выглядит намного лучше:

В то же время вы видим, что рост в конце квартала не настолько быстрый как на самом деле. Если мы знаем, что рост в последние 20 дней квартала следует одним и тем же тенденциям, то можно дополнить этим знанием модель:

drop mining model ProductsDynamic;

alter mining structure ProductAnalysis

add mining model ProductsDynamic

(

dtDate,

iProductId,

fIncome predict

) using microsoft_time_series

(

auto_detect_periodicity = 0.99,

forecast_method = 'arima',

periodicity_hint = "{ 20, 92 }",

minimum_series_value = 1000,

maximum_series_value = 350000000

)

with drillthrough

;

delete from mining structure ProductAnalysis

;

insert into mining structure ProductAnalysis ( dtDate, iProductId, fIncome )

openquery

(

DataStructure,

select dtDate, iProductId, fIncome

from dbo.DayIncome

)

;

В этот раз все точно:

Метод временных предоставляет и другие возможности. Предположим, что с 2016-ого года поступил новый продукт, который продается только первые десять дней 2016-ого, но за это время динамика у него такая же как и у продукта Продукт_1 за аналогичный период. Если есть уверенность что данный продукт продается с той же скоростью, что и Продукт_1, то можно построить прогноз для нового продукта, используя часть модели, построенную для первого продукта:

if object_id ( N'dbo.GetNewProdPred', N'V' ) is null

begin

exec sp_executesql N'create view dbo.GetNewProdPred as select i = 1'

end

alter view dbo.GetNewProdPred

select dtDate = [Expression.$TIME], fIncome = [Expression.fIncome]

from openquery

(

DataMining,

select flattened PredictTimeSeries ( fIncome, 185 )

from

ProductsDynamic

prediction join

(

select ''2016-01-01'' as dtDate, 200000 as fIncome, 1 as iProductId

union

select ''2016-01-02'' as dtDate, 1000000 as fIncome, 1 as iProductId

union

select ''2016-01-03'' as dtDate, 3000000 as fIncome, 1 as iProductId

union

select ''2016-01-04'' as dtDate, 8000000 as fIncome, 1 as iProductId

union

select ''2016-01-05'' as dtDate, 12000000 as fIncome, 1 as iProductId

union

select ''2016-01-06'' as dtDate, 15000000 as fIncome, 1 as iProductId

union

select ''2016-01-07'' as dtDate, 17000000 as fIncome, 1 as iProductId

union

select ''2016-01-08'' as dtDate, 23000000 as fIncome, 1 as iProductId

union

select ''2016-01-09'' as dtDate, 29000000 as fIncome, 1 as iProductId

union

select ''2016-01-10'' as dtDate, 43000000 as fIncome, 1 as iProductId

) as src on

ProductsDynamic.dtDate = src.dtDate and

ProductsDynamic.fIncome = src.fIncome and

ProductsDynamic.iProductId = src.iProductId

)

У модели временных рядов есть много других параметров. В частности, можно выполнять анализ типа "что если". Например. если объем продаж зависит от определенных параметров продукта, и они входят в структуру как входные параметры, то можно задать определенные значения этих параметров на будущие периоды и получить прогноз на будущие периоды в зависимости от будущих значений дополнительных параметров.

TSQL-Tasks

Страницы

Страницы

суббота, 2 января 2016 г.

Прогнозирование данных с помощью временных рядов

Страницы

Страницы

суббота, 2 января 2016 г.

Прогнозирование данных с помощью временных рядов

суббота, 2 января 2016 г.