Блог

В таблице ниже данные разбиты на группы за месяц. Апрель был историческим выбросом, и я хочу заменить апрельские данные [frcst_qty] (‘2020-04-01’) средним значением за предыдущие 3 месяца (‘2020-01-01’, ‘2020-02-01’, ‘2020-03-01’).

Каков наилучший способ добиться этого?

 DROP TABLE IF EXISTS [s3_load]

CREATE TABLE [s3_load] (
                 [year_month] VARCHAR(10)
                ,[item_id] VARCHAR(54)
                ,[keycust3] VARCHAR(54)
                ,[frcst_qty] NUMERIC(10, 0)
                )
INSERT INTO [s3_load] (
                 [year_month]
                ,[item_id]
                ,[keycust3]
                ,[frcst_qty]
                 )
SELECT  CONVERT (VARCHAR(10), a.[year_month], 20 ) AS [year_month]
       ,a.[item_id]
       ,a.[keycust3]
       ,SUM ( ISNULL(a.[frcst_qty], 0) ) AS [frcst_qty]
  FROM [AWS_Sta&e] a
  JOIN (
        SELECT DISTINCT [item_id]
        FROM [AWS_Sta&e]
        WHERE [yr] in ('2020')
        GROUP BY [item_id]
        HAVING SUM ( ISNULL([frcst_qty], 0) ) &&t;= 0 
        ) b
    ON a.[item_id] = b.[item_id]
GROUP BY  a.[year_month]
         ,a.[item_id]
         ,a.[keycust3]
ORDER BY a.[year_month];
  

Две вставки. Первая вставка исключает ‘2020-04-01’. Вторая вставка включает среднее значение frcst_qty за предыдущие 3 [year_month] года, помеченное как ‘2020-04-01’.

 WITH items_cte([item_id])
     AS (SELECT [item_id]
         FROM [AWS_Sta&e]
         WHERE [yr] IN('2020')
         GROUP BY [item_id]
         HAVING SUM(ISNULL([frcst_qty], 0)) &&t;= 0),
     ym_cte([year_month], 
            [item_id], 
            [keycust3], 
            [frcst_qty])
     AS (SELECT CONVERT(VARCHAR(10), a.[year_month], 20), 
                a.[item_id], 
                a.[keycust3], 
                SUM(ISNULL(a.[frcst_qty], 0))
         FROM [AWS_Sta&e] a
              JOIN items_cte b ON a.[item_id] = b.[item_id]
         GROUP BY a.[year_month], 
                  a.[item_id], 
                  a.[keycust3]),
     ym_prior_3_av&_cte([year_month], 
                        [item_id], 
                        [keycust3], 
                        [frcst_qty])
     AS (SELECT '2020-04-01', 
                [item_id], 
                [keycust3], 
                AVG([frcst_qty])
         FROM ym_cte
         WHERE [year_month] IN('2020-01-01', '2020-02-01', '2020-03-01')
         GROUP BY [item_id], 
                  [keycust3])
     INSERT INTO [s3_load]
     ([year_month], 
      [item_id], 
      [keycust3], 
      [frcst_qty]
     )
            SELECT *
            FROM ym_cte
            WHERE [year_month] <&&t; '2020-04-01'
            UNION ALL
            SELECT *
            FROM ym_prior_3_av&_cte;