📊 PostgreSQL聚合表达式完全指南：让数据开口说话

聚合表达式就像数据世界的"显微镜"🔬和"望远镜"🔭，它们既能聚焦细节，又能把握全局。通过聚合，我们可以将海量数据浓缩成有价值的洞察，让数据库真正"开口"告诉我们业务背后的故事。

🧩 什么是聚合表达式？

聚合表达式是SQL中的数据压缩器，它接收一堆数据行，输出一个汇总结果。想象你在数一罐糖果：

COUNT() 就像数糖果总数
SUM() 就像计算糖果总重量
AVG() 就像计算每颗糖果平均重量
MAX()/MIN() 就像找出最大/最小的糖果

📜 聚合表达式语法速查手册

sql

-- 通用格式
聚合函数( [DISTINCT] 表达式 [ORDER BY ...] ) 
[FILTER (WHERE 过滤条件)]

-- 特殊形式
聚合函数(*)  -- 统计所有行
聚合函数(表达式) WITHIN GROUP (ORDER BY ...) -- 有序集聚合

语法组件解析：

核心函数：COUNT, SUM, AVG, MAX, MIN等
DISTINCT：只计算唯一值（去重）
ORDER BY：控制聚合顺序（影响array_agg等）
FILTER：条件聚合的利器
WITHIN GROUP：有序集专属语法

TIP

FILTER子句是条件聚合的瑞士军刀🔧，它比多个CASE语句更高效简洁，特别是在同一个查询中需要多种条件统计时

🧪 聚合表达式工作原理实验室

基础聚合实验

sql

-- 实验1：全校学生统计
SELECT 
    COUNT(*) AS 总人数,               -- 统计所有学生
    AVG(score) AS 平均分,             -- 计算平均分
    MAX(score) AS 最高分,             -- 找出最高分
    MIN(score) AS 最低分              -- 找出最低分
FROM students;

NULL值处理规则

sql

-- 实验2：包含NULL值的数据集
CREATE TABLE scores (id SERIAL, score INT);
INSERT INTO scores (score) VALUES (80), (90), (NULL), (85);

SELECT 
    COUNT(*) AS 总行数,               -- 返回4
    COUNT(score) AS 有效分数数量,      -- 返回3（忽略NULL）
    AVG(score) AS 平均分              -- 计算(80+90+85)/3 ≈ 85
FROM scores;

DISTINCT聚合对比

sql

-- 实验3：城市人口分析
SELECT 
    COUNT(city) AS 所有城市记录数,      -- 包含重复城市
    COUNT(DISTINCT city) AS 唯一城市数, -- 去重计数
    AVG(population) AS 平均人口,        -- 所有城市平均
    AVG(DISTINCT population) AS 去重平均人口 -- 唯一人口值平均
FROM cities;

统计类型	原始数据示例	结果
`COUNT(value)`	[北京,上海,北京,广州,NULL]	4
`COUNT(DISTINCT value)`	[北京,上海,北京,广州,NULL]	3
`AVG(value)`	[100,200,100,300]	175
`AVG(DISTINCT value)`	[100,200,100,300]	200

🚀 高级聚合技巧实战

1. ORDER BY控制聚合顺序

sql

-- 将学生名字按字母顺序拼接
SELECT string_agg(name, ' → ' ORDER BY name) AS 名单 
FROM students;

-- 结果示例：'张三 → 李四 → 王五'

2. FILTER条件聚合

sql

-- 多维度销售数据分析
SELECT
    COUNT(*) AS 总订单数,
    SUM(amount) FILTER (WHERE category = '电子产品') AS 电子产品销售额,
    AVG(amount) FILTER (WHERE year = 2023) AS 2023年平均订单额,
    COUNT(*) FILTER (WHERE amount > 1000) AS 大额订单数
FROM sales;

3. 有序集聚合（统计利器）

sql

-- 考试成绩分布分析
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 第三四分位数,
    mode() WITHIN GROUP (ORDER BY score) AS 众数
FROM exam_results;

有序集聚合应用场景

📐 百分位数：薪资分布、成绩分布分析
🏆 排名计算：销售冠军、绩效排名
📊 统计指标：中位数、众数等非平均值统计

⚠️ 聚合表达式使用禁区

sql

-- SELECT列表中使用 ✅
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- HAVING子句中使用 ✅
SELECT department FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;  -- 筛选成员超过5人的部门

sql

-- WHERE子句中直接使用 ❌
SELECT department FROM employees 
WHERE AVG(salary) > 5000  -- 错误！聚合不能在WHERE中使用
GROUP BY department;

-- GROUP BY子句中使用 ❌
SELECT department FROM employees 
GROUP BY AVG(salary);  -- 错误！聚合不能作为分组依据

CAUTION

聚合表达式只能出现在SELECT列表和HAVING子句中！在其他位置使用会导致查询失败，因为聚合发生在数据筛选和分组之后

💼 三大业务场景实战案例

场景1：电商销售仪表盘

业务背景：电商平台需要实时监控关键指标，包括：

当日销售总额
各品类销售占比
高价值客户（单笔>5000）订单数

sql

SELECT
    COUNT(*) AS 总订单数,
    SUM(amount) AS 销售总额,
    -- 使用FILTER进行多条件统计
    SUM(amount) FILTER (WHERE category = '电子产品') AS 电子产品销售额,
    COUNT(*) FILTER (WHERE amount > 5000) AS 高价值订单数,
    -- 使用ROUND和除法计算占比
    ROUND(100.0 * SUM(amount) FILTER (WHERE category = '美妆') / SUM(amount), 2) 
        AS 美妆品类销售占比
FROM orders
WHERE order_date = CURRENT_DATE;

处理前原始数据片段：

order_id	amount	category	order_date
1001	3200	电子产品	2023-10-01
1002	1500	美妆	2023-10-01
1003	6500	奢侈品	2023-10-01
1004	800	电子产品	2023-10-01

聚合结果：

总订单数	销售总额	电子产品销售额	高价值订单数	美妆品类销售占比
4	12000	4000	1	12.50

效果：通过单次查询获取多维度实时指标，支撑管理决策 🚀

场景2：人力资源绩效分析

业务背景：HR部门需要分析：

各部门平均薪资
不同职级的薪资差异
高绩效员工（评分>90）占比

sql

SELECT
    department AS 部门,
    COUNT(*) AS 员工数,
    ROUND(AVG(salary), 0) AS 平均薪资,
    -- 使用DISTINCT避免重复职级干扰
    COUNT(DISTINCT job_level) AS 职级数量,
    -- FILTER计算高绩效比例
    ROUND(100.0 * COUNT(*) FILTER (WHERE performance > 90) / COUNT(*), 1) 
        AS 高绩效员工占比
FROM employees
GROUP BY department
ORDER BY 平均薪资 DESC;

处理前原始数据片段：

employee_id	department	salary	job_level	performance
E001	技术部	15000	P7	95
E002	技术部	18000	P7	88
E003	市场部	12000	P6	92
E004	财务部	10000	P5	85

聚合结果：

部门	员工数	平均薪资	职级数量	高绩效员工占比
技术部	2	16500	1	50.0
市场部	1	12000	1	100.0
财务部	1	10000	1	0.0

效果：精准识别高价值部门，优化薪酬结构 💰

场景3：教育机构成绩分析

业务背景：学校需要深度分析考试成绩：

各分数段分布
班级排名情况
关键百分位数（中位数等）

sql

-- 使用有序集聚合计算关键统计指标
SELECT
    class_id AS 班级,
    COUNT(*) AS 学生数,
    MIN(score) AS 最低分,
    MAX(score) AS 最高分,
    ROUND(AVG(score), 1) AS 平均分,
    -- 计算百分位数
    percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS 中位数,
    percentile_cont(0.25) WITHIN GROUP (ORDER BY score) AS 第一四分位数,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY score) AS 第三四分位数
FROM exam_scores
GROUP BY class_id;

处理前原始数据片段：

student_id	class_id	score
S101	C1	85
S102	C1	92
S103	C1	78
S201	C2	88
S202	C2	95

聚合结果：

班级	学生数	最低分	最高分	平均分	中位数	第一四分位数	第三四分位数
C1	3	78	92	85.0	85	81.5	88.5
C2	2	88	95	91.5	91.5	89.75	93.25

效果：超越简单平均分，揭示成绩分布本质特征 🎯

📌 核心要点总结

聚合本质：多行输入 → 单行输出，数据压缩的魔法
NULL处理：大多数聚合函数自动忽略NULL值
DISTINCT：去重统计的利器
FILTER子句：条件聚合的最佳实践 ✅
有序集聚合：百分位数、中位数等统计指标的神器
使用禁区：只能在SELECT和HAVING中使用

聚合表达式让数据从"记录者"变为"讲述者" 🎤。掌握这些技巧，你就能让海量数据开口讲述业务故事，发现隐藏在数字背后的真相！

📊 PostgreSQL聚合表达式完全指南：让数据开口说话 ​

🧩 什么是聚合表达式？ ​

📜 聚合表达式语法速查手册 ​

语法组件解析： ​

🧪 聚合表达式工作原理实验室 ​

基础聚合实验 ​

NULL值处理规则 ​

DISTINCT聚合对比 ​

🚀 高级聚合技巧实战 ​

1. ORDER BY控制聚合顺序 ​

2. FILTER条件聚合 ​

3. 有序集聚合（统计利器） ​

⚠️ 聚合表达式使用禁区 ​

💼 三大业务场景实战案例 ​

场景1：电商销售仪表盘 ​

场景2：人力资源绩效分析 ​

场景3：教育机构成绩分析 ​

📌 核心要点总结 ​

📊 PostgreSQL聚合表达式完全指南：让数据开口说话

🧩 什么是聚合表达式？

📜 聚合表达式语法速查手册

语法组件解析：

🧪 聚合表达式工作原理实验室

基础聚合实验

NULL值处理规则

DISTINCT聚合对比

🚀 高级聚合技巧实战

1. ORDER BY控制聚合顺序

2. FILTER条件聚合

3. 有序集聚合（统计利器）

⚠️ 聚合表达式使用禁区

💼 三大业务场景实战案例

场景1：电商销售仪表盘

场景2：人力资源绩效分析

场景3：教育机构成绩分析

📌 核心要点总结