Snowflake结构化列的数据类型选型及目标查询实现咨询

1. 最优数据类型选择 #

对于需要通过名称（如data_1.date）访问子元素的结构化数组数据，VARIANT是Snowflake的最优选择：

• VARIANT原生支持JSON等半结构化数据，完美适配“包含日期与关联整数值的元素数组”结构
• 可直接通过点符号（.）或冒号（:）访问嵌套元素，比如data_1[0].date或data_1:date两种写法都生效
• 相比单独使用OBJECT/ARRAY类型，VARIANT灵活性更强，能兼容数组内多对象的复杂场景

2. 查询实现 #

示例表结构与数据 #

假设表名为structured_data_table，数据示例如下：

查询语句（聚合日期为数组） #

SELECT
    id,
    -- 提取data_1最新日期对应的值
    (SELECT value:value::INT
     FROM TABLE(FLATTEN(input => data_1))
     ORDER BY value:date::DATE DESC
     LIMIT 1) AS data_1_latest_value,
    -- 计算data_2所有值的总和
    SUM(value:value::INT) AS data_2_total_value,
    -- 保留data_2的日期列表（按顺序聚合为数组）
    ARRAY_AGG(DISTINCT value:date::DATE) WITHIN GROUP (ORDER BY value:date::DATE) AS data_2_dates
FROM structured_data_table,
     TABLE(FLATTEN(input => data_2))
GROUP BY id, data_1;

期望聚合结果 #

查询语句（展开日期为单独行） #

如果需要保留data_2的每个日期单独成行以便后续操作，可使用窗口函数实现：

SELECT
    id,
    (SELECT value:value::INT
     FROM TABLE(FLATTEN(input => data_1))
     ORDER BY value:date::DATE DESC
     LIMIT 1) AS data_1_latest_value,
    SUM(value:value::INT) OVER (PARTITION BY id) AS data_2_total_value,
    value:date::DATE AS data_2_date
FROM structured_data_table,
     TABLE(FLATTEN(input => data_2));

期望展开结果 #

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