SQL技术问询：统计年度就诊总次数并获取最新就诊Payor

需求背景 #

需筛选系统中首次就诊超1年、2023年就诊次数≥2次的患者，当前核心逻辑是：

• 统计患者2023年度总就诊次数
• 关联该患者最新一次就诊的保险支付方（Payor）

原查询问题 #

用户编写的SQL如下：

select
      tab.PrimaryMrn
      ,sum(tab.Appt_Count) ct
      ,tab.Payor
from
    (
    select
        PatientDim.PrimaryMrn
        ,VisitFact.AppointmentDateKey
        ,(CASE  
            WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
            WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
            ELSE 'Other'
            END) as 'Payor'
        ,count(distinct VisitFact.EncounterKey) Appt_Count
        ,row_number() OVER (PARTITION BY PatientDim.PrimaryMrn ORDER BY VisitFact.AppointmentDateKey DESC) AS rn
    from VisitFact
    INNER JOIN PatientDim
        on VisitFact.PatientDurableKey = PatientDim.DurableKey and PatientDim.IsCurrent=1 
    INNER JOIN CoverageDim
        ON VisitFact.CoverageKey = CoverageDim.CoverageKey
    where VisitFact.AppointmentDateKey between '20230101' and '20231231'
    group by PatientDim.PrimaryMrn ,VisitFact.AppointmentDateKey ,CoverageDim.PayorFinancialClass
    ) tab
group by PrimaryMrn, tab.Payor
order by PrimaryMrn

内查询结果符合预期，但外层因按PrimaryMrn和Payor分组，导致就诊次数被拆分（如患者1234567的次数被拆分为Medicaid 1次、Private 2次），无法得到总次数+最新Payor的预期结果。

用户尝试的两种调整均失效：

• 筛选rn=1：丢失其他就诊记录，无法统计总次数
• 先求和再取最大Payor：因字母顺序问题，错误返回Private而非最新的Medicaid

优化方案 #

方案一：CTE拆分逻辑（清晰易维护） #

用两个CTE分别处理「总就诊次数统计」和「最新Payor获取」，最后关联得到结果：

WITH patient_yearly_stats AS (
    -- 统计2023年每个患者的总就诊次数，同时过滤就诊≥2次的患者
    SELECT
        pd.PrimaryMrn,
        COUNT(DISTINCT vf.EncounterKey) AS total_appt_count
    FROM VisitFact vf
    JOIN PatientDim pd 
        ON vf.PatientDurableKey = pd.DurableKey 
        AND pd.IsCurrent = 1
    WHERE vf.AppointmentDateKey BETWEEN '20230101' AND '20231231'
    GROUP BY pd.PrimaryMrn
    HAVING COUNT(DISTINCT vf.EncounterKey) >= 2
),
latest_payor AS (
    -- 获取每个患者2023年最新就诊的Payor
    SELECT
        pd.PrimaryMrn,
        CASE  
            WHEN cd.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
            WHEN cd.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
            ELSE 'Other'
        END AS Payor,
        ROW_NUMBER() OVER (PARTITION BY pd.PrimaryMrn ORDER BY vf.AppointmentDateKey DESC) AS rn
    FROM VisitFact vf
    JOIN PatientDim pd 
        ON vf.PatientDurableKey = pd.DurableKey 
        AND pd.IsCurrent = 1
    JOIN CoverageDim cd 
        ON vf.CoverageKey = cd.CoverageKey
    WHERE vf.AppointmentDateKey BETWEEN '20230101' AND '20231231'
)
-- 关联两个CTE，得到总次数+最新Payor
SELECT
    p.PrimaryMrn,
    l.Payor,
    p.total_appt_count AS Appt_Count
FROM patient_yearly_stats p
JOIN latest_payor l 
    ON p.PrimaryMrn = l.PrimaryMrn 
    AND l.rn = 1
ORDER BY p.PrimaryMrn;

方案二：单窗口函数实现（更简洁） #

在内层用窗口函数同时计算总次数和标记最新记录，外层筛选即可：

SELECT
    PrimaryMrn,
    Payor,
    total_appt_count AS Appt_Count
FROM (
    SELECT
        pd.PrimaryMrn,
        CASE  
            WHEN cd.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
            WHEN cd.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
            ELSE 'Other'
        END AS Payor,
        -- 窗口函数计算当前患者的总就诊次数
        COUNT(DISTINCT vf.EncounterKey) OVER (PARTITION BY pd.PrimaryMrn) AS total_appt_count,
        -- 标记最新就诊记录
        ROW_NUMBER() OVER (PARTITION BY pd.PrimaryMrn ORDER BY vf.AppointmentDateKey DESC) AS rn
    FROM VisitFact vf
    JOIN PatientDim pd 
        ON vf.PatientDurableKey = pd.DurableKey 
        AND pd.IsCurrent = 1
    JOIN CoverageDim cd 
        ON vf.CoverageKey = cd.CoverageKey
    WHERE vf.AppointmentDateKey BETWEEN '20230101' AND '20231231'
) t
-- 筛选最新记录，且总次数≥2
WHERE rn = 1 AND total_appt_count >= 2
ORDER BY PrimaryMrn;

方案说明 #

两种方案都避免了按Payor分组的问题：

• 方案一将统计和取最新值拆分，逻辑清晰，适合复杂场景扩展
• 方案二用窗口函数一次完成计算，代码更简洁，执行效率较高

最终均可得到预期结果：每个患者一行，包含2023年总就诊次数和最新就诊的Payor值。

内容的提问来源于stack exchange，提问作者Urr-kuh