如何编写高效SQL查询筛选未回复医生的目标患者消息

需求条件 #

• 患者向医生发送过以Appointment Request:%开头的消息
• 医生至少向该患者回复过一条消息
• 患者在医生的最新回复之后没有发送过任何消息
• 患者是7天内注册的

现有查询问题 #

现有查询通过两个相关子查询实现了前两个条件，但这类子查询会逐行执行，效率极低；尝试添加第三个条件的子查询时，因语法错误（HAVING子句使用不当）和重复扫描表导致超时或运行失败。

现有查询代码：

select messages.id from messages
inner join patients on messages.patient_id = patients.id
inner join doctors on messages.doctor_id = doctors.id
where
( SELECT COUNT(*) from messages 
  WHERE patient_id = patients.id 
  and doctor_id = doctors.id 
  and `from` = 'patient' 
  and content LIKE 'Appointment Request:%' ) > 0
and 
( SELECT COUNT(*) from messages 
  WHERE patient_id = patients.id 
  and doctor_id = doctors.id 
  and `from` = 'doctor') > 0
and patients.created_at >= NOW() - INTERVAL 7 DAY
group by messages.patient_id;

尝试的错误子查询：

and
( 
  SELECT COUNT(*) from messages WHERE patient_id = patients.id and doctor_id = doctors.id and `from` = 'patient'
  HAVING messages.created_at > (SELECT max(created_at) WHERE patient_id = patients.id and doctor_id = doctors.id and `from` = 'doctor')
) = 0

优化后的高效查询 #

使用预聚合对话组信息的方式，只扫描一次messages表，避免重复子查询带来的性能损耗：

WITH conversation_stats AS (
    SELECT
        patient_id,
        doctor_id,
        -- 标记是否有患者发送的预约请求
        MAX(CASE WHEN `from` = 'patient' AND content LIKE 'Appointment Request:%' THEN 1 ELSE 0 END) AS has_appt_request,
        -- 标记是否有医生回复
        MAX(CASE WHEN `from` = 'doctor' THEN 1 ELSE 0 END) AS has_doctor_reply,
        -- 医生最新回复的时间
        MAX(CASE WHEN `from` = 'doctor' THEN created_at END) AS latest_doctor_reply_time,
        -- 患者在医生最新回复后发送的消息数量
        COUNT(CASE WHEN `from` = 'patient' AND created_at > MAX(CASE WHEN `from` = 'doctor' THEN created_at END) THEN 1 END) AS patient_messages_after_doctor_reply
    FROM messages
    GROUP BY patient_id, doctor_id
)
SELECT cs.patient_id, cs.doctor_id
FROM conversation_stats cs
JOIN patients p ON cs.patient_id = p.id
WHERE
    cs.has_appt_request = 1
    AND cs.has_doctor_reply = 1
    AND cs.patient_messages_after_doctor_reply = 0
    AND p.created_at >= NOW() - INTERVAL 7 DAY;

优化说明 #

1. CTE预聚合：通过conversation_stats一次性计算每个医患对话组的所有关键指标，只扫描messages表一次，大幅提升效率。
2. 避免相关子查询：相关子查询会对每一行数据重复执行查询，而预聚合只需一次计算，尤其在数据量大时性能差异明显。
3. 清晰的条件判断：用CASE表达式标记各类状态，逻辑清晰且易于维护。
4. 索引优化建议：如果messages表数据量大，建议创建联合索引(patient_id, doctor_id, from, created_at, content)，进一步提升聚合查询的速度。

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