Oracle SQL动态对比当前行与历史行，定义就诊类型

要实现你描述的就诊类型判断逻辑——主就诊（Primary Visit）指当前就诊发生在所有历史就诊结束日期+90天之后；否则为复诊——可以通过两种高效的Oracle SQL方案实现，无需手动对比前N行数据：

方案1：使用窗口函数（推荐，适合大数据量） #

利用窗口函数计算当前就诊之前所有就诊的「结束日期+90天」的最大值，通过对比当前就诊的开始日期与该最大值，直接判断就诊类型：

SELECT
    transaction_id,
    patient_id,
    start_date,
    end_date,
    CASE
        -- 第一个就诊无历史记录，直接判定为主就诊
        WHEN start_date > COALESCE(
            MAX(end_date + INTERVAL '90' DAY) OVER (
                PARTITION BY patient_id 
                ORDER BY start_date 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 
            DATE '-4712-01-01' -- Oracle最小日期，确保第一个就诊满足条件
        ) THEN 'Primary Visit'
        ELSE 'Follow-up Visit'
    END AS visit_type
FROM visits
ORDER BY patient_id, start_date;

逻辑说明： #

• PARTITION BY patient_id：按患者分组，确保只对比同一患者的历史就诊
• MAX(end_date + INTERVAL '90' DAY) OVER (...)：计算当前就诊之前所有就诊的「结束日期+90天」的最大值
• 如果当前就诊的开始日期大于该最大值，说明它晚于所有历史就诊的结束+90天，判定为主就诊；否则为复诊

方案2：使用EXISTS子查询（逻辑直观，适合小数据量） #

通过子查询直接检查是否存在任意一条历史就诊，其结束日期+90天覆盖当前就诊的开始日期：

SELECT
    v1.transaction_id,
    v1.patient_id,
    v1.start_date,
    v1.end_date,
    CASE
        WHEN NOT EXISTS (
            SELECT 1
            FROM visits v2
            WHERE v2.patient_id = v1.patient_id
              AND v2.start_date < v1.start_date
              AND v2.end_date + INTERVAL '90' DAY >= v1.start_date
        ) THEN 'Primary Visit'
        ELSE 'Follow-up Visit'
    END AS visit_type
FROM visits v1
ORDER BY v1.patient_id, v1.start_date;

逻辑说明： #

• 子查询会查找同一患者中，开始日期早于当前就诊，且结束日期+90天 >= 当前就诊开始日期的记录
• 如果不存在这样的记录，说明当前就诊晚于所有历史就诊的结束+90天，判定为主就诊；否则为复诊

示例验证 #

针对你提到的场景：

• 交易ID3的开始日期在交易ID1结束日期+90天范围内，会被判定为Follow-up Visit
• 交易ID4的开始日期超出所有历史就诊结束日期+90天，会被判定为Primary Visit

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