窗口函数不支持distinct时多时间窗口去重user_id统计方案

实现方案 #

方案1：标准SQL通用精确统计方案（兼容所有SQL引擎） #

通过预计算每日去重用户 + 日期自关联的方式实现精确去重统计，规避窗口函数不支持distinct的限制，同时修复原写法用rows between行数偏移导致的缺失日期统计错误问题：

WITH daily_unique_users AS (
    -- 预计算每天的去重用户，大幅降低后续计算数据量
    SELECT
        date(to_timestamp(event_time/1000) AT TIME ZONE 'Europe/Berlin') AS dt,
        user_id
    FROM event_entity
    WHERE type = 'REFRESH_TOKEN'
    GROUP BY 1, 2
),
all_stat_dates AS (
    -- 提取所有需要统计的日期，避免无事件日期被遗漏
    SELECT DISTINCT dt FROM daily_unique_users
)
SELECT
    ad.dt AS "Date",
    -- 过去365天（含当日）去重用户数
    COUNT(DISTINCT CASE WHEN duu.dt >= ad.dt - INTERVAL '365 days' THEN duu.user_id END) AS "Yearly Active",
    -- 过去30天（含当日）去重用户数
    COUNT(DISTINCT CASE WHEN duu.dt >= ad.dt - INTERVAL '30 days' THEN duu.user_id END) AS "Monthly Active",
    -- 过去7天（含当日）去重用户数
    COUNT(DISTINCT CASE WHEN duu.dt >= ad.dt - INTERVAL '7 days' THEN duu.user_id END) AS "Weekly Active",
    -- 当日去重用户数
    COUNT(DISTINCT CASE WHEN duu.dt = ad.dt THEN duu.user_id END) AS "Daily Active"
FROM all_stat_dates ad
LEFT JOIN daily_unique_users duu
  ON duu.dt BETWEEN ad.dt - INTERVAL '365 days' AND ad.dt
GROUP BY ad.dt
ORDER BY ad.dt;

注：不同数据库的时间间隔语法略有差异，例如MySQL写法为ad.dt - INTERVAL 365 DAY，可根据实际使用的引擎调整。

方案2：大数据量场景近似统计方案（适配Presto/Spark SQL等大数据引擎） #

如果可接受可控范围的误差（默认误差率2.3%），可以用大数据引擎内置的近似去重窗口函数，性能远高于精确统计方案：

WITH daily_unique_users AS (
    SELECT
        date(to_timestamp(event_time/1000) AT TIME ZONE 'Europe/Berlin') AS dt,
        user_id
    FROM event_entity
    WHERE type = 'REFRESH_TOKEN'
    GROUP BY 1, 2
)
SELECT
    DISTINCT dt AS "Date",
    approx_distinct(user_id) OVER (ORDER BY dt RANGE BETWEEN INTERVAL '365' days PRECEDING AND CURRENT ROW) AS "Yearly Active",
    approx_distinct(user_id) OVER (ORDER BY dt RANGE BETWEEN INTERVAL '30' days PRECEDING AND CURRENT ROW) AS "Monthly Active",
    approx_distinct(user_id) OVER (ORDER BY dt RANGE BETWEEN INTERVAL '7' days PRECEDING AND CURRENT ROW) AS "Weekly Active",
    count(user_id) OVER (PARTITION BY dt) AS "Daily Active"
FROM daily_unique_users
ORDER BY dt;

优化说明 #

• 原写法使用rows between按行数偏移，若存在无事件的空白日期，会导致统计的时间窗口范围大于实际要求，改用时间范围匹配的逻辑可避免该问题。
• 预计算每日去重用户后，数据量会远小于原始事件表，大幅降低后续关联、聚合的计算开销。

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