在对有序数据子集执行聚合时,WITHIN GROUP
子句在 PostgreSQL 和 TimescaleDB 中非常有用。它本质上允许您将多行数据透视到一行。此子句简化了许多操作,这些操作以前只能通过使用 窗口函数 与 公用表表达式 或 CTE 结合使用来实现。
WITHIN GROUP
之前的表达式称为直接参数,每个聚合调用只计算一次,而不是为输入中的每一行都计算一次。直接参数通常用于百分位数分数和其他聚合计算,当您每次计算使用一个值时,这些计算是有意义的。WITHIN GROUP
子句中的记录需要一组由 ORDER BY
子句组织的行。
以下是 WITHIN GROUP
语法
SELECT <aggregate_function> WITHIN GROUP (ORDER BY <sort_expression>) FROM <table>;
查找一系列数据中最频繁的值
将 WITHIN GROUP 与 GROUP BY 一起使用
假设您在名为 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
的一些示例。
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 并不是很有用。 在此查询中,对数据集中的城市进行分组,以便结果在单独的行中包含每个城市的 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 资源