SQL怎么在分组中统计唯一值的分布_使用NDV或APPROX_COUNT
NDV() 在 Oracle 中直接统计分组内唯一值个数
如果你在使用 Oracle 12c 或更高版本，那么恭喜你，数据库已经内置了一个处理分组去重计数的利器——NDV() 聚合函数。这个函数的核心是 HyperLogLog 算法，专门用来做近似去重计数。在处理海量数据的分组统计时，它的速度通常比传统的 COUNT(DISTINCT ...) 要快得多。
不过，新手常犯的一个错误是把它当作普通函数来调用。这里必须划个重点：NDV() 必须与 GROUP BY 子句配合使用，不能孤零零地放在 SELECT 列表里。正确的打开方式是这样的：
SELECT dept_id, NDV(emp_id) FROM emp GROUP BY dept_id;
有几点需要特别注意：首先，NDV() 返回的是整型的近似值，误差率通常能控制在很低的水平。其次，它的使用场景有一定限制：
- 它不支持
ORDER BY或窗口函数（比如OVER(PARTITION BY ...)）那样的修饰。 - 无法与
ROLLUP或CUBE这类分组扩展操作混合使用。 - 当列中存在大量
NULL值时，NDV()会默认忽略它们，这个行为和标准的COUNT(DISTINCT ...)是一致的。
APPROX_COUNT_DISTINCT() 是跨数据库更通用的替代方案
如果你的技术栈不局限于 Oracle，那么 APPROX_COUNT_DISTINCT() 这个函数名可能更眼熟。它已经成为一种跨数据库的通用方案，在 PostgreSQL 13+、SQL Server 2019+、BigQuery 以及 Spark SQL 中都有提供。虽然语义和用法大同小异，但底层实现算法可能有所不同（例如 Spark 就采用了 K-Minimum Values 算法）。
它的典型应用场景，就是替换那些慢到让人无法忍受的 COUNT(DISTINCT user_id) 查询：
SELECT country, APPROX_COUNT_DISTINCT(user_id) AS uniq_users FROM logs WHERE dt = '2024-06-01' GROUP BY country;
性能提升往往是立竿见影的。在百亿行级别的日志表上进行测试，APPROX_COUNT_DISTINCT() 通常比精确去重快上 3 到 5 倍，内存占用更是能降低一个数量级。
当然，使用时也有几个坑要避开：
- 返回值虽然是
BIGINT类型，但它本质是近似整数，切忌用它来做精确的等值判断（例如WHERE APPROX_COUNT_DISTINCT(x) = 1000）。 - 在某些计算引擎（如 Presto）中，该函数必须配合
GROUP BY使用，否则会抛出INVALID_FUNCTION_ARGUMENT这类错误。 - 对于数据量很小的场景，它的性能优势可能并不明显，甚至不如精确计数。
为什么不能在同一个 SELECT 里混用精确和近似去重
很多开发者会想当然地尝试在一个查询里同时获取精确值和近似值，比如这样写：
SELECT dept_id,
COUNT(DISTINCT emp_id), -- 精确计数
NDV(salary) -- 近似计数
FROM emp GROUP BY dept_id;
结果往往是行不通的。Oracle 会报错 ORA-30497: Argument should be a constant or a function of constants，而 Spark 则会抛出 AnalysisException: cannot resolve 'NDV' given input columns。其根本原因在于，精确去重和近似去重走的是两套完全不同的计算路径：前者通常依赖排序合并或哈希聚合，后者则基于草图算法进行流式聚合。查询引擎无法将这两套执行计划有效地合并起来。
如果业务上确实需要同时输出两种结果，该怎么办呢？一个可行的思路是拆分成两个子查询，然后再进行 JOIN。但这里要格外小心，确保连接键（包括对 NULL 值的处理方式）完全一致。
- 另外要注意的是，不同系统间的函数命名可能造成混淆。例如在 Hive 中，
APPROX_COUNT_DISTINCT()实际上调用的是名为ndv()的用户自定义聚合函数，容易让人误以为是同一个东西。 - 还有一点很重要：不要试图用
APPROX_COUNT_DISTINCT()配合HA VING子句去做强过滤。因为固有的误差可能导致本应被选中的分组被意外漏掉。
误差控制和验证建议
近似计算函数并非一个不可知的黑盒。我们可以通过一些方法来评估和控制其误差，确保结果在业务可接受的范围内。
一个常见的做法是引入采样验证。例如，可以在分组计算后，增加一层校验逻辑：
WITH approx AS ( SELECT dept_id, APPROX_COUNT_DISTINCT(emp_id) AS est FROM emp GROUP BY dept_id ), exact AS ( SELECT dept_id, COUNT(DISTINCT emp_id) AS cnt FROM emp WHERE dept_id IN (SELECT dept_id FROM approx LIMIT 10) GROUP BY dept_id ) SELECT a.dept_id, a.est, e.cnt, ROUND(ABS(a.est-e.cnt)*100.0/e.cnt, 2) AS err_pct FROM approx a JOIN exact e USING(dept_id);
观察的重点在于误差是否稳定，以及是否会随着数据量的增长而收敛。如果某个分组的误差率突然飙升到 15% 以上，那很可能意味着该组内的数据分布非常极端（例如 99% 的值都相同），而这类草图算法在此类场景下的表现往往会打折扣。
- 因此，在生产环境全面上线前，务必要对业务关心的核心分组（比如 VIP 客户、高价值区域）跑一次精确对比，做到心中有数。
- 切记，不要把
APPROX_COUNT_DISTINCT()的近似结果存入那些要求强一致性的下游业务表。 - 部分引擎（如 Trino）提供了更灵活的接口，允许你调整误差率参数，例如
APPROX_COUNT_DISTINCT(x, 0.01)。但需要明白，对精度要求越高，相应的内存和时间开销也会越大。
总而言之，在实际采用这些近似函数之前，先确认两件事：第一，你的数据库版本是否确实支持；第二，你的业务逻辑是否能容忍一定的误差。这两点如果没搞清楚，后面的所有优化努力都可能白费。
