Oracle分析函数，窗口函数

原创已于 2023-10-27 14:33:00 修改 · 832 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2023-10-25 17:43:38 首次发布

本文详细介绍了Oracle的窗口函数，包括分析函数的定义、与聚合函数的区别，以及常用的排序、求最值、翻页和排列组合分析函数。通过实例演示了如何在数据分组后进行复杂统计操作。

Oracle窗口函数也叫分析函数，是系统自带的一种函数。可以对数据的结果集进行分组操作，然后对分组的数据进行分析统计，可以在每个分组的每一行中返回统计值。

分析函数-定义：

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数区别：

1）普通的聚合函数用group by分组，每个分组返回一个统计值，只是对数据集进行分组操作然后返回值，而且不能够在原来的数据集上返回。

2）分析函数采用partition by分组，通常和over()开窗函数结合使用，并且每组每行都可以返回一个统计值。

分析函数的形式：

<窗口函数> over (partition by <用于分组的列名>
               order by <用于排序的列名>
               rows/range窗口子句)
               
OVER(PARTITION BY xxx PORDER BY yyy **ROWS BETWEEN rowStart AND rowEnd**)

rows/range：窗口子句，是在分组(partition by)后，组内的子分组（也称窗口）。窗口有两种，rows和range，主要用来限制行数和数据范围。窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，从当前分组起点到当前行。行比较分析函数lead和lag无窗口子句。

滑动窗口子句：

①BETWEEN 开始关键字 AND 结束关键字

为保证时间复杂度O(1),聚合函数和last_value函数的滑动窗口边界为分区的第一个和当前行.所以使用聚合函数搭配order 和last_value函数的输出结果有些怪异，每次都不和分区内的后面数据计算，这时候就需要指定窗口边界.

窗口边界：
unbounded：无界限
preceding：往前
following：往后
current row：当前行，偏移量为0，一般和其他范围关键字一起使用
unbounded preceding ：往前无界限，边界是分区中的第一行
unbounded following ：往后无界限，边界是分区中的最后一行
N preceding 边界是当前行减去N的值,N为相对当前行向前的偏移量.从分区第一行头开始，则为 unbounded.
N following 边界是当前行加上N的值,N为相对当前行向后的偏移量.与preceding相反,到该分区结束，则为 unbounded.

常用的分析函数

1.聚合分析函数：

聚合分析函数很少使用order by排序,常见如下：

sum() 
count() 
avg() 
max() 
min() 
wm_concat()  
listagg() WITHIN GROUP (ORDER BY xx)

2.排序分析函数：

排序分析函数可以使用partition by和order by语句,常见如下：

### row_number() over()	
row_number()返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
### rank() over()	
rank()返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
### dense_rank() over()	
dense_rank()返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间紧邻递增。

3.求最值对应的分析函数：

函数作用：
first_value() over() 第一个值
last_value() over() 最后一个值

实际应用1：电流填平补齐

first_value() over()，一分钟内实时电流监控情况，采集器采集频率5-10s有一条电流数据，但是这并不意味这中间几秒中没有数据，通常在1分钟内，认为上一次有数据的电流值，可以近似补全未采集到的电流数据。向后填平补齐。

 with  tab as(      
select '2023-10-27 14:18:01'   time,1    dc  from dual union all
select '2023-10-27 14:18:02'   time,null dc  from dual union all
select '2023-10-27 14:18:03'   time,null dc  from dual union all
select '2023-10-27 14:18:04'   time,null dc  from dual union all
select '2023-10-27 14:18:05'   time,null dc  from dual union all
select '2023-10-27 14:18:06'   time,null dc  from dual union all
select '2023-10-27 14:18:07'   time,3    dc  from dual union all
select '2023-10-27 14:18:08'   time,null dc  from dual union all
select '2023-10-27 14:18:09'   time,null dc  from dual union all
select '2023-10-27 14:18:10'   time,null dc  from dual union all
select '2023-10-27 14:18:11'   time,null dc  from dual union all
select '2023-10-27 14:18:12'   time,null dc  from dual union all
select '2023-10-27 14:18:13'   time,5    dc  from dual union all
select '2023-10-27 14:18:14'   time,null dc  from dual union all
select '2023-10-27 14:18:15'   time,null dc  from dual union all
select '2023-10-27 14:18:16'   time,null dc  from dual union all
select '2023-10-27 14:18:17'   time,null dc  from dual union all
select '2023-10-27 14:18:18'   time,null dc  from dual union all
select '2023-10-27 14:18:19'   time,5    dc  from dual union all
select '2023-10-27 14:18:20'   time,null dc  from dual union all
select '2023-10-27 14:18:21'   time,null dc  from dual union all
select '2023-10-27 14:18:22'   time,null dc  from dual union all
select '2023-10-27 14:18:23'   time,null dc  from dual union all
select '2023-10-27 14:18:24'   time,null dc  from dual union all
select '2023-10-27 14:18:25'   time,null dc  from dual union all
select '2023-10-27 14:18:26'   time,2    dc  from dual union all
select '2023-10-27 14:18:27'   time,null dc  from dual union all
select '2023-10-27 14:18:28'   time,null dc  from dual union all
select '2023-10-27 14:18:29'   time,null dc  from dual union all
select '2023-10-27 14:18:30'   time,null dc  from dual union all
select '2023-10-27 14:18:31'   time,null dc  from dual union all
select '2023-10-27 14:18:32'   time,null dc  from dual union all
select '2023-10-27 14:18:33'   time,7    dc  from dual union all
select '2023-10-27 14:18:34'   time,null dc  from dual union all
select '2023-10-27 14:18:35'   time,null dc  from dual union all
select '2023-10-27 14:18:36'   time,null dc  from dual union all
select '2023-10-27 14:18:37'   time,null dc  from dual union all
select '2023-10-27 14:18:38'   time,null dc  from dual union all
select '2023-10-27 14:18:39'   time,null dc  from dual union all
select '2023-10-27 14:18:40'   time,null dc  from dual union all
select '2023-10-27 14:18:41'   time,null dc  from dual union all
select '2023-10-27 14:18:42'   time,null dc  from dual union all
select '2023-10-27 14:18:43'   time,null dc  from dual union all
select '2023-10-27 14:18:44'   time,null dc  from dual union all
select '2023-10-27 14:18:45'   time,null dc  from dual union all
select '2023-10-27 14:18:46'   time,null dc  from dual union all
select '2023-10-27 14:18:47'   time,null dc  from dual union all
select '2023-10-27 14:18:48'   time,null dc  from dual union all
select '2023-10-27 14:18:49'   time,null dc  from dual union all
select '2023-10-27 14:18:50'   time,null dc  from dual union all
select '2023-10-27 14:18:51'   time,null dc  from dual union all
select '2023-10-27 14:18:52'   time,8    dc  from dual union all
select '2023-10-27 14:18:53'   time,null dc  from dual union all
select '2023-10-27 14:18:54'   time,null dc  from dual union all
select '2023-10-27 14:18:55'   time,null dc  from dual union all
select '2023-10-27 14:18:56'   time,null dc  from dual union all
select '2023-10-27 14:18:57'   time,null dc  from dual union all
select '2023-10-27 14:18:58'   time,null dc  from dual union all
select '2023-10-27 14:18:59'   time,null dc  from dual union all
select '2023-10-27 14:19:00'   time,null dc  from dual 

 )

 select t1.time ,t1.dc oldDc,nvl(last_value(t1.dc IGNORE NULLS) over(ORDER BY t1.time ),0) newDc
 from tab t1

4.翻页分析函数，求之前或之后的第N行：

函数作用
lag() over() lag()函数向下偏移。
lead() over() lead()函数是向上偏移

lag(arg1,arg2,arg3) 和 lead(arg1,arg2,arg3) 可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。

arg1:参数是列名，
arg2:参数是偏移的offset，
arg3:参数是超出记录窗口时的默认值。

5.排列组合函数：

函数作用：各层级分层汇总

group by rollup(a, b, c)	首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，其后再对(a)进行group by，最后对全表进行汇总操作。
group by cube(a, b, c)	首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，最后对全表进行汇总操作。