如何用SQL查询首次就诊后7天内有第二次就诊的患者

• 第一步：将TEXT类型的date_visit字段转换为日期格式，才能正确计算日期间隔
• 第二步：用窗口函数给每个患者的就诊记录按就诊日期升序排序，标记出首次、第二次就诊记录
• 第三步：关联同一个患者的首次和第二次就诊记录，计算两次就诊的间隔天数
• 第四步：筛选出间隔小于7天的患者ID

SELECT DISTINCT t1.patient_id
FROM (
    -- 给每个患者的就诊记录排序，取首次就诊
    SELECT 
        patient_id,
        STR_TO_DATE(date_visit, '%m/%d/%Y') AS visit_date,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY STR_TO_DATE(date_visit, '%m/%d/%Y')) AS rn
    FROM TestData
) t1
INNER JOIN (
    -- 给每个患者的就诊记录排序，取第二次就诊
    SELECT 
        patient_id,
        STR_TO_DATE(date_visit, '%m/%d/%Y') AS visit_date,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY STR_TO_DATE(date_visit, '%m/%d/%Y')) AS rn
    FROM TestData
) t2 ON t1.patient_id = t2.patient_id
WHERE t1.rn = 1 
  AND t2.rn = 2
  AND DATEDIFF(t2.visit_date, t1.visit_date) < 7;

• PostgreSQL：将日期转换函数替换为TO_DATE(date_visit, 'MM/DD/YYYY')，日期间隔计算改为`t2.visit_date - t1.visit_date < 7
• SQL Server：日期转换函数替换为CONVERT(DATE, date_visit, 101)，日期间隔计算改为DATEDIFF(day, t1.visit_date, t2.visit_date) < 7

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