Snowflake中聚合函数以*为参数的语法原理及实用场景咨询

基础语法表现 #

在Snowflake中，以下语法可以正常执行：

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*);

执行后所有字段结果均为NULL，通过DESCRIBE RESULT可查看返回字段的数据类型：

DESCRIBE RESULT LAST_QUERY_ID();
/*
name          type          kind
SUM(*)        NUMBER(30,0)  COLUMN
AVG(*)        NUMBER(36,6)  COLUMN
MIN(*)        VARCHAR(0)    COLUMN
MAX(*)        VARCHAR(0)    COLUMN
ANY_VALUE(*)  VARCHAR(0)    COLUMN
*/

解析逻辑与多列场景限制 #

通过查询计划可得知其解析规则：当查询指定表或子查询时，*会被解析为该表/子查询的第一列。

• 单列表查询下，能得到正确的聚合结果：

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)
FROM (VALUES (1)) sub(c);
/*
SUM(*)  AVG(*)  MIN(*)  MAX(*)  ANY_VALUE(*)
1       1       1       1       1
*/

• 多列表查询时，会因为函数参数数量超出允许范围报错：

SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)
FROM (VALUES (1,2));

错误：函数[SUM(VALUES.COLUMN1, VALUES.COLUMN2)]的参数过多

另外，*仅允许在SELECT子句中作为函数参数使用，在HAVING子句中调用会直接报错：

SELECT SUM(*)
FROM (VALUES(1))
HAVING SUM(*) > 1;

错误：仅允许在SELECT子句中使用作为函数参数*

多参数聚合函数的差异表现 #

对于支持多输入参数的聚合函数，不同函数对*的兼容性存在差异：

• LISTAGG(*)会报错，因为该函数要求第二个参数为常量，而*解析后传入了多列值：

SELECT LISTAGG(*) FROM (SELECT 'a', 'b'); 
-- error: argument 2 to function LISTAGG needs to be constant, found'"values"."B"'

• MIN_BY(*)可以正常运行，解析后会使用第一列作为排序依据、第二列作为返回值：

SELECT MIN_BY(*) 
FROM (VALUES('a', 'b'), ('aa', 'a')) AS sub(c1, c2);
/*
MIN_BY(*)
aa
*/

扩展至标量函数的表现 #

该特性并非仅适用于聚合函数，部分标量函数也支持使用*作为参数，例如COALESCE(*)会依次检查解析后的列值，返回第一个非NULL值：

SELECT COALESCE(*)
FROM (VALUES (NULL, 'a', 'b'));
-- 返回结果：'a'

疑问：该语法的实用场景 #

目前Snowflake官方文档未提及SUM、MIN/MAX等聚合函数使用*作为参数的相关说明，希望了解该语法的实际有效使用场景。

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