Oracle开窗函数：提升数据处理和分析的效率

一只fish

已于 2023-08-24 13:08:54 修改

阅读量572

点赞数 1

分类专栏：数据库技术分享文章标签： oracle 数据库

于 2023-08-20 21:53:43 首次发布

本文链接：https://blog.csdn.net/renkejia1988/article/details/132396459

版权

数据库技术分享专栏收录该内容

39 篇文章 0 订阅

订阅专栏

Oracle开窗函数：提升数据处理和分析的效率

在现代数据驱动的应用程序中，数据分析和报表功能变得越来越重要。数据库开窗函数是一种强大的工具，可以帮助我们在查询结果集的特定窗口内执行复杂的窗口操作，从而满足各种数据分析需求。

一、基本概念

1.概述

开窗函数是一种用于执行窗口操作的函数，它可以在查询结果集的特定窗口内进行计算。开窗函数通常与聚合函数一起使用，可以对每一行进行计算，并返回结果集中的每一行的一个值。

2.语法

在Oracle数据库中，开窗函数的语法如下：

<函数名> OVER ([PARTITION BY <列名>] [ORDER BY <排序列>] [窗口子句])

其中，<函数名>表示要使用的开窗函数，<列名>表示要进行分区的列，<排序列>表示用于排序的列，窗口子句定义了窗口的范围。

二、常见的开窗函数类型

测试数据员工表employees如下：

CREATE TABLE employees  
(  
id NUMBER PRIMARY KEY,  
name VARCHAR(20),  
groupname VARCHAR(20),
salary NUMBER
);
    
INSERT INTO employees VALUES
(1,'小明','开发部',7000);
INSERT INTO employees VALUES 
(4,'小张','行政部',7600);
INSERT INTO employees VALUES  
(5,'小白','开发部',7000);
INSERT INTO employees VALUES    
(8,'小王','财务部',8500);
INSERT INTO employees VALUES 
(9, '小赵','行政部',6900);
INSERT INTO employees VALUES 
(15,'小刘','财务部',6000);
INSERT INTO employees VALUES  
(16,'小高','行政部',4500);
INSERT INTO employees VALUES  
(18,'小王','行政部',6000);
INSERT INTO employees VALUES 
(23,'小李','开发部',7500);
INSERT INTO employees VALUES  
(29,'小吴','财务部',4700);

1.排名函数

排名函数用于计算行的排名，常见的排名函数包括RANK()、DENSE_RANK()和ROW_NUMBER()。下面是一个示例：

SELECT id,
       name,
       groupname
       salary,
       RANK() OVER(ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_number
  FROM employees;

RANK()函数计算行的排名，如果有相同的值，则会跳过下一个排名。
DENSE_RANK()函数计算行的排名，如果有相同的值，则会跳过下一个排名，但不会跳过相同的排名。
ROW_NUMBER()函数为每一行分配一个唯一的行号。

有时我们需要在分组内对数据进行排序，并计算每个组内的排名和汇总。下面是一个示例：

SELECT id,
       name,
       groupname,
       salary,
       ROW_NUMBER() OVER(PARTITION BY groupname ORDER BY salary DESC) rank_group,
       SUM(salary) OVER(PARTITION BY groupname) sum_salary_group,
       SUM(salary) OVER() sum_salary
  FROM employees;

PARTITION BY起到了按groupname进行分组的作用。

2.累计函数

累计函数用于计算累计值，常见的累计函数包括SUM()、AVG()、COUNT()等。下面是一个示例：

SELECT id,
       name,
       groupname,
       salary,
       SUM(salary) OVER(ORDER BY salary DESC) AS cumulative_sum,
       AVG(salary) OVER(ORDER BY salary DESC) AS cumulative_avg,
       COUNT(id) OVER(ORDER BY salary DESC) AS cumulative_count
  FROM employees;

SUM()函数计算指定列的累计总和。
AVG()函数计算指定列的累计平均值。
COUNT()函数计算指定列的累计计数。

3.分析函数

分析函数用于在查询结果集中执行分析操作，常见的分析函数包括LEAD()、LAG()、FIRST_VALUE()和LAST_VALUE()。下面是一个示例：

SELECT id,
       name,
       groupname,
       salary,
       LEAD(salary, 1) OVER(PARTITION BY groupname ORDER BY salary DESC) AS next_salary,
       LAG(salary, 1) OVER(PARTITION BY groupname ORDER BY salary DESC) AS previous_salary,
       FIRST_VALUE(salary) OVER(PARTITION BY groupname ORDER BY salary DESC) AS first_salary,
       LAST_VALUE(salary) OVER(PARTITION BY groupname ORDER BY salary DESC) AS last_salary
  FROM employees;

LEAD()函数用于获取指定列的后一行的值。
LAG()函数用于获取指定列的前一行的值。
FIRST_VALUE()函数用于获取指定列的第一行的值。
LAST_VALUE()函数用于获取指定列的最后一行的值。

4.移动平均

移动平均是一种常见的数据分析技术，可以用于平滑时间序列数据。下面是一个计算移动平均的示例：

SELECT id,
       name,
       groupname,
       salary,
       AVG(salary) OVER(ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
  FROM employees;