窗口函数 - SQL 数据库

01. 窗口函数是干啥的

普通聚合函数（SUM、AVG、COUNT）会把多行压成一行，分组是什么就看到什么，分组之外的信息没了。窗口函数不一样——它也是分组计算，但不会把行合在一起，原来的每一行都在，只是在旁边加一列计算结果。打个比方：一个班的学生成绩表，你想看每个人成绩外加全班平均分——普通 GROUP BY 做不到，因为每人一行、平均分只有一个值。窗口函数能轻松搞定：AVG(score) OVER() 在每行旁边补上全校平均分。窗口函数 = 聚合函数的进化版，语法是：函数名() OVER (PARTITION BY 分组 ORDER BY 排序)。OVER 是固定关键字。

sql

-- 每人成绩 + 全班平均分
SELECT name, score,
  AVG(score) OVER() AS class_avg
FROM students;

-- 每个部门里按工资排名
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

02. 排名函数——ROW_NUMBER、RANK、DENSE_RANK、NTILE

排名是最常用的窗口函数场景。四个排名函数各有不同： ROW_NUMBER()——纯粹编号，1、2、3、4 往下排，值相同的也不会并列，按某种顺序连续编号。 RANK()——奥运会排名，同分同名次，后面跳号。两人并列第一，下一个就是第三名。 DENSE_RANK()——同分同名次，但不跳号。两人并列第一，下一个是第二名。 NTILE(n)——把数据均匀分成 n 个桶，每行标上桶编号。做数据分片、用户分层特别好用。

sql

-- 按工资排名
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_num,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_num
FROM employees;

-- 把用户按消费金额分成 4 档
SELECT user_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS tier
FROM user_spending;

ROW_NUMBER 去重特别好用：PARTITION BY 重复字段 ORDER BY 某个优先级，WHERE rn=1 就只留一条。

03. 偏移函数——LAG、LEAD

LAG 和 LEAD 是回头看和向前看的函数。LAG(col, n) 拿当前行前面第 n 行的值，LEAD(col, n) 拿后面第 n 行的值。典型场景：计算环比增长（跟前一天比）、计算相邻行的时间差、找连续登录天数。这些用普通 SQL 写得绕来绕去，用 LAG/LEAD 几行搞定。注意：LAG 和 LEAD 需要 ORDER BY 来确定前后的顺序，不然不知道哪行是前哪行是后。

sql

-- 每天销售额跟前一天比（日环比）
SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS diff
FROM daily_sales;

-- 计算每笔订单和下一笔的时间间隔
SELECT order_id, created_at,
  LEAD(created_at, 1) OVER (ORDER BY created_at) AS next_order_time
FROM orders WHERE user_id = 1;

04. 累积计算——SUM、AVG 做窗口

SUM() OVER (ORDER BY ...) 能做累计求和——每一行是把前面所有行加起来。不像 GROUP BY 汇总成一行，窗口 SUM 每行都是一个到目前为止的总数。典型场景：累计销售额（每天一行，旁边显示截至当天的总销售额）、移动平均、余额计算。加上 PARTITION BY 就是分组累计——每个用户各自的消费轨迹，每个产品各自的销售趋势。用 ROWS BETWEEN 参数可以精确控制计算范围（比如只看最近 7 行）。

sql

-- 累计销售额
SELECT date, daily_sales,
  SUM(daily_sales) OVER (ORDER BY date) AS cumulative_sales
FROM sales;

-- 每个用户的累计消费
SELECT user_id, order_id, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM orders;

-- 移动平均（近 7 天）
SELECT date, sales,
  AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

ROWS BETWEEN ... AND ... 叫窗口框架，精确控制计算范围。没有它的话 ORDER BY 下默认是累加到当前行。

05. 窗口函数 vs GROUP BY

很多人学完 GROUP BY 再学窗口函数会困惑：这不差不多吗？区别在于： GROUP BY 把多行压缩成一行，每个分组只剩一条结果。原来表的行变少了，非分组列的信息会丢失。窗口函数不改变行数，原来多少行还是多少行，只是在每行旁边加上计算结果。什么时候用哪个？想看汇总（每个部门多少人、平均工资多少）用 GROUP BY；想看明细加汇总（每个人工资排名、每个人和部门平均的差距）用窗口函数。两者也能嵌套使用。

sql

-- GROUP BY：每个部门一行
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- 窗口函数：每个员工一行，旁边附上部门平均
SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

-- 两者结合：查找各部门工资最高的员工
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) r
  FROM employees
) t WHERE r = 1;

知识测验

第 1/5 题正确 0

RANK() 和 DENSE_RANK() 的区别？

MySQL 管理