SQL 中如何处理“累计去重计数”distinct count over window

DISTINCT count OVER winDOW 是指在窗口内对某列去重后计数，但sql标准不支持COUNT(DISTINCT col) OVER(…)，因聚合函数与窗口函数语义冲突；postgresql常用array_agg+unnest+DISTINCT+cardinality模拟，mysql 8+则缺乏高效原生方案。

什么是 DISTINCT COUNT OVER WINDOW？

SQL 标准不支持直接写 COUNT(DISTINCT col) OVER (ORDER BY ...)，几乎所有主流数据库（PostgreSQL、MySQL 8+、SQL Server、oracle）都会报错，比如 PostgreSQL 报 Error: aggregate function calls cannot be nested，因为 COUNT(DISTINCT ...) 本身是聚合函数，而 OVER 要求的是窗口函数 —— 二者语义冲突。

PostgreSQL 中用 array_agg + cardinality 模拟

利用数组累积去重再算长度，是 PostgreSQL 最常用且可读性尚可的方案。注意：必须配合 DISTINCT 和 ORDER BY 避免重复累积，且性能随窗口变大明显下降。

示例（按时间顺序累计统计用户去重数）：

select   event_time,   user_id,   cardinality(ARRAY(SELECT DISTINCT x FROM unnest(array_agg(user_id) OVER (ORDER BY event_time)) AS x)) AS cum_distinct_users FROM events;

• array_agg(user_id) OVER (ORDER BY event_time) 累积生成用户 ID 数组（含重复）
• unnest(...) 展开后用 SELECT DISTINCT x 去重，再重新聚合成新数组
• cardinality(...) 返回数组长度 —— 即当前窗口内去重后的用户数
• ⚠️ 缺点：窗口越大，unnest+DISTINCT 开销越高；无法处理 NULL（需提前 WHERE user_id IS NOT NULL 或用 COALESCE）

MySQL 8+ 用 jsON_AGG + 自定义去重逻辑（不推荐）

MySQL 没有原生数组类型，json_AGG 可替代，但去重需靠子查询或变量模拟，极易出错且不可靠。更现实的做法是：放弃纯 SQL，改用应用层累计或临时表预计算。

如果坚持尝试（仅限小数据量验证）：

SELECT   event_time,   user_id,   (SELECT COUNT(DISTINCT t2.user_id)    FROM events t2    WHERE t2.event_time <= t1.event_time) AS cum_distinct_users FROM events t1;

• 这是典型的“相关子查询”，逻辑清晰但复杂度 O(n²)，万级数据就明显卡顿
• 必须确保 event_time 有索引，否则全表扫描叠加嵌套，性能崩塌
• MySQL 不支持 array_agg 或 string_agg 的去重变体，别指望用 GROUP_CONCAT(DISTINCT ...) 再解析 —— 长度限制和字符集问题会反噬

真正可行的工程解法：物化中间状态

累计去重本质是状态依赖型计算，SQL 不是它的天然主场。生产环境应避免实时计算，优先考虑：

• 用每日/每小时任务跑一次 SELECT date, COUNT(DISTINCT user_id) FROM events WHERE dt ，结果存入汇总表
• 在应用层（Python/Java）读取有序事件流，用 set 或 HyperLogLog 结构增量更新计数，写回缓存或宽表
• clickhouse 用户可直接用 uniqState / uniqMerge 实现近似去重窗口（牺牲少量精度换性能）
• ⚠️ 最容易被忽略的一点：业务是否真需要“任意时间点”的精确累计？很多时候“截至昨日”“截至整点”已足够，那就根本不需要窗口函数