static SqlClientFactory ()
 {
         lock (lockStatic) 
         {
                 if (Instance == null)
                         Instance = new SqlClientFactory ();
         }
 }
Пример #2
0
        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);
        }