tsql - 如何计算复杂年龄/性别/等群体的人数？

Question

我得到了以下 Patients 表。

HospitalId INT,
GenderId BIT,
Age TINYINT,
DiseaseId SMALLINT

GenderId = 0 为男性

GenderId = 1 为女性

HospitalA 的 HospitalId 为 0

HospitalB 的 HospitalId 1

这是我想要生成的输出:

DiseaseId | HospitalA_Male_18-30 | HospitalA_Male_31-40 |
---------------------------------------------------------
0         |   (count here)       |   (count here)       |
1         |   (count here)       |   (count here)       |
2         |   (count here)       |   (count here)       |
3         |   (count here)       |   (count here)       |

(专栏继续)

HospitalA_Female_18-30 | HospitalA_Female_31-40 |
-------------------------------------------------
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |

(专栏继续)

HospitalB_Male_18-30 | HospitalB_Male_31-40 |
---------------------------------------------
    (count here)     |     (count here)     |
    (count here)     |     (count here)     |
    (count here)     |     (count here)     |
    (count here)     |     (count here)     |

(专栏继续)

HospitalB_Female_18-30 | HospitalB_Female_31-40 |
-------------------------------------------------
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |
    (count here)       |     (count here)       |

(结果集中9列)

如您所见，我实际上需要针对每种疾病计算每个特定组中有多少患者患有这种疾病(按医院、性别和年龄类别)。

如何在 T-SQL 中(最有效地)完成此类分组？

Answer 1

您可以使用数据透视查询来完成:

select * from 
(
  select diseaseid, 
         'Hospital'
         + case hospitalid when 0 then 'A' when 1 then 'B' end
         + '_'
         + case genderid when 1 then 'Female' else 'Male' end
         + '_'
         + case when age between 18 and 30 
              then '18-30' 
              else (case when age between 31 and 40 then '31-40' end) 
              end Title,
         1 Cnt
  from Patients
  where age between 18 and 40
) t
pivot (
  count (Cnt) for Title in (
    [HospitalA_Male_18-30],   [HospitalA_Male_31-40],
    [HospitalA_Female_18-30], [HospitalA_Female_31-40],
    [HospitalB_Male_18-30],   [HospitalB_Male_31-40],
    [HospitalB_Female_18-30], [HospitalB_Female_31-40]
  )
) as Q

更新

作为上述解决方案的发展，您还可以将名称部分从 CASE 表达式移动到它们自己的虚拟表中，并将 Patients 表连接到它们:

;with
hospital (hospitalid, hospitalname) as (
  select 0, 'HospitalA' union all
  select 1, 'HospitalB'
),
gender (genderid, gendername) as (
  select 0, 'Male' union all
  select 1, 'Female'
),
agerange (agefrom, ageto) as (
  select 18, 30 union all
  select 31, 40
)
select * from 
(
  select p.diseaseid, 
         h.hospitalname + '_' + g.gendername + '_'
         + rtrim(a.agefrom) + '-' + rtrim(a.ageto) as Title,
         1 Cnt
  from Patients p
    inner join hospital h on p.hospitalid = h.hospitalid
    inner join gender   g on p.genderid   = g.genderid
    inner join agerange a on p.age between a.agefrom and a.ageto
  where p.age between 18 and 40
) t
pivot (
  count (Cnt) for Title in (
    [HospitalA_Male_18-30],   [HospitalA_Male_31-40],
    [HospitalA_Female_18-30], [HospitalA_Female_31-40],
    [HospitalB_Male_18-30],   [HospitalB_Male_31-40],
    [HospitalB_Female_18-30], [HospitalB_Female_31-40]
  )
) as Q

添加子选择和连接的开销被更容易的维护所弥补:

• (元)数据部分与逻辑部分分离；

• 名称部分列表更方便根据需要扩展；

• 连接表达式更易于修改，以防您需要更改目标列名称的格式。