聚合函数

聚合函数概述

INFO

什么是聚合函数？聚合函数是一类特殊的函数，它们能够从多行数据计算出单个结果值。PostgreSQL 提供了丰富的聚合函数，帮助我们对数据进行统计分析。

PostgreSQL 中常用的聚合函数包括：

函数名	描述	示例	返回值示例
count	计算行数	count(*)	42（共 42 行）
sum	求和	sum(price)	199.99
avg	平均值	avg(temp)	24.5
max	最大值	max(height)	185
min	最小值	min(weight)	45.3

示例数据

在本章节中，我们将使用一个简化的气象数据表weather来演示聚合函数的使用：

表结构

CREATE TABLE weather (
    city        varchar(80),
    temp_lo     int,  -- 最低温度
    temp_hi     int,  -- 最高温度
    prcp        real, -- 降水量
    date        date
);

示例数据

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather VALUES ('Hayward', 37, 54, 0.0, '1994-11-29');

下表展示了weather表中的所有数据：

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27
San Francisco	43	57	0.0	1994-11-29
Hayward	37	54	0.0	1994-11-29

基本用法示例

让我们通过一个简单的气象数据表示例来理解聚合函数的使用：

-- 查找所有城市中最低温度的最高值
SELECT max(temp_lo) FROM weather;

执行结果：

 max
-----
  46
(1 row)

这个查询从weather表中找出了所有最低温度(temp_lo)记录中的最大值。

常见错误与解决方案

初学者常常会犯的一个错误是尝试在 WHERE 子句中使用聚合函数：

-- 这是错误的用法
SELECT city FROM weather WHERE temp_lo = max(temp_lo);  -- 错误❌

聚合函数不能在 WHERE 子句中使用，因为 WHERE 子句是用来确定哪些行将被包含在聚合计算中的，必须在计算聚合函数之前执行。

正确的解决方案：使用子查询

-- 正确的做法是使用子查询
SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

执行结果：

     city
---------------
 San Francisco
(1 row)

子查询是一个独立的计算，它可以单独计算出最大值，然后外部查询使用这个结果进行比较。

GROUP BY：分组聚合

GROUP BY子句与聚合函数结合使用，能够对数据进行分组统计：

分组聚合可视化

GROUP BY 的工作原理如下图所示：

-- 按城市分组，计算每个城市的记录数和最高的最低温度
SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

执行结果：

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

上面的查询为每个不同的城市生成一行结果，显示该城市的记录数量和最低温度的最大值。

TIP

理解 GROUP BY GROUP BY 子句将具有相同分组列值的行组合在一起，为每组生成一个汇总行。

HAVING：过滤分组结果

当我们需要对分组后的结果进行过滤时，可以使用HAVING子句：

HAVING 与 WHERE 的区别

• WHERE 筛选原始表中的行
• HAVING 筛选分组后聚合计算的结果

这就像是"二级过滤"：先用 WHERE 过滤原始数据，再用 HAVING 过滤聚合结果。

-- 只显示最低温度最大值小于40度的城市
SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

执行结果：

  city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

WHERE 与 HAVING 的区别

IMPORTANT

• WHERE：在分组和聚合计算之前过滤行，控制哪些行参与聚合计算
• HAVING：在分组和聚合计算之后过滤结果行，基于聚合结果进行过滤

综合示例

-- 查询名称以"S"开头的城市的统计信息
SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'  -- 在分组前过滤以"S"开头的城市
    GROUP BY city;

执行结果：

     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

LIKE 运算符 LIKE运算符用于模式匹配：

• % 匹配任意长度的字符序列
• _ 匹配任意单个字符

FILTER 子句：按聚合函数过滤

PostgreSQL 提供了更精细的控制方式：FILTER子句，它可以为每个聚合函数单独指定过滤条件：

FILTER 子句的工作原理

FILTER 子句允许为每个聚合函数定义自己的过滤条件，而不影响整个查询的其他部分。

以下图表展示了 FILTER 的工作方式：

FILTER 实际上为聚合函数创建了"虚拟子集"，而不改变原始分组。

-- 使用FILTER子句计算温度低于45度的记录数
SELECT city,
       count(*) FILTER (WHERE temp_lo < 45) AS cool_days_count,
       max(temp_lo)
    FROM weather
    GROUP BY city;

执行结果：

     city      | cool_days_count | max
---------------+----------------+-----
 Hayward       |              1 |  37
 San Francisco |              1 |  46
(2 rows)

FILTER 的具体应用示例

在上例中，我们能看到每个城市的两种数据：

1. cool_days_count：温度低于 45 度的天数
2. max(temp_lo)：该城市记录的最高"最低温度"

对于 San Francisco，它有 2 条记录，但只有 1 条记录的温度低于 45 度，所以 cool_days_count 为 1。对于 Hayward，它只有 1 条记录且温度为 37 度，所以 cool_days_count 也为 1。

关于 FILTER 的兼容性注意事项

FILTER 子句是 PostgreSQL 9.4 版本引入的功能，不是标准 SQL 的一部分，因此在其他数据库系统中可能不适用。如果需要跨数据库兼容，可以使用 CASE 表达式达到类似效果：

SELECT city,
       SUM(CASE WHEN temp_lo < 45 THEN 1 ELSE 0 END) AS cool_days_count,
       max(temp_lo)
    FROM weather
    GROUP BY city;

实用技巧

1. 性能优化：尽可能在WHERE子句中应用过滤条件，而不是在HAVING中，这样可以减少需要分组和计算聚合的行数。

2. 命名聚合结果：使用 AS 关键字给聚合结果命名，提高可读性。

   SELECT city, count(*) AS record_count, max(temp_lo) AS max_low_temp
   FROM weather
   GROUP BY city;

3. 组合聚合函数：可以在同一查询中使用多个聚合函数。

   SELECT city, min(temp_lo) AS min_low, max(temp_lo) AS max_low,
          avg(temp_lo) AS avg_low
   FROM weather
   GROUP BY city;

聚合函数是数据分析的强大工具，掌握它们的使用可以帮助您从数据中提取有价值的统计信息，而无需编写复杂的程序。