Calcule el promedio de pronóstico usando CTE recursivo
Estaba tratando de responder una preguntaaquí, donde necesito calcular un pronóstico de ventas basado en los 3 meses anteriores que pueden ser reales o pronosticados.
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
He estado tratando de hacer esto usando un CTE recursivo:
;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;
Violín:http://sqlfiddle.com/#!6/9ac4a/3
Pero no funciona como se esperaba, ya que devuelve el siguiente resultado:
| 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) |
Rendimiento esperado:
| 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 |
¿Alguien puede decirme qué pasa con esta consulta?