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); }