SQL怎么在分组中统计唯一值的分布_使用NDV或APPROX_COUNT

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)。但需要明白，对精度要求越高，相应的内存和时间开销也会越大。

总而言之，在实际采用这些近似函数之前，先确认两件事：第一，你的数据库版本是否确实支持；第二，你的业务逻辑是否能容忍一定的误差。这两点如果没搞清楚，后面的所有优化努力都可能白费。