如何统计每个case_id最新记录中SIQdisp1_a=14的每日数量？

你的核心需求是去重同一个case_id的多次呼叫，仅保留最新的那条记录，再判断其结果是否为14，最后按日期统计符合条件的数量。原SQL的问题在于没有先筛选每个case_id的最新记录，直接统计了所有SIQdisp1_a=14的条目，导致同一个case_id的多次呼叫被重复计数。

原SQL语句 #

SELECT DATE(last_called), COUNT(SIQdisp1_a), Access.batch_number 
FROM disposition_log 
LEFT JOIN Access ON disposition_log.case_id = Access.identifier 
WHERE SIQdisp1_a = 14 AND case_id NOT LIKE 'test%' AND batch_number = 171205 
GROUP BY DATE(last_called);

示例数据 #

期望结果 #

方案1：用窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等现代数据库） #

窗口函数ROW_NUMBER()可以给每个case_id的记录按呼叫时间倒序编号，最新的记录编号为1。我们先筛选出编号为1的记录，再统计其中SIQdisp1_a=14的条目：

WITH latest_calls AS (
    SELECT 
        d.case_id,
        DATE(d.last_called) AS call_date,
        d.SIQdisp1_a,
        a.batch_number,
        -- 按case_id分组，每组内按last_called倒序排序，最新记录的rn=1
        ROW_NUMBER() OVER (PARTITION BY d.case_id ORDER BY d.last_called DESC) AS rn
    FROM disposition_log d
    LEFT JOIN Access a ON d.case_id = a.identifier
    WHERE d.case_id NOT LIKE 'test%' AND a.batch_number = 171205
)
SELECT 
    call_date,
    COUNT(*) AS valid_count
FROM latest_calls
WHERE rn = 1 AND SIQdisp1_a = 14
GROUP BY call_date
ORDER BY call_date;

方案2：用子查询（兼容老版本数据库，比如MySQL 5.x） #

如果你的数据库不支持CTE（公共表表达式），可以用子查询筛选每个case_id的最新呼叫时间，再匹配对应的记录：

SELECT 
    DATE(d.last_called) AS call_date,
    COUNT(*) AS valid_count
FROM disposition_log d
LEFT JOIN Access a ON d.case_id = a.identifier
WHERE 
    d.case_id NOT LIKE 'test%' 
    AND a.batch_number = 171205
    -- 确保当前记录是该case_id的最新呼叫
    AND d.last_called = (
        SELECT MAX(last_called) 
        FROM disposition_log 
        WHERE case_id = d.case_id
    )
    AND d.SIQdisp1_a = 14
GROUP BY call_date
ORDER BY call_date;

为什么这两种方法有效？ #

两种方法都先锁定每个case_id的唯一最新记录，而非直接统计所有SIQdisp1_a=14的条目：

• 对于case_id=1003018，最新记录的SIQdisp1_a是13，因此不会被计入统计
• 对于case_id=1002175，最新记录的SIQdisp1_a是14，仅被计数1次
  完全符合你的期望结果。

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