聚合表达式

什么是聚合表达式？

聚合表达式是SQL中的一种特殊表达式，它可以将多行数据汇总成单个结果值。简单来说，聚合函数就像一个数据"收集器"，它接收多个输入值，然后输出一个汇总结果。

常见的聚合操作包括：

• 计算总和（sum）
• 求平均值（avg）
• 计数（count）
• 查找最大/最小值（max/min）

聚合表达式的基本语法

PostgreSQL中聚合表达式的语法有几种形式：

-- 基本形式
aggregate_name (expression [, ...] [order_by_clause]) [FILTER (WHERE filter_clause)]

-- 显式指定ALL（默认行为）
aggregate_name (ALL expression [, ...] [order_by_clause]) [FILTER (WHERE filter_clause)]

-- 处理不同值
aggregate_name (DISTINCT expression [, ...] [order_by_clause]) [FILTER (WHERE filter_clause)]

-- 特殊形式，常用于count(*)
aggregate_name (*) [FILTER (WHERE filter_clause)]

-- 有序集聚合
aggregate_name ([expression [, ...]]) WITHIN GROUP (order_by_clause) [FILTER (WHERE filter_clause)]

其中：

• aggregate_name：聚合函数的名称（如sum、avg、count等）
• expression：要进行聚合的表达式（通常是列名）
• order_by_clause：可选的排序子句
• filter_clause：可选的过滤条件

聚合表达式的工作原理

基本聚合

最简单的聚合是对整个结果集中的某一列进行操作。例如：

-- 计算所有员工的平均工资
SELECT AVG(salary) FROM employees;

-- 计算销售总额
SELECT SUM(amount) FROM sales;

-- 统计客户总数
SELECT COUNT(*) FROM customers;

处理NULL值

大多数聚合函数会自动忽略NULL值：

-- 假设表中有10行，其中3行的score为NULL
SELECT 
    COUNT(*) AS 总行数,           -- 返回10
    COUNT(score) AS 非空值数量     -- 返回7
FROM students;

DISTINCT聚合

如果想只对不同的值进行聚合，可以使用DISTINCT关键字：

-- 计算有多少种不同的职位
SELECT COUNT(DISTINCT job_title) FROM employees;

-- 计算不同城市的平均人口
SELECT AVG(DISTINCT population) FROM cities;

下面是不使用和使用DISTINCT的对比示例：

数据	COUNT(value)	COUNT(DISTINCT value)
1,2,2,3,3,3,4	7	4
A,A,B,C,C,C,NULL,NULL	6	3

使用ORDER BY控制聚合顺序

某些聚合函数（如array_agg和string_agg）的结果会受到输入行顺序的影响。这时，我们可以使用ORDER BY子句来指定所需的顺序：

-- 将所有名字按字母顺序连接成一个逗号分隔的字符串
SELECT string_agg(name, ', ' ORDER BY name) FROM students;

-- 将所有值组成一个降序排列的数组
SELECT array_agg(value ORDER BY value DESC) FROM samples;

示例：

-- 假设有以下数据
WITH names (name) AS (
    VALUES ('张三'), ('李四'), ('王五'), ('赵六')
)
-- 不指定顺序（结果可能随机）
SELECT string_agg(name, ', ') FROM names;
-- 可能的结果: 张三, 王五, 李四, 赵六

-- 指定按名字排序
SELECT string_agg(name, ', ' ORDER BY name) FROM names;
-- 结果: 李四, 王五, 张三, 赵六

使用FILTER过滤聚合输入

FILTER子句让我们可以在同一个查询中创建多个条件聚合，而不需要重复写WHERE条件：

-- 在一个查询中统计不同类别的员工数量
SELECT
    COUNT(*) AS 总员工数,
    COUNT(*) FILTER (WHERE department = '技术') AS 技术部员工数,
    COUNT(*) FILTER (WHERE department = '销售') AS 销售部员工数,
    COUNT(*) FILTER (WHERE salary > 10000) AS 高薪员工数
FROM employees;

这比使用多个子查询或CASE表达式更加简洁和高效。

有序集聚合

有序集聚合是一类特殊的聚合函数，它们需要输入行按特定顺序排序才能正确计算结果。这些函数使用WITHIN GROUP (ORDER BY ...)语法。

常见的有序集聚合函数包括：

• percentile_cont：连续百分位数
• percentile_disc：离散百分位数
• rank、dense_rank等排名函数

示例：

-- 计算收入的中位数（第50百分位）
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;

-- 计算多个百分位数
SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY score) AS "第一四分位数",
    percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS "中位数",
    percentile_cont(0.75) WITHIN GROUP (ORDER BY score) AS "第三四分位数"
FROM exam_results;

在有序集聚合中：

• WITHIN GROUP中的表达式为每一行计算一次，然后排序
• WITHIN GROUP前面的参数（如0.5）称为直接参数，每次聚合调用只计算一次

聚合表达式的使用限制

聚合表达式只能出现在以下位置：

• SELECT语句的结果列表中
• HAVING子句中

它们不能出现在WHERE、GROUP BY或其他子句中，因为这些子句在逻辑上是在形成聚合结果之前执行的。

-- 正确：在SELECT列表中使用聚合
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- 正确：在HAVING子句中使用聚合
SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 5;

-- 错误：在WHERE子句中使用聚合
SELECT department FROM employees WHERE AVG(salary) > 5000 GROUP BY department;

子查询中的聚合表达式

当聚合表达式出现在子查询中时，通常会在子查询的行上计算。但有一个例外：如果聚合的参数只包含外部查询的变量，则该聚合会在外部查询的行上计算。

小结

聚合表达式是数据分析中的强大工具，它们可以：

• 汇总大量数据，生成有用的统计信息
• 使用DISTINCT处理唯一值
• 通过ORDER BY控制特定聚合的排序
• 使用FILTER有条件地应用聚合
• 通过有序集聚合计算统计量如百分位数和排名

通过合理使用这些功能，我们可以编写出简洁而强大的数据分析查询。