sql - 使用 Snowflake 中多列值的 SQL 计数和转置表输出
问题描述
我有一个有 5 个列的表,看起来像这样(示例):
Conf_1 | Conf_2 | Conf_3 | Conf_4 | Conf_5
High No Match Perfect No Match Perfect
High Perfect Review No Match Perfect
No Match No Match Perfect Medium Low
Perfect Medium No Match Low Medium
Low Low Review Medium High
Medium No Match No Match Perfect High
这些值包括 Perfect、High、Medium、Low、No Match 和 Review。某些列包含所有值,而某些列仅包含一个或两个值。
我想计算每个值在每列中显示的次数 - 将表格转换为 6 个值(Perfect - Review)作为行,然后计算 5 个 Conf 字段中的每一个的计数。
所需的输出如下所示:
这里的置信度列是来自组合 conf_1,conf_2......conf_5 的不同值
Confidence | Conf_1 Count | Conf_2 Count.....| Conf_5
Perfect 25 40 50
High 25 Null 50
Medium 5 Null 50
Low 100 100 Null
No Match 4 5 Null
Review Null 15 Null
解决方案
Select
confidence,
count(case when Confidence = 'Perfect' then 1 else 0) end as conf_1,
count(case when Confidence = 'High' then 1 else 0) end as conf_2,
count(case when Confidence = 'Medium' then 1 else 0) end as conf_3,
count(case when Confidence = 'Low' then 1 else 0) end as conf_4,
count(case when Confidence = 'No Match' then 1 else 0) end as conf_5,
count(case when Confidence = 'Review' then 1 else 0) end as conf_6
from table
group by 1
您可以使用以下方法建立信心:
select * from (
select conf_1 as confidence from table
union
select conf_2 as col from table
union
select conf_3 as col from table
union
select conf_4 as col from table
union
select conf_5 as col from table
union
select conf_6 as col from table
) t where confidence is not null
order by confidence