MySQL 8 窗口函数

MySQL 8 窗口函数要点

• ROW_NUMBER() 给每行一个不重复序号，同值也绝不并列
• RANK() 排名会跳号，DENSE_RANK() 排名不跳号
• OVER (PARTITION BY 分组 ORDER BY 排序) 是窗口函数的灵魂
• PARTITION BY 决定『在哪一组内』计算，类似 GROUP BY 但不合并行
• 聚合函数也能接 OVER，算累计和、移动平均
• NTILE(n) 把数据均匀切成 n 份，做分位分析很方便

MySQL 8 窗口函数示例

						-- 每个用户的订单按金额排名
						SELECT user_id, order_id, amount,
						       RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk
						FROM orders;

						-- 按时间累计成交额
						SELECT order_id, created_at, amount,
						       SUM(amount) OVER (ORDER BY created_at) AS running_total
						FROM orders;

						-- 取每类商品销量前 3
						SELECT * FROM (
						  SELECT product_id, sales,
						         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
						  FROM product
						) t WHERE rn ≤ 3;

MySQL 函数知识点