SQL 窗口函数进阶

SQL窗口函数进阶：FIRST_VALUE/LAST_VALUE/NTH_VALUE位置窗口、PERCENT_RANK/CUME_DIST分布窗口、窗口帧ROWS/RANGE/GROUPS精确控制、命名窗口WINDOW子句 · 难度：入门 · +10XP

SQL 窗口函数进阶 —— 排名、偏移、累积

窗口函数（Window Functions）在保留所有行的同时做分组计算。它们不折叠行，比GROUP BY灵活得多。

排名函数

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS 行号,
  RANK() OVER (ORDER BY score DESC) AS 排名,
  DENSE_RANK() OVER (ORDER BY score DESC) AS 密集排名,
  NTILE(4) OVER (ORDER BY score DESC) AS 四分位
FROM students;

偏移函数

SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS 昨日收入,
  LEAD(revenue, 1) OVER (ORDER BY date) AS 明日收入,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS 环比变化
FROM daily_sales;

动手练习

1. 基础练习：为销售数据添加排名和环比变化列。
2. 进阶应用：用窗口函数计算7天移动平均。
3. 项目实战：在报表中用窗口函数替代复杂的子查询。