Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

Mining–Time Series Example

Posted by Diego em Julho 16, 2012


http://technet.microsoft.com/en-us/library/ms345287(v=sql.100).aspx

 

CREATE MINING STRUCTURE [Bike Buyer]
(
   [Customer Key] LONG KEY,
   [Age]LONG DISCRETIZED(Automatic,10),
   [Bike Buyer] LONG DISCRETE,
   [Commute Distance] TEXT DISCRETE,
   [Education] TEXT DISCRETE,
   [Gender] TEXT DISCRETE,
   [House Owner Flag] TEXT DISCRETE,
   [Marital Status] TEXT DISCRETE,
   [Number Cars Owned]LONG DISCRETE,
   [Number Children At Home]LONG DISCRETE,
   [Occupation] TEXT DISCRETE,
   [Region] TEXT DISCRETE,
   [Total Children]LONG DISCRETE,
   [Yearly Income] DOUBLE CONTINUOUS
)
WITH HOLDOUT (30 PERCENT or 1000 CASES)
ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Decision Tree]
(
   [Customer Key],
   [Age],
   [Bike Buyer] PREDICT,
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]
) USING Microsoft_Decision_Trees
WITH DRILLTHROUGH
ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Clustering]
USING Microsoft_Clustering
INSERT INTO MINING STRUCTURE [Bike Buyer]
(
   [Customer Key],
   [Age],
   [Bike Buyer],
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]   
)
OPENQUERY([Adventure Works DW2008],
   'SELECT CustomerKey, Age, BikeBuyer,
         CommuteDistance,EnglishEducation,
         Gender,HouseOwnerFlag,MaritalStatus,
         NumberCarsOwned,NumberChildrenAtHome, 
         EnglishOccupation,Region,TotalChildren,
         YearlyIncome 
    FROM dbo.vTargetMail')
SELECT * FROM [Clustering].CONTENT

SELECT * 
FROM [Decision Tree].CASES

SELECT DISTINCT [Bike Buyer] 
FROM [Decision Tree]


SELECT
   [Bike Buyer] AS Buyer,
   PredictHistogram([Bike Buyer]) AS Statistics
FROM
   [Decision Tree]
NATURAL PREDICTION JOIN
(SELECT 35 AS [Age],
   '5-10 Miles' AS [Commute Distance],
   '1' AS [House Owner Flag],
   2 AS [Number Cars Owned],
   2 AS [Total Children]) AS t

 

http://technet.microsoft.com/en-us/library/cc879270(v=sql.100).aspx

 

CREATE MINING MODEL [Forecasting_MIXED]
     (
    [Reporting Date] DATE KEY TIME,
    [Model Region] TEXT KEY,
    [Quantity] LONG CONTINUOUS PREDICT,
    [Amount] DOUBLE CONTINUOUS PREDICT
    )
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = 0.8, FORECAST_METHOD = 'MIXED')
WITH DRILLTHROUGH
ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
ADD MINING MODEL [Forecasting_ARIMA]
   (
   [Reporting Date],
    [Model Region],
    [Quantity] PREDICT,
    [Amount] PREDICT
   )
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08, FORECAST_METHOD = 'ARIMA')
WITH DRILLTHROUGH
ALTER MINING STRUCTURE [Forecasting_MIXED_Structure]
ADD MINING MODEL [Forecasting_ARTXP]
   (
   [Reporting Date],
    [Model Region],
    [Quantity] PREDICT,
    [Amount] PREDICT
   ) 
USING Microsoft_Time_Series (AUTO_DETECT_PERIODICITY = .08, FORECAST_METHOD = 'ARTXP')
WITH DRILLTHROUGH
INSERT INTO MINING STRUCTURE [Forecasting_MIXED_Structure]
(  
[Reporting Date],[Model Region],[Quantity],[Amount]
)  
OPENQUERY(
[Adventure Works DW2008],
'SELECT [ReportingDate],[ModelRegion],[Quantity],[Amount] FROM vTimeSeries ORDER BY [ReportingDate]'
)
SELECT
[Forecasting_MIXED].[Model Region],
PredictTimeSeries([Forecasting_MIXED].[Quantity],6) AS PredictQty,
PredictTimeSeries ([Forecasting_MIXED].[Amount],6) AS PredictAmt
FROM 
[Forecasting_MIXED]
WHERE [Model Region] = 'M200 Europe' OR
[Model Region] = 'M200 Pacific'


SELECT [Model Region],
PredictTimeSeries([Quantity],6, EXTEND_MODEL_CASES) AS PredictQty
FROM
   [Forecasting_MIXED]
NATURAL PREDICTION JOIN 
(
   SELECT 1 AS [Reporting Date],
  '10' AS [Quantity],
  'M200 Europe' AS [Model Region]
UNION SELECT
  2 AS [Reporting Date],
  15 AS [Quantity],
  'M200 Europe' AS [Model Region]
) AS t
WHERE [Model Region] = 'M200 Europe' OR
[Model Region] = 'M200 Pacific'


SELECT [Model Region],
PredictTimeSeries([Quantity],3,6, EXTEND_MODEL_CASES) AS PredictQty
FROM
   [Forecasting_MIXED]
NATURAL PREDICTION JOIN
( 
   SELECT 1 AS [Reporting Date],
  '10' AS [Quantity],
  'M200 Europe' AS [Model Region]
UNION SELECT
  2 AS [Reporting Date],
  15 AS [Quantity],
  'M200 Europe' AS [Model Region]
) AS t
WHERE [Model Region] = 'M200 Europe'

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: