SQL Server : moving average calculation
我有一个脚本可以根据过去 3 个月计算移动平均值。
是否可以修改此移动平均线以仅显示第 4 个月的值？或者当我想基于 4 个月计算时，显示第 5 行的值，依此类推…
因此，当我有足够的数据进行计算时，仅显示该月的值。
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT
[Brand], [month], CAST(SUM([Volume]) AS INT) AS [Volume (t)], AVG(CAST(SUM([Volume]) AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)], AVG(CAST(SUM([Volume]) AS INT)) OVER (PARTITION BY [Brand] ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS [Moving Average (3)] FROM dw_lc_full_aggregated_1 WHERE [year] = 2016 AND [month] BETWEEN 1 AND 9 AND [Navision Source] = ‘HU’ AND [Brand] = ‘xxxxxxx’ GROUP BY [Brand], [month] |
- 您是否尝试过使用 CASE 和 COUNT？
- 还没有，因为我不知道该怎么做:)
- 试一试，当 COUNT OVER 您的 PARTITION 大于 3 时，您基本上想要 CASE。您可以将其直接添加到当前查询中，否则使用您的 <x5 添加行号>，找到它的最大值，然后过滤掉小于3的最大行数
- 您如何决定移动平均字段中必须包含多少个月？该查询的外观如何？
谢谢！我解决了它如下：
|
1
2 3 4 5 6 7 8 9 10 |
SELECT [Brand],
[month], cast(sum([Volume]) AS INT) as [Volume (t)], AVG(cast(sum([Volume])AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)], CASE WHEN (Row_Number() OVER (ORDER BY [month]))>3 |
来源：https://www.codenong.com/48764068/
