PHP小码农

Appearance

分组（GROUP BY / HAVING） ​

GROUP BY 把结果集按指定列切成若干组，聚合函数在每个组内独立计算；HAVING 则用于过滤分组后的结果。本节讲解分组规则、WHERE 与 HAVING 的执行顺序与性能差异、聚合函数对 NULL 的处理，以及多维报表的实战写法。

前置知识

阅读本节前，建议先了解：去重（DISTINCT）

基础概念 ​

术语对照 ​

中文English说明
分组GROUP BY按列取值把行划分为若干组
分组键Grouping Key参与分组的列或表达式
聚合函数Aggregate Function把一组值归约为单个值的函数
分组过滤HAVING对分组结果做筛选
行过滤WHERE在分组前对原始行做筛选
分组集GROUPING SETS一次查询产出多个分组维度的并集
条件聚合Conditional Aggregate用 FILTER 或 CASE 只聚合部分行

分组与聚合的基本语义 ​

sql
SELECT 分组键, 聚合函数(列) [, ...]
FROM 表
[WHERE 行条件]
GROUP BY 分组键 [, ...]
[HAVING 分组条件]
[ORDER BY ...];

逻辑执行顺序：

顺序子句作用对象说明
1FROM / JOIN原始行组装数据集
2WHERE原始行分组前过滤，不能引用聚合
3GROUP BY原始行划分组
4聚合函数每组计算组内聚合值
5HAVING分组分组后过滤，可引用聚合
6SELECT结果行输出列与别名
7ORDER BY结果行排序
8LIMIT / OFFSET结果行截断

关键结论：WHERE 不能使用聚合函数，HAVING 可以使用。因为 WHERE 执行时分组还不存在。

分组列规则 ​

SELECT 列表中的非聚合表达式，必须出现在 GROUP BY 中（SQL 标准要求）。PostgreSQL 允许 SELECT 使用分组键的函数依赖列（当分组键是主键时），但为可移植性起见，建议显式列出。

sql
-- 正确：category 出现在 GROUP BY 中
SELECT category, count(*) FROM products GROUP BY category;

-- 错误！price 未聚合也未分组
SELECT category, price, count(*) FROM products GROUP BY category;
text
ERROR:  column "products.price" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT category, price, count(*) FROM products GROUP BY cate...

语法与用法 ​

示例数据 ​

后续示例统一使用 demo 库中的 orders 表与 order_items 表：

sql
CREATE TABLE orders (
  order_no    varchar(20)  PRIMARY KEY,
  user_name   varchar(50)  NOT NULL,
  city        varchar(50),
  amount      numeric(10,2) NOT NULL,
  status      varchar(10)  NOT NULL,
  created_at  timestamptz  NOT NULL DEFAULT now()
);

INSERT INTO orders (order_no, user_name, city, amount, status, created_at) VALUES
  ('SO-1001', 'alice', '北京', 1200.00, 'paid',     '2026-01-05 10:00:00+08'),
  ('SO-1002', 'bob',   '上海',  350.50, 'paid',     '2026-01-06 11:30:00+08'),
  ('SO-1003', 'alice', '北京',  880.00, 'pending',  '2026-01-07 09:15:00+08'),
  ('SO-1004', 'carol', '广州', 1200.00, 'paid',     '2026-01-08 14:20:00+08'),
  ('SO-1005', 'bob',   '上海',  220.00, 'canceled', '2026-01-09 16:45:00+08'),
  ('SO-1006', 'dave',  '北京',  450.00, 'pending',  '2026-01-10 08:05:00+08'),
  ('SO-1007', 'carol', '广州', 1200.00, 'shipped',  '2026-01-11 12:40:00+08'),
  ('SO-1008', 'eve',   '深圳',  760.00, 'paid',     '2026-01-12 15:10:00+08');

CREATE TABLE order_items (
  id         bigserial PRIMARY KEY,
  order_no   varchar(20) NOT NULL REFERENCES orders(order_no),
  product    varchar(50) NOT NULL,
  qty        integer     NOT NULL,
  unit_price numeric(10,2) NOT NULL
);

INSERT INTO order_items (order_no, product, qty, unit_price) VALUES
  ('SO-1001', '显示器', 1, 1200.00),
  ('SO-1002', '键盘',   2, 150.00),
  ('SO-1002', '鼠标',   1,  50.50),
  ('SO-1003', '显示器', 1, 880.00),
  ('SO-1004', '主机',   1, 1200.00),
  ('SO-1005', '鼠标',   4,  55.00),
  ('SO-1006', '键盘',   3, 150.00),
  ('SO-1007', '主机',   1, 1200.00),
  ('SO-1008', '耳机',   2, 380.00);
text
 order_no | product | qty | unit_price
----------+---------+-----+------------
 SO-1001  | 显示器  |   1 |    1200.00
 SO-1002  | 键盘    |   2 |     150.00
 SO-1002  | 鼠标    |   1 |      50.50
 SO-1003  | 显示器  |   1 |     880.00
 SO-1004  | 主机    |   1 |    1200.00
 SO-1005  | 鼠标    |   4 |      55.00
 SO-1006  | 键盘    |   3 |     150.00
 SO-1007  | 主机    |   1 |    1200.00
 SO-1008  | 耳机    |   2 |     380.00
(9 rows)

常用聚合函数 ​

函数作用忽略 NULL
count(*)统计行数不适用
count(col)统计非 NULL 值个数是
sum(col)求和是
avg(col)平均值是
min(col) / max(col)最值是
string_agg(col, sep)字符串拼接是
array_agg(col)聚合为数组是
jsonb_agg(col)聚合为 JSON 数组是
stddev(col) / variance(col)标准差 / 方差是
bool_and(col) / bool_or(col)逻辑与 / 或是

详细说明 ​

单列分组 ​

sql
SELECT city, count(*) AS order_cnt, sum(amount) AS total_amount
FROM orders
GROUP BY city
ORDER BY total_amount DESC;
text
  city  | order_cnt | total_amount
--------+-----------+--------------
 北京   |         3 |      2530.00
 广州   |         2 |      2400.00
 深圳   |         1 |       760.00
 上海   |         2 |       570.50
(4 rows)

count(*) 统计该组行数，sum(amount) 累加该组金额。

多列分组 ​

sql
SELECT city, status, count(*) AS cnt, sum(amount) AS total
FROM orders
GROUP BY city, status
ORDER BY city, status;
text
  city  |  status  | cnt |  total
--------+----------+-----+---------
 上海   | canceled |   1 |  220.00
 上海   | paid     |   1 |  350.50
 北京   | paid     |   1 | 1200.00
 北京   | pending  |   2 | 1330.00
 广州   | paid     |   1 | 1200.00
 广州   | shipped  |   1 | 1200.00
 深圳   | paid     |   1 |  760.00
(7 rows)

多列分组的组数等于各列取值组合数。注意与 DISTINCT city, status 的结果对比——组合完全相同，但 GROUP BY 额外给出了每组统计量。

表达式分组 ​

GROUP BY 的键可以是表达式，如时间截断、条件分支：

sql
-- 按月统计订单额
SELECT to_char(created_at, 'YYYY-MM') AS month,
       count(*) AS cnt,
       sum(amount) AS total
FROM orders
GROUP BY to_char(created_at, 'YYYY-MM')
ORDER BY month;
text
  month  | cnt |  total
---------+-----+---------
 2026-01 |   8 | 6260.50
(1 row)

注意：GROUP BY 必须重复表达式，或使用 GROUP BY 1（按序号）——但不推荐后者，因为序号含义脆弱。

下列写法利用别名分组会报错（GROUP BY 阶段 SELECT 的别名尚未生效）：

sql
-- 错误！GROUP BY 不能引用 SELECT 别名
SELECT to_char(created_at, 'YYYY-MM') AS month, count(*)
FROM orders
GROUP BY month;

HAVING 过滤分组 ​

sql
-- 只看订单数大于 1 的城市
SELECT city, count(*) AS cnt
FROM orders
GROUP BY city
HAVING count(*) > 1
ORDER BY cnt DESC;
text
  city  | cnt
--------+-----
 北京   |   3
 上海   |   2
 广州   |   2
(3 rows)

HAVING 条件可以直接写聚合函数，也可以引用分组键：

sql
-- 等价写法：过滤条件放在 HAVING 中，引用分组键
SELECT city, count(*) AS cnt
FROM orders
GROUP BY city
HAVING city <> '深圳';

WHERE 与 HAVING 的执行顺序与性能 ​

两者的差异不只是"能不能用聚合"，更体现在执行时机与代价上：

对比项WHEREHAVING
执行时机分组之前分组之后
作用对象原始行分组结果
能否用聚合不能能
能否走索引能（配合索引条件下推）一般不能（在分组结果上过滤）
代价低，越早过滤越省高，需先完整分组
能否省掉分组部分组可整体跳过分组已计算完毕才丢弃

因此一条重要原则是：能在 WHERE 里过滤的条件，不要放到 HAVING。

sql
-- 不推荐：先分组再丢弃，白白计算了 canceled 组
SELECT city, count(*) AS cnt
FROM orders
GROUP BY city
HAVING status <> 'canceled';

-- 推荐：分组前先过滤，组数与输入行数都减少
SELECT city, count(*) AS cnt
FROM orders
WHERE status <> 'canceled'
GROUP BY city;

用执行计划可以直观看出差异：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT city, count(*) FROM orders GROUP BY city HAVING status <> 'canceled';
text
                    QUERY PLAN
---------------------------------------------------
 GroupAggregate (actual time=0.040..0.043 rows=4 loops=1)
   Group Key: city
   Filter: ((status)::text <> 'canceled'::text)
   ->  Sort (actual time=0.030..0.032 rows=8 loops=1)
         Sort Key: city
         ->  Seq Scan on orders (actual time=0.010..0.014 rows=8 loops=1)
 Planning Time: 0.120 ms
 Execution Time: 0.080 ms
(7 rows)

可以看到 Filter 挂在 GroupAggregate 之上——说明先扫描 8 行、再排序、再分组，status 条件完全没用上。

聚合函数与 NULL ​

所有标准聚合函数（sum、avg、min、max、count(col)）都忽略 NULL。这会带来一个经典陷阱：

sql
-- 若 city 列存在 NULL：count(*) 计入 NULL 行，count(city) 不计
SELECT count(*)              AS total_rows,
       count(city)           AS city_filled,
       count(*) - count(city) AS city_null,
       round(avg(amount), 2) AS avg_amount,
       sum(amount)           AS total_amount
FROM orders;
text
 total_rows | city_filled | city_null | avg_amount | total_amount
------------+-------------+-----------+------------+--------------
          8 |           8 |         0 |     782.56 |      6260.50
(1 row)
情形count(*)count(col)avg(col)
组内全为 NULL返回 N返回 0返回 NULL
组内部分 NULL计全部行只计非 NULL只对非 NULL 求平均
空表 / 无匹配组返回 0（无 GROUP BY）返回 0返回 NULL

空表且带 GROUP BY 时不返回任何行；不带 GROUP BY 的聚合（如 SELECT count(*) FROM t）永远返回一行。

用 FILTER 做条件聚合（行转列） ​

FILTER (WHERE ...) 让每个聚合函数只处理满足条件的行，是实现"行转列"的标准手段（PostgreSQL 9.4+）：

sql
SELECT city,
       count(*)                                          AS total_cnt,
       count(*) FILTER (WHERE status = 'paid')            AS paid_cnt,
       count(*) FILTER (WHERE status = 'pending')         AS pending_cnt,
       count(*) FILTER (WHERE status = 'canceled')        AS canceled_cnt,
       sum(amount) FILTER (WHERE status = 'paid')         AS paid_amount
FROM orders
GROUP BY city
ORDER BY city;
text
  city  | total_cnt | paid_cnt | pending_cnt | canceled_cnt | paid_amount
--------+-----------+----------+-------------+--------------+-------------
 上海   |         2 |        1 |           0 |            1 |      350.50
 北京   |         3 |        1 |           2 |            0 |     1200.00
 广州   |         2 |        1 |           0 |            0 |     1200.00
 深圳   |         1 |        1 |           0 |            0 |      760.00
(4 rows)

若数据库版本不支持 FILTER，可用 CASE 模拟：

sql
-- 等价写法：聚合内嵌 CASE
SELECT city,
       sum(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount
FROM orders
GROUP BY city
ORDER BY city;

GROUPING SETS / ROLLUP / CUBE ​

当报表需要"同时看明细维度与汇总行"时，逐个维度跑 UNION ALL 很啰嗦。GROUPING SETS、ROLLUP、CUBE 是 SQL 标准提供的一次性多维分组语法（PostgreSQL 9.5+）：

语法生成的维度组合适用场景
GROUPING SETS ((a), (b), ())显式列出各组任意自定义维度组合
ROLLUP (a, b)(a,b)、(a)、()层级汇总（年→月→总计）
CUBE (a, b)(a,b)、(a)、(b)、()全维度交叉汇总
sql
-- ROLLUP：城市 × 状态明细 + 城市小计 + 总计
SELECT coalesce(city, '总计') AS city,
       coalesce(status, '小计') AS status,
       count(*) AS cnt,
       sum(amount) AS total
FROM orders
GROUP BY ROLLUP (city, status)
ORDER BY GROUPING(city), city NULLS LAST, GROUPING(status), status NULLS LAST;
text
  city  |  status  | cnt |  total
--------+----------+-----+---------
 上海   | canceled |   1 |  220.00
 上海   | paid     |   1 |  350.50
 上海   | 小计     |   2 |  570.50
 北京   | paid     |   1 | 1200.00
 北京   | pending  |   2 | 1330.00
 北京   | 小计     |   3 | 2530.00
 广州   | paid     |   1 | 1200.00
 广州   | shipped  |   1 | 1200.00
 广州   | 小计     |   2 | 2400.00
 深圳   | paid     |   1 |  760.00
 深圳   | 小计     |   1 |  760.00
 总计   | 小计     |   8 | 6260.50
(12 rows)

小计行的 status 被替换为"小计"，总计行的 city 被替换为"总计"。若需要区分"真实的 NULL 值"和"汇总产生的 NULL"，用 GROUPING(col) 判断：

sql
SELECT city, status,
       GROUPING(city)   AS g_city,    -- 1 表示该列被汇总（非真实分组）
       GROUPING(status) AS g_status,
       count(*) AS cnt
FROM orders
GROUP BY ROLLUP (city, status)
ORDER BY g_city, g_status, city, status;

聚合结果排序 ​

GROUP BY 不保证输出顺序，需要顺序必须显式 ORDER BY。此外，ORDER BY 可以引用聚合值或其别名：

sql
SELECT city, sum(amount) AS total
FROM orders
GROUP BY city
ORDER BY total DESC;
text
  city  |  total
--------+---------
 北京   | 2530.00
 广州   | 2400.00
 深圳   |  760.00
 上海   |  570.50
(4 rows)

实战示例 ​

需求：为运营团队产出一份"城市经营看板"，包含城市汇总、状态分布、热销商品、月度趋势与总体占比。步骤 1~8。

步骤 1：城市级汇总 ​

sql
SELECT city,
       count(*)                          AS order_cnt,
       count(DISTINCT user_name)         AS user_cnt,
       sum(amount)                       AS gmv,
       round(avg(amount), 2)             AS avg_order,
       min(amount)                       AS min_amount,
       max(amount)                       AS max_amount
FROM orders
GROUP BY city
ORDER BY gmv DESC;
text
  city  | order_cnt | user_cnt |   gmv   | avg_order | min_amount | max_amount
--------+-----------+----------+---------+-----------+------------+------------
 北京   |         3 |        2 | 2530.00 |    843.33 |     450.00 |    1200.00
 广州   |         2 |        1 | 2400.00 |   1200.00 |    1200.00 |    1200.00
 深圳   |         1 |        1 |  760.00 |    760.00 |     760.00 |     760.00
 上海   |         2 |        1 |  570.50 |    285.25 |     220.00 |     350.50
(4 rows)

步骤 2：状态分布（行转列） ​

sql
SELECT city,
       count(*) FILTER (WHERE status = 'paid')     AS paid,
       count(*) FILTER (WHERE status = 'pending')  AS pending,
       count(*) FILTER (WHERE status = 'shipped')  AS shipped,
       count(*) FILTER (WHERE status = 'canceled') AS canceled
FROM orders
GROUP BY city
ORDER BY city;
text
  city  | paid | pending | shipped | canceled
--------+------+---------+---------+----------
 上海   |    1 |       0 |       0 |        1
 北京   |    1 |       2 |       0 |        0
 广州   |    1 |       0 |       1 |        0
 深圳   |    1 |       0 |       0 |        0
(4 rows)

步骤 3：热销商品排名 ​

sql
SELECT product,
       sum(qty)                       AS total_qty,
       sum(qty * unit_price)          AS sales_amount,
       count(DISTINCT order_no)       AS order_cnt
FROM order_items
GROUP BY product
ORDER BY sales_amount DESC;
text
 product | total_qty | sales_amount | order_cnt
---------+-----------+--------------+-----------
 主机    |         2 |      2400.00 |         2
 显示器  |         2 |      2080.00 |         2
 耳机    |         2 |       760.00 |         1
 键盘    |         5 |       750.00 |         2
 鼠标    |         5 |       270.50 |         2
(5 rows)

步骤 4：用 HAVING 筛出高价值分组 ​

sql
-- 只看销售额超过 1000 的商品
SELECT product, sum(qty * unit_price) AS sales_amount
FROM order_items
GROUP BY product
HAVING sum(qty * unit_price) > 1000
ORDER BY sales_amount DESC;
text
 product | sales_amount
---------+--------------
 主机    |      2400.00
 显示器  |      2080.00
(2 rows)

步骤 5：WHERE 与 HAVING 组合的标准姿势 ​

"先过滤行、再分组、最后筛组"：

sql
-- 排除已取消订单，再按城市统计，最后只保留订单额超过 1000 的城市
SELECT city, count(*) AS cnt, sum(amount) AS total
FROM orders
WHERE status <> 'canceled'        -- 分组前过滤原始行
GROUP BY city
HAVING sum(amount) > 1000          -- 分组后过滤
ORDER BY total DESC;
text
  city  | cnt |  total
--------+-----+---------
 北京   |   3 | 2530.00
 广州   |   2 | 2400.00
(2 rows)

上海 因去掉 canceled 的 220 后仅剩 350.50，深圳 的 760 也未过线，二者被 HAVING 剔除。

步骤 6：按月 × 状态的双维报表 ​

sql
SELECT to_char(created_at, 'YYYY-MM') AS month,
       city,
       count(*)                        AS cnt,
       sum(amount)                     AS total
FROM orders
GROUP BY to_char(created_at, 'YYYY-MM'), city
ORDER BY month, total DESC;
text
  month  |  city  | cnt |  total
---------+--------+-----+---------
 2026-01 | 北京   |   3 | 2530.00
 2026-01 | 广州   |   2 | 2400.00
 2026-01 | 深圳   |   1 |  760.00
 2026-01 | 上海   |   2 |  570.50
(4 rows)

步骤 7：ROLLUP 产出含小计与总计的报表 ​

sql
SELECT coalesce(city, '全部城市') AS city,
       count(*)                   AS cnt,
       sum(amount)                AS total
FROM orders
GROUP BY ROLLUP (city)
ORDER BY GROUPING(city), city;   -- 明细按城市排序，总计行固定排在最后
text
    city    | cnt |  total
------------+-----+---------
 上海       |   2 |  570.50
 北京       |   3 | 2530.00
 广州       |   2 | 2400.00
 全部城市   |   8 | 6260.50
(4 rows)

ROLLUP (city) 等价于 GROUPING SETS ((city), ())，最后一行是总计。

步骤 8：占比计算（窗口函数配合） ​

分组结果想算"每个城市占总量的百分比"，可在分组结果之上套一层窗口函数：

sql
SELECT city,
       total,
       round(100.0 * total / sum(total) OVER (), 2) AS pct
FROM (
  SELECT city, sum(amount) AS total
  FROM orders
  GROUP BY city
) t
ORDER BY total DESC;
text
  city  |  total  |  pct
--------+---------+-------
 北京   | 2530.00 | 40.41
 广州   | 2400.00 | 38.34
 深圳   |  760.00 | 12.14
 上海   |  570.50 |  9.11
(4 rows)

注意 GROUP BY 与窗口函数不能在同一层混用：窗口函数在分组之后计算，因此需要先分组、再在外层用窗口函数。

注意事项 ​

  1. SELECT 中的非聚合列必须出现在 GROUP BY 中。 否则报错；不要依赖 PostgreSQL 对主键函数依赖的宽容行为，显式列出更安全。

  2. WHERE 不能用聚合，HAVING 能用。 这是由逻辑执行顺序决定的，不是语法糖。

sql
-- 错误！WHERE 中不能出现聚合函数
SELECT city, count(*) FROM orders WHERE count(*) > 1 GROUP BY city;

  1. GROUP BY 不能引用 SELECT 别名（ORDER BY、HAVING 可以）。要么重复表达式，要么外面套一层子查询。

  2. GROUP BY 不保证输出顺序。 要顺序就写 ORDER BY。

  3. 聚合函数忽略 NULL，count(*) 例外。 avg 的分母是非 NULL 计数，NULL 多时平均值会偏高。

  4. 空输入的行为差异。 有 GROUP BY 时无组则无行；无 GROUP BY 的聚合返回一行（count 为 0，其余为 NULL）。

  5. GROUPING SETS 产生的 NULL 与真实 NULL 无法直接区分，需用 GROUPING() 函数判别。

  6. GROUP BY 列上有索引时可走 GroupAggregate 免排序，但若选择 HashAggregate 则可能因内存不足落盘（Batches > 1）。

最佳实践 ​

  1. 过滤条件尽量前移到 WHERE。 缩小输入行数是提升分组性能最直接的手段。
sql
SELECT city, count(*) FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY city;
  1. 为分组键建索引，让分组免排序。
sql
-- 支撑 GROUP BY city（可用 GroupAggregate 复用索引顺序）
CREATE INDEX idx_orders_city ON orders (city);
  1. 行转列优先用 FILTER，兼容性要求高时用 CASE。
sql
SELECT city,
       count(*) FILTER (WHERE status = 'paid') AS paid
FROM orders GROUP BY city;
  1. 多维报表用 GROUPING SETS / ROLLUP / CUBE 替代多个 UNION ALL。 只需扫一遍表。
sql
SELECT coalesce(city, '总计') AS city, sum(amount)
FROM orders GROUP BY ROLLUP (city);
  1. 计算占比、排名、累计时用窗口函数包裹分组结果，不要试图在同一层混用。
sql
SELECT city, total, rank() OVER (ORDER BY total DESC) AS rk
FROM (SELECT city, sum(amount) AS total FROM orders GROUP BY city) t;
  1. 大表分组前确认 work_mem 充足，避免 HashAggregate 落盘。
sql
-- 会话级临时提升
SET work_mem = '128MB';
  1. count(DISTINCT col) 在大表上代价高，若只是为过滤用 HAVING count(DISTINCT ...) > 1，可考虑先 GROUP BY 去重再统计。
sql
SELECT count(*) FROM (SELECT user_name FROM orders GROUP BY user_name HAVING count(*) > 1) t;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 确认分组实现方式与是否落盘，关注 HashAggregate 的 Batches 与 Memory Usage。

下一节

继续学习：行锁查询（FOR UPDATE）

参考链接 ​