sql - 滚动日期期间的平均值

Question

我有 4 个维度，其中一个是日期。我需要计算每个日期的每个维度值过去 30 天的平均值。

我尝试以以下形式对 4 个维度的分区求平均值:

SELECT
Date, Produce,Company, Song, Revenues,
Average(case when Date between Date -Interval '31' day and Date - Interval '1' Day then Revenues else null End) over (partition by Date,Company,Song,Revenues order by Date) as "Running Average"
From 
Base_Table

我尝试的每个聚合都只得到空值。感谢帮助。谢谢

Answer 1

你可以试试下面-

SELECT
Date, Produce,Company, Song, Revenues,
Average(Revenues) over (partition by Company,Song rows between 30 preceding and current row) as "Running Average"
From 
Base_Table