MySQL窗口函数与递归CTE：报表查询的两种写法

Author： zz1984
发布时间：October 31, 2026
8 views
No comments
7629 字数
Categories：技术教程

为什么自建站的聚合报表总要绕一圈

做站长久了，总会遇到这类需求：想知道每个栏目最近三篇更新的间隔、想看每个用户最近一次登录是第几次、想算每天新增文章的七日移动平均、想统计每个分类在全部文章里的排名。放在几年前，这些需求的答案一律是「先写一条 GROUP BY 求出中间结果，再用应用层的 for 循环拼出来」，或者干脆把整张表拉进 PHP 里排序。表小的时候能跑，表到了几十万行以后，一次后台报表就能把 MySQL 的连接池占满，前台跟着变慢。

MySQL 8.0 提供的窗口函数和公共表表达式（CTE，其中递归的那种叫递归 CTE）正好覆盖了这两类场景：窗口函数在保留明细行的同时做聚合和排名，递归 CTE 则能在一条 SQL 里做层级展开和序列生成。本文用一个接近真实的自建博客库做例子，把这两类语法在「报表、分页、树形结构、日期补全」四个具体场景里的写法、性能影响和常见坑一次讲清楚。所有语句都在 MySQL 8.0 上验证过，5.7 用户会在相应位置看到不兼容提示。

建一张能说明问题的示例表

先在测试库里建一张文章表和一张分类表。分类表带自关联的 parent 字段，用来演示递归；文章表带作者、发布时间和浏览量，用来演示窗口函数。字段刻意保持精简，避免例子被无关列淹没。

CREATE TABLE category (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  parent_id INT UNSIGNED NOT NULL DEFAULT 0,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL,
  author VARCHAR(32) NOT NULL,
  title VARCHAR(200) NOT NULL,
  created_at DATETIME NOT NULL,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_cat_time (category_id, created_at),
  KEY idx_author_time (author, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

两个索引不是随手加的。窗口函数和窗口计算都伴随 PARTITION BY 与 ORDER BY，如果分区列和排序列正好落在同一个联合索引上，MySQL 可以直接按索引顺序扫描，省掉一次内部排序。下面每一条查询都会体现这一点带来的差别。

窗口函数：保留明细的同时做聚合

普通聚合的关键词是「塌缩」——GROUP BY 之后每个分组只剩一行。窗口函数的关键词是「叠加」——每一行都保留，同时多出若干列表示它所在分组的信息。语法上就是在函数名后面跟一个 OVER (...) 子句，括号里写 PARTITION BY（怎么分组）和 ORDER BY（组内怎么排序），必要时再加 ROWS BETWEEN ... 限定参与计算的行范围。

SELECT id, title, author, views,
       ROW_NUMBER() OVER (PARTITION BY author ORDER BY views DESC) AS rn,
       RANK()       OVER (PARTITION BY author ORDER BY views DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY author ORDER BY views DESC) AS drk
FROM post
WHERE created_at >= '2026-01-01';

三个排名函数的差别只有一句话：ROW_NUMBER 不管并列，永远给出 1、2、3；RANK 并列时占位，并列两个第一之后直接跳到第三；DENSE_RANK 并列时不占位，并列两个第一之后是第二。选错的那个后果很具体：用 ROW_NUMBER 取「每个作者浏览量最高的那篇」没问题，但如果需求是「取每个作者的前三档」，ROW_NUMBER 会把并列第一的两篇文章只留下一篇。

取每组最新一条：别再写相关子查询

本站后台曾经最慢的一条查询是「每个分类最新的一篇文章」，老写法是相关子查询：外层扫全部文章，内层对每篇文章再查一次同分类里时间更大的记录。窗口函数的写法是先在任何过滤之前打好行号，再在外层筛行号等于一的那些行。

SELECT id, category_id, title, created_at
FROM (
  SELECT id, category_id, title, created_at,
         ROW_NUMBER() OVER (PARTITION BY category_id
                            ORDER BY created_at DESC, id DESC) AS rn
  FROM post
) t
WHERE rn = 1;

有两个细节值得单独说明。第一，ORDER BY created_at DESC, id DESC 里的 id 是必需的：同一天发布的多篇文章发布时间可能精确到秒都相同，只按时间排序时谁排第一是不确定的，多次执行结果会跳动，加自增主键做兜底才能稳定。第二，筛 rn 的动作必须放在外层，不能写在窗口函数的同一层 WHERE 里——SQL 的执行顺序决定了窗口函数在 WHERE 之后才求值，同层引用会报「Unknown column」。

移动平均与环比：用 ROWS BETWEEN 划框

日增文章数的七日移动平均是典型的「框内聚合」。窗口默认的范围是「从分组开头到当前行」，要算滑动窗口必须显式写 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW。这里一定用 ROWS 而不是 RANGE：ROWS 按物理行数算，RANGE 按值的范围算，如果某天没有数据导致日期不连续，RANGE 的语义会变得很反直觉。

SELECT d, cnt,
       ROUND(AVG(cnt) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7,
       cnt - LAG(cnt) OVER (ORDER BY d) AS diff
FROM (
  SELECT DATE(created_at) AS d, COUNT(*) AS cnt
  FROM post GROUP BY DATE(created_at)
) t;

LAG(cnt) 取上一行的值，做环比就是这么简单；对应的 LEAD 取下一行。注意前六天的移动平均是「不足七天的平均值」，如果业务要求必须满七天窗口才出数，加一列 ROW_NUMBER() OVER (ORDER BY d) 再在外层判大于等于七，比在应用层补逻辑干净得多。

递归 CTE：把循环搬进 SQL

分类表是多层结构，前台需要输出「分类 A / 子分类 B / 孙分类 C」这样的完整路径，后台还需要按父分类汇总所有子孙分类的文章数。传统做法是在 PHP 里递归查询，一层一次 SQL，层级一深就是经典的 N+1 问题。递归 CTE 用一条语句完成。

结构上它分两部分，中间用 UNION ALL 连接：第一部分叫锚点成员，是不引用自身的初始结果集；第二部分叫递归成员，引用 CTE 的名字，每次以上一轮的结果为输入。MySQL 反复执行第二部分直到不再产生新行，默认最多一千轮，可以用会话变量 cte_max_recursion_depth 调整。

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, CAST(name AS CHAR(500)) AS path, 0 AS depth
  FROM category WHERE parent_id = 0
  UNION ALL
  SELECT c.id, c.parent_id, c.name,
         CONCAT(tree.path, ' / ', c.name), tree.depth + 1
  FROM category c JOIN tree ON c.parent_id = tree.id
  WHERE tree.depth < 8
)
SELECT * FROM tree ORDER BY path;

三处细节是这条语句能安全跑在生产上的关键。其一，CAST(name AS CHAR(500)) 不能省：MySQL 推断锚点成员的路径列长度时按第一个表达式算，如果不显式加宽，拼接两层之后就会触发截断报错，报错信息只说「Data too long」，很难联想到是类型推断问题。其二，WHERE tree.depth < 8 是防环保险：parent_id 数据出过脏值、A 的父是 B 而 B 的父又是 A 的时候，没有深度上限的递归会一路撞到一千轮上限然后整条查询失败。其三，depth 同时可以当作展示缩进用，前端拿它乘以固定像素就是树形缩进，不需要另算。

递归 CTE 补全缺失日期

做流量报表时，某天没有访问数据，GROUP BY 出来的结果里就整天缺失，前端折线图会把这天「缩掉」，图上看起来一切正常，实际少了一个点。标准解法是用递归 CTE 先生成一串连续日期，再左连接真实数据。

WITH RECURSIVE days AS (
  SELECT DATE('2026-10-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days
  WHERE d < DATE('2026-10-31')
)
SELECT days.d, IFNULL(v.cnt, 0) AS cnt
FROM days
LEFT JOIN (
  SELECT DATE(created_at) AS d, COUNT(*) AS cnt FROM post GROUP BY DATE(created_at)
) v ON v.d = days.d;

生成五年的日期也就一千八百多行，比在应用层写循环、或者在数据库里常驻一张日历表要省维护成本。如果数据量真的到了要常驻日历表的地步（比如需要跨多张表多种粒度关联），再换表也不迟。

性能：窗口函数不是免费的

窗口函数用法上很优雅，但它内部通常要建临时表并排序。用 EXPLAIN 看执行计划，出现 Using temporary; Using filesort 是常态。有三条实践能显著减少代价。

第一，把过滤条件尽早写出，用派生表先缩小行数。窗口函数无法命中索引来跳过排序，行数越少代价越低。上面「取每组最新一条」的例子如果只关心最近三个月，就应该把时间条件写进派生表内部。

SELECT id, category_id, title FROM (
  SELECT id, category_id, title,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC, id DESC) AS rn
  FROM post WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
) t WHERE rn = 1;

第二，让分区列和排序列和既有索引顺序一致。idx_cat_time (category_id, created_at) 恰好是 PARTITION BY category_id ORDER BY created_at 的顺序，优化器可以顺扫索引直接得到分组好的有序流，省掉一次显式排序。反过来，如果写成 PARTITION BY author ORDER BY created_at 却只有分类索引，排序就躲不掉。

第三，不要为了省一次查询把窗口函数硬塞进大表的全量扫描里。一次后台报表跑十秒可以接受，但如果这段 SQL 被包在列表页的分页查询里，就变成了每个访客都触发十秒排序。判断标准很简单：窗口函数只出现在后台管理、定时统计、导出这三类脚本里，前台列表页一律走索引加 LIMIT。

五个容易踩的坑

第一，窗口函数和 GROUP BY 混用导致重复计数。在同一个 SELECT 里既写 GROUP BY 又写窗口函数，窗口是在分组之后的行上计算的，而不是原始明细行上。想要「明细行 + 分组统计」必须用窗口函数做统计、或者先做子查询聚合再连接，不能指望同层 GROUP BY 兼顾两者。

第二，把窗口函数写在 WHERE 里。窗口函数的求值阶段在 WHERE 之后，别名也不能在同一层的 WHERE 中使用。所有针对窗口结果的条件都必须在外层查询里写，这是写错频率最高的一条。

第三，默认窗口范围不是整组。带 ORDER BY 的聚合窗口函数默认范围是「组首到当前行」，所以 SUM(views) OVER (PARTITION BY author ORDER BY created_at) 得到的是累计值而不是作者总计。要总计就去掉 ORDER BY，要累计就明确接受这个语义。

第四，递归 CTE 忘记上限。脏数据造成环时查询会跑到 cte_max_recursion_depth 才停，在生产上表现为一条 SQL 卡住并占满 CPU。永远写上深度条件，并且给 parent_id 加一条「不能等于自身」的约束或应用层校验。

第五，5.7 上没有这些语法。站上如果还有跑 5.7 的老库，直接复制过去会报语法错误。判断兼容性时不要只看版本号，还要看 sql_mode 与客户端：某些云数据库的代理层对 WITH 的支持也有差异，先在测试库跑一条最简的 SELECT ROW_NUMBER() OVER (ORDER BY id) FROM t LIMIT 1 确认。

小结：什么时候该用、什么时候不该用

窗口函数和递归 CTE 解决的是同一类问题：原本要在应用层用循环拼装的逻辑，可以下沉成一条声明式 SQL。它们的收益在报表、去重取最新、树形展开、日期补全这四个场景最明显，这四类场景在自建站里几乎每周都会遇到。反之，凡是前台高频访问的列表查询、以及需要走覆盖索引快速返回的场景，都该继续用索引加 LIMIT，不要因为语法新就往上套。

落地顺序建议是：先把后台那几条最慢的报表用窗口函数重写，用 EXPLAIN 对比前后是否去掉了相关子查询；再把分类树的递归查询换成递归 CTE，顺手补上深度上限；最后把日期补全逻辑从 PHP 移到 SQL 里。三步做完之后，后台报表的 SQL 总量会明显下降，而每次排查时能看的「一条语句」比散在各处的循环更容易推理。这套语法不难，难的是判断哪些查询值得改写——先看慢日志，按总耗时占比排前十，改前五条就够本了。

Last modification：October 31st, 2026 at 10:26 pm

MySQL窗口函数与递归CTE：报表查询的两种写法

zz1984 • 2026 年 10 月 31 日

为什么自建站的聚合报表总要绕一圈

建一张能说明问题的示例表

CREATE TABLE category (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  parent_id INT UNSIGNED NOT NULL DEFAULT 0,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  category_id INT UNSIGNED NOT NULL,
  author VARCHAR(32) NOT NULL,
  title VARCHAR(200) NOT NULL,
  created_at DATETIME NOT NULL,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_cat_time (category_id, created_at),
  KEY idx_author_time (author, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

窗口函数：保留明细的同时做聚合

SELECT id, title, author, views,
       ROW_NUMBER() OVER (PARTITION BY author ORDER BY views DESC) AS rn,
       RANK()       OVER (PARTITION BY author ORDER BY views DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY author ORDER BY views DESC) AS drk
FROM post
WHERE created_at >= '2026-01-01';

取每组最新一条：别再写相关子查询

SELECT id, category_id, title, created_at
FROM (
  SELECT id, category_id, title, created_at,
         ROW_NUMBER() OVER (PARTITION BY category_id
                            ORDER BY created_at DESC, id DESC) AS rn
  FROM post
) t
WHERE rn = 1;

移动平均与环比：用 ROWS BETWEEN 划框

SELECT d, cnt,
       ROUND(AVG(cnt) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS ma7,
       cnt - LAG(cnt) OVER (ORDER BY d) AS diff
FROM (
  SELECT DATE(created_at) AS d, COUNT(*) AS cnt
  FROM post GROUP BY DATE(created_at)
) t;

递归 CTE：把循环搬进 SQL

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, CAST(name AS CHAR(500)) AS path, 0 AS depth
  FROM category WHERE parent_id = 0
  UNION ALL
  SELECT c.id, c.parent_id, c.name,
         CONCAT(tree.path, ' / ', c.name), tree.depth + 1
  FROM category c JOIN tree ON c.parent_id = tree.id
  WHERE tree.depth < 8
)
SELECT * FROM tree ORDER BY path;

递归 CTE 补全缺失日期

WITH RECURSIVE days AS (
  SELECT DATE('2026-10-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days
  WHERE d < DATE('2026-10-31')
)
SELECT days.d, IFNULL(v.cnt, 0) AS cnt
FROM days
LEFT JOIN (
  SELECT DATE(created_at) AS d, COUNT(*) AS cnt FROM post GROUP BY DATE(created_at)
) v ON v.d = days.d;

性能：窗口函数不是免费的

窗口函数用法上很优雅，但它内部通常要建临时表并排序。用 EXPLAIN 看执行计划，出现 Using temporary; Using filesort 是常态。有三条实践能显著减少代价。

SELECT id, category_id, title FROM (
  SELECT id, category_id, title,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC, id DESC) AS rn
  FROM post WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
) t WHERE rn = 1;

MySQL窗口函数与递归CTE：报表查询的两种写法

为什么自建站的聚合报表总要绕一圈

建一张能说明问题的示例表

窗口函数：保留明细的同时做聚合

取每组最新一条：别再写相关子查询

移动平均与环比：用 ROWS BETWEEN 划框

递归 CTE：把循环搬进 SQL

递归 CTE 补全缺失日期

性能：窗口函数不是免费的

五个容易踩的坑

小结：什么时候该用、什么时候不该用

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

Let's Encrypt 免费 HTTPS 证书实战：申请、自动续期与常见问题排查

宝塔面板安全使用指南：端口收口、两步验证与数据库防暴露，图形化建站如何不踩安全坑

弱网适配：地铁里也能打开的首屏优化

页面排名掉了怎么查：五层排查顺序

自建站密码存储与登录安全：从bcrypt到动态口令

MySQL窗口函数与递归CTE：报表查询的两种写法

为什么自建站的聚合报表总要绕一圈

建一张能说明问题的示例表

窗口函数：保留明细的同时做聚合

取每组最新一条：别再写相关子查询

移动平均与环比：用 ROWS BETWEEN 划框

递归 CTE：把循环搬进 SQL

递归 CTE 补全缺失日期

性能：窗口函数不是免费的

五个容易踩的坑

小结：什么时候该用、什么时候不该用