SQL如何统计各日期下拥有多个唯一value的ID数量

你写的SQL无法运行和得到预期结果有两个核心问题：

• SQL语法不支持在同一层查询中嵌套使用聚合函数，你在SUM内部调用COUNT(DISTINCT value)的写法本身不符合语法规范
• 你的分组粒度是date + id，输出的是每个日期下每个ID的统计结果，无法直接得到按日期聚合的符合条件ID总数

你可以通过两层聚合实现需求：先统计每个日期下每个ID的唯一value数量，再按日期分组统计符合条件的ID总数，代码如下：

SELECT 
  date,
  SUM(CASE WHEN distinct_value_cnt > 1 THEN 1 ELSE 0 END) AS qualified_id_num
FROM (
  -- 内层先计算每个日期+ID对应的唯一value数量
  SELECT 
    date,
    id,
    COUNT(DISTINCT value) AS distinct_value_cnt
  FROM your_table_name
  GROUP BY date, id
) t
GROUP BY date

如果你的数据库支持COUNT_IF简化函数，外层统计可以直接写为COUNT_IF(distinct_value_cnt > 1) AS qualified_id_num。

如果需要匹配你给出的示例输出（仅ID1符合要求），说明你额外需要判断ID下存在重复的value记录，内层统计可以调整为：

SELECT 
  date,
  id,
  COUNT(DISTINCT value) AS distinct_value_cnt,
  COUNT(*) AS total_value_cnt
FROM your_table_name
GROUP BY date, id

外层判断条件改为distinct_value_cnt > 1 AND total_value_cnt > distinct_value_cnt即可。

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