数据库原理与选型 / 系统课时

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.0

ELSE 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.0

FILTER 可读性更高,但旧版 SQLite 不支持时应改用 CASE。部署前可通过 SELECT sqlite_version(); 检查运行版本。

组合行级与组级条件

实际报表常同时使用两种过滤:先限制统计周期或业务状态,再筛选达到阈值的分组。应优先把能在明细阶段确定的条件放入 WHERE,减少进入分组的数据量;只把依赖聚合结果的条件放入 HAVING。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

HAVING 与条件聚合

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通