深入解析 COUNT(DISTINCT) OVER(ORDER BY)：原理、问题与高效替代方案

目录

一、累计去重需求场景

二、COUNT(DISTINCT) OVER(ORDER BY) 语法解析 

2.1 基础语法 

2.2 执行原理

 三、三大核心问题分析

3.1 兼容性问题 

3.2 性能问题

3.3 正确性问题

四、四大高效替代方案

4.1 预聚合 + 窗口函数（推荐） 

步骤1：计算用户首次出现日期

步骤2：统计每日新增用户 

 步骤3：窗口累加计算

4.2 位图压缩（BitMap） 

ClickHouse 实现步骤

步骤1：按天聚合位图

步骤2：逐日合并位图 

4.3 HyperLogLog 近似计算

误差测试数据

4.4 递归CTE逐日累加

MySQL兼容方案： 

 五、性能对比与选型建议

5.1 详细性能测试对比

 5.2 选型决策树

​编辑

5.3 选型决策矩阵

六、总结与展望

一、累计去重需求场景

在数据分析中，累计去重计数是常见需求。例如：

• 统计每日新增用户数及历史累计独立用户数

• 计算截止当前订单日期的累计独立客户数

• 监控每天活跃设备数及历史总设备数

传统 COUNT(DISTINCT) OVER(ORDER BY)配合窗口函数的写法看似直观，但隐藏巨大性能陷阱,且不通用，某些数据库并不支持该写法。本文通过原理拆解、问题分析和替代方案对比，为不同场景提供最佳实践。

二、COUNT(DISTINCT) OVER(ORDER BY) 语法解析 

2.1 基础语法 

SELECT 
  date,
  COUNT(DISTINCT user_id) OVER(ORDER BY date) AS cumulative_users
FROM user_activity;

结果：

2.2 执行原理

1. 排序阶段：按 ORDER BY 列排序全表数据

2. 窗口滑动：逐行构建从第一行到当前行的数据窗口

3. 去重计算：对窗口内数据执行 COUNT(DISTINCT)

 三、三大核心问题分析

3.1 兼容性问题 

3.2 性能问题

测试数据：1000万行用户访问记录（100万独立用户）
表结构：

CREATE TABLE user_activity (
    date DATE,
    user_id INT
);

执行查询：

SELECT 
  date,
  COUNT(DISTINCT user_id) OVER(ORDER BY date) AS cumulative_users
FROM user_activity;

性能瓶颈：

1. 全表排序：按日期排序1000万行数据，耗时 12秒。

2. 窗口逐行计算：每行需维护一个独立的哈希表去重，内存峰值 15GB。

3. 无法并行化：单线程处理导致CPU利用率不足 30%。

3.3 正确性问题

当数据存在重复日期时，计算结果可能不符合预期：

/* 原始数据 */
2023-01-01 | A
2023-01-01 | B
2023-01-02 | A

/* 错误结果 */
2023-01-01 | 2
2023-01-01 | 2  -- 同一日期重复计算
2023-01-02 | 3

四、四大高效替代方案

4.1 预聚合 + 窗口函数（推荐） 

步骤1：计算用户首次出现日期

-- 中间表：first_occurrence
SELECT 
  user_id, 
  MIN(date) AS first_date
FROM user_activity
GROUP BY user_id;

结果示例：

步骤2：统计每日新增用户 

-- 中间表：daily_new
SELECT 
  first_date AS date,
  COUNT(*) AS new_users
FROM first_occurrence
GROUP BY first_date;

结果示例：

 步骤3：窗口累加计算

SELECT 
  date,
  SUM(new_users) OVER(ORDER BY date) AS cumulative_users
FROM daily_new;

最终结果：

完整SQL如下：

WITH first_occurrence AS (
  SELECT 
    user_id, 
    MIN(date) AS first_date
  FROM user_activity
  GROUP BY user_id
),
daily_new AS (
  SELECT 
    first_date AS date,
    COUNT(*) AS new_users
  FROM first_occurrence
  GROUP BY first_date
)
SELECT 
  date,
  SUM(new_users) OVER(ORDER BY date) AS cumulative_users
FROM daily_new;

性能提升：

• 执行时间：从58秒降至3.2秒（18倍加速）

• 内存占用：从12GB降至800MB

优势：

• 时间复杂度 O(n)

• 兼容所有支持窗口函数的数据库

4.2 位图压缩（BitMap） 

ClickHouse 实现步骤

步骤1：按天聚合位图

SELECT
  date,
  groupBitmapState(user_id) AS bitmap
FROM user_activity
GROUP BY date;

中间位图结构：

步骤2：逐日合并位图 

SELECT
  date,
  bitmapOr(bitmap) OVER(ORDER BY date) AS cumulative_bitmap
FROM daily_bitmaps;

合并过程示例：

1. 2023-01-01：位图包含用户1、2（基数2）

2. 2023-01-02：合并后包含用户1、2、3（基数3）

3. 2023-01-03：合并后包含用户1-4（基数4） 

存储优化对比：

4.3 HyperLogLog 近似计算

误差测试数据

 执行步骤：

-- 生成每日HLL草图
WITH daily_sketches AS (
  SELECT 
    date,
    hll_add_agg(hll_hash_text(user_id)) AS sketch
  FROM user_activity
  GROUP BY date
)
-- 合并累计草图
SELECT
  date,
  hll_cardinality(hll_union_agg(sketch) OVER(ORDER BY date)) AS cumulative_users
FROM daily_sketches;

4.4 递归CTE逐日累加

MySQL兼容方案： 

WITH RECURSIVE dates AS (
  SELECT MIN(date) AS date FROM user_activity
  UNION ALL
  SELECT date + INTERVAL 1 DAY
  FROM dates
  WHERE date < (SELECT MAX(date) FROM user_activity)
),
cumulative AS (
  SELECT 
    d.date,
    COUNT(DISTINCT u.user_id) AS users
  FROM dates d
  LEFT JOIN user_activity u ON u.date <= d.date
  GROUP BY d.date
)
SELECT * FROM cumulative;

适用场景：

• 小数据量（<100万行）

• 需要精确结果且数据库不支持窗口函数

 五、性能对比与选型建议

5.1 详细性能测试对比

    性能对比分析图：  

误差分析图： 

 5.2 选型决策树

5.3 选型决策矩阵

六、总结与展望

通过具体数据和中间步骤的拆解，我们可以清晰看到：

1. COUNT(DISTINCT) OVER 的性能缺陷：其时间复杂度达到O(n²)，在大数据场景完全不可用。

2. 替代方案的核心优势：

   • 预聚合窗口函数：通过预处理将复杂度降至O(n)

   • 位图压缩：利用位运算实现O(1)复杂度的集合操作

   • HLL算法：以可控误差换取百倍性能提升

未来趋势：

• 硬件加速：GPU/FPGA加速位图运算

• 算法融合：HLL+位图的混合去重方案

• 云原生集成：Snowflake等平台内置智能去重优化器

建议开发者在实际工作中：

1. 建立数据规模的监控预警机制

2. 对核心业务表预先设计去重方案

3. 定期Review去重逻辑，避免技术债累积

通过合理选择技术方案，可使千万级数据集的去重计算从小时级降至秒级，真正释放数据价值。

 往期精彩

双写+灰度发布：高并发场景下的维度表拆分零事故迁移实践

数仓建模太难？5 分钟读懂核心名词，小白也能秒上手！

宽表爆炸？动态Schema + 增量更新，轻松化解千字段扩展难题

数仓数据源字段频繁变更怎么办？一套监控方案让你高枕无忧

别再为用户流失头疼啦！掌握SQL秘籍，从零构建用户流失风险评估模型

数仓建模：如何评估数仓模型的可扩展性？

如果您觉得本文还不错，对你有帮助，那么不妨可以关注一下我的数字化建设实践之路专栏，这里的内容会更精彩。

注意：年后专栏将涨价，恢复到原价99

专栏优势：

  （1）一次收费持续更新。

 （2）实战中总结的SQL技巧，帮助SQLBOY 在SQL语言上有质的飞越，无论你应对业务难题及面试都会游刃有余【全网唯一讲SQL实战技巧，方法独特】 

SQL很简单，可你却写不好？每天一点点，收获不止一点点_sql断点-CSDN博客文章浏览阅读1.3k次，点赞54次，收藏19次。在写本文之前，我需要跟大家探讨以下几个话题。SQL进阶技巧：车辆班次问题分析SQL 进阶技巧：断点重分组应用求连续段的最后一个数及每段的个数【拼多多面试题】SQL进阶技巧-：字符串时间序列分析法应用之用户连续签到天数及历史最大连续签到天数问题【腾讯面试题】SQL进阶技巧：断点重分组算法应用之用户订单日期间隔异常问题分析SQL进阶技巧：如何对连续多条记录进行合并？【GroingIO 面试题】SQL进阶技巧：断点重分组算法应用之相邻时间间隔跳变问题分析。_sql断点https://flyingsql.blog.csdn.net/article/details/143609283

（3）实战中数仓建模技巧总结，让你认识不一样的数仓。【数据建模+业务建模，不一样的认知体系】（如果只懂数据建模而不懂业务建模，数仓体系认知是不全面的）

（4）数字化建设当中遇到难题解决思路及问题思考。

我的专栏具体链接如下：  

https://blog.csdn.net/godlovedaniel/category_12706766.html?spm=1001.2014.3001.5482https://blog.csdn.net/godlovedaniel/category_12706766.html?spm=1001.2014.3001.5482