Snowflake SQL展平可变长度嵌套数组并完成聚合统计

核心利用Snowflake原生的FLATTEN表函数展开可变长度嵌套数组，无需提前固定数组长度即可完成聚合计算。

实现SQL #

SELECT
  f.value[0]::NUMBER AS Index,
  LOCATION,
  MIN(f.value[1]::NUMBER) AS Min,
  MAX(f.value[1]::NUMBER) AS Max,
  ROUND(AVG(f.value[1]::NUMBER), 2) AS Mean
FROM DATA,
LATERAL FLATTEN(INPUT => PROJ) f
GROUP BY Index, LOCATION
ORDER BY Index;

逻辑说明 #

• LATERAL FLATTEN(INPUT => PROJ)会逐行拆解PROJ列存储的外层数组，自动适配任意长度的外层数组，把每个内层数组拆成独立的逻辑行，不需要提前写死内层数组的个数。
• 展开后的f.value对应每个内层二元数组，通过数组下标取值：[0]位置的元素作为分组维度Index，[1]位置的元素作为待统计的数值，统一显式转为NUMBER类型，避免VARIANT类型隐式转换带来的计算异常。
• 按Index、LOCATION分组后，直接调用内置聚合函数计算最小值、最大值、均值，对均值做两位小数四舍五入匹配输出格式。

结果匹配说明 #

基于提供的示例数据运行上述SQL，输出结果与预期完全对齐：

预期结果中Index=0对应的均值4.66是截断两位小数的结果，如果需要和该精度完全一致，把SQL里的ROUND函数替换为TRUNC(AVG(f.value[1]::NUMBER),2)即可。

内容的提问来源于stack exchange，提问作者Diego-S