Snowflake - 聚合函数：SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*)

Question

以下语法能够正常工作的原因可能是什么？

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

SUM(*)	AVG(*)	MIN(*)	MAX(*)	ANY_VALUE(*)
null	null	null	null	null

数据类型：

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
*/

根据查询配置文件，解析结果如下：

EXPLAIN USING TABULAR
SELECT SUM(*), AVG(*), MIN(*), MAX(*), ANY_VALUE(*);

---

当提供一个表/子查询时，*会被解析为第一列。

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 SUM(*)
FROM (VALUES(1))
HAVING SUM(*) > 1;

只有在SELECT子句中才允许将*用作函数参数。

---

聚合函数的文档对于SUM, MIN/MAX等情况下的*没有提供任何线索。

我正在寻找一个有效的使用案例，它可能会有用。

---

编辑：

接受多个输入的聚合函数：

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

但是：

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

---

编辑2：

它不仅适用于聚合函数，还适用于标量函数：

SELECT COALESCE(*)
FROM (VALUES (NULL, 'a', 'b'));
-- 'a'

- Lukasz Szozda

---

这真是个愚蠢的问题/发现啊，你是怎么遇到这种奇怪的事情的？ - Simeon Pilgrim

@SimeonPilgrim 我在玩查询配置文件时发现了这个珍珠 :) - Lukasz Szozda

Answer 1

我的假设是这是一个来自count(*)的"魔法神器"，这确实是一个奇怪的表达。

嗯，是和不是。但是考虑到解析器将看到<inbuilt_built_function(token)><paren><star><paren>，而count是一个内置函数，其他函数也是如此。*将作为“在这里放入所有输入”的模板，我猜测。这应该可以通过使用count(distinct *)来进行测试，其结果与count(distinct a,b,c)（对于一个三列表）相同。

因此，*扩展为"所有输入"只是自动魔法化的，然后sum(a,b)无法通过sum的参数计数检查，这不是一个解析问题，也不是一个AST问题，而是一个几乎是运行时的问题。

测试：

select 
    count(*) as c1, 
    count(distinct *) as c2, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);

那并没有证明我的想法，但它确实显示了分词器生成了一个起始标记... :-)

更多大声思考：

select 
    count(*) as c1, 
    count(column1) as c2, 
    count(column2) as c3, 
    count(column1, column2) as c4, 
    
    --count(distinct *) as c2, 
    --count(distinct(column1, column2)) as c2, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);
    select 
    count(*) as c1, 
    count(column1) as c2, 
    count(column2) as c3, 
    count(column1, column2) as c4, 
    count(distinct column1, column2) as c3
from values 
    (1,2), 
    (1,2), 
    (null, null);

啊，是的，* 表示存在一行，而显式名称触发非空值，因此它是参数的扩展。但是，必须理解计数的整个类型类别参数，“行”，SUM 和 MAX 等可以处理，但只能自动魔法成“必须只有一列”。

这个回答太弱了，因为自动魔法就像免费午餐一样“好看”。

或者说它很“恶心”，但某些大客户有很多 SQL 代码“不能更改，并且需要支持以赚取大量 $$$，所以为什么不呢？”我不喜欢这些，因为它们似乎过于简单和停止思考。但它们作为选项存在。

魔法行选项：

在Medium上有一个非常好的解释，关于PIVOT的用法，并且它里面隐含了GROUPING BY ALL，适用于源参数（在结果列或输出列中没有命名的参数，与所选列无关），因此已经存在一种“只需使用给定的所有内容，并理解其含义”的概念，以支持这一点。也许就像C语言中的ARGC、ARGV或者C#中的params，它变成了一个动态处理的容器，因此出现了LISTAGG错误，这真的展示了参数的概念。

这真是太有趣了！

- Simeon Pilgrim

---

有趣的是，对于期望单个或两个参数的LISTAGG函数，扩展可能会起作用（尽管分隔符必须是常量）。SELECT LISTAGG(*) FROM (SELECT 'a', 'b'); --函数LISTAGG的第二个参数需要是常量，找到了'"values"."'B'"' - Lukasz Szozda

哦，那真是一颗宝石。很想听听@GregPavlik或者团队的解释，而不是凭我的猜测来玩乐。 - Simeon Pilgrim

还有一个期望两列的聚合函数：SELECT MIN_BY(*) FROM (VALUES('a', 'b'), ('aa', 'a')) AS sub(c1, c2) --> 没有错误输出 aa - Lukasz Szozda