获取同一用户同日致电同一客户且仅多次无响应的SQL查询

样本数据表 #

id    date        user_id    customer_id     status
 1   2022-06-23      1            12         no response
 2   2022-06-23      1            12         no response
 3   2022-06-24      1            12         no response
 4   2022-06-23      2            15         no response
 4   2022-06-23      2            15         successful
 5   2022-06-23      3            16         call later   

查询需求 #

筛选满足以下条件的记录：同一user_id在同一天致电同一customer_id，且该组合下仅存在多次no response状态，无其他任何状态。

解决方案SQL #

SELECT t.id
FROM your_table t
INNER JOIN (
    -- 先筛选出符合条件的用户-日期-客户组合
    SELECT user_id, date, customer_id
    FROM your_table
    GROUP BY user_id, date, customer_id
    HAVING 
        -- 仅存在一种状态
        COUNT(DISTINCT status) = 1 
        -- 唯一的状态是no response
        AND MAX(status) = 'no response'
        -- 呼叫次数大于1（多次呼叫）
        AND COUNT(*) > 1
) AS valid_pairs 
ON t.user_id = valid_pairs.user_id 
   AND t.date = valid_pairs.date 
   AND t.customer_id = valid_pairs.customer_id;

逻辑说明 #

1. 子查询valid_pairs通过分组聚合，筛选出满足三个条件的组合：
   • COUNT(DISTINCT status) = 1：确保该组合下所有记录的状态唯一
   • MAX(status) = 'no response'：确认这个唯一状态是「无响应」
   • COUNT(*) > 1：保证该组合下存在多次呼叫记录
2. 主查询通过关联原表，获取这些有效组合对应的所有记录ID

预期查询结果 #

id
1
2

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