当前位置：首页 > ai >正文

Oracle 开窗函数

ai 2025/7/3 15:05:24

Oracle 开窗函数（Window Functions）允许在不合并行的前提下对数据进行复杂分析，常用于排名、累计计算、前后行对比等场景。

一、核心语法结构

函数名() OVER ([PARTITION BY 分区列] [ORDER BY 排序列 [ASC|DESC]] [窗口帧子句 (ROWS | RANGE BETWEEN ... AND ...)]
)

PARTITION BY： 将数据划分为独立的分区，函数在每个分区内单独计算。

ORDER BY： 定义分区内的排序规则。

窗口帧子句： 指定计算范围（如当前行前 N 行、后 N 行等）。

二、常用开窗函数分类
1. 排名函数
(1) ROW_NUMBER()
功能： 为每行分配唯一的连续序号（相同值序号不同）。

-- 语法：
ROW_NUMBER() OVER ([PARTITION BY col1] ORDER BY col2)-- 示例：
SELECT name, score,ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;

结果：

name	score	rank
Alice	95    	1
Bob 	90  	2
Carol	90  	3

(2) RANK()
**功能：**允许并列排名，后续序号跳跃（如 1, 2, 2, 4）。

-- 语法：
RANK() OVER ([PARTITION BY col1] ORDER BY col2)-- 示例：
SELECT name, score,RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

结果：

name	score	rank
Alice	95	    1
Bob   	90	    2
Carol	90	    2
Dave	85	    4

(3) DENSE_RANK()
**功能：**允许并列排名，后续序号连续（如 1, 2, 2, 3）。

-- 语法：
DENSE_RANK() OVER ([PARTITION BY col1] ORDER BY col2)-- 示例：
SELECT name, score,DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

结果：

name	score	rank
Alice	95	    1
Bob 	90    	2
Carol	90  	2
Dave	85	    3

2. 聚合函数 + 窗口
(1) SUM()
功能： 计算分区内的总和。

-- 语法：
SUM(col) OVER ([PARTITION BY col1] [ORDER BY col2] [窗口帧子句])示例（累计销售额）：
SELECT month, sales,SUM(sales) OVER (ORDER BY month) AS cumulative_sales
FROM monthly_sales;

结果：

month	sales	cumulative_sales
2023-01	100	        100
2023-02	200	        300
2023-03	150     	450

(2) AVG()
功能： 计算分区内的平均值。

-- 语法：
AVG(col) OVER ([PARTITION BY col1] [ORDER BY col2] [窗口帧子句])-- 示例（部门平均工资）：
SELECT department, salary,AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

(3) COUNT()
功能： 计算分区内的行数。

-- 语法：
COUNT(col) OVER ([PARTITION BY col1] [ORDER BY col2] [窗口帧子句])-- 示例（统计每个部门的员工数）：
SELECT department, employee_id,COUNT(*) OVER (PARTITION BY department) AS dept_emp_count
FROM employees;

3. 前后行分析函数
(1) LAG(col, n, default)
功能： 获取当前行前第 n 行的值（默认 n=1）。

-- 语法：
LAG(col, n, default) OVER ([PARTITION BY col1] ORDER BY col2)-- 示例（计算环比增长）：
SELECT month, sales,LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_sales,sales - LAG(sales, 1, 0) OVER (ORDER BY month) AS growth
FROM monthly_sales;

(2) LEAD(col, n, default)
功能： 获取当前行后第 n 行的值（默认 n=1）。

-- 语法：
LEAD(col, n, default) OVER ([PARTITION BY col1] ORDER BY col2)
-- 示例（预测下月销售额）：
SELECT month, sales,LEAD(sales, 1, 0) OVER (ORDER BY month) AS next_sales
FROM monthly_sales;

4. 首尾值函数
(1) FIRST_VALUE(col)
功能： 返回窗口内的第一个值。

-- 语法：
FIRST_VALUE(col) OVER ([PARTITION BY col1] ORDER BY col2 [窗口帧子句])
-- 示例（获取部门最高工资）：
SELECT department, salary,FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_salary
FROM employees;

(2) LAST_VALUE(col)
功能： 返回窗口内的最后一个值（需结合窗口帧子句）。

-- 语法：
LAST_VALUE(col) OVER ([PARTITION BY col1] ORDER BY col2 [窗口帧子句])
-- 示例（默认范围需扩展至末尾）：
SELECT month, sales,LAST_VALUE(sales) OVER (ORDER BY month ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_sales
FROM monthly_sales;

(3) NTH_VALUE(col, n)
功能： 返回窗口内的第 n 个值。

-- 语法：
NTH_VALUE(col, n) OVER ([PARTITION BY col1] ORDER BY col2 [窗口帧子句])-- 示例（获取每个部门第二高的工资）：
SELECT department, salary,NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest
FROM employees;

5. 分布函数
(1) NTILE(n)
功能： 将数据分为 n 个桶，并为每行分配桶号。

-- 语法：
NTILE(n) OVER ([PARTITION BY col1] ORDER BY col2)--示例（将学生分为三等）：
SELECT student_id, score,NTILE(3) OVER (ORDER BY score DESC) AS tier
FROM students;

(2) CUME_DIST()
功能： 计算累积分布值（当前行值在分区内的相对位置，范围 [0,1]）。

-- 语法：
CUME_DIST() OVER ([PARTITION BY col1] ORDER BY col2)
-- 示例（计算工资累积分布）：
SELECT employee_id, salary,CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;

(3) PERCENT_RANK()
功能： 计算百分比排名（基于 RANK()，范围 [0,1]）。

-- 语法：
PERCENT_RANK() OVER ([PARTITION BY col1] ORDER BY col2)-- 示例：
SELECT student_id, score,PERCENT_RANK() OVER (ORDER BY score) AS percent_rank
FROM students;

6. 窗口帧子句详解
(1) ROWS BETWEEN
范围定义：基于物理行偏移。

常用选项：
UNBOUNDED PRECEDING： 分区第一行。

n PRECEDING： 当前行前 n 行。

CURRENT ROW： 当前行。

n FOLLOWING： 当前行后 n 行。

UNBOUNDED FOLLOWING： 分区最后一行。

示例（计算近3个月移动平均）：
sql
SELECT month, sales,AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM monthly_sales;

(2) RANGE BETWEEN
范围定义：基于逻辑值范围（如日期差值）。

-- 常用选项：
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW-- 示例（计算近7天累计销售额）：
SELECT sale_date, sales,SUM(sales) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS weekly_sales
FROM daily_sales;

三、总结

需求	推荐函数
唯一连续序号	ROW_NUMBER()
允许并列的排名	RANK() 或 DENSE_RANK()
累计计算（总和/平均）	SUM() OVER() 或 AVG() OVER()
前后行数据对比	LAG() 或 LEAD()
分组首尾值	FIRST_VALUE() 或 LAST_VALUE()
数据分桶	NTILE(n)
累积分布分析	CUME_DIST() 或 PERCENT_RANK()