ShowDocs
搜索文章Ctrl K

外观

← 返回MySQL列表
知识专题

MySQL 窗口函数与每组 Top N ​

窗口函数在保留明细行的同时计算组内序号、累计值或前后行。GROUP BY 通常把多行汇成一行，两者适用的结果粒度不同。示例沿用 SELECT 查询样例，使用 MySQL 8.0+ 的窗口函数，以 8.4 为准。

常用方法对照 ​

需求写法关键点
每组严格取 N 行ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC)并列时也分配不同序号，需唯一的次级排序
允许并列、后续跳名次RANK() OVER (ORDER BY amount DESC)例如 1、1、3
允许并列、不跳名次DENSE_RANK() OVER (ORDER BY amount DESC)例如 1、1、2
每行附带本组总额SUM(amount) OVER (PARTITION BY customer_id)不加窗口 ORDER BY，得到整组总额
按顺序累计SUM(amount) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)显式定义行窗口，避免同序值的默认 frame 误解
与上一条比较LAG(amount) OVER (PARTITION BY customer_id ORDER BY created_at, id)首行没有上一条，结果为 NULL
与下一条比较LEAD(amount) OVER (...)末行没有下一条

每个客户最新一笔订单 ​

sql
WITH ranked AS (
  SELECT id, customer_id, amount, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders
)
SELECT id, customer_id, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

预期为客户 1 的订单 102（500），客户 2 的订单 103（200）。Wu 没有订单，所以不在结果中；需要保留所有用户时，再用 users 左连接这个结果。

将 rn = 1 改为 rn <= 2 可每组取最多两行。窗口计算结果需要在外层筛选，不能在同层 WHERE 中直接使用 rn；MySQL 8.4 也没有 QUALIFY 语法。

同分时如何排名 ​

sql
WITH scores AS (
  SELECT 1 AS uid, 100 AS score
  UNION ALL SELECT 2, 100
  UNION ALL SELECT 3, 80
)
SELECT uid, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, uid) AS row_no,
       RANK() OVER (ORDER BY score DESC) AS rank_no,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_no
FROM scores ORDER BY score DESC, uid;
uidscorerow_norank_nodense_no
1100111
2100211
380332

RANK/DENSE_RANK 的窗口排序中没有加入 uid，否则相同 score 也会因为 uid 不同而不再并列。最终输出用 uid 稳定排序，与“怎样定义并列”是两个问题。

按客户累计消费，并比较上一笔 ​

sql
SELECT id, customer_id, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id ORDER BY created_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       LAG(amount) OVER (
         PARTITION BY customer_id ORDER BY created_at, id
       ) AS previous_amount
FROM orders
ORDER BY customer_id, created_at, id;

预期订单 101 的累计值为 800、上一笔为 NULL；102 的累计值为 1300、上一笔为 800；103 属于另一客户，累计值为 200、上一笔为 NULL。

WHERE 先决定哪些行参与窗口计算。若只想显示最近一笔，但累计值需要覆盖全部历史，应在计算窗口后用外层查询筛选；在内层先截掉历史会改变累计值。

使用边界 ​

  • 窗口 ORDER BY 定义计算顺序，不保证最终返回顺序；外层需要自己的 ORDER BY。
  • 有排序的聚合窗口要明确 frame。LAST_VALUE 尤其容易只看到当前窗口尾部，而不是整组最后一行。
  • 每组 Top N 并不意味着只扫描 N 行。索引、排序、临时结果和分组规模都影响成本，需看执行计划。
  • 本文结果按小样例推导，本次没有连接 MySQL 执行查询。

参考：窗口函数概念、窗口函数列表、窗口 frame。

继续阅读