聚合表达式

概述

到目前为止，我们已经学习了如何使用 GROUP BY 子句在不同分组级别上计算最小值、最大值、求和以及计数等聚合结果。然而，在 SQL 中还有一种无需使用 GROUP BY 即可计算聚合值的方法。

1CREATE TABLE temperatures (
2    day DATE,
3    city VARCHAR(30),
4    temperature INT
5);
6
7INSERT INTO temperatures (day, city, temperature) 
8VALUES 
9    ('2021-01-01', 'NY', 10),
10    ('2021-01-02', 'NY', 12),
11    ('2021-01-03', 'NY', 13),
12    ('2021-01-04', 'NY', 14),
13    ('2021-01-05', 'NY', 18),
14    ('2021-01-06', 'NY', 15),
15    ('2021-01-07', 'NY', 16),
16    ('2021-01-08', 'NY', 17),
17    
18    ('2021-01-01', 'LA', 22),
19    ('2021-01-02', 'LA', 21),
20    ('2021-01-03', 'LA', 19),
21    ('2021-01-04', 'LA', 22),
22    ('2021-01-05', 'LA', 25),
23    ('2021-01-06', 'LA', 27),
24    ('2021-01-07', 'LA', 25),
25    ('2021-01-08', 'LA', 27)
26;
27
28SELECT * FROM temperatures;

1SELECT 
2    MAX(temperature) 
3FROM 
4    temperatures;

该查询只返回一行结果——所有城市中历史最高温度。如果我们希望将每一天的温度与该城市自身的最高温度进行比较，则需要在 GROUP BY 子句中加入城市字段：

1SELECT 
2    city, 
3    MAX(temperature) 
4FROM 
5    temperatures 
6GROUP BY 
7    city;

通过 GROUP BY 我们定义了两个分组——每个不同的 city 值对应一个组。数据库会分别计算每组的最大温度，最终得到两行结果，分别表示每个城市的最高温度。

此时，我们可能想利用这个聚合结果与原始数据中的每日温度做差值运算。但如果我们直接尝试如下写法，将会失败：

1SELECT 
2    city, 
3    temperature - MAX(temperature) as diff_from_hottest
4FROM 
5    temperatures 
6GROUP BY 
7    city;

另一种方法是将聚合查询的结果与原表进行连接操作：

1WITH hottest AS (
2    SELECT 
3        city, 
4        MAX(temperature) as temperature
5    FROM 
6        temperatures 
7    GROUP BY 
8        city
9)
10SELECT 
11    t.*,
12    h.temperature as hottest_temperature,
13    t.temperature - h.temperature AS diff_from_hottest
14FROM
15    temperatures t
16    JOIN hottest h ON t.city = h.city
17ORDER BY
18    t.city,
19    t.day;

而更简洁的方式是使用窗口函数：

1SELECT 
2    *,
3    MAX(temperature) OVER (PARTITION BY city) AS max_temperature_at_city
4FROM 
5    temperatures;

现在结果中包含了一个新列 max_temperature_at_city，其值为各城市内的最高温度。

在聚合函数 MAX 后添加 OVER() 关键字后，它就变成了一个聚合表达式（或称窗口函数）。表达式 MAX(temperature) OVER (PARTITION BY city) 的结果即为每个城市的最高气温。

我们使用 PARTITION BY 子句来指定用于计算聚合表达式的分组方式，这与 GROUP BY 的作用类似。在此例中，我们希望按城市分组，因此以 city 列作为分区依据。

需要注意的是：尽管我们使用了聚合函数 MAX，但查询结果仍然保留了原始表中的所有行。数据库是在原有数据集的基础上，额外为每一行计算出对应分区的聚合值。这是传统 GROUP BY 聚合与窗口聚合表达式之间的核心区别。

MAX() 与 MAX() OVER() 的对比

为了完成我们的分析报告，我们可以进一步添加一个表达式，用当前温度减去该城市的最高温度，从而得出温差：

1SELECT 
2    *,
3    temperature - MAX(temperature) OVER (PARTITION BY city) AS diff_from_hottest
4FROM 
5    temperatures;

此查询利用聚合表达式的结果，计算出每一天温度与其所在城市历史最高温度之间的差值。整个语句非常简洁，无需子查询、公共表表达式或连接操作。

按表达式分区

PARTITION BY 后面不仅可以接列名，还可以是一个表达式。例如，我们可以从日期中提取年份，并新增一列表示每年的最高温度：

1SELECT 
2    *,
3    EXTRACT('YEAR' FROM day) AS year,
4    MAX(temperature) OVER (PARTITION BY EXTRACT('YEAR' FROM day)) AS max_temperature_in_year
5FROM 
6    temperatures;

按多个表达式分区

分区并不局限于单个列或表达式，也可以基于多个列或表达式进行。例如，我们可以同时按城市和年份分区，添加一列表示“当年该城市的最高温度”：

1SELECT 
2    *,
3    EXTRACT('YEAR' FROM day) AS year,
4    MAX(temperature) OVER (
5        PARTITION BY city, EXTRACT('YEAR' FROM day)
6    ) AS max_temperature_in_city_at_year
7FROM 
8    temperatures;

以下两个查询效果相同，均表示在整个数据集中计算全局最大值：

1SELECT 
2    *,
3    EXTRACT('YEAR' FROM day) AS year,
4    MAX(temperature) OVER (PARTITION BY 1) AS max_temperature
5FROM 
6    temperatures;

1SELECT 
2    *,
3    EXTRACT('YEAR' FROM day) AS year,
4    MAX(temperature) OVER () AS max_temperature
5FROM 
6    temperatures;

其中 OVER () 表示不分区，对全部数据进行聚合计算。

多个聚合表达式

一条查询可以同时包含多个针对不同分组的聚合表达式。例如，我们可以在一个查询中同时获取：每个城市的最高温度、最低温度，以及全地区的历史最高和最低温度：

1SELECT 
2    *,
3    MAX(temperature) OVER () AS max_temperature,
4    MIN(temperature) OVER () AS min_temperature,
5    MAX(temperature) OVER (PARTITION BY city) AS max_temperature_in_city,
6    MIN(temperature) OVER (PARTITION BY city) AS min_temperature_in_city
7FROM 
8    temperatures;

为了提高可读性和复用性，可以使用 WINDOW 子句定义命名窗口：

1SELECT 
2    *,
3    MAX(temperature) OVER all_rows AS max_temperature,
4    MIN(temperature) OVER all_rows AS min_temperature,
5    MAX(temperature) OVER city_rows AS max_temperature_in_city,
6    MIN(temperature) OVER city_rows AS min_temperature_in_city
7FROM 
8    temperatures
9WINDOW 
10    city_rows AS (PARTITION BY city),
11    all_rows AS (PARTITION BY 1);