SQL(Redshift)-前面所有值的滚动平均值

Question

我有一个这样的表：

值

天
1	3
1	5
1	1
2	4
2	7
3	1
3	1
3	2
3	5

首先按天进行聚合，以获得每天的值和计数的总和。然后使用分析函数来找到滚动平均值。

WITH cte AS (
SELECT Day, SUM(Value) ValueSum, COUNT(*) AS Count
FROM yourTable
GROUP BY Day
)
SELECT Day, SUM(ValueSum) OVER (ORDER BY Day) /
SUM(Count) OVER (ORDER BY Day) AS Rolling_avg
FROM cte
ORDER BY Day;

演示