static SqlClientFactory ()
{
lock (lockStatic)
{
if (Instance == null)
Instance = new SqlClientFactory ();
}
}
static void Main(string[] args)
{
Console.WriteLine("Training time series analysis");
//Step 1. Create a ML Context
var ctx = new MLContext();
string connectionString = "Data Source=localhost;Initial Catalog=kaggle_wallmart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
connectionString = "Server=localhost;Database=kaggle_wallmart;Integrated Security=True";
string Query = @"
SELECT
CAST(X.[Value] AS REAL) AS [TotalSales],
CAST(Y.date AS DATE) AS [SalesDate],
CAST(year(Y.date) AS REAL) As [Year]
FROM [dbo].[RAW_Train_Eval] AS X
INNER JOIN [dbo].RAW_Calendar AS Y ON Y.d=X.dCode
where Id='HOBBIES_1_278_CA_1_evaluation'
order by 2
";
Console.WriteLine("Connecting to the database...");
//dbChecks dbchecks = new dbChecks();
//dbchecks.ExecuteQuery(connectionString, Query);
System.Data.SqlClient.SqlClientFactory newFactory = SqlClientFactory.Instance;
Console.WriteLine("Loading data...");
DatabaseSource dbSource = new DatabaseSource(SqlClientFactory.Instance, connectionString, Query);
DatabaseLoader loader = ctx.Data.CreateDatabaseLoader <ModelInput>();
IDataView dataView = loader.Load(dbSource);
Console.WriteLine($"Loaded {dataView.GetRowCount()} rows...");
IDataView trainingData = ctx.Data.FilterRowsByColumn(dataView, "Year", upperBound: 2016);
IDataView ValidationData = ctx.Data.FilterRowsByColumn(dataView, "Year", lowerBound: 2016);
var forecastingPipeline = ctx.Forecasting.ForecastBySsa(
outputColumnName: "ForecastedSales",
inputColumnName: "TotalSales",
windowSize: 7,
seriesLength: 60,
trainSize: 300,
horizon: 30,
confidenceLevel: 0.95f,
confidenceLowerBoundColumn: "LowerBoundSales",
confidenceUpperBoundColumn: "UpperBoundSales");
SsaForecastingTransformer forecaster = forecastingPipeline.Fit(trainingData);
Evaluate(ValidationData, forecaster, ctx);
var forecastEngine = forecaster.CreateTimeSeriesEngine <ModelInput, ModelOutput>(ctx);
forecastEngine.CheckPoint(ctx, "c:\\temp\\Model.zip");
forecastEngine.CheckPoint(ctx, "C:\\Temp\\WallMartModels\\evaluation\\Model_HOBBIES_1_278_CA_1_evaluation.zip");
Forecast(ValidationData, 7, forecastEngine, ctx);
Console.WriteLine("Training time series analysis completed");
}
public string ExtractData(int PreviousDaysRef, int FutureDaysRef, string CutOffDateRef)
{
string QuerySTR = @"
DECLARE @FutureDays INT = " + FutureDaysRef.ToString() + @";
DECLARE @PreviousDays INT = " + PreviousDaysRef.ToString() + @";
DECLARE @CutOffdate DATE='" + CutOffDateRef + @"';
DECLARE @ProductId VARCHAR(250)='" + this.product + @"';
DECLARE @Enddate DATE=DATEADD(dd, @FutureDays, @CutOffdate);
WITH daysRange (n, currentdate, enddate) AS (
SELECT 0 AS n, @CutOffdate AS currentdate, @Enddate AS enddate
UNION ALL
SELECT X.n + 1 AS n, dateadd(dd,1,X.currentdate) AS startdate, X.enddate
FROM daysRange AS X
WHERE dateadd(dd,1,X.currentdate) < X.enddate
)
SELECT
CAST(COALESCE(X.[Value], '0') AS REAL) AS [TotalSales],
CAST(Y.currentdate AS DATE) AS [SalesDate],
CAST(year(Y.currentdate) AS REAL) As [Year]
FROM daysRange AS Y
LEFT JOIN dbo.RAW_Calendar AS Z ON CAST(Z.date AS DATE) =Y.currentdate
LEFT JOIN dbo.RAW_Train_Eval AS X ON X.dCode = Z.d
ORDER BY CAST(Y.currentdate AS DATE)
";
System.Data.SqlClient.SqlClientFactory newFactory = SqlClientFactory.Instance;
DatabaseSource dbSource = new DatabaseSource(SqlClientFactory.Instance, this.connectionstring, QuerySTR);
DatabaseLoader loader = ctx.Data.CreateDatabaseLoader <ModelInput>();
this.DataExtracted = loader.Load(dbSource);
return(QuerySTR);
}
