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

ndv()是oracle 12c+内置的近似去重计数函数，基于hyperloglog算法，需配合group by使用，返回整型近似值，误差率低，性能优于count(distinct...)。

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() 是跨数据库更通用的替代方案

PostgreSQL 13+、SQL Server 2019+、BigQuery 和 Spark SQL 都支持 APPROX_COUNT_DISTINCT()，语义和用法几乎一致，但底层算法可能不同（比如 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
在小数据集（COUNT(DISTINCT)

为什么不能在同一个 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。根本原因是两套计算路径不兼容：精确去重走 sort-merge 或 hash-aggregate，而近似去重走 sketch-based 流式聚合，引擎无法合并执行计划。

若必须同时输出，拆成两个子查询再 JOIN，但注意 JOIN 键要严格一致（包括 NULL 处理）
在 Hive 中，APPROX_COUNT_DISTINCT() 实际调用的是 ndv() UDAF，但函数名不统一，容易误以为是同一能力
别指望用 APPROX_COUNT_DISTINCT() + HAVING 做强过滤——误差会导致本该进结果的组被漏掉

误差控制和验证建议

近似函数不是黑盒，你可以通过采样或小范围精确对比来评估误差是否可接受。

例如，在分组后加一层验证：

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% 值相同），sketch 类算法在这种情况下表现差。