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 子句来定义窗口中的行如何排序。这些选项允许您更精细地控制窗口函数的行为。

  • 6
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值