如何针对boolean=1的行计算前3天boolean=0的fact滚动平均值？

原数据表 #

需求说明 #

仅在boolean列值为1的行中，计算其前3天内boolean列值为0的fact列的滚动平均值，其余行的rolling_avg字段为空。

预期结果 #

示例说明 #

• 2022-01-05的boolean=1行，前3天为2022-01-02至2022-01-04，其中boolean=0的行fact值为110和70，平均值为90；
• 2022-01-09的boolean=1行，前3天为2022-01-06至2022-01-08，所有行boolean=0，fact值为40、50、70，平均值为53.33。

尝试的错误代码 #

用户最初尝试的窗口函数未满足需求：

AVG(expression) OVER (PARTITION BY id1, id2 
                      ORDER BY date DESC 
                      ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as rolling_avg

正确实现方法 #

逻辑分析 #

原代码存在三个核心问题：

1. 用ROWS BETWEEN按行计数，无法准确匹配“前3天”的日期范围要求；
2. 未过滤boolean=0的行来计算平均值；
3. 未控制仅boolean=1的行显示结果，其余行置空。

正确思路：

• 使用范围窗口结合日期函数限定前3天的区间；
• 在平均值计算中通过条件判断仅纳入boolean=0的fact值；
• 最后通过条件语句确保只有boolean=1的行展示结果。

具体SQL代码 #

SELECT 
    date,
    id1,
    id2,
    fact,
    boolean,
    CASE 
        WHEN boolean = 1 THEN 
            ROUND(
                AVG(CASE WHEN boolean = 0 THEN fact ELSE NULL END) 
                OVER (
                    PARTITION BY id1, id2 
                    ORDER BY date 
                    RANGE BETWEEN INTERVAL '3 days' PRECEDING AND INTERVAL '1 day' PRECEDING
                ),
                2
            )
        ELSE NULL 
    END AS rolling_avg
FROM your_table_name;

代码解释 #

• RANGE BETWEEN INTERVAL '3 days' PRECEDING AND INTERVAL '1 day' PRECEDING：限定窗口为当前行日期的前3天到前1天，排除当天数据；
• AVG(CASE WHEN boolean = 0 THEN fact ELSE NULL END)：仅对窗口内boolean=0的fact值计算平均值；
• CASE WHEN boolean = 1 THEN ... ELSE NULL：仅在boolean=1的行展示计算结果，其余行置空；
• ROUND(..., 2)：将平均值保留两位小数，匹配预期结果格式。

注意：不同SQL方言对日期区间的语法略有差异，比如MySQL中需替换为RANGE BETWEEN DATE_SUB(date, INTERVAL 3 DAY) AND DATE_SUB(date, INTERVAL 1 DAY)，上述代码适用于PostgreSQL等支持INTERVAL语法的数据库。

内容的提问来源于stack exchange，提问作者Mahesh Shankar