oracle 窗口函数使用

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

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

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

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

1. 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;

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

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

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

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

1. 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;

1. 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;

1. 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;

1. CUME_DIST()：计算当前行的值相对于窗口中的值的相对位置（0到1之间）。
2. PERCENT_RANK()：与 RANK() 类似，但返回的是百分比排名（0到1之间）。

请注意，在窗口函数中使用 OVER() 子句时，您可以指定 PARTITION BY 子句来定义窗口如何分区，以及 ORDER BY 子句来定义窗口中的行如何排序。这些选项允许您更精细地控制窗口函数的行为。