在 Oracle 数据库中,开窗函数(Window Functions)是一类非常强大的 SQL 函数,用于在结果集的“窗口”上执行计算。这些窗口函数允许你在查询的每一行上执行聚合操作(如求和、平均、计数等),同时保留原始行的详细数据。
以下是一些常见的 Oracle 开窗函数及其用法:
1. ROW_NUMBER()
为结果集中的每一行分配一个唯一的序号。
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM
employees;
2. RANK()
类似于 ROW_NUMBER()
,但如果有相同的值,它们会共享相同的排名,并且后续排名会跳过。
SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept
FROM
employees;
3. DENSE_RANK()
类似于 RANK()
,但后续排名不会跳过。
SELECT
employee_id,
department_id,
salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_in_dept
FROM
employees;
4. NTILE(n)
将结果集划分为 n
个桶,并为每一行分配一个桶号。
SELECT
employee_id,
department_id,
salary,
NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM
employees;
5. LAG() 和 LEAD()
用于访问结果集中当前行的前一行或后一行的数据。
SELECT
employee_id,
department_id,
salary,
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary,
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS next_salary
FROM
employees;
6. FIRST_VALUE() 和 LAST_VALUE()
返回窗口框架中的第一行或最后一行的值。
SELECTemployee_id,department_id,salary,FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_salary_in_dept,LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_salary_in_deptFROMemployees;
7. SUM(), AVG(), MIN(), MAX() 等聚合函数
在窗口上执行常见的聚合操作。
SELECT
employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM
employees;
窗口子句(Window Clause)
在开窗函数中,OVER
子句用于定义窗口的分区和排序。PARTITION BY
子句用于将结果集划分为分区,ORDER BY
子句用于定义每个分区内的排序顺序。
OVER (
[PARTITION BY partition_expression, ...]
[ORDER BY sort_expression [ASC|DESC], ...]
[ROWS|RANGE BETWEEN frame_start AND frame_end]
)
PARTITION BY
:将结果集划分为多个分区,每个分区独立计算。ORDER BY
:定义每个分区内的排序顺序。ROWS
或RANGE
:定义窗口框架(frame),即窗口内包含的行范围。
示例:综合使用
SELECT
employee_id,
department_id,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM
employees;
以上示例展示了如何使用多个开窗函数,并结合 PARTITION BY
和 ORDER BY
子句进行复杂查询。