如何在Snowflake中计算Variant列events中值为Y的指标总和？

问题背景 #

现有Snowflake表user_activity，其中events列为Variant类型，存储JSON格式的各类指标（如calls、imps、leads等），不同行的指标数量不一致。需求如下：

• 将指标值为"Y"计为1，"N"计为0
• 统计每行events中值为"Y"的指标总数（对应total_events列）
• 同时单独统计各指标的数值

示例数据准备 #

-- 建表语句
CREATE OR REPLACE TABLE user_activity (
    user_id INT,
    events VARIANT
);

-- 插入示例数据
INSERT INTO user_activity VALUES
(1, PARSE_JSON('{"calls": "Y", "imps": "N", "leads": "Y"}')),
(2, PARSE_JSON('{"calls": "N", "form_submits": "Y"}')),
(3, PARSE_JSON('{"leads": "N", "imps": "Y", "clicks": "Y", "calls": "Y"}'));

完善后的SQL语句 #

SELECT
    user_id,
    -- 单独提取各指标并转换为数值
    IFF(events:calls = 'Y', 1, 0) AS calls,
    IFF(events:imps = 'Y', 1, 0) AS imps,
    IFF(events:leads = 'Y', 1, 0) AS leads,
    IFF(events:form_submits = 'Y', 1, 0) AS form_submits,
    IFF(events:clicks = 'Y', 1, 0) AS clicks,
    -- 计算值为"Y"的指标总数
    (SELECT SUM(IFF(value::STRING = 'Y', 1, 0)) 
     FROM TABLE(FLATTEN(input => events))) AS total_events
FROM user_activity;

关键逻辑说明 #

• FLATTEN函数：将Variant类型的JSON对象拆分为键值对的行结构，实现对所有动态指标的遍历
• 子查询求和：遍历拆分后的所有指标值，将"Y"转换为1、"N"转换为0后求和，得到该行符合条件的指标总数
• IFF函数：针对已知的固定指标单独提取转换，不存在的指标会自动返回0（NULL值会触发IFF的else分支）

预期输出 #

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