如何在Snowflake SQL中实现Oracle的二维FOR子句透视？

原Oracle PL/SQL透视查询因Snowflake不支持多列透视键、聚合后别名后缀等特性无法直接运行，以下是等价实现：

Snowflake SQL 实现 #

SELECT 
    RACE,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS MNH_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS FNH_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS UNH_G,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS MH_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS FH_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS UH_G,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Unknown' THEN line_number END) AS MU_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Unknown' THEN line_number END) AS FU_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Unknown' THEN line_number END) AS UU_G,
    -- 匹配原Oracle的TOTAL计算逻辑
    (COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Unknown' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Total' THEN line_number END)) AS TOTAL
FROM total_demographics
GROUP BY RACE
ORDER BY 
    CASE RACE
        WHEN 'American Indian or Alaska Native' THEN 0
        WHEN 'Asian' THEN 1
        WHEN 'Native Hawaiian or other Pacific Islander' THEN 2
        WHEN 'Black' THEN 3
        WHEN 'White' THEN 4
        WHEN 'More than one race' THEN 5
        WHEN 'Unknown' THEN 6
        WHEN 'Total' THEN 7
        ELSE 6
    END;

说明：Snowflake原生PIVOT不支持多列透视维度，使用条件聚合替代，通过CASE WHEN精准匹配原查询的GENDER+ETHNICITY组合，统计逻辑与原Oracle完全一致，输出结果与示例表格匹配。

T-SQL 实现 #

SELECT 
    RACE,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS MNH_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS FNH_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) AS UNH_G,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS MH_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS FH_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) AS UH_G,
    COUNT(CASE WHEN GENDER = 'Male' AND ETHNICITY = 'Unknown' THEN line_number END) AS MU_G,
    COUNT(CASE WHEN GENDER = 'Female' AND ETHNICITY = 'Unknown' THEN line_number END) AS FU_G,
    COUNT(CASE WHEN GENDER = 'Unknown' AND ETHNICITY = 'Unknown' THEN line_number END) AS UU_G,
    -- 匹配原Oracle的TOTAL计算逻辑
    (COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Non-Hispanic' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Hispanic or Latino' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Unknown' THEN line_number END) +
     COUNT(CASE WHEN GENDER = 'Total' AND ETHNICITY = 'Total' THEN line_number END)) AS TOTAL
FROM total_demographics
GROUP BY RACE
ORDER BY 
    CASE RACE
        WHEN 'American Indian or Alaska Native' THEN 0
        WHEN 'Asian' THEN 1
        WHEN 'Native Hawaiian or other Pacific Islander' THEN 2
        WHEN 'Black' THEN 3
        WHEN 'White' THEN 4
        WHEN 'More than one race' THEN 5
        WHEN 'Unknown' THEN 6
        WHEN 'Total' THEN 7
        ELSE 6
    END;

说明：T-SQL同样不支持多列PIVOT，条件聚合是最直接的兼容方案，完全复刻原Oracle查询的统计逻辑与排序规则，输出结果一致。

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