Snowflake中使用SQL统计多列值出现次数并转置表输出

以下两种方案都可以满足需求，你可以根据自己的使用习惯选择：

方案1：UNPIVOT + PIVOT 实现（扩展性更好） #

适合后续Conf系列列有新增的场景，修改成本低：

-- 先生成完整的置信度取值维度表，确保所有6种取值都出现在结果中
WITH ALL_CONF_VALUES AS (
    SELECT COLUMN_VALUE AS Confidence
    FROM TABLE(FLATTEN(input => ARRAY_CONSTRUCT('Perfect','High','Medium','Low','No Match','Review')))
),
-- 把原表5个Conf列转成行维度，统计每个取值的出现次数
UNPIVOTED_STATS AS (
    SELECT 
        Confidence,
        Conf_Column,
        COUNT(*) AS occur_cnt
    FROM 你的原表名 -- 替换为实际表名
    UNPIVOT (
        Confidence FOR Conf_Column IN (Conf_1, Conf_2, Conf_3, Conf_4, Conf_5)
    )
    GROUP BY Confidence, Conf_Column
)
-- 关联维度表并转置为最终输出格式
SELECT 
    a.Confidence,
    p."Conf_1" AS "Conf_1 Count",
    p."Conf_2" AS "Conf_2 Count",
    p."Conf_3" AS "Conf_3 Count",
    p."Conf_4" AS "Conf_4 Count",
    p."Conf_5" AS "Conf_5 Count"
FROM ALL_CONF_VALUES a
LEFT JOIN UNPIVOTED_STATS u
    ON a.Confidence = u.Confidence
PIVOT (
    MAX(occur_cnt) 
    FOR Conf_Column IN ('Conf_1' AS "Conf_1", 'Conf_2' AS "Conf_2", 'Conf_3' AS "Conf_3", 'Conf_4' AS "Conf_4", 'Conf_5' AS "Conf_5")
) p
-- 按常规取值顺序排序
ORDER BY 
    CASE a.Confidence 
        WHEN 'Perfect' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
        WHEN 'No Match' THEN 5
        WHEN 'Review' THEN 6
    END;

说明 #

如果无匹配的位置想要显示0而非NULL，把SELECT部分的p."Conf_1"替换为IFNULL(p."Conf_1", 0)即可。 #

方案2：手动聚合实现（逻辑更直观） #

不需要理解UNPIVOT/PIVOT语法也能直接修改使用：

WITH ALL_CONF_VALUES AS (
    SELECT COLUMN_VALUE AS Confidence
    FROM TABLE(FLATTEN(input => ARRAY_CONSTRUCT('Perfect','High','Medium','Low','No Match','Review')))
)
SELECT 
    a.Confidence,
    SUM(CASE WHEN t.Conf_1 = a.Confidence THEN 1 END) AS "Conf_1 Count",
    SUM(CASE WHEN t.Conf_2 = a.Confidence THEN 1 END) AS "Conf_2 Count",
    SUM(CASE WHEN t.Conf_3 = a.Confidence THEN 1 END) AS "Conf_3 Count",
    SUM(CASE WHEN t.Conf_4 = a.Confidence THEN 1 END) AS "Conf_4 Count",
    SUM(CASE WHEN t.Conf_5 = a.Confidence THEN 1 END) AS "Conf_5 Count"
FROM ALL_CONF_VALUES a
CROSS JOIN 你的原表名 t -- 替换为实际表名
GROUP BY a.Confidence
ORDER BY 
    CASE a.Confidence 
        WHEN 'Perfect' THEN 1
        WHEN 'High' THEN 2
        WHEN 'Medium' THEN 3
        WHEN 'Low' THEN 4
        WHEN 'No Match' THEN 5
        WHEN 'Review' THEN 6
    END;

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