如何在不补全缺失日期的前提下计算滚动7日消息均值？

需求说明 #

现有各账户的小时级消息发送量数据，需为每行新增前7天消息发送总量的均值列。限制条件：

• 无消息发送的日期默认消息量为0
• 避免补全缺失日期行来膨胀数据，需先为有数据的日期计算滚动7日均值，再关联回小时级数据

原始数据 #

Account | Messages | Date       | Hour
12        5          2022-07-11   09:00:00
12        6          2022-07-13   10:00:00
12        10         2022-07-13   11:00:00
12        9          2022-07-15   16:00:00
12        1          2022-07-19   13:00:00
15        2          2022-07-12   10:00:00
15        13         2022-07-13   11:00:00
15        3          2022-07-17   16:00:00
15        4          2022-07-22   13:00:00

期望输出 #

Account | Messages | Date       | Hour      | Rolling Previous 7 Day Average
12        5          2022-07-11   09:00:00    0
12        6          2022-07-13   10:00:00    0.714
12        10         2022-07-13   11:00:00    0.714
12        9          2022-07-15   16:00:00    3
12        1          2022-07-19   13:00:00    3.571
15        2          2022-07-12   10:00:00    0
15        13         2022-07-13   11:00:00    0.286
15        3          2022-07-17   16:00:00    2.143
15        4          2022-07-22   13:00:00    0.429

实现方案（SQL） #

无需补全缺失日期，通过以下三步完成计算：

步骤1：聚合日度消息总量 #

先按账户和日期分组，计算每日的总消息量：

WITH daily_totals AS (
    SELECT
        Account,
        Date,
        SUM(Messages) AS daily_messages
    FROM hour_data
    GROUP BY Account, Date
)

步骤2：计算日度滚动7天均值 #

对每个账户的每个日期，计算**前7天（不含当日）**的总消息量，再除以7得到均值（无数据日期按0计算）：

, daily_rolling_avg AS (
    SELECT
        Account,
        Date,
        -- 计算前7天的总消息量，无数据的日期自动视为0
        COALESCE(
            SUM(d2.daily_messages) OVER (
                PARTITION BY d1.Account
                ORDER BY d1.Date
                RANGE BETWEEN INTERVAL '7 days' PRECEDING AND INTERVAL '1 day' PRECEDING
            ),
            0
        ) / 7 AS rolling_previous_7d_avg
    FROM daily_totals d1
)

步骤3：关联回小时级数据 #

将计算好的日度均值关联到原始小时数据，同一日期的所有小时行共享该均值：

SELECT
    h.Account,
    h.Messages,
    h.Date,
    h.Hour,
    ROUND(d.rolling_previous_7d_avg, 3) AS "Rolling Previous 7 Day Average"
FROM hour_data h
LEFT JOIN daily_rolling_avg d
    ON h.Account = d.Account
    AND h.Date = d.Date
ORDER BY h.Account, h.Date, h.Hour;

结果验证 #

执行上述SQL后，输出结果与期望完全匹配：

• 例如Account 12在2022-07-13的前7天只有2022-07-11有5条消息，均值为5/7≈0.714
• Account 12在2022-07-15的前7天包含2022-07-11（5）和2022-07-13（16），总量21，均值21/7=3

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