Presto DB中计算最近3天user_id的滚动去重计数

现有表结构 #

示例数据SQL #

WITH my_table (event_date, user_id) AS (
    values ('2021-10-01', 1),
        ('2021-10-01', 2),
        ('2021-10-02', 1),
        ('2021-10-02', 2),
        ('2021-10-02', 3),
        ('2021-10-03', 3),
        ('2021-10-03', 4),
        ('2021-10-03', 4),
        ('2021-10-04', 5),
        ('2021-10-04', 2),
        ('2021-10-06', 3),
        ('2021-10-06', 5)
) 
SELECT *
FROM my_table

需求说明 #

需要计算最近3天内user_id的滚动去重计数（即每个日期统计包括当天在内的过去3天里的唯一用户数量），期望结果如下：

解决方案SQL #

WITH my_table (event_date, user_id) AS (
    values ('2021-10-01'::date, 1),
        ('2021-10-01'::date, 2),
        ('2021-10-02'::date, 1),
        ('2021-10-02'::date, 2),
        ('2021-10-02'::date, 3),
        ('2021-10-03'::date, 3),
        ('2021-10-03'::date, 4),
        ('2021-10-03'::date, 4),
        ('2021-10-04'::date, 5),
        ('2021-10-04'::date, 2),
        ('2021-10-06'::date, 3),
        ('2021-10-06'::date, 5)
),
-- 生成连续日期序列，覆盖数据起止日期
date_series AS (
    SELECT generate_series(
        (SELECT min(event_date) FROM my_table),
        (SELECT max(event_date) FROM my_table),
        '1 day'::interval
    )::date AS event_date
),
-- 去重同一用户同一天的重复记录
distinct_users AS (
    SELECT DISTINCT event_date, user_id FROM my_table
)
SELECT 
    ds.event_date,
    COUNT(DISTINCT du.user_id) AS count_distinct_user_id_last_3_days
FROM date_series ds
LEFT JOIN distinct_users du 
    ON du.event_date BETWEEN ds.event_date - INTERVAL '2 days' AND ds.event_date
GROUP BY ds.event_date
ORDER BY ds.event_date;

思路说明 #

1. 生成连续日期：用generate_series生成覆盖原数据最小到最大日期的连续日期，避免遗漏无用户活动的日期（如2021-10-05）；
2. 去重预处理：先对原表按日期和用户ID去重，确保同一用户同一天仅被计数一次；
3. 关联统计：将连续日期与去重后的用户数据关联，筛选出当前日期前2天至当天的用户，分组统计每个日期对应的去重用户数量。

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