第 07 章：聚合与窗口函数

zjc 于 2026-01-07 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。聚合回答“一组数据的总量是多少”，窗口函数回答“每行在分组内的排名、累计和前后关系是什么”。它们是报表、运营分析和数据治理的基础工具。

7.1 聚合函数

常用聚合函数：

函数	作用	是否忽略 NULL
`COUNT(*)`	统计行数	不适用
`COUNT(expr)`	统计 expr 非 NULL 行数	是
`SUM`	求和	是
`AVG`	平均值	是
`MAX`	最大值	是
`MIN`	最小值	是
`GROUP_CONCAT`	拼接字符串	是

示例：

SELECT
  COUNT(*) AS total_orders,
  COUNT(paid_at) AS paid_orders,
  SUM(total_amount) AS total_amount,
  AVG(total_amount) AS avg_amount,
  MIN(created_at) AS first_created,
  MAX(created_at) AS last_created
FROM orders;

COUNT(*) 统计行；COUNT(paid_at) 统计支付时间不为空的行。如果 paid_at 为 NULL 表示未支付，两者差值就是未支付订单数。

7.2 GROUP BY

按商户统计已支付订单：

SELECT
  merchant_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS gmv
FROM orders
WHERE status = 'PAID'
GROUP BY merchant_id
ORDER BY gmv DESC;

执行逻辑可以理解为：

FROM / WHERE 得到基础行集；
按 GROUP BY 键分组；
对每组计算聚合函数；
HAVING 过滤分组；
ORDER BY 排序输出。

7.2.1 ONLY_FULL_GROUP_BY

MySQL 8.0 默认开启 ONLY_FULL_GROUP_BY。以下 SQL 可能报错：

SELECT merchant_id, order_no, COUNT(*)
FROM orders
GROUP BY merchant_id;

原因是 order_no 不在分组键中，也不在聚合函数中。一个商户有多笔订单时，MySQL 不知道输出哪一笔的 order_no。

正确写法：

SELECT merchant_id, COUNT(*) AS order_count
FROM orders
GROUP BY merchant_id;

如需明细，先按订单粒度查询，再聚合。

7.3 HAVING

查询订单数大于等于 2 的用户：

SELECT
  user_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY order_count DESC;

普通条件尽量放 WHERE，因为可以先过滤再分组，减少参与聚合的数据量。HAVING 用于过滤聚合结果。

条件	放哪里
原始行条件	WHERE
聚合结果条件	HAVING
窗口函数结果	外层查询 WHERE

7.4 WITH ROLLUP

按类目和小计汇总：

SELECT
  COALESCE(category, 'ALL') AS category,
  COUNT(*) AS product_count,
  SUM(price) AS price_sum
FROM products
GROUP BY category WITH ROLLUP;

ROLLUP 会在每个分组后追加汇总行，最后一行是总计。它适合简单报表，复杂层级报表建议使用专门的分析任务或 BI 模型。

7.5 GROUP_CONCAT

拼接用户订单号：

SELECT
  user_id,
  GROUP_CONCAT(
    order_no
    ORDER BY created_at DESC
    SEPARATOR ','
  ) AS order_nos
FROM orders
GROUP BY user_id;

结果长度受 group_concat_max_len 限制：

SHOW VARIABLES LIKE 'group_concat_max_len';
SET SESSION group_concat_max_len = 10240;

如果结果可能很长，应查询明细，由应用聚合，或使用 JSON 数组：

SELECT
  user_id,
  JSON_ARRAYAGG(order_no) AS order_nos
FROM orders
GROUP BY user_id;

7.6 窗口函数基础

窗口函数在保留每一行的同时计算一组相关行的结果。

基本结构：

function() OVER (
  PARTITION BY partition_columns
  ORDER BY sort_columns
  frame_definition
)

子句	作用
`PARTITION BY`	分区，类似分组但不折叠行
`ORDER BY`	区内排序，决定排名和累计方向
`frame`	定义移动窗口范围

按用户给订单编号：

SELECT
  user_id,
  order_no,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY created_at DESC, id DESC
  ) AS rn
FROM orders;

MySQL 8.0 开始支持窗口函数，5.7 不支持。

7.7 排名函数

函数	行为
`ROW_NUMBER`	连续唯一编号：1、2、3
`RANK`	并列同名，跳号：1、1、3
`DENSE_RANK`	并列同名，不跳号：1、1、2

SELECT
  product_name,
  category,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY category ORDER BY price DESC
  ) AS row_no,
  RANK() OVER (
    PARTITION BY category ORDER BY price DESC
  ) AS rank_no,
  DENSE_RANK() OVER (
    PARTITION BY category ORDER BY price DESC
  ) AS dense_no
FROM products;

7.7.1 每组前 N 条

取每个类目价格前二商品：

WITH ranked_products AS (
  SELECT
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY price DESC, id
    ) AS rn
  FROM products
)
SELECT product_name, category, price
FROM ranked_products
WHERE rn <= 2;

窗口函数不能直接写在 WHERE 中，因此需要派生表或 CTE。

7.7.2 用户最近一单

WITH ranked_orders AS (
  SELECT
    u.username,
    o.order_no,
    o.created_at,
    ROW_NUMBER() OVER (
      PARTITION BY o.user_id
      ORDER BY o.created_at DESC, o.id DESC
    ) AS rn
  FROM orders AS o
  INNER JOIN users AS u ON u.id = o.user_id
)
SELECT username, order_no, created_at
FROM ranked_orders
WHERE rn = 1;

7.8 聚合窗口函数

累计支付金额：

SELECT
  user_id,
  order_no,
  created_at,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM orders
WHERE status = 'PAID';

常用 frame：

Frame	含义
`UNBOUNDED PRECEDING`	分区第一行
`CURRENT ROW`	当前行
`N PRECEDING`	前 N 行
`N FOLLOWING`	后 N 行
`UNBOUNDED FOLLOWING`	分区最后一行

三日移动销售额：

WITH daily_gmv AS (
  SELECT DATE(paid_at) AS pay_date, SUM(total_amount) AS daily_amount
  FROM orders
  WHERE status = 'PAID'
    AND paid_at IS NOT NULL
  GROUP BY DATE(paid_at)
)
SELECT
  pay_date,
  daily_amount,
  AVG(daily_amount) OVER (
    ORDER BY pay_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_gmv;

7.9 偏移函数

函数	作用
`LAG`	取排序后前一行
`LEAD`	取排序后后一行
`FIRST_VALUE`	分区第一行
`LAST_VALUE`	分区最后一行
`NTH_VALUE`	分区第 N 行

环比示例：

WITH daily_gmv AS (
  SELECT DATE(paid_at) AS pay_date, SUM(total_amount) AS amount
  FROM orders
  WHERE status = 'PAID'
    AND paid_at IS NOT NULL
  GROUP BY DATE(paid_at)
)
SELECT
  pay_date,
  amount,
  LAG(amount, 1) OVER (ORDER BY pay_date) AS prev_amount,
  amount - LAG(amount, 1) OVER (ORDER BY pay_date) AS diff_amount,
  ROUND(
    (amount - LAG(amount, 1) OVER (ORDER BY pay_date))
      / LAG(amount, 1) OVER (ORDER BY pay_date) * 100,
    2
  ) AS diff_percent
FROM daily_gmv
ORDER BY pay_date;

如果首日没有前值，LAG 返回 NULL，需要用 IFNULL 处理展示。

7.10 去重与差集

窗口去重：

WITH ranked_rows AS (
  SELECT
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY order_no, product_id
      ORDER BY id DESC
    ) AS rn
  FROM order_items_dedup AS t
)
SELECT *
FROM ranked_rows
WHERE rn = 1;

窗口去重适合查询，不适合替代唯一约束。数据质量仍必须靠主键和唯一键保证。

查询最近 7 天没有下单的用户：

SELECT u.id, u.username
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
    AND o.created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
);

7.11 报表实践

商户日报：

SELECT
  DATE(o.paid_at) AS stat_date,
  o.merchant_id,
  COUNT(*) AS paid_orders,
  SUM(o.paid_amount) AS paid_amount,
  COUNT(DISTINCT o.user_id) AS buyers,
  ROUND(AVG(o.paid_amount), 2) AS avg_paid_amount
FROM orders AS o
WHERE o.status = 'PAID'
  AND o.paid_at >= '2026-08-01'
  AND o.paid_at < '2026-09-01'
GROUP BY DATE(o.paid_at), o.merchant_id
ORDER BY stat_date, paid_amount DESC;

注意：

日期范围使用半开区间；
指标口径写清楚；
状态过滤放在 WHERE；
报表优先走只读实例；
高频报表考虑预聚合表；
巨量历史扫描交给分析库。

预聚合表示例：

CREATE TABLE merchant_daily_stats (
    stat_date DATE NOT NULL,
    merchant_id BIGINT UNSIGNED NOT NULL,
    paid_orders BIGINT UNSIGNED NOT NULL DEFAULT 0,
    paid_amount DECIMAL(14, 2) NOT NULL DEFAULT 0.00,
    buyers BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
      ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (stat_date, merchant_id),
    KEY idx_merchant_date (merchant_id, stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

7.12 性能建议

聚合前先缩小数据范围；
分组列尽量与索引前缀匹配；
大表 DISTINCT 和 GROUP BY 可能排序或临时表；
窗口函数的 PARTITION BY 和 ORDER BY 也要考虑索引；
避免在超宽窗口上无限制扫描；
报表和交易查询隔离；
数据量增长后引入预聚合或 OLAP；
使用 EXPLAIN ANALYZE 验证真实耗时。

本章小结

GROUP BY 会把多行折叠成组，窗口函数保留每行并计算组内关系。COUNT(*) 与 COUNT(col) 的 NULL 差异、WHERE 与 HAVING 的阶段差异、窗口函数不能直接用于 WHERE，是实际写 SQL 时最容易出错的地方。排名、Top N、累计、同比环比和移动平均都可以用窗口函数清晰表达，但必须结合索引和数据量控制成本。

思考题

COUNT(*)、COUNT(1)、COUNT(col) 有什么区别？
为什么窗口函数不能直接写在 WHERE 中？
ROW_NUMBER、RANK、DENSE_RANK 在并列值上有什么差异？
如何用 LAG 计算每日销售额环比？
设计一个订单日报预聚合任务，说明幂等和延迟数据处理方式。