sql-server - 使用递归 CTE 计算预测平均值

Question

我试图回答一个问题 here ，我需要根据前 3 个月来计算销售预测，这可以是实际的也可以是预测的。

Month   Actuals Forecast  
1       10    
2       15    
3       17    
4                14.00 
5                15.33  
6                15.44 
7                14.93

Month 4 = (10+15+17)/3 
Month 5 = (15+17+14)/3 
Month 6 = (17+14+15.33)/3
Month 7 = (14+15.33+15.44)/3

我一直在尝试使用递归 CTE:

;WITH cte([month],forecast) AS (
    SELECT 1,CAST(10 AS DECIMAL(28,2))
    UNION ALL
    SELECT 2,CAST(15 AS DECIMAL(28,2))
    UNION ALL 
    SELECT 3,CAST(17 AS DECIMAL(28,2))
    UNION ALL
    SELECT
        [month]=[month]+1,
        forecast=CAST(AVG(forecast) OVER (ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS DECIMAL(28,2))
    FROM
        cte
    WHERE
        [month]<=12
)
SELECT * FROM cte WHERE month<=12;

fiddle :http://sqlfiddle.com/#!6/9ac4a/3

但它并没有按预期工作，因为它返回以下结果:

| month | forecast |
|-------|----------|
|     1 |       10 |
|     2 |       15 |
|     3 |       17 |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |
|     3 |   (null) |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |
|     2 |   (null) |
|     3 |   (null) |
|     4 |   (null) |
|     5 |   (null) |
|     6 |   (null) |
|     7 |   (null) |
|     8 |   (null) |
|     9 |   (null) |
|    10 |   (null) |
|    11 |   (null) |
|    12 |   (null) |

预期输出:

| month | forecast |
|-------|----------|
|     1 |       10 |
|     2 |       15 |
|     3 |       17 |
|     4 |    14.00 |
|     5 |    15.33 |
|     6 |    15.44 |
|     7 |    14.93 |
|     8 |    15.23 |
|     9 |    15.20 |
|    10 |    15.12 |
|    11 |    15.18 |
|    12 |    15.17 |

谁能告诉我这个查询有什么问题？

Answer 1

我提出这样的建议:

WITH T AS
(
    SELECT 1 AS [month], CAST(10 AS DECIMAL(28,2)) AS [forecast], CAST(-5 AS DECIMAL(28,2)) AS three_months_ago_forecast, CAST(9 AS decimal(28,2)) AS two_months_ago_forecast, CAST(26 AS decimal(28,2)) as one_month_ago_forecast
    UNION ALL
    SELECT 2,CAST(15 AS DECIMAL(28,2)), CAST(9 AS decimal(28,2)), CAST(26 AS decimal(28,2)), CAST(10 AS DECIMAL(28,2))
    UNION ALL 
    SELECT 3,CAST(17 AS DECIMAL(28,2)), CAST(26 AS decimal(28,2)), CAST(10 AS DECIMAL(28,2)), CAST(15 AS DECIMAL(28,2))
),
LT AS -- LastForecast
(
    SELECT *
    FROM T
    WHERE [month] = 3
),
FF AS -- Future Forecast
(
    SELECT *
    FROM LT

    UNION ALL

    SELECT 
        FF.[month] + 1 AS [month], 
        CAST( (FF.forecast * 4 - FF.three_months_ago_forecast) / 3 AS decimal(28,2)) AS forecast,
        FF.two_months_ago_forecast as three_months_ago_forecast,
        FF.one_month_ago_forecast as two_months_ago_forecast,
        FF.forecast as one_month_ago_forecast
    FROM FF
    WHERE
        FF.[month] < 12

)
SELECT * FROM T
WHERE [month] < 3
UNION ALL
SELECT * FROM FF

输出:

+-------+----------+---------------------------+-------------------------+------------------------+
| month | forecast | three_months_ago_forecast | two_months_ago_forecast | one_month_ago_forecast |
+-------+----------+---------------------------+-------------------------+------------------------+
|     1 | 10.00    | -5.00                     | 9.00                    | 26.00                  |
|     2 | 15.00    | 9.00                      | 26.00                   | 10.00                  |
|     3 | 17.00    | 26.00                     | 10.00                   | 15.00                  |
|     4 | 14.00    | 10.00                     | 15.00                   | 17.00                  |
|     5 | 15.33    | 15.00                     | 17.00                   | 14.00                  |
|     6 | 15.44    | 17.00                     | 14.00                   | 15.33                  |
|     7 | 14.92    | 14.00                     | 15.33                   | 15.44                  |
|     8 | 15.23    | 15.33                     | 15.44                   | 14.92                  |
|     9 | 15.20    | 15.44                     | 14.92                   | 15.23                  |
|    10 | 15.12    | 14.92                     | 15.23                   | 15.20                  |
|    11 | 15.19    | 15.23                     | 15.20                   | 15.12                  |
|    12 | 15.18    | 15.20                     | 15.12                   | 15.19                  |
+-------+----------+---------------------------+-------------------------+------------------------+