使用递归 CTE 计算预测平均值 [英] Calculate forecast average using recursive CTE

问题描述

我试图回答一个问题这里，我需要计算基于前 3 个月的销售额，可以是实际值，也可以是预测值.

I was trying to answer a question here, where I need to calculate a forecast of sales based on the 3 previous months which either can be actuals or forecast.

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:

I've been trying to do this using a recursive 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;

小提琴:http://sqlfiddle.com/#!6/9ac4a/3

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

But it doesn't work as expected, as It returns the following result:

| 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 |

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

Can someone tell me what's wrong with this query?

推荐答案

我的提议是这样的:

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                  |
+-------+----------+---------------------------+-------------------------+------------------------+

这篇关于使用递归 CTE 计算预测平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！