sql窗口函数

一、窗口函数是什么

 

窗口函数（Window Function），也叫开窗 / 分析函数，核心特点：

 

• 不减少行数（保留明细），每行附带一个 “窗口内的计算值”
• 用 OVER() 定义窗口（分区、排序、行范围）
• 与 GROUP BY 聚合的区别：聚合是多行为一行，窗口是一行为一行并附加统计

 

二、基本语法

 

sql

 

 

函数名(列) OVER (
  [PARTITION BY 分组列]   -- 可选：按字段分区（类似GROUP BY，不合并行）
  [ORDER BY 排序列]       -- 可选：分区内排序
  [ROWS/RANGE 帧范围]     -- 可选：窗口行范围（默认：首行→当前行）
)

 

 

• PARTITION BY：分组，窗口在每组内独立计算
• ORDER BY：排序，影响排名、累计、帧范围
• ROWS/RANGE：精确控制窗口包含哪些行（移动平均、累计求和常用）

 

三、常用窗口函数分类

 

1）排名类（Ranking）

 

必须带 ORDER BY，用于排序编号。

 

表格

 

函数	说明	特点
ROW_NUMBER()	连续序号	同值也不重复：1,2,3,4
RANK()	跳跃排名	同值同排名，后续跳号：1,1,3,4
DENSE_RANK()	密集排名	同值同排名，后续连续：1,1,2,3

 

示例（部门内薪资排名）：

 

sql

 

 

SELECT
  dept, name, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
FROM employees;

 

 

2）聚合类（Aggregate）

 

SUM/AVG/COUNT/MAX/MIN + OVER()，保留明细 + 聚合统计。

 

• 整表窗口：SUM(salary) OVER () → 全体总和
• 分区聚合：SUM(salary) OVER (PARTITION BY dept) → 部门总和
• 累计求和：SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) → 部门内入职顺序累计

 

示例（薪资与部门均值对比）：

 

sql

 

 

SELECT
  name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees;

 

 

3）取值类（Analytic：前后行 / 首尾行）

 

• LAG(col, n)：取当前行前第 n 行的值（默认 n=1）
• LEAD(col, n)：取当前行后第 n 行的值
• FIRST_VALUE(col)：窗口第一行值
• LAST_VALUE(col)：窗口最后一行值（常需加帧范围）

 

示例（同比 / 环比）：

 

sql

 

 

SELECT
  month, sales,
  LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
  sales - LAG(sales, 1) OVER (ORDER BY month) AS mom_gain
FROM monthly_sales;

 

 

4）分布 / 分桶类

 

• PERCENT_RANK()：百分位排名（0~1）
• CUME_DIST()：累计分布比例
• NTILE(n)：把分区均匀分成 n 组（如四分位）

 

示例（成绩四分位）：

 

sql

 

 

SELECT
  student_id, score,
  NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM scores;

 

 

四、窗口帧（ROWS/RANGE）详解

 

默认帧：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（首行→当前行）。

 

常用帧写法：

 

• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING → 分区所有行
• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW → 当前行 + 前 1 行（2 行移动平均）
• ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING → 前 2 行～后 1 行

 

示例（3 日移动平均）：

 

sql

 

 

SELECT
  dt, price,
  AVG(price) OVER (
    ORDER BY dt
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS ma3
FROM stock;

 

 

五、典型场景速查

 

1. 分组排名：部门内业绩 TOP N → ROW_NUMBER() + PARTITION BY
2. 明细 + 汇总：每行显示部门 / 全局均值 / 总和 → AVG/SUM + OVER(PARTITION BY)
3. 累计计算：按月累计销售额 → SUM + ORDER BY
4. 移动平均 / 滑动统计：7 日均值 → AVG + ROWS帧
5. 前后行对比：环比 / 同比、上一个状态 → LAG/LEAD
6. 分档分级：成绩 A/B/C、客户分层 → NTILE

 

六、易错点提醒

 

• 窗口函数不能直接用在 WHERE/HAVING（先执行 WHERE 再开窗），需用子查询 / CTE
• LAST_VALUE 默认到当前行，必须显式写帧范围才是分区最后一行
• 分区 + 排序后，聚合默认是累计；要整区聚合需加 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

 

一、基础语法

 

sql

 

 

函数() OVER(
  PARTITION BY 分组字段   -- 分区/分组
  ORDER BY 排序字段       -- 分区内排序
  ROWS 范围               -- 滑动窗口（可选）
)

 

 

---

 

二、排名类（面试高频）

 

1. 连续序号（同分不并列）

 

sql

 

 

ROW_NUMBER() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)

 

 

2. 跳跃排名（同分同名次、跳号）

 

sql

 

 

RANK() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)

 

 

3. 密集排名（同分同名次、不跳号）

 

sql

 

 

DENSE_RANK() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)

 

 

场景：部门内薪资排名、班级成绩排名、TOP1 取数

 

---

 

三、前后行取值（环比 / 对比）

 

1. 取上一行数据（上期值）

 

sql

 

 

LAG(字段,1) OVER(ORDER BY 日期)

 

 

2. 取下一行数据（下期值）

 

sql

 

 

LEAD(字段,1) OVER(ORDER BY 日期)

 

 

场景：环比销售额、上月营收、前后单状态对比

 

---

 

四、聚合开窗（保留明细 + 统计）

 

1. 分区内总和 / 均值 / 最大 / 最小

 

sql

 

 

SUM(金额) OVER(PARTITION BY 部门)   -- 部门总金额
AVG(薪资) OVER(PARTITION BY 部门)   -- 部门平均薪资
MAX(分数) OVER(PARTITION BY 班级)
MIN(价格) OVER(PARTITION BY 品类)

 

 

2. 累计求和（逐日累计）

 

sql

 

 

SUM(销售额) OVER(ORDER BY 日期)

 

 

场景：每行展示部门汇总、累计 GMV、个人业绩对比整体

 

---

 

五、分桶分组

 

等分成 N 组（分层 / 评级）

 

sql

 

 

NTILE(4) OVER(ORDER BY 分数 DESC)

 

 

场景：四分位评级、客户分层、绩效等级划分

 

---

 

六、首尾取值

 

sql

 

 

FIRST_VALUE(金额) OVER(PARTITION BY 部门 ORDER BY 日期) -- 分区第一条
LAST_VALUE(金额) OVER(PARTITION BY 部门 
  ORDER BY 日期 
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

 

 

---

 

七、滑动窗口（移动平均）

 

近 3 日移动平均

 

sql

 

 

AVG(价格) OVER(
  ORDER BY 日期 
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

 

 

---

 

八、万能高频模板

 

1. 分组取每组最新一条（必背）

 

sql

 

 

SELECT * FROM (
  SELECT *,
  ROW_NUMBER() OVER(PARTITION BY 用户ID ORDER BY 时间 DESC) AS rn
) t WHERE rn = 1

 

 

2. 每日销售额 + 累计销售额

 

sql

 

 

SELECT 日期,日销,
SUM(日销) OVER(ORDER BY 日期) AS 累计销售额
FROM (
  SELECT DATE(创建时间) AS 日期,SUM(金额) AS 日销 
  FROM 订单表 GROUP BY 日期
) t

 

 

3. 计算同比 / 环比差值

 

sql

 

 

SELECT 日期,销售额,
LAG(销售额) OVER(ORDER BY 日期) AS 上月销售额,
销售额-LAG(销售额) OVER(ORDER BY 日期) AS 环比差额
FROM 销售表

 

 

---

 

九、核心口诀

 

1. PARTITION BY = 分组，等价 group by 但不合并行
2. 窗口函数不减少行数，适合明细 + 指标共存
3. 排名必加ORDER BY，累计 / 滑动必控排序 + 范围
4. 分组取 TOP，统一用 ROW_NUMBER + rn=1