Snowflake中如何为字符串类型列表列新增中位数/均值列？

问题背景 #

现有表结构如下：

其中second列的数据类型为字符串，需新增列计算该列中列表的均值和中位数。

实现方案 #

方法1：展开数组后聚合计算 #

先将字符串解析为数值数组，展开为单行数值后聚合统计：

WITH parsed_list AS (
    SELECT
        first,
        second,
        -- 去除首尾括号并分割为数值数组
        TRY_CAST(SPLIT_TO_ARRAY(REPLACE(REPLACE(second, '[', ''), ']', ''), ',') AS ARRAY(INT)) AS num_array
    FROM your_table
),
flattened_data AS (
    SELECT
        first,
        second,
        value::INT AS num
    FROM parsed_list,
    LATERAL FLATTEN(input => num_array)
)
SELECT
    first,
    second,
    AVG(num) AS list_average,
    MEDIAN(num) AS list_median
FROM flattened_data
GROUP BY first, second;

方法2：直接基于数组元素计算（更简洁） #

利用Snowflake的横向展开和窗口函数，无需额外CTE：

SELECT DISTINCT
    first,
    second,
    AVG(TRY_CAST(value AS INT)) OVER (PARTITION BY first, second) AS list_average,
    MEDIAN(TRY_CAST(value AS INT)) OVER (PARTITION BY first, second) AS list_median
FROM your_table,
LATERAL FLATTEN(input => SPLIT_TO_ARRAY(REPLACE(REPLACE(second, '[', ''), ']', ''), ','));

核心函数说明 #

• REPLACE()：移除字符串首尾的[和]符号
• SPLIT_TO_ARRAY()：将处理后的字符串按逗号分割为数组
• TRY_CAST()：安全地将数组中的字符串元素转为整数（避免转换失败导致查询中断）
• FLATTEN()：将数组展开为多行数据，便于聚合计算
• AVG()/MEDIAN()：分别计算均值和中位数

持久化新增列 #

如果需要将结果作为永久列添加到原表，可执行以下语句：

ALTER TABLE your_table
ADD COLUMN list_average FLOAT,
ADD COLUMN list_median FLOAT;

UPDATE your_table
SET 
    list_average = (SELECT AVG(TRY_CAST(value AS INT)) FROM LATERAL FLATTEN(input => SPLIT_TO_ARRAY(REPLACE(REPLACE(second, '[', ''), ']', ''), ','))),
    list_median = (SELECT MEDIAN(TRY_CAST(value AS INT)) FROM LATERAL FLATTEN(input => SPLIT_TO_ARRAY(REPLACE(REPLACE(second, '[', ''), ']', ''), ',')));

内容的提问来源于stack exchange，提问作者H.Okan