MySQL 开窗函数

开窗函数

→ 返回 MySQL

8.0+。在保留所有行的前提下按分区计算排名、累计等；与 GROUP BY（每组一行）不同。

语法

窗口函数(...) OVER (
  [PARTITION BY 分组列 [, ...]]
  [ORDER BY 排序列 [, ...]]
  [frame_clause]
)

排名

SELECT id, user_id, amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS drk
FROM orders;

函数	说明
ROW_NUMBER()	连续序号
RANK()	并列跳号
DENSE_RANK()	并列不跳号
NTILE(n)	分 n 桶

LAG / LEAD / FIRST_VALUE

SELECT user_id, created_at, amount,
  LAG(amount, 1)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amt,
  LEAD(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS next_amt
FROM orders;

累计

SELECT DATE(created_at) AS d, amount,
  SUM(amount) OVER (ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders WHERE status = 'paid';

Top N 每分组

SELECT * FROM (
  SELECT o.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t WHERE rn <= 3;

MySQL 无 QUALIFY，用外层 WHERE rn <= N。

开窗 vs GROUP BY

	GROUP BY	OVER
行数	每组一行	保留原行
典型	汇总	排名、累计

---

相关

• 联表与查询
• PostgreSQL 高级查询（窗口函数更成熟）