Snowflake VALUES子句关联列报错 如何实现多列转行统计计数

错误原因 #

Snowflake的VALUES子句暂不支持在横向关联（LATERAL）场景下引用外部查询的列，因此PostgreSQL的兼容写法会抛出Invalid expression [CORRELATION(N.N0)] in VALUES clause错误。

实现方案 #

方案1：使用FLATTEN+数组构造（推荐） #

这是Snowflake场景下多列转多行的最优写法，代码简洁性能优异：

SELECT 
  v.value::NUMBER AS n,
  COUNT(*) AS "Count"
FROM npi
,TABLE(FLATTEN(input => ARRAY_CONSTRUCT(n0, n1, n2))) v
GROUP BY v.value
ORDER BY n;

逻辑说明：

• 先通过ARRAY_CONSTRUCT将每行的n0、n1、n2三个列值拼接为一个数组
• 再通过表函数FLATTEN将数组拆分为独立行，每行对应一个原始列值
• 最后分组统计每个值的出现次数即可得到预期结果

方案2：使用LATERAL + UNION ALL 兼容写法 #

如果需要保持和PostgreSQL写法逻辑对齐，可以用UNION ALL替代VALUES子句：

SELECT 
  el AS n,
  COUNT(*) AS "Count"
FROM 
(
  SELECT  n0, n1, n2 from npi
) n
CROSS JOIN LATERAL
     (SELECT n0 AS el UNION ALL
      SELECT n1 AS el UNION ALL
      SELECT n2 AS el
     ) v
GROUP BY el
ORDER BY el;

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