推荐订阅源

一、窗口函数是什么

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

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

二、基本语法

函数名(列) 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

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

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) → 部门内入职顺序累计

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

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)：窗口最后一行值（常需加帧范围）

示例（同比 / 环比）：

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 组（如四分位）

示例（成绩四分位）：

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 日移动平均）：

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

一、基础语法

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

---

二、排名类（面试高频）

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

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

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

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

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

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

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

---

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

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

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

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

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

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

---

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

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

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

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

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

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

---

五、分桶分组

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

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

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

---

六、首尾取值

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

---

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

近 3 日移动平均

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

---

八、万能高频模板

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

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

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

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

3. 计算同比 / 环比差值

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