PHP小码农

Appearance

聚合查询与分组 ​

本节讲解如何用聚合函数（Aggregate Function）对多行数据做汇总计算，用 GROUP BY 分组统计，用 HAVING 过滤分组结果，以及 FILTER、GROUPING SETS、ROLLUP、CUBE 等进阶能力。学完本节，你将能独立完成报表类统计查询。

前置知识

阅读本节前，建议先了解：条件查询与排序

基础概念 ​

聚合函数（Aggregate Function） 把多行输入折叠成一个输出值。它与普通函数的区别在于：普通函数一行进一行出，聚合函数多行进一行出。

概念术语说明
聚合函数Aggregate Function把多行合并为一个值，如 sum、count、avg
分组Grouping按某列的值把行划分成若干组，每组产出一行
分组键Grouping KeyGROUP BY 中指定的列或表达式
分组过滤HAVING对分组结果（而非原始行）做过滤
聚合过滤器FILTER只把符合条件的行送入聚合函数
分组集GROUPING SETS一次查询中计算多组不同维度的分组
有序集聚合Ordered-Set Aggregate依赖排序位置的聚合，如中位数 percentile_cont
窗口函数Window Function不折叠行的"聚合"，每行都保留并附带计算结果
统计信息Statistics优化器用来估算数据分布的数据，影响聚合计划

WHERE 与 HAVING 的区别是最核心的概念，必须在动手前弄清：

对比项WHEREHAVING
作用对象原始行分组后的组
执行时机分组之前分组之后
能否用聚合函数不能能
能否用 SELECT 别名不能可以（PostgreSQL 允许）
典型用途缩小参与统计的数据范围过滤掉统计结果不符合要求的组

标准 SQL 中 SELECT 列表里出现的非聚合列，必须出现在 GROUP BY 中；反之 GROUP BY 中的列可以不出现在 SELECT 中。

sql
-- 合法：category 在 GROUP BY 中
SELECT category, count(*) FROM products GROUP BY category;

-- 错误！name 既不在 GROUP BY 中，也没有被聚合
SELECT category, name, count(*) FROM products GROUP BY category;
-- 报错：column "products.name" must appear in the GROUP BY clause
--       or be used in an aggregate function

-- 合法但语义特殊：PostgreSQL 允许按主键分组后选取该行的其他列
SELECT id, sku, name, count(*) FROM products GROUP BY id;

语法与用法 ​

聚合查询的基本骨架：

text
SELECT 分组键, 聚合函数(列) [AS 别名]
FROM 表名
[WHERE 行过滤条件]
GROUP BY 分组键
[HAVING 组过滤条件]
[ORDER BY 排序键]
[LIMIT 行数];

准备本节贯穿使用的示例表：

sql
-- 订单明细表：用于演示各类聚合统计
CREATE TABLE orders (
  id          INTEGER       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT          NOT NULL,
  customer    TEXT          NOT NULL,
  region      TEXT          NOT NULL,
  channel     TEXT          NOT NULL,           -- 下单渠道
  sku         TEXT          NOT NULL,
  category    TEXT          NOT NULL,
  qty         INTEGER       NOT NULL CHECK (qty > 0),
  unit_price  NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
  discount    NUMERIC(10, 2),                   -- 可空：部分订单无折扣
  is_paid     BOOLEAN       NOT NULL DEFAULT false,
  created_at  DATE          NOT NULL
);

INSERT INTO orders (order_no, customer, region, channel, sku, category, qty, unit_price, discount, is_paid, created_at) VALUES
  ('SO-1001', 'alice', 'east',  'web',    'SKU-1001', 'peripherals', 1, 349.00, 20.00, true,  DATE '2026-01-05'),
  ('SO-1001', 'alice', 'east',  'web',    'SKU-1002', 'peripherals', 2, 129.00, 10.00, true,  DATE '2026-01-05'),
  ('SO-1002', 'bob',   'north', 'app',    'SKU-1003', 'accessories', 1, 199.00,  NULL, true,  DATE '2026-01-08'),
  ('SO-1003', 'carol', 'south', 'web',    'SKU-1001', 'peripherals', 3, 349.00,  NULL, false, DATE '2026-01-12'),
  ('SO-1004', 'dave',  'east',  'store',  'SKU-1005', 'cable',       5,  39.00,  5.00, true,  DATE '2026-02-02'),
  ('SO-1005', 'alice', 'east',  'app',    'SKU-1004', 'accessories', 2, 259.00,  NULL, true,  DATE '2026-02-09'),
  ('SO-1006', 'bob',   'north', 'web',    'SKU-1006', 'cable',      10,  49.00,  0.00, true,  DATE '2026-02-15'),
  ('SO-1007', 'carol', 'south', 'store',  'SKU-1003', 'accessories', 1, 199.00,  NULL, false, DATE '2026-02-20'),
  ('SO-1008', 'dave',  'east',  'web',    'SKU-1009', 'storage',     1, 899.00,  NULL, true,  DATE '2026-03-01'),
  ('SO-1009', 'eve',   'west',  'app',    'SKU-1010', 'accessories',20,  29.00,  3.00, true,  DATE '2026-03-05'),
  ('SO-1010', 'eve',   'west',  'web',    'SKU-1007', 'peripherals', 1, 429.00,  NULL, true,  DATE '2026-03-11');

最常用的五个聚合函数：

sql
-- 总行数与总金额
SELECT count(*) AS order_cnt, sum(qty * unit_price) AS gmv FROM orders;

-- 平均值、最大值、最小值
SELECT
  round(avg(unit_price), 2) AS avg_price,   -- round 需指定小数位
  max(unit_price)           AS max_price,
  min(unit_price)           AS min_price
FROM orders;

-- 按分组键统计
SELECT region, count(*) AS cnt, sum(qty) AS total_qty
FROM orders
GROUP BY region
ORDER BY total_qty DESC;

PostgreSQL 内置的常用聚合函数一览：

函数说明典型用途
count(*)统计行数（含 NULL 行）订单数、记录数
count(列)统计该列非 NULL 的行数去空统计
count(DISTINCT 列)统计不同值个数去重客户数
sum(列)求和GMV、总数量
avg(列)平均值客单价
max(列) / min(列)最大值 / 最小值首末订单、价格区间
string_agg(列, 分隔符)字符串拼接汇总标签、拼接名称
array_agg(列)聚合成数组一单多商品的列表
bool_and / bool_or布尔与 / 或是否全部付款
stddev / variance标准差 / 方差数据波动分析
percentile_cont(0.5)连续分位数（中位数）价格中位数

详细说明 ​

常用聚合函数与 NULL 的处理 ​

聚合函数普遍忽略 NULL，count(*) 是唯一的例外：

sql
-- 对比 count(*) 与 count(列)
SELECT
  count(*)               AS all_rows,       -- 所有行
  count(discount)        AS non_null_rows,  -- 只统计 discount 非 NULL 的行
  count(DISTINCT sku)    AS distinct_sku    -- 不同 SKU 数（NULL 不计入）
FROM orders;

-- NULL 被忽略导致平均值的分母变小，可能出现"平均单价高于任何单价"的错觉
SELECT
  count(*)                       AS rows_all,
  count(discount)                AS rows_with_discount,
  avg(discount)                  AS avg_discount,     -- 分母是 rows_with_discount
  avg(COALESCE(discount, 0))     AS avg_discount_all  -- 分母是 rows_all
FROM orders;

string_agg 与 array_agg 把多行折叠为字符串或数组，常与 GROUP BY 搭配：

sql
-- 每个客户买过的所有 SKU 拼成一个字符串
SELECT customer, string_agg(DISTINCT sku, ', ' ORDER BY sku) AS skus
FROM orders
GROUP BY customer
ORDER BY customer;

-- 每个订单的商品列表聚合成数组
SELECT order_no, array_agg(sku ORDER BY sku) AS sku_list, sum(qty) AS total_qty
FROM orders
GROUP BY order_no
ORDER BY order_no;
text
 order_no |             sku_list              | total_qty
----------+-----------------------------------+-----------
 SO-1001  | {SKU-1001,SKU-1002}               |         3
 SO-1002  | {SKU-1003}                        |         1
 SO-1003  | {SKU-1001}                        |         3

bool_and / bool_or 用于布尔聚合：

sql
-- 每个订单是否全部付款完成
SELECT order_no, bool_and(is_paid) AS all_paid, count(*) AS item_cnt
FROM orders
GROUP BY order_no
ORDER BY order_no;

GROUP BY 的多列与表达式分组 ​

分组键可以是多列、表达式、甚至 SELECT 中的别名：

sql
-- 多列分组：地区 + 渠道
SELECT region, channel, count(*) AS cnt, sum(qty * unit_price) AS gmv
FROM orders
GROUP BY region, channel
ORDER BY region, channel;

-- 按表达式分组：按月统计
SELECT to_char(created_at, 'YYYY-MM') AS ym, count(*) AS cnt, sum(qty * unit_price) AS gmv
FROM orders
GROUP BY to_char(created_at, 'YYYY-MM')
ORDER BY ym;

-- 使用 SELECT 别名分组（PostgreSQL 允许）
SELECT to_char(created_at, 'YYYY-MM') AS ym, count(*) AS cnt
FROM orders
GROUP BY ym
ORDER BY ym;

-- 按序号分组（不推荐，可读性差）
SELECT to_char(created_at, 'YYYY-MM'), count(*) FROM orders GROUP BY 1 ORDER BY 1;

也可以按分组键的集合分组，例如把所有渠道合为"线上/线下"两类：

sql
-- 用 CASE 归一化后再分组
SELECT
  CASE WHEN channel IN ('web', 'app') THEN '线上' ELSE '门店' END AS channel_type,
  count(DISTINCT order_no)                                        AS order_cnt,
  sum(qty * unit_price)                                           AS gmv
FROM orders
GROUP BY channel_type
ORDER BY gmv DESC;
text
 channel_type | order_cnt |   gmv
--------------+-----------+---------
 线上         |         8 | 4769.00
 门店         |         2 |  394.00

GROUP BY 与 ORDER BY 都可以使用序号，但分组的数值优先级：当 GROUP BY 中的名称同时匹配列名与输出列别名时，优先按输入列名解析。因此别名与列名重名时行为会令人意外。

sql
-- 危险：SELECT 别名 与 输入列同名时，GROUP BY 优先按输入列解释
SELECT upper(category) AS category, count(*)
FROM orders
GROUP BY category;      -- 实际按 orders.category 分组，不是按 upper(category)

HAVING：过滤分组结果 ​

HAVING 在分组之后执行，用于筛掉不满足条件的组。它与 WHERE 分工明确：

sql
-- WHERE 过滤行，HAVING 过滤组
SELECT customer, count(DISTINCT order_no) AS order_cnt, sum(qty * unit_price) AS gmv
FROM orders
WHERE is_paid                                   -- 先排除未付款的明细行
GROUP BY customer
HAVING sum(qty * unit_price) > 500              -- 再筛掉消费额不足的客户
ORDER BY gmv DESC;
text
 customer | order_cnt |   gmv
----------+-----------+---------
 alice    |         2 | 1125.00
 dave     |         2 | 1094.00
 eve      |         2 | 1009.00
 bob      |         2 |  689.00
(4 rows)

HAVING 中也可以使用 SELECT 别名与序号：

sql
SELECT customer, sum(qty * unit_price) AS gmv
FROM orders
GROUP BY customer
HAVING sum(qty * unit_price) > 500
ORDER BY gmv DESC;

-- 等价写法：使用别名（PostgreSQL 允许）
SELECT customer, sum(qty * unit_price) AS gmv
FROM orders
GROUP BY customer
HAVING gmv > 500
ORDER BY gmv DESC;

WHERE 中不能写聚合函数，这是最常见的一类语法错误：

sql
-- 错误！WHERE 在分组前执行，聚合函数尚不可用
SELECT customer, sum(qty * unit_price)
FROM orders
WHERE sum(qty * unit_price) > 500
GROUP BY customer;

-- 正确：条件放 HAVING
SELECT customer, sum(qty * unit_price)
FROM orders
GROUP BY customer
HAVING sum(qty * unit_price) > 500;

FILTER：条件聚合 ​

FILTER 子句让同一个聚合函数只统计满足指定条件的行，是 pivot（行列转换）类报表的利器，比 CASE WHEN 写法更简洁。

sql
-- PostgreSQL 9.4+
-- 一次扫描得到多个口径的统计
SELECT
  region,
  count(*)                                                    AS item_cnt,
  count(*) FILTER (WHERE is_paid)                             AS paid_cnt,
  count(*) FILTER (WHERE NOT is_paid)                         AS unpaid_cnt,
  sum(qty * unit_price) FILTER (WHERE is_paid)                AS paid_gmv,
  sum(qty * unit_price) FILTER (WHERE channel = 'web')        AS web_gmv,
  count(DISTINCT customer) FILTER (WHERE qty >= 5)            AS bulk_customers
FROM orders
GROUP BY region
ORDER BY region;
text
 region | item_cnt | paid_cnt | unpaid_cnt | paid_gmv | web_gmv | bulk_customers
--------+----------+----------+------------+----------+---------+----------------
 east   |        5 |        5 |          0 |  2219.00 | 1506.00 |              1
 north  |        2 |        2 |          0 |   689.00 |  490.00 |              1
 south  |        2 |        0 |          2 |          | 1047.00 |              0
 west   |        2 |        2 |          0 |  1009.00 |  429.00 |              1

FILTER 与 CASE WHEN 的等价关系：

sql
-- 写法一：FILTER（推荐，语义清晰）
SELECT count(*) FILTER (WHERE is_paid) AS paid_cnt FROM orders;

-- 写法二：CASE WHEN（老版本兼容写法）
SELECT count(CASE WHEN is_paid THEN 1 END) AS paid_cnt FROM orders;

-- 注意！以下写法错误：sum 会累加所有行（不满足的行取 0 会参与求和）
SELECT sum(CASE WHEN is_paid THEN 1 ELSE 1 END) AS wrong_cnt FROM orders;

用 FILTER 实现条件求和时，需要给不满足条件的行返回 NULL 而非 0（因为 sum 忽略 NULL）：

sql
-- 正确：不满足条件返回 NULL，sum 会忽略
SELECT sum(qty * unit_price) FILTER (WHERE is_paid) AS paid_gmv FROM orders;

-- 等价正确写法
SELECT sum(CASE WHEN is_paid THEN qty * unit_price END) AS paid_gmv FROM orders;

-- 错误：else 0 虽然结果相同（加 0 不影响和），但若用 avg 会摊薄分母
SELECT avg(CASE WHEN is_paid THEN unit_price ELSE 0 END) AS wrong_avg FROM orders;

GROUPING SETS、ROLLUP 与 CUBE ​

多维度报表需要"总计 + 分维度小计 + 明细"混排。若用普通 GROUP BY 需要写多条查询再 UNION ALL。PostgreSQL 提供了 GROUPING SETS 一族语法，一次扫描输出多个分组层次。

sql
-- PostgreSQL 9.5+：一次查询输出 (region, channel) 明细 + region 小计 + channel 小计 + 总计
-- 用 GROUPING() 排序，把各类小计与总计排在明细之后
SELECT
  coalesce(region,  '(全部)')  AS region,
  coalesce(channel, '(全部)')  AS channel,
  sum(qty * unit_price)        AS gmv
FROM orders
GROUP BY GROUPING SETS (
  (region, channel),   -- 报表主体
  (region),            -- 按地区小计
  (channel),           -- 按渠道小计
  ()                   -- 总计
)
ORDER BY GROUPING(region), region, GROUPING(channel), channel;
text
 region | channel |   gmv
--------+---------+---------
 east   | app     |  518.00
 east   | store   |  195.00
 east   | web     | 1506.00
 north  | app     |  199.00
 north  | web     |  490.00
 south  | store   |  199.00
 south  | web     | 1047.00
 west   | app     |  580.00
 west   | web     |  429.00
 east   | (全部)  | 2219.00
 north  | (全部)  |  689.00
 south  | (全部)  | 1246.00
 west   | (全部)  | 1009.00
 (全部) | app     | 1297.00
 (全部) | store   |  394.00
 (全部) | web     | 3472.00
 (全部) | (全部)  | 5163.00
(17 rows)

ROLLUP 与 CUBE 是 GROUPING SETS 的语法糖：

sql
-- PostgreSQL 9.5+
-- ROLLUP：按列顺序逐层汇总（适合层级维度：年 → 月 → 日）
SELECT
  extract(year FROM created_at)::int  AS yr,
  to_char(created_at, 'MM')           AS mon,
  sum(qty * unit_price)               AS gmv
FROM orders
GROUP BY ROLLUP (extract(year FROM created_at), to_char(created_at, 'MM'))
ORDER BY yr NULLS LAST, mon;

-- CUBE：对列的所有组合都汇总（适合交叉分析）
SELECT
  coalesce(region, '(全部)')  AS region,
  coalesce(channel, '(全部)') AS channel,
  count(*)                    AS cnt
FROM orders
GROUP BY CUBE (region, channel)
ORDER BY region, channel;

判断某行是否为小计行，可以用 GROUPING() 函数（返回 1 表示该列是汇总产生的 NULL），避免与真实 NULL 混淆：

sql
-- GROUPING 区分"真 NULL"与"汇总产生的 NULL"
SELECT
  CASE WHEN GROUPING(region) = 1 THEN '【合计】' ELSE region END AS region,
  count(*) AS cnt,
  sum(qty * unit_price) AS gmv
FROM orders
GROUP BY ROLLUP (region)
ORDER BY GROUPING(region), region;

三种语法的关系与适用场景：

语法生成的分组集行数（n 列）适用场景
GROUP BY a, b(a,b)1 组单维度明细
GROUP BY GROUPING SETS ((a,b),(a),())自定义按需精确控制汇总层次
GROUP BY ROLLUP (a,b)(a,b),(a),()n+1 组层级汇总（时间、地区树）
GROUP BY CUBE (a,b)(a,b),(a),(b),()2ⁿ 组交叉分析（全部组合）

有序集聚合与分位数 ​

普通聚合不关心行的顺序，而有序集聚合（Ordered-Set Aggregate） 依赖排序位置，最典型的是中位数与分位数。

sql
-- 中位数（连续分位数 0.5）：比 avg 更抗极端值
SELECT
  avg(unit_price)                                  AS mean_price,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY unit_price) AS median_price
FROM orders;

-- 价格的四分位数
SELECT
  percentile_cont(0.25) WITHIN GROUP (ORDER BY unit_price) AS p25,
  percentile_cont(0.50) WITHIN GROUP (ORDER BY unit_price) AS p50,
  percentile_cont(0.75) WITHIN GROUP (ORDER BY unit_price) AS p75,
  percentile_cont(0.90) WITHIN GROUP (ORDER BY unit_price) AS p90
FROM orders;

-- 每组的中位数
SELECT category,
       count(*) AS cnt,
       round(percentile_cont(0.5) WITHIN GROUP (ORDER BY unit_price), 2) AS median_price
FROM orders
GROUP BY category
ORDER BY median_price DESC;

-- mode()：众数
SELECT mode() WITHIN GROUP (ORDER BY channel) AS most_used_channel FROM orders;
text
 category    | cnt | median_price
-------------+-----+--------------
 storage     |   1 |       899.00
 peripherals |   4 |       349.00
 accessories |   4 |       199.00
 cable       |   2 |        44.00

聚合查询的执行顺序与优化器限制 ​

完整执行顺序如下，理解它就能解释绝大多数报错：

text
FROM / JOIN → WHERE → GROUP BY → 聚合函数 → HAVING → SELECT → DISTINCT
           → ORDER BY → LIMIT / OFFSET

因此存在以下限制：

想做的事错误写法正确做法
用聚合结果过滤WHERE sum(x) > 100HAVING sum(x) > 100
对分组结果排序ORDER BY sum(x) 可以直接写，ORDER BY 在最后
对聚合结果再聚合嵌套聚合函数用子查询 / CTE
引用 SELECT 别名WHERE 别名 > 1重复表达式或用子查询
sql
-- 错误！聚合函数不能嵌套
SELECT max(sum(qty)) FROM orders GROUP BY customer;

-- 正确：先分组聚合，再在外层取最大值
SELECT max(gmv) AS max_customer_gmv
FROM (
  SELECT customer, sum(qty * unit_price) AS gmv
  FROM orders
  GROUP BY customer
) t;

-- 用 CTE 写法更清晰（PostgreSQL 8.4+）
WITH customer_gmv AS (
  SELECT customer, sum(qty * unit_price) AS gmv
  FROM orders
  GROUP BY customer
)
SELECT max(gmv) AS max_customer_gmv,
       min(gmv) AS min_customer_gmv
FROM customer_gmv;

实战示例 ​

下面用一段完整流程，从简单统计逐步做到多维报表。

sql
-- 步骤 1：全局概览
SELECT
  count(*)                    AS item_cnt,
  count(DISTINCT order_no)    AS order_cnt,
  count(DISTINCT customer)    AS customer_cnt,
  sum(qty)                    AS total_qty,
  sum(qty * unit_price)       AS gmv
FROM orders;
text
 item_cnt | order_cnt | customer_cnt | total_qty |   gmv
----------+-----------+--------------+-----------+---------
       11 |        10 |            5 |        47 | 5163.00
(1 row)
sql
-- 步骤 2：按分类分组，并计算各分类占总额的比例
SELECT
  category,
  count(*)                                          AS item_cnt,
  sum(qty)                                          AS total_qty,
  sum(qty * unit_price)                             AS gmv,
  round(100 * sum(qty * unit_price) / sum(sum(qty * unit_price)) OVER (), 2) AS pct
FROM orders
GROUP BY category
ORDER BY gmv DESC;
text
 category    | item_cnt | total_qty |   gmv   |  pct
-------------+----------+-----------+---------+-------
 peripherals |        4 |         7 | 2083.00 | 40.35
 accessories |        4 |        24 | 1496.00 | 28.98
 storage     |        1 |         1 |  899.00 | 17.41
 cable       |        2 |        15 |  685.00 | 13.27
(4 rows)
sql
-- 步骤 3：按地区 + 渠道分组，只看 GMV 超过 300 的组合
SELECT region, channel, sum(qty * unit_price) AS gmv
FROM orders
WHERE is_paid                              -- 只统计已付款
GROUP BY region, channel
HAVING sum(qty * unit_price) > 300
ORDER BY gmv DESC;
text
 region | channel |   gmv
--------+---------+---------
 east   | web     | 1506.00
 west   | app     |  580.00
 east   | app     |  518.00
 north  | web     |  490.00
 west   | web     |  429.00
(5 rows)
sql
-- 步骤 4：条件聚合 —— 一张表同时给出付款/未付款双口径
SELECT
  region,
  count(DISTINCT order_no)                                          AS orders,
  count(*) FILTER (WHERE is_paid)                                   AS paid_items,
  coalesce(sum(qty * unit_price) FILTER (WHERE is_paid), 0)         AS paid_gmv,
  coalesce(sum(qty * unit_price) FILTER (WHERE NOT is_paid), 0)     AS unpaid_gmv
FROM orders
GROUP BY region
ORDER BY paid_gmv DESC;
text
 region | orders | paid_items | paid_gmv | unpaid_gmv
--------+--------+------------+----------+------------
 east   |      4 |          5 |  2219.00 |       0.00
 west   |      2 |          2 |  1009.00 |       0.00
 north  |      2 |          2 |   689.00 |       0.00
 south  |      2 |          0 |     0.00 |    1246.00
(4 rows)
sql
-- 步骤 5：用 ROLLUP 生成"月度 + 总计"报表
SELECT
  CASE WHEN GROUPING(ym) = 1 THEN '【总计】' ELSE ym END AS ym,
  count(DISTINCT order_no)                              AS order_cnt,
  sum(qty)                                              AS total_qty,
  sum(qty * unit_price)                                 AS gmv
FROM (
  SELECT order_no, qty, unit_price, to_char(created_at, 'YYYY-MM') AS ym
  FROM orders
) t
GROUP BY ROLLUP (ym)
ORDER BY GROUPING(ym), ym;
text
    ym    | order_cnt | total_qty |   gmv
----------+-----------+-----------+---------
 2026-01  |         3 |         7 | 1853.00
 2026-02  |         4 |        18 | 1402.00
 2026-03  |         3 |        22 | 1908.00
 【总计】 |        10 |        47 | 5163.00
(4 rows)
sql
-- 步骤 6：找出"下单金额高于平均客单价"的客户
WITH customer_gmv AS (
  SELECT customer, sum(qty * unit_price) AS gmv
  FROM orders
  WHERE is_paid
  GROUP BY customer
)
SELECT customer, gmv
FROM customer_gmv
WHERE gmv > (SELECT avg(gmv) FROM customer_gmv)
ORDER BY gmv DESC;
text
 customer |   gmv
----------+---------
 alice    | 1125.00
 dave     | 1094.00
 eve      | 1009.00
(3 rows)
sql
-- 步骤 7：客户分层统计（按消费额分档后再聚合）
WITH customer_gmv AS (
  SELECT customer, sum(qty * unit_price) AS gmv
  FROM orders
  GROUP BY customer
)
SELECT
  CASE
    WHEN gmv >= 1000 THEN 'A 高价值'
    WHEN gmv >=  500 THEN 'B 中价值'
    ELSE                  'C 普通'
  END                        AS tier,
  count(*)                   AS customer_cnt,
  sum(gmv)                   AS tier_gmv,
  round(avg(gmv), 2)         AS avg_gmv
FROM customer_gmv
GROUP BY tier
ORDER BY tier;
text
    tier     | customer_cnt | tier_gmv | avg_gmv
-------------+--------------+----------+---------
 A 高价值    |            4 |  4474.00 | 1118.50
 B 中价值    |            1 |   689.00 |  689.00
(2 rows)
sql
-- 步骤 8：用 FILTER 做渠道 × 付款状态的透视表
SELECT
  channel,
  count(*)                              AS total,
  count(*) FILTER (WHERE is_paid)       AS paid,
  count(*) FILTER (WHERE NOT is_paid)   AS unpaid,
  round(100.0 * count(*) FILTER (WHERE is_paid) / count(*), 1) AS paid_rate_pct
FROM orders
GROUP BY channel
ORDER BY paid_rate_pct DESC;
text
 channel | total | paid | unpaid | paid_rate_pct
---------+-------+------+--------+---------------
 app     |     3 |    3 |      0 |         100.0
 web     |     6 |    5 |      1 |          83.3
 store   |     2 |    1 |      1 |          50.0
(3 rows)

注意事项 ​

  • WHERE 中不能使用聚合函数。聚合发生在 WHERE 之后，因此聚合条件必须写进 HAVING。
  • count(*) 与 count(列) 结果可能不同。后者忽略 NULL；统计"有效数据行数"时应明确用哪者。
  • count(DISTINCT a, b) 的括号语义是"多列组合去重"，不是函数嵌套：
sql
-- 正确：统计 (customer, order_no) 组合数
SELECT count(DISTINCT (customer, order_no)) FROM orders;

-- 错误！DISTINCT 不是函数，不能这样写
SELECT count(DISTINCT customer, order_no) FROM orders;
  • avg 的分母是"非 NULL 行数"，容易造成误导性结论。需要把 NULL 当 0 参与时，先用 COALESCE 转换：
sql
-- 错误认知：以为 avg 除以总行数
SELECT avg(discount) FROM orders;                   -- 分母 = discount 非 NULL 的行数

-- 明确语义
SELECT avg(COALESCE(discount, 0)) FROM orders;      -- 分母 = 所有行
  • GROUP BY 中的别名可能被列名覆盖。当输出别名与输入列同名时，PostgreSQL 优先按输入列解析，行为可能不符合直觉。避免别名与列名重名。
  • ROLLUP / CUBE 会产生大量行。CUBE 的分组集数量是 2ⁿ，列数多时行数会爆炸式增长，且产生大量 NULL 汇总行。务必用 GROUPING() 区分汇总行与真实 NULL。
  • 简单查询不要盲目用 GROUP BY 替代 DISTINCT。两者语义不同：DISTINCT 去重保持原行，GROUP BY 折叠行并允许附加聚合。
  • GROUP BY 会隐式排序（旧版本）。PostgreSQL 中 GROUP BY 不保证输出顺序，需要顺序时显式写 ORDER BY。
  • 在 GROUP BY 中对列使用函数会阻止索引利用。按月份分组时，若希望用索引加速，可改为对列做范围过滤而非函数包裹。
sql
-- 不推荐（大表上无法利用 created_at 索引做分组）
GROUP BY to_char(created_at, 'YYYY-MM')

-- 更优：直接用 date_trunc，并对时间列做范围过滤
SELECT date_trunc('month', created_at)::date AS mon, count(*)
FROM orders
WHERE created_at >= DATE '2026-01-01' AND created_at < DATE '2027-01-01'
GROUP BY date_trunc('month', created_at)
ORDER BY mon;
  • HAVING 条件尽量能提前到 WHERE。能在分组前排除的行，先排除可以减少聚合计算量。
  • 超大数据集的精确 count(DISTINCT) 代价高。它需要保存去重集合；近似计数场景可考虑 pg_stat_statements 或 HyperLogLog 类扩展。

最佳实践 ​

  1. WHERE 缩小输入，HAVING 筛选输出。 先想清楚条件属于"行级"还是"组级"，这决定了它该写在哪个子句。
  2. 多口径统计用 FILTER 而非多次查询。 一次全表扫描产出多个指标，既高效又能保证口径一致（同一时间点同一份数据）。
sql
-- 一次扫描产出多指标
SELECT
  region,
  count(*)                                          AS orders,
  count(*) FILTER (WHERE is_paid)                   AS paid_orders,
  sum(qty * unit_price) FILTER (WHERE is_paid)      AS paid_gmv,
  count(DISTINCT customer) FILTER (WHERE qty >= 5)  AS bulk_customers
FROM orders
GROUP BY region;
  1. 报表的中位数、分位数用 percentile_cont，不要用 avg 代替。价格、响应时间、停留时长等偏态分布数据，平均值极易被极端值带偏。
  2. 多维度汇总用 GROUPING SETS / ROLLUP，并始终配合 GROUPING() 区分汇总行与真实 NULL，避免报表出现"看起来是空值实际是合计"的错误。
  3. 为 GROUP BY 与 HAVING 涉及的列建索引。 等值分组列在前、范围过滤列在后的复合索引通常最优；纯聚合查询可考虑覆盖索引（INCLUDE）实现 index-only scan。
sql
-- 支撑按 region 分组、按 created_at 过滤的报表
CREATE INDEX idx_orders_region_created ON orders (region, created_at) INCLUDE (qty, unit_price);
  1. 复杂统计拆成 CTE 分层书写。 与把子查询层层嵌套相比，CTE 让每一层的聚合语义独立可读，也便于逐层验证。
sql
-- 分层：明细 → 订单级 → 客户级 → 分档统计
WITH order_level AS (
  SELECT order_no, customer, region,
         sum(qty * unit_price) AS order_gmv
  FROM orders
  WHERE is_paid
  GROUP BY order_no, customer, region
),
customer_level AS (
  SELECT customer, count(*) AS order_cnt, sum(order_gmv) AS customer_gmv
  FROM order_level
  GROUP BY customer
)
SELECT
  CASE WHEN customer_gmv >= 1000 THEN 'A' WHEN customer_gmv >= 500 THEN 'B' ELSE 'C' END AS tier,
  count(*)          AS customer_cnt,
  sum(customer_gmv) AS tier_gmv
FROM customer_level
GROUP BY tier
ORDER BY tier;
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 观察聚合计划。 关注是否出现 HashAggregate（通常优于 GroupAggregate 的排序开销）、Sort 是否可被索引消除，以及是否存在 Seq Scan 全表扫描。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT category, sum(qty * unit_price) AS gmv
FROM orders
GROUP BY category
ORDER BY gmv DESC;
  1. 大表统计考虑预聚合或物化视图。 对实时性要求不高的报表，用物化视图（Materialized View）定期刷新，把秒级聚合降为毫秒级查询。
sql
-- 物化视图：定期刷新的日维度报表
CREATE MATERIALIZED VIEW mv_daily_gmv AS
SELECT created_at AS stat_date,
       category,
       count(*)             AS item_cnt,
       sum(qty * unit_price) AS gmv
FROM orders
GROUP BY created_at, category;

CREATE UNIQUE INDEX ON mv_daily_gmv (stat_date, category);

-- 刷新（并发刷新需唯一索引，且不会阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_gmv;

下一节

继续学习：表连接基础

参考链接 ​