SQL 如何处理“累计去重用户数”在时间窗口内的计算

主流sql引擎不支持count(DISTINCT)直接用于窗口函数，因去重逻辑与窗口累积语义冲突；可用ARRAY_AGG+ARRAY_DISTINCT模拟累计去重，适用于中小规模数据。

为什么 COUNT(DISTINCT ...) 不能直接套窗口函数

直接写 select COUNT(DISTINCT user_id) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 会报错——主流 SQL 引擎（如 postgresql、mysql 8.0+、spark SQL）都不支持对 COUNT(DISTINCT) 使用窗口函数。本质是去重逻辑与窗口的累积语义冲突：窗口需要逐行维护状态，而 DISTINCT 是聚合操作，不可拆分。

用 ARRAY_AGG + ARRAY_DISTINCT 模拟累计去重（PostgreSQL / Spark SQL）

适用于中小规模数据（日活 user_id 收集为数组，再去重取长度：

SELECT   dt,   ARRAY_LENGTH(ARRAY_DISTINCT(ARRAY_AGG(user_id) OVER (ORDER BY dt)), 1) AS cumu_unique_users FROM events GROUP BY dt;

• ARRAY_AGG 必须配合 GROUP BY dt，否则同一日期多行会重复累加
• PostgreSQL 需要 ARRAY_DISTINCT（v14+），旧版可用 ARRAY(SELECT DISTINCT unnest(...)) 替代，但性能差
• Spark SQL 中对应函数是 size(array_distinct(collect_list(user_id)))，注意 collect_list 不去重，必须套 array_distinct
• 内存风险：若某天用户量极大（如千万级），ARRAY_AGG 可能 OOM

用自连接 + 子查询实现精确累计（通用兼容方案）

不依赖高级数组函数，所有 SQL 引擎都支持，但需注意性能和去重逻辑边界：

SELECT   t1.dt,   (SELECT COUNT(DISTINCT t2.user_id)    FROM events t2    WHERE t2.dt <= t1.dt) AS cumu_unique_users FROM (SELECT DISTINCT dt FROM events) t1 ORDER BY t1.dt;

• 外层 SELECT DISTINCT dt 是必须的，避免同一天多次计算
• 子查询里 t2.dt 确保“截至当天”的语义，不是“当天及之前所有分区”（注意分区字段是否严格等于 dt）
• 如果表有分区（如 Hive 分区字段 ds），务必在子查询中显式过滤分区，否则全表扫描代价爆炸
• 加复合索引 (dt, user_id) 能显著加速子查询，尤其当 dt 基数低、user_id 基数高时

用增量更新 + 状态表规避实时计算（生产环境推荐）

真正上线时几乎不会跑全量累计，而是每日追加计算新增去重用户，并维护一个累计状态表：

-- 每日执行（假设今天是 2024-06-15） INSERT INTO cumu_user_stats (dt, cumu_unique_users) SELECT   '2024-06-15',   (SELECT COALESCE(MAX(cumu_unique_users), 0) FROM cumu_user_stats WHERE dt = '2024-06-14')   + (SELECT COUNT(DISTINCT user_id) FROM events WHERE dt = '2024-06-15'      AND user_id NOT IN (SELECT user_id FROM events WHERE dt < '2024-06-15'));

• 关键点是 NOT IN 部分：必须排除历史已出现的 user_id，否则就是当日去重数，不是“净新增”
• 实际生产中建议改用 LEFT JOIN ... IS NULL 替代 NOT IN，避免空值导致整个条件为 NULL
• 这个逻辑看似简单，但 user_id 历史全集扫描仍是瓶颈，需要用布隆过滤器或 HyperLogLog 近似优化，不过那就超出纯 SQL 范畴了

真正麻烦的从来不是语法，而是“去重”本身在时间维度上的不可逆性——你没法靠一条 SQL 把昨天漏掉的用户补进前天的累计值里，所以初始化和后续校验比写法更重要。