窗口函数（Window Function），也叫开窗 / 分析函数，核心特点：
GROUP BY 聚合的区别：聚合是多行为一行，窗口是一行为一行并附加统计函数名(列) OVER (
[PARTITION BY 分组列] -- 可选：按字段分区（类似GROUP BY，不合并行）
[ORDER BY 排序列] -- 可选：分区内排序
[ROWS/RANGE 帧范围] -- 可选：窗口行范围（默认：首行→当前行）
)
必须带 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;
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;
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;
PERCENT_RANK()：百分位排名（0~1）CUME_DIST()：累计分布比例NTILE(n)：把分区均匀分成 n 组（如四分位）示例（成绩四分位）：
SELECT
student_id, score,
NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM scores;
默认帧：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;
ROW_NUMBER() + PARTITION BYAVG/SUM + OVER(PARTITION BY)SUM + ORDER BYAVG + ROWS帧LAG/LEADNTILELAST_VALUE 默认到当前行，必须显式写帧范围才是分区最后一行ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING函数() OVER(
PARTITION BY 分组字段 -- 分区/分组
ORDER BY 排序字段 -- 分区内排序
ROWS 范围 -- 滑动窗口（可选）
)
ROW_NUMBER() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)
RANK() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)
DENSE_RANK() OVER(PARTITION BY 部门 ORDER BY 薪资 DESC)
场景：部门内薪资排名、班级成绩排名、TOP1 取数
LAG(字段,1) OVER(ORDER BY 日期)
LEAD(字段,1) OVER(ORDER BY 日期)
场景：环比销售额、上月营收、前后单状态对比
SUM(金额) OVER(PARTITION BY 部门) -- 部门总金额
AVG(薪资) OVER(PARTITION BY 部门) -- 部门平均薪资
MAX(分数) OVER(PARTITION BY 班级)
MIN(价格) OVER(PARTITION BY 品类)
SUM(销售额) OVER(ORDER BY 日期)
场景：每行展示部门汇总、累计 GMV、个人业绩对比整体
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
)
AVG(价格) OVER(
ORDER BY 日期
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY 用户ID ORDER BY 时间 DESC) AS rn
) t WHERE rn = 1
SELECT 日期,日销,
SUM(日销) OVER(ORDER BY 日期) AS 累计销售额
FROM (
SELECT DATE(创建时间) AS 日期,SUM(金额) AS 日销
FROM 订单表 GROUP BY 日期
) t
SELECT 日期,销售额,
LAG(销售额) OVER(ORDER BY 日期) AS 上月销售额,
销售额-LAG(销售额) OVER(ORDER BY 日期) AS 环比差额
FROM 销售表
PARTITION BY = 分组，等价 group by 但不合并行ORDER BY，累计 / 滑动必控排序 + 范围ROW_NUMBER + rn=1此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。