Snowflake视图创建：将Type列转为4个事件计数列的SQL实现

你可以通过CASE WHEN条件判断来实现需求，将原查询扩展后封装为视图即可。以下是完整的SQL语句：

CREATE OR REPLACE VIEW your_view_name AS
SELECT 
    id, 
    what_id, 
    who_id, 
    event_date, 
    type,
    CASE WHEN type = 'CALL' THEN 1 ELSE 0 END AS total_calls,
    CASE WHEN type = 'EMAIL' THEN 1 ELSE 0 END AS total_emails,
    CASE WHEN type = 'OTHER' THEN 1 ELSE 0 END AS total_other,
    1 AS total_all
FROM table1
WHERE who_id LIKE 'id_1';

逻辑说明 #

• total_calls/total_emails/total_other：通过CASE WHEN匹配当前行的type值，匹配成功返回1，否则返回0，实现单条记录对应类型的计数标记
• total_all：由于每行对应一个独立事件，直接设置为1即可

执行后输出示例 #

内容的提问来源于stack exchange，提问作者A. Molinari