PostgreSQL如何比对当前行与下一行不同列值判断30天内是否有后续就诊

核心使用LEAD窗口函数获取同用户下一行的就诊开始时间，再判断日期间隔即可，你表已经提前按user和encounter_start排序，直接复用排序逻辑即可。

标准SQL写法（兼容Spark SQL、Hive、PostgreSQL等） #

SELECT 
    `user`,
    encounter_start,
    encounter_stop,
    CASE 
        WHEN next_encounter_start IS NULL THEN 0
        WHEN DATEDIFF(next_encounter_start, encounter_stop) <= 30 THEN 1
        ELSE 0
    END AS subsequent_encounter_within_30_days
FROM (
    SELECT 
        *,
        LEAD(encounter_start) OVER (PARTITION BY `user` ORDER BY encounter_start) AS next_encounter_start
    FROM user_dates
) t

MySQL适配写法（如果日期字段为字符串格式） #

如果你的encounter_start和encounter_stop是字符串类型存储，需要先转成日期类型再计算差值：

SELECT 
    `user`,
    encounter_start,
    encounter_stop,
    CASE 
        WHEN next_encounter_start IS NULL THEN 0
        WHEN DATEDIFF(STR_TO_DATE(next_encounter_start, '%m-%d-%Y'), STR_TO_DATE(encounter_stop, '%m-%d-%Y')) <= 30 THEN 1
        ELSE 0
    END AS subsequent_encounter_within_30_days
FROM (
    SELECT 
        *,
        LEAD(encounter_start) OVER (PARTITION BY `user` ORDER BY STR_TO_DATE(encounter_start, '%m-%d-%Y')) AS next_encounter_start
    FROM user_dates
) t

逻辑说明 #

• 内层查询通过LEAD()窗口函数，按user分组后按就诊开始时间排序，取每条记录对应下一次就诊的开始时间
• 外层通过CASE分支判断：如果没有下一次就诊（即对应用户最后一条就诊记录）直接标记为0；如果本次就诊结束时间和下一次就诊开始时间间隔小于等于30天标记为1，否则标记为0

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