什么是SQL窗口函数

SQL窗口函数（Window Functions）是SQL标准中最强大的功能之一，它允许对一组相关行进行计算，同时保留每一行的原始细节。通俗说：传统GROUP BY会把多行压缩成一行，窗口函数保留每行的同时还能做”分组内的计算”。比如计算每个员工的部门内排名、每个月的累计销售、用户的前后行为对比，都是窗口函数的典型场景。本教程从实战角度详解10个可直接复用的高价值模板。

第一步：理解窗口函数语法

窗口函数基础语法：

函数名() OVER (
[PARTITION BY 分组列]
[ORDER BY 排序列]
[ROWS/RANGE 窗口范围]
) [AS 别名]

三个核心子句：

  • PARTITION BY：分组（类似GROUP BY）
  • ORDER BY：组内排序
  • ROWS/RANGE：定义窗口边界

第二步：ROW_NUMBER()行号

ROW_NUMBER()给每行一个唯一序号：

语法：ROW_NUMBER() OVER (PARTITION BY 分组列 ORDER BY 排序列)

实战场景：每个部门工资最高的员工

SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS rn
FROM employees
) t
WHERE rn = 1;

输出每个部门工资最高的1位员工。

第三步：RANK和DENSE_RANK排名

两个排名函数的区别：

  • RANK()：相同值同名次，后续跳号（1,1,3）
  • DENSE_RANK()：相同值同名次，后续不跳号（1,1,2）

实战：销售排行榜

SELECT
salesperson,
sales_amount,
RANK() OVER (ORDER BY sales_amount DESC) AS rk,
DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS drk
FROM sales;

提示：RANK用于颁奖（前三名都有奖），DENSE_RANK用于分级（前10%是A级）

第四步：LAG和LEAD前后行

LAG()取前面行的值，LEAD()取后面行的值。

语法：

  • LAG(列, offset, default) OVER (ORDER BY ...)
  • LEAD(列, offset, default) OVER (ORDER BY ...)

实战：每日销售额环比

SELECT
date,
sales,
LAG(sales) OVER (ORDER BY date) AS prev_sales,
sales - LAG(sales) OVER (ORDER BY date) AS diff,
ROUND(
(sales - LAG(sales) OVER (ORDER BY date)) * 100.0 /
LAG(sales) OVER (ORDER BY date),
2
) AS growth_pct
FROM daily_sales;

第五步：SUM() OVER累计和

SUM() OVER实现累计求和（running total）：

实战：累计销售达成率

SELECT
month,
sales,
SUM(sales) OVER (
ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sales
FROM monthly_sales;

ROWS BETWEEN定义窗口边界：

  • UNBOUNDED PRECEDING：从分组开始
  • CURRENT ROW：当前行
  • UNBOUNDED FOLLOWING：到分组结束
  • n PRECEDING：前n行
  • n FOLLOWING：后n行

第六步：NTILE分桶

NTILE(n)把数据等分为n桶：

实战：用户价值分层（RFM分析）

SELECT
user_id,
total_amount,
NTILE(4) OVER (ORDER BY total_amount DESC) AS quartile
FROM user_rfm;

quartile=1是头部用户，quartile=4是尾部用户。

第七步：FIRST_VALUE和LAST_VALUE

取窗口内第一/最后一个值：

实战：每个用户首次购买距今天数

SELECT
user_id,
purchase_date,
FIRST_VALUE(purchase_date) OVER (
PARTITION BY user_id
ORDER BY purchase_date
) AS first_purchase,
CURRENT_DATE - FIRST_VALUE(purchase_date) OVER (
PARTITION BY user_id
ORDER BY purchase_date
) AS days_since_first
FROM purchases;

第八步：移动平均

实战：7天移动平均销售额

SELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;

第九步：用户留存分析

实战：用户次日/7日/30日留存

WITH first_purchase AS (
SELECT user_id, MIN(purchase_date) AS first_date
FROM purchases
GROUP BY user_id
)
SELECT
f.user_id,
f.first_date,
MAX(CASE WHEN p.purchase_date = f.first_date + INTERVAL '1 day' THEN 1 ELSE 0 END) AS d1_retained,
MAX(CASE WHEN p.purchase_date = f.first_date + INTERVAL '7 day' THEN 1 ELSE 0 END) AS d7_retained
FROM first_purchase f
LEFT JOIN purchases p ON f.user_id = p.user_id
GROUP BY f.user_id, f.first_date;

第十步：性能优化

窗口函数在大数据上的优化：

  1. 避免在窗口函数外再用GROUP BY（先聚合再用窗口）
  2. 用PostgreSQL/MySQL 8+ 优化器
  3. 建立合适的索引：CREATE INDEX idx_user_date ON purchases(user_id, purchase_date)
  4. 避免ROWS BETWEEN和RANGE混合使用
  5. 用EXPLAIN分析查询计划

三大数据库语法对比

PostgreSQL：
• 最完善，支持所有窗口函数
• 支持RANGE BETWEEN INTERVAL ‘7 day’ PRECEDING

MySQL 8+：
• 支持ROW_NUMBER/RANK/DENSE_RANK/LAG/LEAD/FIRST_VALUE/LAST_VALUE
• 早期版本（5.7）不支持窗口函数

SQL Server：
• 全部支持
• 支持OFFSET/FETCH NEXT分页

效率数据

对比子查询方案：

  • 原方案（子查询）：平均100ms，300行SQL
  • 窗口函数方案：平均20ms，100行SQL
  • 10倍以上效率提升

常见误区

  • Q：窗口函数vs GROUP BY？A：GROUP BY压缩行，窗口保留行
  • Q：能嵌套使用吗？A：可以，但要注意性能
  • Q：WHERE子句里能用吗？A：不行，需要子查询包裹
  • Q：MySQL 5.7能用吗？A：必须8.0+