了解 PostgreSQL WITHIN GROUP

Abstract shapes over a dark background.

在对有序数据子集执行聚合时,WITHIN GROUP 子句在 PostgreSQL 和 TimescaleDB 中非常有用。它本质上允许您将多行数据透视到一行。此子句简化了许多操作,这些操作以前只能通过使用 窗口函数公用表表达式 或 CTE 结合使用来实现。

WITHIN GROUP 之前的表达式称为直接参数,每个聚合调用只计算一次,而不是为输入中的每一行都计算一次。直接参数通常用于百分位数分数和其他聚合计算,当您每次计算使用一个值时,这些计算是有意义的。WITHIN GROUP 子句中的记录需要一组由 ORDER BY 子句组织的行。

以下是 WITHIN GROUP 语法

SELECT <aggregate_function> WITHIN GROUP (ORDER BY <sort_expression>) FROM <table>;

示例

假设您在名为 city_data 的数据库表中有以下数据

id

城市

温度

降水量

17

2021-09-04

迈阿密

68.36

0.00

19

2021-09-05

迈阿密

72.50

0.00

11

2021-09-01

迈阿密

65.30

0.28

13

2021-09-02

迈阿密

64.40

0.79

15

2021-09-03

迈阿密

68.18

0.47

18

2021-09-04

亚特兰大

67.28

0.00

20

2021-09-05

亚特兰大

68.72

0.00

12

2021-09-01

亚特兰大

63.14

0.20

14

2021-09-02

亚特兰大

62.60

0.59

16

2021-09-03

亚特兰大

62.60

0.39

以下是在 SQL 查询中使用 WITHIN GROUP 的一些示例。

将 WITHIN GROUP 与百分位数函数一起使用

PostgreSQL 和 TimescaleDB 有两个经常与 WITHIN GROUP 一起使用的统计窗口函数。 percentile_cont 和 percentile_disc 都可以计算百分位数,但它们的计算方式不同。

以下是如何使用 percentile_disc 进行聚合的示例

SELECT
	percentile_disc(0.5) WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果是

percentile_disc

65.30

请注意,此结果是数据中存在的值。 WITHIN GROUP 子句指定百分位数函数使用的数值。 在这种情况下,percentile_disc 从数据集中返回一个离散值。 另一方面,percentile_cont 对值进行插值。 以下是使用 percentile_cont 的查询

SELECT
	percentile_cont(0.5) WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果是

percentile_cont

66.28999999999999

查找一系列数据中最频繁的值

mode() 函数返回 WITHIN GROUP 指定的数据集中出现次数最多的特定值。 以下是在 city_data 数据集上使用 mode() 的示例查询。

SELECT
	mode() WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果如下,因为 62.60 是温度列中唯一出现两次的值

mode

62.60

查找一系列数据中值的排名

如果想知道 city_data 表中的特定温度在 WITHIN GROUP 子句中指定的组中的排名,可以使用 rank 函数。 以下是一个示例

SELECT
	rank(72.5) WITHIN GROUP (
	ORDER BY temperature)
FROM city_data;

结果如下

rank

10

温度 72.5 是表中的最高值,因为默认情况下它按升序排序;该值最终位于值列表的末尾。 以下查询给出了不同的结果

SELECT
	rank(72.5) WITHIN GROUP (
	ORDER BY temperature DESC)
FROM city_data;

rank

1

将 WITHIN GROUP 与 GROUP BY 一起使用

在整个表中使用 WITHIN GROUP 并不是很有用。 在此查询中,对数据集中的城市进行分组,以便结果在单独的行中包含每个城市的 50% 分位数

SELECT
	city,
	percentile_cont(0.5) WITHIN GROUP (
ORDER BY
	temperature)
FROM
	city_data
GROUP BY
	city;

结果

城市

percentile_cont

亚特兰大

63.14

迈阿密

68.18

后续步骤

要详细了解 WITHIN GROUP 以及如何在 PostgreSQL 中使用它,请参阅 PostgreSQL 文档中的聚合表达式有序集聚合函数假设集聚合函数

有关如何在自己的查询中使用 WITHIN GROUP 的更多示例,请参阅以下 Timescale 资源