SQL实现：基于患者分组计算同一列的就诊日期差值

问题说明 #

需要计算同一患者多次就诊的日期间隔天数，最终剔除同一患者两次就诊间隔小于7天的记录，核心是借助窗口函数高效实现日期差值计算。

原始数据 #

Patient          Consult Date
A             2022-07-14 08:41:59
B             2022-07-01 11:23:59
B             2022-07-01 12:34:15
B             2022-01-04 09:25:14
B             2021-10-30 10:45:56
C             2022-07-24 14:55:43
C             2022-03-14 10:11:46
C             2022-03-14 09:35:22

期望间隔计算结果 #

Patient           Consult Date            Difference(Days)
A             2022-07-14 08:41:59          N/A
B             2022-07-01 11:23:59          0
B             2022-07-01 12:34:15          183
B             2022-01-04 09:25:14          64
B             2021-10-30 10:45:56          N/A
C             2022-07-24 14:55:43          130
C             2022-03-14 10:11:46          0
C             2022-03-14 09:35:22          N/A

实现方案 #

1. 计算就诊日期间隔天数 #

使用PARTITION BY按患者分组，ORDER BY将就诊日期降序排列，通过LEAD()函数获取下一条更早的就诊记录日期，再用日期差函数计算间隔天数。

PostgreSQL 实现

WITH patient_consults AS (
    SELECT 
        Patient,
        "Consult Date" AS consult_date,
        LEAD("Consult Date") OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC) AS next_consult_date
    FROM your_table_name
)
SELECT 
    Patient,
    consult_date,
    CASE 
        WHEN next_consult_date IS NULL THEN 'N/A'
        ELSE CAST(EXTRACT(DAY FROM (consult_date - next_consult_date)) AS INTEGER)
    END AS "Difference(Days)"
FROM patient_consults
ORDER BY Patient, consult_date DESC;

MySQL 8.0+ 实现

WITH patient_consults AS (
    SELECT 
        Patient,
        `Consult Date` AS consult_date,
        LEAD(`Consult Date`) OVER (PARTITION BY Patient ORDER BY `Consult Date` DESC) AS next_consult_date
    FROM your_table_name
)
SELECT 
    Patient,
    consult_date,
    CASE 
        WHEN next_consult_date IS NULL THEN 'N/A'
        ELSE DATEDIFF(consult_date, next_consult_date)
    END AS `Difference(Days)`
FROM patient_consults
ORDER BY Patient, consult_date DESC;

2. 剔除间隔小于7天的记录 #

在间隔计算基础上，筛选出间隔≥7天的记录；若需对连续间隔小于7天的记录仅保留最新一条，可结合ROW_NUMBER()实现。

基础过滤（保留间隔≥7天及最后一条记录）

WITH patient_consults AS (
    SELECT 
        Patient,
        "Consult Date" AS consult_date,
        LEAD("Consult Date") OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC) AS next_consult_date,
        EXTRACT(DAY FROM ("Consult Date" - LEAD("Consult Date") OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC))) AS diff_days
    FROM your_table_name
),
filtered_consults AS (
    SELECT 
        *,
        CASE 
            WHEN next_consult_date IS NULL THEN TRUE
            WHEN diff_days >=7 THEN TRUE
            ELSE FALSE
        END AS keep_record
    FROM patient_consults
)
SELECT 
    Patient,
    consult_date,
    CASE 
        WHEN next_consult_date IS NULL THEN 'N/A'
        ELSE CAST(diff_days AS INTEGER)
    END AS "Difference(Days)"
FROM filtered_consults
WHERE keep_record
ORDER BY Patient, consult_date DESC;

分组保留最新记录（处理连续间隔小于7天的情况）

WITH patient_consults AS (
    SELECT 
        Patient,
        "Consult Date" AS consult_date,
        LEAD("Consult Date") OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC) AS next_consult_date,
        EXTRACT(DAY FROM ("Consult Date" - LEAD("Consult Date") OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC))) AS diff_days,
        SUM(CASE WHEN diff_days >=7 OR diff_days IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY Patient ORDER BY "Consult Date" DESC) AS group_id
    FROM your_table_name
),
ranked_consults AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Patient, group_id ORDER BY consult_date DESC) AS rn
    FROM patient_consults
)
SELECT 
    Patient,
    consult_date,
    CASE 
        WHEN next_consult_date IS NULL THEN 'N/A'
        ELSE CAST(diff_days AS INTEGER)
    END AS "Difference(Days)"
FROM ranked_consults
WHERE rn = 1
ORDER BY Patient, consult_date DESC;

关键逻辑说明 #

• PARTITION BY Patient：确保仅在同一患者的就诊记录范围内计算间隔。
• ORDER BY "Consult Date" DESC：按就诊日期从新到旧排序，LEAD()可精准获取当前记录的下一条更早就诊日期。
• 日期差函数：PostgreSQL用EXTRACT(DAY FROM 日期差)，MySQL用DATEDIFF()，高效计算天数间隔。
• 过滤逻辑：通过标记或分组排序，剔除冗余的短间隔记录，保留有效就诊数据。

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