PostgreSQL如何查询event=1后7天数据并按用户ID聚合运算

核心逻辑说明 #

你原方案的问题在于两点：

1. 使用date_part('day', day_ts)仅提取日期中的日数值，跨月场景会出现计算错误，且lead()函数仅按行偏移取数，无法校验时间间隔是否在7天范围内
2. 分组逻辑存在字段不匹配问题，内层分组字段和外层聚合引用字段不一致

方案1：窗口函数RANGE帧实现（性能优于自连接，推荐） #

PostgreSQL 14支持时间类型的范围窗口帧定义，可以直接在窗口函数中限定时间区间，无需自连接：

WITH user_daily_agg AS (
    -- 先按用户、日期聚合得到每日的value_x总和，避免单日多条数据重复计算
    SELECT
        user_id,
        day_ts::date AS dt,
        SUM(value_x) AS daily_value_x,
        -- 标记该日期是否有event=1的触发事件
        MAX(CASE WHEN event = 1 THEN 1 ELSE 0 END) AS has_event
    FROM table_1
    GROUP BY user_id, day_ts::date
),
event_window_calc AS (
    SELECT
        user_id,
        dt,
        daily_value_x,
        has_event,
        -- 计算当前触发事件后7天内的所有daily_value_x总和/均值，可根据需求调整聚合函数
        SUM(CASE WHEN has_event = 1 THEN daily_value_x END) OVER w AS event_7d_sum,
        AVG(CASE WHEN has_event = 1 THEN daily_value_x END) OVER w AS event_7d_avg
    FROM user_daily_agg
    WINDOW w AS (
        PARTITION BY user_id
        ORDER BY dt
        -- 窗口范围：当前行（触发事件日期）之后的0到6天，刚好覆盖7天区间
        RANGE BETWEEN CURRENT ROW AND '6 days' FOLLOWING
    )
)
-- 最终按用户维度聚合，可根据需求调整输出指标
SELECT
    user_id,
    AVG(event_7d_sum) AS avg_7d_total_after_event,
    AVG(event_7d_avg) AS avg_7d_daily_value_after_event
FROM event_window_calc
WHERE has_event = 1 -- 仅统计有触发事件的窗口
GROUP BY user_id
ORDER BY user_id;

方案2：自连接实现（逻辑更直观，适合需要自定义关联规则的场景） #

如果你更倾向使用自连接实现，可参考以下语句：

WITH user_daily_agg AS (
    SELECT
        user_id,
        day_ts::date AS dt,
        SUM(value_x) AS daily_value_x
    FROM table_1
    GROUP BY user_id, day_ts::date
),
-- 提取所有触发事件的日期
event_dates AS (
    SELECT DISTINCT
        user_id,
        day_ts::date AS event_dt
    FROM table_1
    WHERE event = 1
)
SELECT
    ed.user_id,
    -- 按需求调整聚合逻辑，以下为示例
    AVG(uda.daily_value_x) AS avg_daily_value_7d,
    SUM(uda.daily_value_x) AS total_value_7d
FROM event_dates ed
LEFT JOIN user_daily_agg uda
    ON ed.user_id = uda.user_id
    -- 限定关联的日期在事件发生后7天内，包含事件发生当天
    AND uda.dt >= ed.event_dt
    AND uda.dt < ed.event_dt + INTERVAL '7 days'
GROUP BY ed.user_id
ORDER BY ed.user_id;

样例数据适配说明 #

针对你提供的样例数据，以上两个方案会自动过滤事件发生7天外的无效数据：

• 用户50的4月2日事件会包含4月2日-4月8日的数据，4月15日事件仅包含4月15日-4月16日的数据，4月29日数据不会被纳入
• 用户55的4月17日事件会包含4月17日-4月23日的数据，4月29日、4月30日数据不会被纳入

如果需要调整是否包含事件发生当日、是否需要对重叠窗口的重复数据去重，可直接修改关联条件或窗口帧范围即可。

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