数据库原理与选型 / 系统课时
HAVING 与条件聚合
HAVING 与条件聚合
本课解决分组统计后如何筛选结果,以及如何在一条 SQL 中计算已支付数量、退款数量和分类金额的问题。完成后,你可以正确区分 WHERE、HAVING、CASE 与 FILTER。
学习目标
- 区分
WHERE的行级过滤与HAVING的组级过滤。 - 使用
CASE编写兼容性良好的条件聚合。 - 使用
FILTER让多个统计条件更直观。
WHERE 与 HAVING 的执行范围
WHERE 在分组前过滤明细行,不能直接使用聚合结果;HAVING 在分组后过滤分组结果。
sql
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'refunded', 10.0),
('west', 'paid', 50.0)
)
SELECT
region,
sum(amount) AS paid_amount
FROM orders
WHERE status = 'paid'
GROUP BY region
HAVING sum(amount) >= 100
ORDER BY region;预期输出
text
region | paid_amount
north | 180.0
south | 120.0退款行先被 WHERE 排除,随后 HAVING 只保留已支付金额不少于 100 的地区。
使用 CASE 进行条件聚合
把条件写进聚合函数,可以在同一组中计算多个指标。
sql
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'refunded', 10.0),
('west', 'paid', 50.0)
)
SELECT
region,
count(*) AS total_orders,
sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
sum(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders,
sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount
FROM orders
GROUP BY region
ORDER BY region;预期输出
text
region | total_orders | paid_orders | refunded_orders | paid_amount
north | 3 | 2 | 1 | 180.0
south | 2 | 1 | 1 | 120.0
west | 1 | 1 | 0 | 50.0ELSE 0 能保证没有匹配行时返回 0,而不是 NULL。这种写法兼容性好,也便于迁移到其他数据库。
使用 FILTER 简化条件
SQLite 3.30.0 及以上版本支持 FILTER,可以把每个聚合指标的条件直接写在函数后面。
sql
WITH orders(region, status, amount) AS (
VALUES
('north', 'paid', 100.0),
('north', 'paid', 80.0),
('north', 'refunded', 20.0),
('south', 'paid', 120.0),
('south', 'refunded', 10.0),
('west', 'paid', 50.0)
)
SELECT
region,
count(*) FILTER (WHERE status = 'paid') AS paid_orders,
count(*) FILTER (WHERE status = 'refunded') AS refunded_orders,
sum(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM orders
GROUP BY region
ORDER BY region;预期输出
text
region | paid_orders | refunded_orders | paid_amount
north | 2 | 1 | 180.0
south | 1 | 1 | 120.0
west | 1 | 0 | 50.0FILTER 可读性更高,但旧版 SQLite 不支持时应改用 CASE。部署前可通过 SELECT sqlite_version(); 检查运行版本。
组合行级与组级条件
实际报表常同时使用两种过滤:先限制统计周期或业务状态,再筛选达到阈值的分组。应优先把能在明细阶段确定的条件放入 WHERE,减少进入分组的数据量;只把依赖聚合结果的条件放入 HAVING。
按自己的节奏,确认真正理解。