SQL需求：统计每日唯一ID数量及当日重复记录总数

原SQL中的COUNT(*)统计的是当日总记录数，并非你需要的重复记录总数，这是导致结果不符合预期的核心原因。

通过嵌套子查询先统计每个id在每日的出现次数，再基于该结果计算目标指标：

1. 当日唯一id数量：直接用count(distinct id)统计当日不同id的个数。
2. 当日重复记录总数：对出现次数大于1的id，累加其出现次数（完全匹配你给出的样本期望结果）。

完整SQL语句 #

SELECT
    day_date AS date,
    COUNT(DISTINCT id) AS "distinct id",
    SUM(CASE WHEN cnt > 1 THEN cnt ELSE 0 END) AS "no. of duplicates on that day"
FROM (
    -- 子查询：统计每个id在每日的出现次数
    SELECT
        TRUNC(date) AS day_date,
        id,
        COUNT(*) AS cnt
    FROM abc
    -- 如需仅统计当天，保留以下WHERE条件；统计所有日期则删除
    -- WHERE TRUNC(date) = TRUNC(sysdate)
    GROUP BY TRUNC(date), id
) t
GROUP BY day_date;

样本数据验证 #

针对你提供的样本数据，子查询会输出：

外层分组后得到最终结果：

完全匹配你的期望输出。

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