SQL逆透视疾病列后成员数统计异常，求解决方法

原始数据 #

原查询语句 #

sel DISEASE, 
SUM(URBAN) URBAN, 
SUM(RURAL) RURAL,
COUNT(DISTINCT MEMBER_NUMBER) MEMBS
FROM final
UNPIVOT (
MEMBS FOR DISEASE IN (
CMS_ANXIETY AS 'ANXIETY',
CMS_ASTHMA AS 'ASTHMA'
)
)AS P
GROUP BY 1,2,3
ORDER BY DISEASE

当前错误结果 #

问题根源 #

1. UNPIVOT中错误地将原表的asthma/anxiety列值命名为MEMBS，但这些列实际是0/1的患病标记，并非成员数
2. 分组时包含了URBAN和RURAL，导致统计的是所有成员的城乡总和，而非对应疾病患者的城乡分布
3. COUNT(DISTINCT MEMBER_NUMBER)会统计所有成员，不管该成员是否患有对应疾病，所以两类疾病的成员数完全一致

修正后的查询语句 #

SELECT 
    DISEASE,
    SUM(CASE WHEN urban = 1 THEN 1 ELSE 0 END) AS URBAN,
    SUM(CASE WHEN rural = 1 THEN 1 ELSE 0 END) AS RURAL,
    COUNT(DISTINCT MEMBER_NUMBER) AS MEMBS
FROM final
UNPIVOT (
    HAS_DISEASE FOR DISEASE IN (
        anxiety AS 'ANXIETY',
        asthma AS 'ASTHMA'
    )
) AS P
WHERE HAS_DISEASE = 1  -- 仅筛选患有对应疾病的成员
GROUP BY DISEASE
ORDER BY DISEASE;

修正后的预期结果 #

基于你提供的原始数据，运行修正后的查询会得到：

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