本文全面解析Oracle开窗函数（分析函数）的核心概念与应用。

---

开窗函数通过OVER()子句实现，能在保留明细数据的同时进行聚合计算、排名、移动分析等操作。

---

文章详细对比了开窗函数与普通聚合函数的区别，分类讲解五大类开窗函数（聚合、排序、偏移、首尾、分布），深入剖析PARTITIONBY和三种窗口范围模式（ROWS/RANGE/GROUPS）的使用场景与执行逻辑。

---

通过典型应用案例（占比分析、累计统计、移动平均等）演示实战技巧，并提供性能优化建议和常见问题解答。

---

最后强调编写开窗函数需明确的三个关键点：分区方式、排序规则和窗口范围定义。

---

掌握这些知识能显著提升SQL数据分析效率。

Oracle 开窗函数（分析函数）完全指南

从基础概念到高级用法，一文掌握Oracle开窗函数的核心知识

前言

在日常的数据分析工作中，我们经常需要在保留明细数据的同时进行汇总计算、排名对比或前后行数据比较。Oracle的开窗函数（Analytic Function）正是解决这些需求的利器。本文将从基础概念入手，通过大量实例深入讲解开窗函数的核心知识。

---

一、基础概念澄清

1.1 开窗函数 vs 分析函数

很多Oracle初学者会对这两个概念感到困惑：

• 开窗函数：其他数据库（如SQL Server、PostgreSQL）常用的术语

• 分析函数：Oracle官方文档使用的标准术语

它们本质上是同一个东西，都是指带有 OVER() 子句的函数。只是叫法不同而已。

1.2 关键区别：开窗函数 vs 普通聚合函数

对比项	普通聚合函数（GROUP BY）	开窗函数（OVER）
结果集行数	减少（按分组汇总）	不变（保留所有行）
明细与汇总	只能看到汇总结果	明细+汇总同时展现
典型语法	GROUP BY deptno	OVER(PARTITION BY deptno)

示例对比：

sql

-- 普通聚合：只返回3行（3个部门）
SELECT deptno, SUM(sal) AS total_sal
FROM emp
GROUP BY deptno;

-- 开窗函数：返回14行（所有员工），每行都显示部门汇总
SELECT e.*, 
       SUM(sal) OVER(PARTITION BY deptno) AS dept_total
FROM emp e;

---

二、开窗函数的分类

2.1 五大类开窗函数

类别	典型函数	用途
聚合类	SUM, AVG, MAX, MIN, COUNT	在窗口内进行聚合计算
排序类	ROW_NUMBER, RANK, DENSE_RANK, NTILE	生成序号或排名
偏移类	LAG, LEAD	访问前后行数据
首尾类	FIRST_VALUE, LAST_VALUE	获取窗口首尾行数据
分布类	PERCENT_RANK, CUME_DIST	计算相对位置和分布

2.2 判断标准

判断一个函数是否为开窗函数的唯一标准：是否包含 OVER() 子句

sql

-- ✅ 是开窗函数
ROW_NUMBER() OVER(ORDER BY sal)
SUM(sal) OVER(PARTITION BY deptno)
LAG(sal, 1) OVER(ORDER BY hiredate)

-- ❌ 不是开窗函数（普通聚合）
SUM(sal) 
FROM emp 
GROUP BY deptno

聚合开窗

---

✅ 带 ORDER BY：累计值（逐行累加）
✅ 不带 ORDER BY：全组聚合值（每行相同）

---

三、PARTITION BY 的作用范围

3.1 核心理解

PARTITION BY 只影响当前开窗函数的计算范围，不影响 SELECT 中的其他字段。

sql

SELECT 
  e.*,  -- 返回所有行，不受PARTITION BY影响
  LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate) AS next_sal
FROM emp e;

3.2 实际效果

empno	ename	deptno	sal	hiredate	next_sal
7369	SMITH	20	800	1980-12-17	2975
7566	JONES	20	2975	1981-04-02	(null)
7499	ALLEN	30	1600	1981-02-20	1250
7521	WARD	30	1250	1981-02-22	(null)

可以看到，next_sal 只在同一部门内计算，但所有员工记录都被保留。

---

四、窗口范围（Window Frame）完全指南

4.1 基本语法

sql

函数() OVER (
    [PARTITION BY 分区字段]
    [ORDER BY 排序字段]
    [ROWS | RANGE | GROUPS] BETWEEN 边界1 AND 边界2
)

4.2 三种窗口模式对比

模式	计算方式	适用场景	示例
ROWS	按物理行数	固定行数移动平均	最近7天平均
RANGE	按逻辑值范围	按日期/数值区间	前后3天的汇总
GROUPS	按排序值分组	相同值分组统计	按薪资等级分组

4.3 边界定义

sql

-- 全分区范围
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- 从开头到当前行（默认行为）
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 从当前行到结尾
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 前后各n行（滑动窗口）
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- 前n行到当前行
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

4.4 模式详解与示例

ROWS 模式（物理行）

sql

-- 5日移动平均
SELECT 
  trade_date,
  price,
  AVG(price) OVER(ORDER BY trade_date 
                  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS ma_5d
FROM stock_price;

RANGE 模式（逻辑值范围）

sql

-- 按日期范围：当前日期前后3天
SELECT 
  order_date,
  amount,
  SUM(amount) OVER(ORDER BY order_date 
                  RANGE BETWEEN INTERVAL '3' DAY PRECEDING 
                            AND INTERVAL '3' DAY FOLLOWING) AS range_sum
FROM orders;

-- 按数值范围：当前薪资±1000的员工数
SELECT 
  ename,
  sal,
  COUNT(*) OVER(ORDER BY sal 
                RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS same_range_count
FROM emp;

GROUPS 模式（Oracle 12c+）

sql

-- 按薪资值分组，包含前后各1组
SELECT 
  ename,
  sal,
  SUM(sal) OVER(ORDER BY sal 
                GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS group_total
FROM emp;

4.5 默认行为总结

sql

-- 有ORDER BY，无显式范围 → 默认 RANGE UNBOUNDED PRECEDING
SUM(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
-- 等价于：
SUM(sal) OVER(PARTITION BY deptno ORDER BY hiredate
              RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 无ORDER BY → 默认全分区
SUM(sal) OVER(PARTITION BY deptno)
-- 等价于：
SUM(sal) OVER(PARTITION BY deptno
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

---

五、深入理解：执行顺序与窗口范围的影响

5.1 逻辑执行顺序

sql

SELECT 
  ename,
  sal,
  FIRST_VALUE(sal) OVER(PARTITION BY deptno 
                        ORDER BY hiredate
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sal
FROM emp;

执行步骤：

1. FROM emp → 获取基础数据

2. PARTITION BY deptno → 按部门分组

3. ORDER BY hiredate → 部门内排序

4. 定义窗口范围 → ROWS BETWEEN ...

5. 计算 FIRST_VALUE() → 在窗口内取首行值

6. SELECT → 输出最终结果

5.2 窗口范围对FIRST_VALUE的影响

很多人误以为 FIRST_VALUE 不受窗口范围影响，其实不然：

sql

-- 场景1：默认范围（从分区开头到当前行）
SELECT 
  ename,
  sal,
  FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) AS default_first
FROM emp;
-- 所有行都显示同一部门的最小薪资

-- 场景2：滑动窗口（当前行及前2行）
SELECT 
  ename,
  sal,
  FIRST_VALUE(sal) OVER(PARTITION BY deptno 
                        ORDER BY sal
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_first
FROM emp;
-- 每行显示窗口内（当前行+前2行）的最小薪资

关键结论：

• 全分区范围和默认范围对 FIRST_VALUE 结果相同（都是分区第一行）

• 但在滑动窗口中，FIRST_VALUE 会随窗口移动而变化

• LAST_VALUE 受窗口范围影响更明显

---

六、实战应用场景

6.1 占比与贡献度分析

sql

SELECT 
  ename,
  deptno,
  sal,
  ROUND(sal / SUM(sal) OVER(PARTITION BY deptno) * 100, 2) AS dept_pct,
  ROUND(sal / SUM(sal) OVER() * 100, 2) AS company_pct,
  RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS dept_rank
FROM emp;

6.2 累计统计（帕累托分析）

sql

SELECT 
  product,
  sales,
  SUM(sales) OVER(ORDER BY sales DESC) AS cumulative_sales,
  ROUND(SUM(sales) OVER(ORDER BY sales DESC) / SUM(sales) OVER() * 100, 2) AS cumulative_pct
FROM product_sales
ORDER BY sales DESC;

6.3 同比环比计算

sql

SELECT 
  month,
  revenue,
  LAG(revenue, 1) OVER(ORDER BY month) AS prev_month,
  LAG(revenue, 12) OVER(ORDER BY month) AS prev_year,
  ROUND((revenue - LAG(revenue, 1) OVER(ORDER BY month)) / 
        LAG(revenue, 1) OVER(ORDER BY month) * 100, 2) AS mom_growth
FROM monthly_revenue;

6.4 移动平均（趋势分析）

sql

SELECT 
  trade_date,
  close_price,
  AVG(close_price) OVER(ORDER BY trade_date 
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,
  AVG(close_price) OVER(ORDER BY trade_date 
                        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30d
FROM stock_daily;

---

七、性能优化建议

7.1 模式选择

• ROWS > GROUPS > RANGE（性能从高到低）

• RANGE 需要排序和值比较，开销较大

7.2 分区策略

• 合理使用 PARTITION BY 减少计算范围

• 避免全表开窗（无 PARTITION BY）的大数据量操作

7.3 索引建议

sql

-- 为开窗函数常用的排序字段创建索引
CREATE INDEX idx_emp_dept_hiredate ON emp(deptno, hiredate);

---

八、常见陷阱与注意事项

8.1 NULL值处理

sql

-- NULL默认排最后，可使用NULLS FIRST/LAST控制
ROW_NUMBER() OVER(ORDER BY comm NULLS LAST)

8.2 ORDER BY的必要性

• ROW_NUMBER(), RANK() 等必须要有 ORDER BY

• 聚合开窗可以没有 ORDER BY（此时窗口为全分区）

8.3 窗口范围的限制

• LAG/LEAD 不支持 ROWS/RANGE 子句（通过偏移量参数控制）

• RANGE 模式的 ORDER BY 字段必须是数值或日期类型

8.4 简写形式

sql

-- 以下写法等价
ROWS UNBOUNDED PRECEDING                    -- 默认到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING    -- 显式写法
ROWS 1 PRECEDING AND 1 FOLLOWING            -- 可省略BETWEEN

---

九、速查表

常用开窗函数一览

函数	用途	示例
ROW_NUMBER()	行号（唯一）	ROW_NUMBER() OVER(ORDER BY sal DESC)
RANK()	排名（有间隙）	RANK() OVER(PARTITION BY deptno ORDER BY sal DESC)
DENSE_RANK()	排名（无间隙）	DENSE_RANK() OVER(ORDER BY sal DESC)
SUM()	累计求和	SUM(sal) OVER(ORDER BY hiredate)
AVG()	移动平均	AVG(sal) OVER(ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
LAG()	取前一行	LAG(sal, 1, 0) OVER(ORDER BY hiredate)
LEAD()	取后一行	LEAD(sal, 1) OVER(ORDER BY hiredate)
FIRST_VALUE()	取窗口首行	FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY hiredate)
LAST_VALUE()	取窗口末行	LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY hiredate ROWS UNBOUNDED PRECEDING)

窗口范围快速参考

需求	语法
全分区统计	ROWS UNBOUNDED PRECEDING
累计到当前	ROWS UNBOUNDED PRECEDING（默认）
N日移动平均	ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW
中心移动平均	ROWS BETWEEN N PRECEDING AND N FOLLOWING
时间范围统计	RANGE BETWEEN INTERVAL 'N' DAY PRECEDING AND CURRENT ROW
数值范围统计	RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING

---

结语

Oracle开窗函数是数据分析中不可或缺的工具，掌握它能够让您的SQL代码更加简洁高效。本文从基础概念到高级用法，系统地梳理了开窗函数的核心知识，希望能帮助您在实际工作中更好地运用这些强大的功能。

核心要点回顾：

1. 开窗函数 = 分析函数，都带 OVER() 子句

2. 不减少结果集行数，明细与汇总共存

3. PARTITION BY 只影响当前函数

4. 窗口范围（ROWS/RANGE/GROUPS）控制计算边界

5. 不同的开窗函数适用不同的分析场景

最后建议：
在编写开窗函数时，务必明确三个问题：

1. 按什么分区？（PARTITION BY）

2. 按什么排序？（ORDER BY）

3. 窗口范围是什么？（ROWS/RANGE/GROUPS）

理清这三个问题，您就能驾驭绝大多数开窗函数的应用场景。