FILTER 子句
同一张订单表上，报表往往要同时给出「总订单数、已付款数、已退款数、已付款金额、平均客单价」——如果每个指标写一条 SQL，表就要被扫五遍。PostgreSQL 9.4 起支持的 FILTER 子句解决了这个问题：让每个聚合函数自带一个 WHERE 条件，count(*) FILTER (WHERE status = 'paid') 就像给这个聚合计了一道私有门。它语法只多一个括号，但把「多指标报表」从多次扫描变成一次扫描，是写分析类 SQL 时最值得养成的习惯之一。
前置知识
阅读本节前，建议先了解：STDDEV / VARIANCE / REGR_xxx
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 过滤子句 | FILTER (WHERE ...) | 附加在聚合函数后的私有条件，只影响该聚合 |
| 聚合输入集 | Aggregate Input | 进入聚合过渡函数的行集合 |
| 条件聚合 | Conditional Aggregate | 用 FILTER 或 CASE WHEN 只聚合部分行 |
| 行级过滤 | WHERE | 在聚合之前执行，所有聚合共享同一份输入 |
| 组级过滤 | HAVING | 在聚合之后执行，过滤的是分组结果 |
| 单次扫描 | Single Scan | 一条 SQL 只扫一次表，产出多个指标 |
| 伪 NULL 元素 | Spurious NULL | CASE WHEN 缺 ELSE 时产生的中间 NULL，会污染 array_agg |
| 过渡函数 | Transition Function | 聚合内部逐行累积状态的函数，FILTER 决定哪些行进入它 |
三种「过滤」的执行时机完全不同，这是本节的核心：
FROM 表
│
▼
WHERE 条件 ← ① 行级过滤：决定哪些行进入聚合，所有聚合共享
│
▼
GROUP BY 分组
│
▼
聚合函数 FILTER (WHERE 条件) ← ② 聚合级过滤：只决定这一行是否喂给这个聚合
│
▼
HAVING 条件 ← ③ 组级过滤：拿到聚合结果后再筛掉整组
│
▼
ORDER BY / LIMIT| 子句 | 执行阶段 | 作用范围 | 能引用聚合结果吗 |
|---|---|---|---|
WHERE | 聚合前 | 全查询 | 不能 |
FILTER | 聚合过程中（每一行、每一个聚合独立判断） | 单个聚合 | 不能 |
HAVING | 聚合后 | 整组 | 能 |
QUALIFY（PostgreSQL 无此关键字） | 窗口后 | 整行 | — |
一句话记忆：WHERE 决定「谁能进场」，FILTER 决定「每个聚光灯照到谁」，HAVING 决定「谁留下来」。
FILTER 与 CASE WHEN 的关系也必须先理清：
| 写法 | 等价形式 | 结果是否一致 |
|---|---|---|
count(*) FILTER (WHERE c) | count(CASE WHEN c THEN 1 END) | 一致 |
sum(x) FILTER (WHERE c) | sum(CASE WHEN c THEN x END) | 一致 |
avg(x) FILTER (WHERE c) | avg(CASE WHEN c THEN x END) | 一致 |
string_agg(x, ',') FILTER (WHERE c) | string_agg(CASE WHEN c THEN x END, ',') | 一致（string_agg 跳过 NULL） |
array_agg(x) FILTER (WHERE c) | array_agg(CASE WHEN c THEN x END) | 不一致（后者会插入 NULL 元素） |
bool_and(b) FILTER (WHERE c) | bool_and(CASE WHEN c THEN b END) | 一致 |
最小可运行示例：
SELECT
count(*) AS 全部,
count(*) FILTER (WHERE amount > 100) AS 大额,
count(*) FILTER (WHERE amount IS NULL) AS 缺金额
FROM (VALUES (50.00), (200.00), (300.00), (NULL), (80.00)) AS t(amount); 全部 | 大额 | 缺金额
------+------+--------
5 | 2 | 1
(1 row)贯穿本节的示例表：
-- 示例库：demo
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL,
status TEXT NOT NULL, -- pending / paid / refunded / cancelled
amount NUMERIC(12,2), -- 可为 NULL：未定价订单
channel TEXT NOT NULL, -- app / web / miniapp
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
sku TEXT NOT NULL,
qty INTEGER NOT NULL
);语法与用法
-- 基本形态
聚合函数(expr) FILTER (WHERE 条件)
-- 与 DISTINCT 同时出现（DISTINCT 在参数里，FILTER 在后面）
count(DISTINCT user_id) FILTER (WHERE status = 'paid')
-- 与聚合内的 ORDER BY 同时出现（ORDER BY 在括号内，FILTER 在括号外）
string_agg(sku, ', ' ORDER BY created_at) FILTER (WHERE qty > 1)
-- 与窗口函数同时出现（FILTER 在 OVER 之前）
sum(amount) FILTER (WHERE status = 'paid') OVER (PARTITION BY user_id)
-- 一条 SELECT 里多个聚合各带各的 FILTER
SELECT
count(*) AS 订单数,
count(*) FILTER (WHERE status = 'paid') AS 已付款,
sum(amount) FILTER (WHERE status = 'paid') AS 已付款额
FROM orders;位置规则只有一条：FILTER 紧跟在聚合函数的参数括号之后、在 OVER 之前。写成 FILTER 后再接 DISTINCT、或把 FILTER 塞进参数括号里，都是语法错误。
-- 正确
count(DISTINCT user_id) FILTER (WHERE status = 'paid')
-- 错误：DISTINCT 必须写在参数内
-- count(user_id) FILTER (WHERE status = 'paid') DISTINCT详细说明
FILTER 的语义：对每个聚合独立的分流器
FILTER 不是「先过滤行再聚合」，而是「聚合时逐行判断这一行要不要参与」。多个聚合可以各自带不同的 FILTER，优化器仍然只扫描一次表：
SELECT
count(*) AS 订单总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款,
count(*) FILTER (WHERE status = 'refunded') AS 已退款,
count(*) FILTER (WHERE status = 'cancelled') AS 已取消,
count(*) FILTER (WHERE status = 'pending') AS 待付款
FROM orders;四种状态的计数之和正好等于订单总数——这也是一个天然的自检断言：如果不等，说明 status 里出现了你没覆盖的取值。
-- 用 HAVING 找出「有未知状态」的组
SELECT status, count(*)
FROM orders
GROUP BY status;FILTER 与 CASE WHEN：一处不等价
CASE WHEN 是老写法，FILTER 是新写法，绝大多数场景结果一致，但有一个必须记住的例外。
-- 老写法：CASE WHEN 无 ELSE 时产生 NULL，array_agg 会把 NULL 塞进数组
SELECT array_agg(CASE WHEN qty > 1 THEN sku END) AS 老写法
FROM order_items;
-- 新写法：FILTER 版本数组里只有满足条件的元素
SELECT array_agg(sku) FILTER (WHERE qty > 1) AS 新写法
FROM order_items; 老写法 | 新写法
---------------------------------------+------------
{A001,NULL,NULL,NULL,D020,NULL} | {A001,D020}原因在于 CASE WHEN ... END 缺 ELSE 时返回 NULL，而 array_agg 是保留 NULL 的聚合函数（见 STRING_AGG / ARRAY_AGG / JSONB_AGG）。string_agg / sum / avg / count / bool_and 都会跳过 NULL，所以看不出差别。
用 FILTER，不要用 CASE 造中间 NULL
array_agg / json_agg / jsonb_agg / jsonb_object_agg 都保留 NULL。用 CASE WHEN 实现条件聚合时，一旦漏写 ELSE，数组里就会多出一串 NULL，而这类 bug 在开发环境的小数据量下往往看不出来。条件聚合统一用 FILTER。
FILTER 还有一个隐性优势：条件写在聚合外面，读者一眼就能看到「这个指标只统计什么行」，而 CASE WHEN 需要钻进参数里找。
与 sum / avg 的 ELSE 0 陷阱
CASE 写法还有一个比「多出 NULL」更危险的变体：为了「看起来干净」，给它补上 ELSE 0。对 count 和 avg 来说，ELSE 0 会直接算错。
SELECT
count(*) AS 全部行,
count(*) FILTER (WHERE status = 'paid') AS filter_正确,
count(CASE WHEN status = 'paid' THEN 1 END) AS case_无else_正确,
count(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS case_else0_错误
FROM (VALUES ('paid'),('paid'),('paid'),
('canceled'),('canceled'),('canceled')) AS t(status); 全部行 | filter_正确 | case_无else_正确 | case_else0_错误
--------+-------------+------------------+-----------------
6 | 3 | 3 | 6
(1 row)count 统计的是非 NULL 的行数，ELSE 0 让不满足条件的行也返回了一个非 NULL 值（0），于是它们全被计入，count 直接等于总行数。avg 的分母同样被撑大：
SELECT
round(avg(amount) FILTER (WHERE status = 'paid'), 2) AS filter_正确,
round(avg(CASE WHEN status = 'paid' THEN amount END), 2) AS case_无else,
round(avg(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS case_else0_错误,
round(sum(amount) FILTER (WHERE status = 'paid'), 2) AS sum_filter,
round(sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END), 2) AS sum_else0
FROM (VALUES ('paid',100.00),('paid',200.00),('paid',300.00),
('canceled',10.00),('canceled',20.00),('canceled',30.00)) AS t(status, amount); filter_正确 | case_无else | case_else0_错误 | sum_filter | sum_else0
-------------+-------------+-----------------+------------+-----------
200.00 | 200.00 | 100.00 | 600.00 | 600.00
(1 row)| 聚合 | ELSE 0 是否影响结果 | 原因 |
|---|---|---|
count | 影响（3 → 6） | 0 非 NULL，被计入行数 |
avg | 影响（200 → 100） | 分母从 3 行变成 6 行，分子不变 |
sum | 不影响（600 = 600） | 加 0 不改变总和 |
min / max | 影响（若 0 越界） | 0 可能小于真实最小值 |
string_agg | 影响 | 0 会被拼进字符串 |
array_agg | 影响 | 数组里多出一串 0 |
avg 用 CASE 时必须写 ELSE NULL，或者干脆不写 ELSE
avg 的语义是「非 NULL 值的平均」，分母只应包含真正满足条件的行。写成 ELSE 0 会把所有其它行的 0 也算进分母，平均值被系统性压低。ELSE NULL（或省略 ELSE）是唯一正确的写法；而用了 FILTER 就完全不存在这个选择，因为条件根本不在参数里。
结论很直接：CASE 的写错方式有两种（漏 ELSE 多出 NULL、补 ELSE 0 撑大分母），FILTER 一种都没有。
FILTER 的语法禁区
除「条件里不能放聚合」之外，FILTER 还有三条硬约束，每一条都会直接报语法或语义错误。
① 只能跟在聚合函数后面，非聚合函数会报错：
-- 错误：upper 是普通函数，不是聚合
-- SELECT upper(status) FILTER (WHERE id > 1) FROM orders;
-- ERROR: FILTER specified, but upper is not an aggregate function② 不能叠加，一个聚合只能带一个 FILTER：
-- 错误：两个 FILTER 连写
-- SELECT count(*) FILTER (WHERE id > 1) FILTER (WHERE id < 5) FROM orders;
-- ERROR: syntax error at or near "FILTER"需要「两个条件同时满足」时应合并成一个条件：count(*) FILTER (WHERE id > 1 AND id < 5)。
③ 条件里不能出现窗口函数：
-- 错误：窗口函数不允许出现在 FILTER 条件中
-- SELECT count(*) FILTER (WHERE row_number() OVER () > 0) FROM orders;
-- ERROR: window functions are not allowed in FILTER| 写法 | 结果 |
|---|---|
count(*) FILTER (WHERE c1 AND c2) | 合法（合并条件） |
count(*) FILTER (WHERE c1) FILTER (WHERE c2) | syntax error at or near "FILTER" |
upper(x) FILTER (WHERE c) | FILTER specified, but upper is not an aggregate function |
count(*) FILTER (WHERE count(*) > 1) | aggregate functions are not allowed in FILTER |
count(*) FILTER (WHERE row_number() OVER () > 0) | window functions are not allowed in FILTER |
FILTER 内不能出现聚合函数
FILTER 的条件是对单行的判断，不能引用聚合结果：
-- 错误：FILTER 里放聚合
-- SELECT count(*) FILTER (WHERE count(*) > 1) FROM orders;
-- ERROR: aggregate functions are not allowed in FILTER也不允许子查询：
-- 错误：FILTER 里的子查询
-- SELECT count(*) FILTER (WHERE amount > (SELECT avg(amount) FROM orders)) FROM orders;
-- ERROR: aggregate functions are not allowed in FILTER「只保留达到平均值的行」这类需求要分两步：先用 CTE 算出基准，再在外层过滤。
WITH baseline AS (
SELECT avg(amount) AS avg_amount FROM orders WHERE amount IS NOT NULL
)
SELECT count(*) FILTER (WHERE o.amount > b.avg_amount) AS 高于均价,
count(*) AS 全部
FROM orders o
CROSS JOIN baseline b;「按组筛掉整组」用 HAVING，而不是 FILTER：
-- 只保留订单数 >= 3 的用户，正确写法是 HAVING
SELECT user_id, count(*) AS cnt
FROM orders
GROUP BY user_id
HAVING count(*) >= 3;FILTER 与 WHERE：别把组织者当成了入场券
一个常见误解是「FILTER 能减少扫描量，所以可以替代 WHERE」。事实相反：FILTER 不会让优化器少读一行，它只是在已经读到的行上做分流。
-- 写法 A：WHERE 限制输入，扫描器只读 paid 行（若有分区/索引可跳过其它行）
SELECT count(*), sum(amount)
FROM orders
WHERE status = 'paid';
-- 写法 B：FILTER 分流，仍然读全部行，只是把非 paid 行丢给别的聚合
SELECT count(*) FILTER (WHERE status = 'paid') AS paid_cnt,
sum(amount) FILTER (WHERE status = 'paid') AS paid_sum,
count(*) FILTER (WHERE status = 'refunded') AS refund_cnt
FROM orders;两者用途不同：
| 目标 | 正确选择 |
|---|---|
| 只要一个指标的明细/金额 | WHERE（能利用索引或分区裁剪） |
| 一条 SQL 里要多个互斥指标 | FILTER（一次扫描换多个指标） |
| 指标之间不互斥、需要重叠计数 | FILTER（WHERE 做不到） |
| 与其它明细列一起返回 | FILTER + 窗口函数 |
当 WHERE 的条件能命中索引时，写法 A 的扫描代价更低；但如果你本来就要在同一张表上算 5 个互斥指标，写法 B 的一次扫描通常比 5 次带索引扫描更快——尤其是这些指标覆盖了全表大部分行的时候。
FILTER 与 ORDER BY 同时使用
聚合内的 ORDER BY 写在参数括号内部，FILTER 写在参数括号外部，两者顺序不能调换：
-- 每个订单里数量 >= 2 的商品，按加入顺序拼成字符串
SELECT order_id,
string_agg(sku, ', ' ORDER BY id) FILTER (WHERE qty >= 2) AS 多件商品
FROM order_items
GROUP BY order_id; order_id | 多件商品
----------+----------
1 | A001
2 | D020注意：只有订单 1、2 含 qty >= 2 的行，其余订单根本不在 order_items 里，所以没有输出行；但若某订单有行却全被 FILTER 排除，string_agg 返回的是 NULL，不是空字符串。报表里若要显示 -，得包 coalesce：
coalesce(string_agg(sku, ', ' ORDER BY id) FILTER (WHERE qty >= 2), '-') AS 多件商品FILTER 与 GROUP BY / HAVING / ROLLUP 组合
FILTER 只是聚合的一个修饰，因此它可以出现在任何聚合可以出现的位置。最常用的组合是「GROUP BY 提供行维度 + FILTER 提供列维度」：
SELECT
channel,
count(*) AS 总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款,
count(*) FILTER (WHERE status = 'refunded') AS 已退款,
count(*) FILTER (WHERE status = 'cancelled') AS 已取消
FROM orders
GROUP BY channel
ORDER BY channel; channel | 总数 | 已付款 | 已退款 | 已取消
---------+------+--------+--------+--------
app | 4 | 1 | 1 | 0
miniapp | 1 | 1 | 0 | 0
web | 3 | 2 | 0 | 1
(3 rows)HAVING 里同样可以引用带 FILTER 的聚合，用来「按条件聚合的结果筛组」：
SELECT channel,
count(*) AS 总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款
FROM orders
GROUP BY channel
HAVING count(*) FILTER (WHERE status <> 'paid') >= 2
ORDER BY channel; channel | 总数 | 已付款
---------+------+--------
app | 4 | 1
(1 row)ROLLUP / CUBE / GROUPING SETS 也可以与 FILTER 叠加，一次算出明细行与小计行：
SELECT channel,
count(*) AS 总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款
FROM orders
GROUP BY ROLLUP(channel)
ORDER BY channel NULLS LAST; channel | 总数 | 已付款
---------+------+--------
app | 4 | 1
miniapp | 1 | 1
web | 3 | 2
| 8 | 4
(4 rows)最后一行是小计（channel 为 NULL），FILTER 在各层级上照常生效。
一句话区分三个子句的职责：
| 需求 | 用什么 | 原因 |
|---|---|---|
只要 paid 的明细 | WHERE | 聚合前缩小输入，可用索引 |
一条 SQL 里 paid / refunded / cancelled 各一个指标 | FILTER | 一次扫描，各自分流 |
| 筛掉「非付款单数 < 2」的渠道 | HAVING | 过滤的是分组结果 |
FILTER 与有序集聚合
FILTER 可以与 WITHIN GROUP 的有序集聚合（如 percentile_cont）一起用，先按条件筛行，再在剩下的行上算分位数：
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY id) FILTER (WHERE id < 4) AS 过滤后中位数,
percentile_cont(0.5) WITHIN GROUP (ORDER BY id) AS 全部中位数
FROM orders; 过滤后中位数 | 全部中位数
--------------+------------
2 | 4.5
(1 row)书写顺序是：参数 → WITHIN GROUP (...) → FILTER (...)。与普通聚合一样，FILTER 写在最后、OVER 之前。
FILTER 条件是三值逻辑，条件为 NULL 的行会被丢弃
FILTER (WHERE cond) 只在 cond 求值为 TRUE 时放行。若 cond 的结果是 NULL（三值逻辑里的「未知」），该行不会被计入——这和 WHERE 的行为一致：
SELECT
count(*) AS 全部,
count(*) FILTER (WHERE amount > 100) AS 大于100,
count(*) FILTER (WHERE amount IS NULL) AS 金额为NULL,
count(*) FILTER (WHERE NOT (amount > 100)) AS 不满足大于100
FROM (VALUES (50.00),(200.00),(NULL),(300.00),(NULL)) AS t(amount); 全部 | 大于100 | 金额为NULL | 不满足大于100
------+---------+------------+---------------
5 | 2 | 2 | 1amount 为 NULL 的两行：amount > 100 是 NULL，NOT (amount > 100) 也是 NULL，所以两个条件都不收它们。只有 amount IS NULL 这种能明确判定真假的条件才能选中它们。需要把「NULL 也算作不满足」时，必须显式写成 FILTER (WHERE amount > 100 OR amount IS NULL) 或先 coalesce。
FILTER 与窗口函数结合
FILTER 放在 OVER 之前，窗口照常按分区与帧滑动：
SELECT
id, user_id, status, amount,
sum(amount) FILTER (WHERE status = 'paid')
OVER (PARTITION BY user_id ORDER BY created_at
ROWS UNBOUNDED PRECEDING) AS 累计已付款,
count(*) FILTER (WHERE status = 'refunded')
OVER (PARTITION BY user_id) AS 该用户退款总次数
FROM orders;第一条窗口算「到当前订单为止，该用户累计已付款金额」，第二条算「该用户全程退款次数」。FILTER 在窗口里不会改变帧的边界，它只在帧内的行上再做一次筛选。关于窗口聚合的完整用法见 窗口聚合函数。
窗口 + FILTER 仍要显式写帧
OVER (PARTITION BY user_id ORDER BY created_at) 默认帧是 RANGE UNBOUNDED PRECEDING AND CURRENT ROW，在遇到 created_at 相同的行时会一起去包含（peer 行）。需要严格的「逐行累加」时显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
性能：一次扫描 vs 多次扫描
用 UNION ALL 把五个指标写成五条子查询，与一条 FILTER 查询对比计划：
-- 写法 A：五次扫描
SELECT 'paid' AS metric, count(*) FROM orders WHERE status = 'paid'
UNION ALL
SELECT 'refunded', count(*) FROM orders WHERE status = 'refunded'
UNION ALL
SELECT 'cancelled', count(*) FROM orders WHERE status = 'cancelled'
UNION ALL
SELECT 'pending', count(*) FROM orders WHERE status = 'pending'
UNION ALL
SELECT 'total', count(*) FROM orders;
-- 写法 B：一次扫描
SELECT
count(*) FILTER (WHERE status = 'paid') AS paid,
count(*) FILTER (WHERE status = 'refunded') AS refunded,
count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
count(*) FILTER (WHERE status = 'pending') AS pending,
count(*) AS total
FROM orders;写法 B 的计划里只有一个 Aggregate 节点、一次 Seq Scan，四个条件作为 Filter 挂在聚合节点上：
Aggregate
Filter: (status = 'paid'::text)
Filter: (status = 'refunded'::text)
Filter: (status = 'cancelled'::text)
Filter: (status = 'pending'::text)
-> Seq Scan on orders (cost=0.00..30.40 rows=2040 width=4)写法 A 的计划则是五次 Seq Scan 加上 Append / Result 拼接。
FILTER 条件不会下推到扫描节点
WHERE status = 'paid' 会成为扫描节点的 Filter，有机会配合索引做位图扫描；而 FILTER (WHERE status = 'paid') 只挂在 Aggregate 节点上，扫描节点仍需读出全部行。判断依据看计划里 Seq Scan 上有没有 Filter: 行。
这个差别在有索引时非常直观。造一张 30 万行的表，并建一个只覆盖 paid 行的部分索引：
DROP TABLE IF EXISTS big_orders;
CREATE TABLE big_orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status TEXT NOT NULL,
amount NUMERIC(10,2)
);
INSERT INTO big_orders (status, amount)
SELECT (ARRAY['paid','refunded','canceled','pending'])[1 + (i % 4)],
(i % 500)::numeric
FROM generate_series(1, 300000) i;
CREATE INDEX idx_big_paid ON big_orders (id) WHERE status = 'paid';
ANALYZE big_orders;同一个条件，写成 FILTER 与写成 WHERE 的计划完全不同：
-- 写法 A：FILTER —— 部分索引用不上
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FILTER (WHERE status = 'paid') FROM big_orders; Finalize Aggregate (actual time=19.513..20.834 rows=1.00 loops=1)
Buffers: shared hit=1911
-> Gather (actual time=19.404..20.812 rows=2.00 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (actual time=18.210..18.210 rows=1.00 loops=2)
-> Parallel Seq Scan on big_orders (actual time=0.006..10.614 rows=150000.00 loops=2)-- 写法 B：WHERE —— 部分索引只读 paid 行
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM big_orders WHERE status = 'paid'; Aggregate (actual time=14.252..14.253 rows=1.00 loops=1)
Buffers: shared hit=1911 read=206
-> Index Only Scan using idx_big_paid on big_orders (actual time=0.040..11.076 rows=75000.00 loops=1)
Heap Fetches: 75000| 写法 | 计划节点 | 读到的行数 |
|---|---|---|
count(*) FILTER (WHERE status = 'paid') | Parallel Seq Scan（全表） | 300000 |
count(*) WHERE status = 'paid' | Index Only Scan using idx_big_paid | 75000 |
单个指标时不要用 FILTER
FILTER 的价值在于「一条 SQL 同时产出多个互斥指标」。当查询只算一个指标时，FILTER 相比 WHERE 没有任何收益，反而会丢掉索引——WHERE 能命中部分索引、分区裁剪、位图扫描，而 FILTER 只能全表扫描后再分流。判断标准：SELECT 列表里只有一个带 FILTER 的聚合，且没有其它需要全表输入的聚合 → 改写成 WHERE。
大表上可以用 BUFFERS 验证读了多少页：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FILTER (WHERE status = 'paid') AS paid,
count(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;FILTER 只过滤已有行，不补缺失行
这是「留存漏斗」「权限矩阵」类报表的头号陷阱：
-- 想知道每个用户「付款后是否退款」
SELECT user_id,
bool_or(status = 'paid') AS 付过款,
bool_or(status = 'refunded') AS 退过款
FROM orders
GROUP BY user_id;如果某用户从未退款，bool_or(...) 在只有 false 行时返回 false——看起来没问题。但如果该用户的订单完全没有相应状态（例如根本没有 refunded 这条记录），bool_or 返回的是 false（因为有其它行）还是 NULL（因为过滤后无行）取决于写法。用 FILTER 时更容易踩：
-- 陷阱：该用户从无退款记录 → 过滤后零行 → bool_or 返回 NULL，不是 false
SELECT user_id,
bool_or(status = 'refunded') FILTER (WHERE status = 'refunded') AS 退过款
FROM orders
GROUP BY user_id;要得到确定的 true / false，必须先把维度补全（CROSS JOIN 字典）再 LEFT JOIN 事实表：
WITH users AS (SELECT DISTINCT user_id FROM orders),
states AS (SELECT unnest(ARRAY['paid','refunded']) AS status)
SELECT u.user_id, s.status,
coalesce(bool_or(o.id IS NOT NULL), false) AS 有过
FROM users u
CROSS JOIN states s
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = s.status
GROUP BY u.user_id, s.status;FILTER 过滤掉全部行时返回的是 NULL
bool_or / bool_and / sum / avg / max 在被 FILTER 清空时会返回 NULL，而 count 返回 0。漏掉 coalesce 的直接后果就是：报表上「未退款」显示为空白，下游把它当成「未知」而不是「否」。
实战示例
第 1 步：准备数据
INSERT INTO orders (user_id, status, amount, channel, created_at) VALUES
(1, 'paid', 200.00, 'app', now() - interval '10 days'),
(1, 'paid', 150.00, 'web', now() - interval '7 days'),
(1, 'refunded', 120.00, 'app', now() - interval '5 days'),
(2, 'paid', 999.00, 'miniapp', now() - interval '9 days'),
(2, 'pending', 300.00, 'app', now() - interval '1 days'),
(3, 'cancelled', NULL, 'web', now() - interval '8 days'),
(3, 'paid', 50.00, 'web', now() - interval '2 days'),
(4, 'pending', 80.00, 'app', now() - interval '1 days');
INSERT INTO order_items (order_id, sku, qty) VALUES
(1, 'A001', 2), (1, 'B007', 1), (1, 'C010', 1),
(2, 'A001', 1), (2, 'D020', 3),
(4, 'E030', 1);第 2 步：一次扫描的多维销售报表
SELECT
count(*) AS 订单总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款单数,
count(*) FILTER (WHERE status = 'pending') AS 待付款单数,
count(*) FILTER (WHERE status IN ('refunded','cancelled')) AS 失效单数,
coalesce(sum(amount) FILTER (WHERE status = 'paid'), 0) AS 已付款金额,
round(avg(amount) FILTER (WHERE status = 'paid'), 2) AS 付款平均单价,
round(100.0 * count(*) FILTER (WHERE status = 'paid')
/ nullif(count(*), 0), 2) AS 付款率百分比
FROM orders; 订单总数 | 已付款单数 | 待付款单数 | 失效单数 | 已付款金额 | 付款平均单价 | 付款率百分比
----------+------------+------------+----------+------------+--------------+--------------
8 | 4 | 2 | 2 | 1399.00 | 349.75 | 50.00
(1 row)注意三点：sum 包了 coalesce（否则一个 paid 都没有时是 NULL）；avg 的分母是「paid 且 amount 非 NULL」的行数（此处恰为 4 行）；除以 count(*) 时用 nullif 防除零。
第 3 步：按渠道与状态的双维交叉表
GROUP BY 提供一维，FILTER 提供另一维：
SELECT
channel AS 渠道,
count(*) AS 总数,
count(*) FILTER (WHERE status = 'paid') AS 已付款,
coalesce(sum(amount) FILTER (WHERE status = 'paid'), 0) AS 已付款额,
count(*) FILTER (WHERE amount IS NULL) AS 缺金额
FROM orders
GROUP BY channel
ORDER BY 已付款额 DESC, 渠道; 渠道 | 总数 | 已付款 | 已付款额 | 缺金额
---------+------+--------+----------+--------
miniapp | 1 | 1 | 999.00 | 0
app | 4 | 1 | 200.00 | 0
web | 3 | 2 | 200.00 | 1
(3 rows)交叉表的两种维度分配
「行维度」用 GROUP BY，「列维度」用 FILTER。若两个维度基数都很大（如 50 个渠道 × 20 个状态），FILTER 会把 SQL 写成 1000 个聚合表达式，此时应改写成「GROUP BY 维度1, 维度2 + 应用层做透视」。
第 4 步：转化漏斗（含留存）
漏斗要回答「进入下一步的人里有多少走到了再下一步」，需要先补全所有步骤，再按步骤打标：
WITH steps(step_no, step_name) AS (
VALUES (1, '浏览'), (2, '加购'), (3, '下单'), (4, '付款')
),
funnel AS (
SELECT user_id, 1 AS step_no FROM orders
UNION
SELECT user_id, 2 FROM orders WHERE status <> 'cancelled'
UNION
SELECT user_id, 3 FROM orders
UNION
SELECT user_id, 4 FROM orders WHERE status = 'paid'
)
SELECT s.step_no, s.step_name,
count(DISTINCT f.user_id) AS 到达人数,
count(*) FILTER (WHERE f.step_no > s.step_no) AS 未继续人数
FROM steps s
LEFT JOIN funnel f ON f.step_no >= s.step_no
GROUP BY s.step_no, s.step_name
ORDER BY s.step_no;本例是简化模型（用同一张订单表模拟各步骤），真实漏斗应让每一步来自对应的事件表。要点仍是：FILTER 只对已有的行做分流，缺失的步骤要靠 LEFT JOIN 补成 NULL 行，再用 count / count(DISTINCT) 计数（count 遇 NULL 返回 0，正好符合「无人到达」的语义）。
第 5 步：每个订单的「多件商品」清单
FILTER 与聚合内 ORDER BY 配合：
SELECT o.id AS 订单号, o.status,
coalesce(string_agg(i.sku, ', ' ORDER BY i.id) FILTER (WHERE i.qty >= 2), '-') AS 多件商品,
count(*) FILTER (WHERE i.qty >= 2) AS 多件行数,
coalesce(sum(i.qty) FILTER (WHERE i.qty >= 2), 0) AS 多件总件数
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.status
ORDER BY o.id; 订单号 | status | 多件商品 | 多件行数 | 多件总件数
--------+-----------+----------+----------+------------
1 | paid | A001 | 1 | 2
2 | paid | D020 | 1 | 3
3 | refunded | - | 0 | 0
4 | paid | - | 0 | 0
5 | pending | - | 0 | 0
6 | cancelled | - | 0 | 0
7 | paid | - | 0 | 0
8 | pending | - | 0 | 0
(8 rows)订单 2 有 A001（1 件）和 D020（3 件），FILTER (WHERE qty >= 2) 只留下 D020。订单 3 到 8 没有任何 order_items，LEFT JOIN 产生的全 NULL 行被 FILTER 排除后 string_agg 返回 NULL，由 coalesce 显示为 -。
第 6 步：窗口 + FILTER 的累计指标
SELECT
id, user_id, status, amount, created_at::date AS 日期,
coalesce(sum(amount) FILTER (WHERE status = 'paid')
OVER (PARTITION BY user_id ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS 累计已付款
FROM orders
ORDER BY user_id, created_at, id; id | user_id | status | amount | 日期 | 累计已付款
----+---------+----------+---------+------------+------------
1 | 1 | paid | 200.00 | 2026-09-14 | 200.00
2 | 1 | paid | 150.00 | 2026-09-17 | 350.00
3 | 1 | refunded | 120.00 | 2026-09-19 | 350.00
4 | 2 | paid | 999.00 | 2026-09-15 | 999.00
5 | 2 | pending | 300.00 | 2026-09-23 | 999.00
6 | 3 | cancelled| | 2026-09-16 | 0.00
7 | 3 | paid | 50.00 | 2026-09-22 | 50.00
8 | 4 | pending | 80.00 | 2026-09-23 | 0.00排序键写成 ORDER BY created_at, id 而不是只写 created_at，是为了在时间戳相同时有确定顺序；id 是主键，天然唯一，是最常用的兜底排序键。
第 7 步：清理
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS big_orders;注意事项
FILTER的位置固定在聚合参数括号之后、OVER之前。count(DISTINCT x) FILTER (WHERE ...) OVER (...)的书写顺序是DISTINCT→ 参数 →FILTER→OVER。FILTER里不能出现聚合函数或子查询，报aggregate functions are not allowed in FILTER；「高于平均值」这类需求必须先用 CTE 算基准。FILTER不等于WHERE：它不会减少扫描行数，也不会下推到扫描节点，只在Aggregate内部做分流。FILTER清空输入时，count返回0而其余聚合返回NULL；报表里给非count的指标配coalesce。CASE WHEN与FILTER在array_agg/json_agg上不等价：CASE缺ELSE会插入 NULL 元素，FILTER不会。- 用
CASE时补ELSE 0会让count和avg算错：count(CASE WHEN c THEN 1 ELSE 0 END)等于总行数，avg(CASE WHEN c THEN x ELSE 0 END)的分母被撑大、均值被压低。ELSE NULL或不写ELSE才是正确写法。 FILTER只能跟在聚合函数后，upper(x) FILTER (...)报FILTER specified, but upper is not an aggregate function。FILTER不可叠加，agg(...) FILTER (a) FILTER (b)是语法错误；两个条件必须合并成FILTER (WHERE a AND b)。FILTER条件中不能出现窗口函数，count(*) FILTER (WHERE row_number() OVER () > 0)报window functions are not allowed in FILTER。FILTER条件是三值逻辑：条件求值为NULL的行不会被计入，NOT (x > 1)与x > 1都不收x IS NULL的行。- 只算一个指标时用
WHERE而非FILTER：FILTER不减少扫描行数，会丢掉部分索引与分区裁剪的优势。 - 多个互斥指标的计数之和应等于
count(*)，不等就说明覆盖了未知的状态取值，应回头检查数据而不是改 SQL。 FILTER条件里引用外部列时要注意别名作用域：FILTER在WHERE之后、GROUP BY之后求值，不能引用SELECT里刚定义的别名。FILTER不会让WHERE的索引失效：两者可以叠加使用，WHERE先做粗筛（走索引），FILTER再做细分。- 窗口
OVER里的FILTER不改变帧边界，只对帧内的行再筛选一次；帧内全被滤掉时结果同样是 NULL。 - 不要用
FILTER实现「分组后筛组」，那是HAVING的职责；FILTER表达式里放聚合会直接报错。 FILTER条件应写成「正向可读」形式，例如FILTER (WHERE status = 'paid')优于FILTER (WHERE NOT (status <> 'paid'))，便于与指标名对齐核对。- 一条 SQL 里
FILTER数量不建议超过十几个：每个FILTER都是一个聚合状态，过多会让计划与 SQL 都难以维护，此时应改为长表（GROUP BY维度）+ 应用层透视。
最佳实践
- 「多个互斥指标」一律用
FILTER写一条 SQL，避免同一张大表被扫多遍，也让指标口径集中在同一处便于评审。 - 给每个
FILTER指标配一个中文别名，别名直接对应报表列名，减少下游映射工作：sqlcount(*) FILTER (WHERE status = 'paid') AS 已付款单数 - 非
count的FILTER指标一律包coalesce，把「无数据」明确成0或-，不要把它留给前端判断。 FILTER与WHERE分工明确：先想清楚「这个条件是要缩小全场输入，还是只影响这一个指标」。前者写WHERE，后者写FILTER。- 条件聚合统一用
FILTER，不用CASE WHEN造 NULL，特别是涉及array_agg/json_agg时。 FILTER中的条件与业务状态枚举保持一致，并定期用GROUP BY status核对是否出现枚举外的新取值。- 写多重
FILTER报表时附一条自检断言：互斥指标之和 =count(*)，可以做成回归测试。 - 窗口 +
FILTER一定显式写帧（ROWS BETWEEN ...），不要依赖默认的RANGE帧，否则时间戳相同的行会被一起累加。 - 排序键用「业务字段 + 主键」（
ORDER BY created_at, id），保证结果稳定可复现。 - 漏斗类报表先补维度再聚合：
CROSS JOIN步骤字典 +LEFT JOIN事实表，FILTER只在有行时有效。 - 用
EXPLAIN (ANALYZE, BUFFERS)确认「一次扫描」：计划里应只有一个扫描节点；出现多个就说明还没合并干净。 - 给高频
FILTER条件配套索引：若报表常用WHERE channel = ?缩小范围，索引建在channel上；FILTER内的条件本身不需要索引，但当它能转化为WHERE（只有一个指标时）时应转化为WHERE以利用索引。
下一节
继续学习：ORDER BY 在聚合中