基于Hive SQL Partition By按日期查询符合转诊顺序的患者

原查询的问题分析 #

• 存在语法错误：GROUP BY r.person_id 后，SELECT中包含未聚合的row_number()窗口函数（该函数依赖referral_written_dt_tm字段），多数SQL引擎会直接报错，这是无结果的核心原因之一。
• 仅筛选了同时存在两类转诊的患者，但未验证Neurology转诊时间早于Medical Genetics的顺序要求。

方案一：条件聚合（简洁高效） #

利用条件聚合分别提取两类转诊的最早时间，直接比较顺序：

SELECT person_id
FROM dataproduct.referrals
WHERE medical_service IN ('Neurology', 'Medical Genetics')
GROUP BY person_id
-- 确保患者的最早神经科转诊时间早于最早遗传学转诊时间
HAVING MIN(CASE WHEN medical_service = 'Neurology' THEN referral_written_dt_tm END) 
       < MIN(CASE WHEN medical_service = 'Medical Genetics' THEN referral_written_dt_tm END)

逻辑说明 #

1. 先过滤出仅包含目标两类转诊的记录；
2. 按person_id分组后，用CASE语句分别提取对应服务的转诊时间，取MIN()得到该患者该类转诊的最早时间；
3. 通过HAVING子句判断神经科转诊的最早时间是否早于遗传学转诊的最早时间，满足条件的即为目标患者。

方案二：窗口函数+自连接（适用于需查看详细转诊记录的场景） #

通过窗口函数给每个患者的转诊记录按时间排序，再通过自连接匹配顺序符合要求的记录：

WITH ranked_referrals AS (
    SELECT 
        person_id,
        medical_service,
        referral_written_dt_tm,
        -- 按患者分组，转诊时间升序生成行号
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY referral_written_dt_tm ASC) AS rn
    FROM dataproduct.referrals
    WHERE medical_service IN ('Neurology', 'Medical Genetics')
)
SELECT DISTINCT r1.person_id
FROM ranked_referrals r1
JOIN ranked_referrals r2 
    ON r1.person_id = r2.person_id
    AND r1.medical_service = 'Neurology'
    AND r2.medical_service = 'Medical Genetics'
    AND r1.rn < r2.rn -- 神经科转诊的行号更小，说明时间更早

逻辑说明 #

1. 用CTE生成每个患者两类转诊记录的时间排序行号；
2. 自连接同一患者的两类转诊记录，筛选出神经科转诊行号小于遗传学转诊行号的记录；
3. 去重后得到符合顺序要求的患者ID列表。

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