1、分析函数:
1)分析函数:
Oracle从8.1.6开始提供分析函数,专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。分析函数用于计算基于组的某种聚合值。
它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。普通的聚合函数用group by分组,每个分组返回一个统计值;而分析函数采用partitionby分组,并且每组每行都可以返回一个统计值。
2)开窗函数:
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。例如 over函数
3)分析函数的形式
分析函数带有一个开窗函数over(),在窗口函数中包含三个分析子句:分组(partitionby), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。
例如:统计函数+over()、排序函数+over()、数据分布函数+over()、统计分析函数+over()。
2、开窗函数:
1)over(order by col) :可以理解为按照col列排序进行 累计,orderby是个默认的开窗函数
SQL> select *from t_over;
1 1 1
2 1.75 7
2 1.75 7
2 1.75 7
3 2 10
4 2.33333333 14
5 2.71428571 19
6 3.125 25
7 3.55555556 32
9 4.1 41
10 rows selected.
2)分区(partition by col):按照col进行分区统计
SQL> selecta,sum(a)over(partition by a) from t_over;
A SUM(A)OVER(PARTITIONBYA)
1 10 — 1减2加2范围是-1到3,a在这个范围内有1、2、2、2、3,所以是10
2 14
2 14
2 14
3 19
4 24
5 25 — 5减2加2范围是3到7,a在这个范围内有3、4、5、6、7,所以是25
6 22
7 27
9 16 — 9减2加2范围是7到11,a在这个范围内有7、9,所以是16
10 rows selected.
4)范围over(order by salary rows between 5preceding and 5 following):窗口范围为当前行前后各移动5行。
SQL> selecta,sum(a)over(order by a rows between 2 preceding and 2 following) from t_over;
ASUM(A)OVER(ORDERBYAROWSBETWEEN2PRECEDINGAND2FOLLOWING)
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
30 rows selected.
first_value() 和 last_value() :
- FIRST_VALUE 返回组中数据窗口的第一个值 ;
- LAST_VALUE 返回组中数据窗口的最后一个值。
lag() 和 lead ():
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。
语法:lag(exp_str,offset,defval) over()
- exp_str 是要做对比的字段;
- offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比,第二行和第四行,依次类推,offset的默认值为1!
- defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
Original: https://blog.51cto.com/u_5650011/5387165
Author: 赶路人儿
Title: oracle分析函数——over
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/517300/
转载文章受原作者版权保护。转载请注明原作者出处!