如何通过CTE筛选特定ICD-10编码的首次诊断患者

要筛选出首次诊断目标ICD-10编码的患者，我们可以在diag_final中使用窗口函数为每个患者的诊断记录按时间排序，保留排序为1的记录（即最早的诊断）。以下是完善后的完整SQL代码：

with diag as (
select distinct d.person_id
, d.encntr_id
, d.diagnosis_id
, d.nomenclature_id
, d.diag_dt_tm
, d.diag_prsnl_name
, d.diagnosis_display
, cv.display as Source_Vocabulary 
, nm.source_identifier as ICD_10_Code
, nm.source_string as ICD_10_Diagnosis
, nm.concept_cki 
from wny_prod.diagnosis d 
inner join (select * from nomenclature
        where source_vocabulary_cd in (151782752, 151782747) --ICD 10 CM and PCS
        and (source_identifier ilike 'F10.10' or source_identifier ilike 'F19.10')) nm on d.nomenclature_id = nm.nomenclature_id
left join code_value cv on nm.source_vocabulary_cd = cv.code_value 
),

diag_final as (
select *
from (
    select *,
           -- 按患者分组，诊断时间升序排序，首次诊断标记为1
           row_number() over (partition by person_id order by diag_dt_tm asc) as diag_rank
    from diag
) ranked_diag
where diag_rank = 1 -- 仅保留首次诊断记录
)

select distinct ea.FIN
, dem.patient_name
, dem.dob
, age_in_years(e.beg_effective_dt_tm::date, dem.dob) as Age_at_Encounter
, amb.location_name
, e.beg_effective_dt_tm
, df.Source_Vocabulary 
, df.ICD_10_Code
, df.ICD_10_Diagnosis
from (select * from encounter where year(beg_effective_dt_tm) = 2022) e 
left join (select distinct encntr_id, alias as FIN from encntr_alias where encntr_alias_type_cd = 844) ea on e.encntr_id = ea.encntr_id
inner join diag_final df on e.encntr_id = df.encntr_id -- 替换为diag_final
inner join (select distinct * from DEMOGRAPHICS where mrn_rownum = 1 and phone_rownum = 1 and address_rownum = 1) dem on e.person_id = dem.person_id
inner join locations_amb amb on amb.loc_facility_cd = e.loc_facility_cd
where age_in_years(e.beg_effective_dt_tm::date, dem.dob) > 13 

关键逻辑说明 #

• 在diag_final中，row_number() over (partition by person_id order by diag_dt_tm asc)实现了按患者分组、组内按诊断时间升序排序的功能，首次诊断会被标记为1
• 通过where diag_rank = 1筛选出每组中最早的诊断记录，即患者首次被诊断目标ICD-10编码的记录
• 主查询中替换关联表为diag_final，确保最终结果仅包含首次诊断患者的数据

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