当前位置: gth163->postgresql > PostgreSQL手册-》sql语法-》值表达式-》聚合表达式

PostgreSQL手册-》sql语法-》值表达式-》聚合表达式

2023-08-21作者:gth163来源:www.gth163.com

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子句的限制适用于该聚合所属的查询层次。

  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复
  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • PostgreSQL技术大讲堂 - 第51讲:老陈与德哥聊一聊数据库调优
  • 5月25日,温州大学49名学生参加工信人才PostgreSQL认证考试!
  • 5月30日,PG中级证书来了!工信人才PostgreSQL管理员认证证书!
  • 6月1日,汇华学院12名学生参加工信人才PostgreSQL认证考试!
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • 5月17日,PolarDB开源数据库沙龙(青岛站)成功举办-CUUG
  • PG技术大讲堂 - 第55讲:通义大模型+向量数据库实现AI的外脑
  • PostgreSQL技术大讲堂 - 第53讲:老陈与德哥开讲PostgreSQL 17新特性
  • PostgreSQL技术大讲堂 - 第54讲:如何在上线前精准评估PG SQL性能
  • 有大奖!第13届PostgreSQL中国技术大会:聚焦云端创新,汇聚智慧共享
  • 阿里云PolarDB再获顶会SIGMOD最佳论文奖
  • 阿里云斩获国际数据库顶会ICDE 2024最佳论文
  • 腾讯云数据库TDSQL荣获深圳市科技进步奖一等奖
  • Oracle OCP认证还值得考吗 考OCP证书需要门槛吗
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • PostgreSQL技术大讲堂 - 第52讲:与德哥背后的男人们聊如何实现自动性能调优
  • 5月16日,开源驱动教育创新研讨会(青岛站)成功举办-CUUG
  • oracle ocp证书有效期多长时间
  • PolarDB开源社区走进金蝶,开源数据库沙龙成功举办!
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!