如何按分组统计每组内标识符的去重计数

方案1：最简标准聚合写法（优先推荐） #

直接使用带DISTINCT的聚合函数实现，无需嵌套子查询，逻辑最简洁：

SELECT
  `group`,
  COUNT(DISTINCT identifier) AS distinct_id_count
FROM test
GROUP BY `group`

该写法直接按group维度分组，统计每个分组内不重复identifier的个数，完全匹配你预期的输出结构。对于数千行、上百个分组的数据规模，查询开销极低，是这类统计需求的首选实现。

方案2：基于OVER分区行号的实现（匹配你最初的思路） #

你之前考虑的窗口分区+行号判断的逻辑，核心是先标记重复值、再过滤去重，最后聚合统计，完整实现路径如下：

• 对每一行数据，按group + identifier的组合做分区，给同一组合下的行连续编号：同一identifier在同一group下重复出现时，仅第一次出现的行会被标记为序号1，其余重复行序号大于1
• 过滤掉序号大于1的重复行，此时每个group下的每个identifier仅保留1行
• 按group分组统计剩余行数，即为去重后的identifier数量

对应实现代码：

SELECT
  `group`,
  COUNT(1) AS distinct_id_count
FROM (
  SELECT
    `group`,
    identifier,
    ROW_NUMBER() OVER (
      PARTITION BY `group`, identifier
      ORDER BY identifier
    ) AS row_num
  FROM test
) dedup
WHERE row_num = 1
GROUP BY `group`

实现注意点 #

• 不能只按group单字段做窗口分区，必须同时带上identifier作为分区键，才能精准标记同组下同一id的重复行。如果只按group分区，窗口函数会给每个分组下的所有行按id排序连续编号，无法识别同一个id的重复出现，最终统计结果会出错
• 表中的group是SQL通用保留字，写SQL时需要给字段加对应数据库的转义符（比如MySQL用反引号、PostgreSQL用双引号），否则会触发语法报错
• 该写法“先去重再聚合”的逻辑，在配置了(group, identifier)联合索引的场景下，大数量级查询性能会优于直接COUNT(DISTINCT)的写法

内容的提问来源于stack exchange，提问作者qazwsx123