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

oracle 窗口函数使用

news 2025/7/21 10:42:27

Oracle 数据库中的窗口函数（也称为分析函数或OLAP函数）允许您对一组相关的行执行计算，而不是只针对单行。这些函数在数据分析中特别有用，因为它们允许您执行诸如计算移动平均值、累积总和、百分比排名等操作。

以下是一些常用的 Oracle 窗口函数和它们的使用示例：

ROW_NUMBER()：为结果集中的每一行分配一个唯一的序号。

SELECT employee_id, first_name, last_name, department_id,  ROW_NUMBER() OVER (ORDER BY department_id, salary DESC) as rn  
FROM employees;

RANK() 和 DENSE_RANK()：为结果集中的每一行分配一个排名。它们的区别在于处理并列排名的方式不同。

SELECT employee_id, first_name, last_name, salary,  RANK() OVER (ORDER BY salary DESC) as rank,  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank  
FROM employees;

NTILE(n)：将结果集分成 n 个大致相等的部分，并为每一行分配一个桶号（从1到n）。

SELECT employee_id, first_name, last_name, salary,  NTILE(4) OVER (ORDER BY salary DESC) as quartile  
FROM employees;

SUM() OVER()：计算累积总和。

SELECT employee_id, first_name, last_name, salary,  SUM(salary) OVER (ORDER BY department_id, employee_id) as cumulative_salary  
FROM employees;

AVG() OVER()：计算移动平均值。

SELECT employee_id, first_name, last_name, salary,  AVG(salary) OVER (ORDER BY employee_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as avg_salary_3_rows  
FROM employees;

LEAD() 和 LAG()：访问结果集中的前一行或后一行的值。

SELECT employee_id, first_name, last_name, salary,  LEAD(salary) OVER (ORDER BY employee_id) as next_salary,  LAG(salary) OVER (ORDER BY employee_id) as prev_salary  
FROM employees;

FIRST_VALUE() 和 LAST_VALUE()：返回窗口中的第一行或最后一行的值。

SELECT employee_id, first_name, last_name, salary,  FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) as max_salary_in_dept,  LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary ASC) as min_salary_in_dept  
FROM employees;