基于列n值计算Rolling Average的Presto SQL解决方案咨询

错误原因 #

1. 多余的GROUP BY：窗口函数AVG() OVER()本身就是对分组（PARTITION BY grp）内的行做窗口聚合计算，不需要额外添加GROUP BY子句。添加GROUP BY后，SQL解析会认为你要做聚合分组，而窗口函数的结果属于聚合后的值，和GROUP BY的逻辑冲突，导致报错。
2. 窗口框架不支持动态参数：Presto不允许在ROWS BETWEEN中使用列值（比如n）作为动态的行数参数，而且你的需求是过去n天的滚动平均，使用ROWS会基于行数计算（如果组内日期不连续，行数不等于天数），不符合实际需求。

解决方案 #

方案一：子查询+条件聚合（适合小数据量） #

通过子查询对每一行，筛选同组内日期在当前日期 - n天到当前日期范围内的记录，计算平均值：

SELECT 
    t1.date1,
    t1.grp,
    t1.n,
    (SELECT AVG(t2.value)
     FROM tbl t2
     WHERE t2.grp = t1.grp
       AND t2.date1 >= DATE_SUB(t1.date1, INTERVAL t1.n DAY)
       AND t2.date1 <= t1.date1) AS rolling_avg
FROM tbl t1
ORDER BY t1.grp, t1.date1;

方案二：窗口函数+日期转数值（高效，适合大数据量） #

将日期转换为数值型的天数（比如从1970-01-01开始的天数），利用RANGE窗口支持数值范围的特性，实现动态n天的滚动平均：

WITH date_converted AS (
    SELECT 
        date1,
        grp,
        n,
        value,
        -- 转换日期为从1970-01-01开始的天数
        DATE_DIFF('day', DATE '1970-01-01', date1) AS day_num
    FROM tbl
)
SELECT 
    date1,
    grp,
    n,
    AVG(value) OVER (
        PARTITION BY grp
        ORDER BY day_num
        RANGE BETWEEN n PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM date_converted
ORDER BY grp, date1;

注意事项 #

• 避免使用保留字作为别名：原查询中AS PRIMARY会报错，因为PRIMARY是SQL保留字，建议改为rolling_avg这类自定义名称。
• 日期类型兼容：如果date1是TIMESTAMP类型，DATE_SUB和DATE_DIFF函数依然适用，无需额外转换。

内容的提问来源于stack exchange，提问作者Pratibha UR