400-090-9964


博客 | 论坛

教学文章

PostgreSQL教程-sql语法-值表达式-聚合表达式

时间:2020-01-15 来源:

PostgreSQL教程-sql语法-值表达式-聚合表达式

一个聚合表达式表示在由一个查询选择的行上应用一个聚合函数。一个聚合函数将多个输入减少到一个单一输出值,例如对输入的求和或平均。一个聚合表达式的语法是下列之一:

aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]

aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

这里aggregate_name是一个之前定义的聚合(可能带有一个模式名限定),并且expression是任意自身不包含聚合表达式的值表达式或一个窗口函数调用。可选的order_by_clause和filter_clause描述如下。

第一种形式的聚合表达式为每一个输入行调用一次聚合。第二种形式和第一种相同,因为ALL是默认选项。第三种形式为输入行中表达式的每一个可区分值(或者对于多个表达式是值的可区分集合)调用一次聚合。第四种形式为每一个输入行调用一次聚合,因为没有特定的输入值被指定,它通常只对于count(*)聚合函数有用。最后一种形式被用于有序集聚合函数,其描述如下。

大部分聚合函数忽略空输入,这样其中一个或多个表达式得到空值的行将被丢弃。除非另有说明,对于所有内建聚合都是这样。

例如,count(*)得到输入行的总数。count(f1)得到输入行中f1为非空的数量,因为count忽略空值。而count(distinct f1)得到f1的非空可区分值的数量。

一般地,交给聚合函数的输入行是未排序的。在很多情况中这没有关系,例如不管接收到什么样的输入,min总是产生相同的结果。但是,某些聚合函数(例如array_agg 和string_agg)依据输入行的排序产生结果。当使用这类聚合时,可选的order_by_clause可以被用来指定想要的顺序。order_by_clause与查询级别的ORDER BY子句(如第 7.5 节所述)具有相同的语法,除非它的表达式总是仅有表达式并且不能是输出列名称或编号。例如:

SELECT array_agg(a ORDER BY b DESC) FROM table;

在处理多参数聚合函数时,注意ORDER BY出现在所有聚合参数之后。例如,要这样写:

SELECT string_agg(a, ',' ORDER BY a) FROM table;

而不能这样写:

SELECT string_agg(a ORDER BY a, ',') FROM table; -- 不正确

后者在语法上是合法的,但是它表示用两个ORDER BY键来调用一个单一参数聚合函数(第二个是无用的,因为它是一个常量)。

如果在order_by_clause之外指定了DISTINCT,那么所有的ORDER BY表达式必须匹配聚合的常规参数。也就是说,你不能在DISTINCT列表没有包括的表达式上排序。

注意

在一个聚合函数中指定DISTINCT以及ORDER BY的能力是一种PostgreSQL扩展。

如上所述,如果通用和统计聚合中排序是可选的, 在要为它排序输入行时可以在该聚合的常规参数 列表中放置ORDER BY。有一个聚合函数的子集叫 做有序集聚合,它要求一个 order_by_clause,通常是因为 该聚合的计算只对其输入行的特定顺序有意义。有序集聚合的典 型例子包括排名和百分位计算。按照上文的最后一种语法,对于 一个有序集聚合, order_by_clause被写在 WITHIN GROUP (...)中。 order_by_clause中的表达式 会像常规聚合参数一样对每一个输入行计算一次,按照每个 order_by_clause的要求排序并 且交给该聚合函数作为输入参数(这和非 WITHIN GROUP order_by_clause的情况不同,在其中表达 式的结果不会被作为聚合函数的参数)。如果有在 WITHIN GROUP之前的参数表达式,会把它们称 为直接参数以便与列在 order_by_clause中的 聚合参数相区分。与常规聚合参数不同,针对 每次聚合调用只会计算一次直接参数,而不是为每一个输入行 计算一次。这意味着只有那些变量被GROUP BY 分组时,它们才能包含这些变量。这个限制同样适用于根本不在 一个聚合表达式内部的直接参数。直接参数通常被用于百分数 之类的东西,它们只有作为每次聚合计算用一次的单一值才有意 义。直接参数列表可以为空,在这种情况下,写成() 而不是(*)(实际上 PostgreSQL接受两种拼写,但是只有第一 种符合 SQL 标准)。

有序集聚合的调用例子:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;

percentile_cont

-----------------

50489

这会从表households的 income列得到第 50 个百分位或者中位的值。 这里0.5是一个直接参数,对于百分位部分是一个 在不同行之间变化的值的情况它没有意义。

如果指定了FILTER,那么只有对filter_clause计算为真的输入行会被交给该聚合函数,其他行会被丢弃。例如:

SELECT

count(*) AS unfiltered,

count(*) FILTER (WHERE i < 5) AS filtered

FROM generate_series(1,10) AS s(i);

unfiltered | filtered

------------+----------

10 | 4

(1 row)

预定义的聚合函数在第 9.20 节中描述。其他聚合函数可以由用户增加。

一个聚合表达式只能出现在SELECT命令的结果列表或是HAVING子句中。在其他子句(如WHERE)中禁止使用它,因为那些子句的计算在逻辑上是在聚合的结果被形成之前。

当一个聚合表达式出现在一个子查询中(见第 4.2.11 节和第 9.22 节),聚合通常在该子查询的行上被计算。但是如果该聚合的参数(以及filter_clause,如果有)只包含外层变量则会产生一个异常:该聚合则属于最近的那个外层,并且会在那个查询的行上被计算。该聚合表达式从整体上则是对其所出现于的子查询的一种外层引用,并且在那个子查询的任意一次计算中都作为一个常量。只出现在结果列表或HAVING子句的限制适用于该聚合所属的查询层次。

版权所有@北京神脑资讯技术有限公司(CUUG,中国UNIX用户协会) Copyright 2017 ALL Rights Reserved 京ICP备11008061号 京公网110108006275号

CUUG旗下网站:www.cuug.com.cn www.cuug.com oracle.cuug.com bbs.cuug.com bd.cuug.com

电话:010-59426307 010-59426319 邮政编码:100089

海淀校区:北京市海淀区紫竹院路88号紫竹花园4号楼D座703(CUUG)