条件聚合是什么
条件聚合指在同一次分组里按不同条件分别统计：同一批订单同时算出「全部单量」「已支付单量」「已支付金额」。PostgreSQL 从 9.4 起为聚合函数提供 FILTER (WHERE 条件) 子句，只有满足条件的行才进入这个聚合；MySQL 没有该子句，用 CASE WHEN 或布尔求和达到同样结果，写法更长但语义等价。
基本信息
| 适用平台 | PostgreSQL 9.4+ 原生 FILTER；MySQL 5.7/8.0 用 CASE 替代 |
|---|---|
| 语法 | 聚合函数(表达式) FILTER (WHERE 条件) |
| MySQL 等价 | 聚合函数(CASE WHEN 条件 THEN 表达式 END) |
| 权限要求 | 普通查询权限，与普通聚合相同 |
典型使用场景
- 一份数据出多个口径：报表里同时展示总单量与各状态单量
- 条件求和：只累计已支付金额，未支付不计入
- 条件均值：只让有效样本参与平均，缺测数据不拉低结果
- 条件去重计数：统计某状态下出现过的独立用户数
- 避免为每个条件各写一遍 GROUP BY 子查询再 JOIN 拼宽表
PostgreSQL FILTER
PostgreSQL FILTER
SELECT user_id,
COUNT(*) AS total, -- 全部订单
COUNT(*) FILTER (WHERE status = 'paid') AS paid_cnt, -- 已支付单量
SUM(amount) FILTER (WHERE status = 'paid') AS paid_amount, -- 已支付金额
AVG(amount) FILTER (WHERE amount > 0) AS avg_valid -- 只有有效金额参与均值
FROM orders
GROUP BY user_id;MySQL 等价写法
MySQL 等价写法
SELECT user_id,
COUNT(*) AS total, -- 全部订单
SUM(status = 'paid') AS paid_cnt, -- 布尔求和：TRUE 记为 1
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount,
AVG(CASE WHEN amount > 0 THEN amount END) AS avg_valid -- 省略 ELSE，NULL 自动被忽略
FROM orders
GROUP BY user_id;两库写法对照
| 需求 | PostgreSQL | MySQL |
|---|---|---|
| 条件计数 | COUNT(*) FILTER (WHERE 条件) | SUM(条件) 或 COUNT(CASE WHEN 条件 THEN 1 END) |
| 条件求和 | SUM(x) FILTER (WHERE 条件) | SUM(CASE WHEN 条件 THEN x ELSE 0 END) |
| 条件均值 | AVG(x) FILTER (WHERE 条件) | AVG(CASE WHEN 条件 THEN x END)，不要补 ELSE 0 |
| 条件去重计数 | COUNT(DISTINCT x) FILTER (WHERE 条件) | COUNT(DISTINCT CASE WHEN 条件 THEN x END) |
FILTER 可以和 DISTINCT 组合，例如 COUNT(DISTINCT x) FILTER (WHERE …)，也能用在窗口聚合上。它只决定哪些行进入这一个聚合函数，不能代替 WHERE 做整行的预过滤，两者可以叠加。
MySQL 的 SUM(布尔表达式) 依赖比较结果隐式转成 1/0，写法短；CASE 写法更长但意图直白。同一份报表里统一一种风格，避免交接时误读。
条件聚合适合报表宽表：一次扫描就把多个口径算完，比「多次查询再拼结果」省 IO 也少一份中间数据。但条件一多（十几个口径）语句会又长又难维护，可把固定口径沉淀成视图或汇总表。