SQL中的函数--开窗函数 - 指南

文章目录

• • 一、什么是开窗函数？
  • ️ 二、基本语法
  • 三、常见开窗函数分类
  • 小结：开窗函数三要素
  • 四、常用开窗函数介绍
  • • 一、排名类函数（Ranking Functions）
    • • 1. `ROW_NUMBER()`
      • 2. `RANK()`
      • 3. `DENSE_RANK()`
    • 二、聚合类函数（Aggregate as Window Functions）
    • • 常见函数：
      • 示例：

一、什么是开窗函数？

开窗函数（Window Function）是一种在 不合并行 的前提下，对一组“相关行”进行计算的 SQL 函数。

✅ 和 GROUP BY 的区别：

• GROUP BY 会把多行“压缩”成一行（比如求平均值后只返回一个数）。
• 开窗函数保留每一行，同时告诉你“这行在某个分组/窗口里的位置或统计值”。

---

️ 二、基本语法

函数名(...) OVER (
[PARTITION BY 字段1, 字段2, ...]   -- 按什么分组（可选）
[ORDER BY 字段 ASC|DESC]           -- 窗口内如何排序（可选）
[窗口范围子句]                      -- 比如 ROWS BETWEEN ...（可选）
)

---

三、常见开窗函数分类

类型	常见函数	作用
排名类	ROW_NUMBER(), RANK(), DENSE_RANK()	给行编号或排名
聚合类	SUM(), AVG(), COUNT(), MAX(), MIN()	在窗口内做聚合
偏移类	LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()	获取前/后一行的值

⚠️ 注意：这些函数 只有在 OVER() 子句中使用时才是开窗函数！

---

小结：开窗函数三要素

要素	作用
OVER()	标志这是一个开窗函数
PARTITION BY	“分组”，但不合并行
ORDER BY（在 OVER 里）	定义窗口内顺序，影响排名、累计等

四、常用开窗函数介绍

一、排名类函数（Ranking Functions）

这类函数用于给数据“排序编号”，常用于 Top N、排行榜等场景。

1. ROW_NUMBER()

• 作用：为每一行分配一个 唯一的连续序号，即使值相同也会编号不同。
• 特点：严格按顺序编号，永不重复、不跳号。

ROW_NUMBER() OVER (ORDER BY salary DESC)

示例：
薪资：9000, 9000, 8000
→ 编号：1, 2, 3

---

2. RANK()

• 作用：排名，相同值名次相同，但会 跳过后续名次。
• 特点：有“并列”，但会“断号”。

RANK() OVER (ORDER BY score DESC)

示例：
分数：100, 100, 90
→ 排名：1, 1, 3（跳过了第2名）

---

3. DENSE_RANK()

• 作用：排名，相同值名次相同，但 不会跳号。
• 特点：有“并列”，但名次连续。

DENSE_RANK() OVER (ORDER BY score DESC)

示例：
分数：100, 100, 90
→ 排名：1, 1, 2

✅ 三者对比总结：

分数	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2
80	4	4	3

---

二、聚合类函数（Aggregate as Window Functions）

这些你熟悉的聚合函数，在加上 OVER() 后就变成了开窗函数 —— 保留原表所有行，同时计算窗口内的统计值。

常见函数：

• SUM()：窗口内求和
• AVG()：窗口内平均值
• COUNT()：窗口内行数
• MAX() / MIN()：窗口内最大/最小值

✅ 关键：不用 GROUP BY，每行都保留！

示例：

SELECT
name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
FROM employees;

---