如何用SQL的Partition By统计唯一观测值？ICU计数逻辑修正求助

统计需求 #

• 最终数据集中需将唯一Discharge_ID的数量统计为Total_Discharges。
• ICU_ID计数规则：
  • PT_ID 001：同一出院日期下的4个唯一ICU_ID均在Discharge_DT30天内，仅计数1次，故AZ地区TotalDischarges为1、ICU_Admit为1。
  • PT_ID 002：2个不同Discharge_ID对应同一30天内的ICU_ID，需统计TotalDischarges为2、ICU_Admit为1。

数据集（DF1） #

出院后30天内入住ICU的记录：

期望输出 #

当前使用的SQL代码 #

DROP TABLE IF EXISTS #edit1

WITH CTE_df1 as (
select * from df1
)
select 
        City,
        PT_ID,
        Hospital_ID,
        Admit_Dt,
        Discharge_DT,
        Discharge_ID,
        count(ICU_ID) over (partition by ICU_ID) as ICU_Pts,
        count(distinct Discharge_ID) as Total_Discharges
into #edit1
from CTE_df1
group by City, Discharge_ID, ICU_ID, PT_ID
order by City,



;with CTE_edit1 as (
select * from #edit1
)
select City, sum(ICU_Pts), sum(Total_Discharges)
from CTE_edit1
group by City
order by City

当前问题 #

PT_ID 001结果正确，但PT_ID 002的ICU_Admit显示为2，错误重复计数同一ICU访问。

修正后的SQL代码 #

WITH grouped_data AS (
    -- 按城市和出院记录分组，定位唯一出院记录
    SELECT 
        City,
        Discharge_ID
    FROM df1
    GROUP BY City, Discharge_ID
),
city_summary AS (
    -- 按城市汇总统计
    SELECT 
        gd.City,
        COUNT(DISTINCT gd.Discharge_ID) AS TotalDischarges,
        COUNT(DISTINCT df1.ICU_ID) AS ICU_Admit
    FROM grouped_data gd
    JOIN df1 ON gd.City = df1.City AND gd.Discharge_ID = df1.Discharge_ID
    GROUP BY gd.City
)
SELECT City, TotalDischarges, ICU_Admit
FROM city_summary
ORDER BY City;

代码说明 #

1. grouped_data CTE：先提取每个城市的唯一Discharge_ID，确保每个出院记录只被统计一次。
2. city_summary CTE：
   • TotalDischarges通过COUNT(DISTINCT gd.Discharge_ID)直接统计城市内的唯一出院记录数，符合需求。
   • ICU_Admit通过关联原表后使用COUNT(DISTINCT df1.ICU_ID)，确保同一ICU_ID无论关联多少出院记录，都仅计数一次，解决重复统计问题。

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