咨询：将BigQuery事件查询SQL转换为Snowflake SQL的高效方案

原BigQuery语句通过UNNEST子查询从嵌套数组event_params中提取指定key的多类型值并转为字符串，以下提供两种Snowflake实现方案，均无需多表连接，可轻松扩展至数百个key：

方案1：LATERAL FLATTEN + 条件聚合（高效扩展首选） #

该方式通过展开数组后聚合提取值，适合需要大量key的场景：

SELECT
    event_date,
    user_pseudo_id,
    event_name,
    event_timestamp,
    -- 提取key1，按优先级取第一个非空值
    ANY_VALUE(CASE WHEN ep.KEY = 'key1' THEN 
        COALESCE(ep.VALUE:string_value, 
                 CAST(ep.VALUE:int_value AS STRING), 
                 CAST(ep.VALUE:double_value AS STRING), 
                 CAST(ep.VALUE:float_value AS STRING)) 
    END) AS key1,
    -- 提取key2，逻辑一致
    ANY_VALUE(CASE WHEN ep.KEY = 'key2' THEN 
        COALESCE(ep.VALUE:string_value, 
                 CAST(ep.VALUE:int_value AS STRING), 
                 CAST(ep.VALUE:double_value AS STRING), 
                 CAST(ep.VALUE:float_value AS STRING)) 
    END) AS key2
    -- 新增key只需复制上述CASE块，修改key名称即可
FROM 
    bigquery_analytics.events_20240812, -- 替换为你的Snowflake表名
    LATERAL FLATTEN(input => event_params) ep
GROUP BY
    event_date,
    user_pseudo_id,
    event_name,
    event_timestamp;

• LATERAL FLATTEN等价于BigQuery的UNNEST，用于展开嵌套数组
• ANY_VALUE用于聚合展开后的记录，确保每个事件仅保留对应key的有效值（同一事件的单个key只会有一条匹配记录，用MAX也可）
• Snowflake用冒号语法VALUE:string_value访问嵌套字段，对应BigQuery的value.string_value

方案2：子查询方式（贴近原BigQuery写法） #

如果希望保持和原语句结构一致，可使用子查询+TABLE(FLATTEN)：

SELECT
    event_date,
    user_pseudo_id,
    event_name,
    event_timestamp,
    (SELECT 
        COALESCE(VALUE:string_value, 
                 CAST(VALUE:int_value AS STRING), 
                 CAST(VALUE:double_value AS STRING), 
                 CAST(VALUE:float_value AS STRING))
     FROM TABLE(FLATTEN(input => event_params))
     WHERE KEY = 'key1') AS key1,
    (SELECT 
        COALESCE(VALUE:string_value, 
                 CAST(VALUE:int_value AS STRING), 
                 CAST(VALUE:double_value AS STRING), 
                 CAST(VALUE:float_value AS STRING))
     FROM TABLE(FLATTEN(input => event_params))
     WHERE KEY = 'key2') AS key2
    -- 新增key只需复制子查询块，修改key名称即可
FROM 
    bigquery_analytics.events_20240812; -- 替换为你的Snowflake表名

• TABLE(FLATTEN)替代BigQuery的UNNEST，子查询逻辑与原语句完全对齐
• 无需多表连接，新增key仅需复制子查询并修改key值即可

两种方案均避免了多表连接的繁琐，且能保证输出结果与原BigQuery语句完全一致。

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