orcale分析函数（二）

迷南。 2022-09-20 05:58 177阅读 0赞

分析函数over 及开窗函数  
一：分析函数over  
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是  
对于每个组返回多行，而聚合函数对于每个组只返回一行。  
下面通过几个例子来说明其应用。

1：统计某商店的营业额。

date sale  
1 20  
2 15  
3 14  
4 18  
5 30  
规则：按天统计：每天都统计前面几天的总额  
得到的结果：  
DATE SALE SUM  
\----- -------- ------  
1 20 20 --1天  
2 15 35 --1天＋2天  
3 14 49 --1天＋2天＋3天  
4 18 67 .  
5 30 97 .  
  
2:统计各班成绩第一名的同学信息

NAME CLASS S  
\----- ----- ----------------------  
fda 1 80  
ffd 1 78  
dss 1 95  
cfe 2 74  
gds 2 92  
gf 3 99  
ddd 3 99  
adf 3 45  
asdf 3 55  
3dd 3 78  
  
通过：  
\--  
select \* from  
(  
select name,class,s,rank()over(partition by class order by s desc) mm from t2  
)  
where mm=1  
\--  
得到结果：  
NAME CLASS S MM  
\----- ----- ---------------------- ----------------------  
dss 1 95 1  
gds 2 92 1  
gf 3 99 1  
ddd 3 99 1  
  
注意：  
1.在求第一名成绩的时候，不能用row\_number()，因为如果同班有两个并列第一，row\_number()只返回一个结果  
2.rank()和dense\_rank()的区别是：  
\--rank()是跳跃排序，有两个第二名时接下来就是第四名  
\--dense\_rank()l是连续排序，有两个第二名时仍然跟着第三名  
  
  
3.分类统计 (并显示信息)

A B C  
\-- -- ----------------------  
m a 2  
n a 3  
m a 2  
n b 2  
n b 1  
x b 3  
x b 2  
x b 4  
h b 3

select a,c,sum(c)over(partition by a) from t2  
得到结果：  
A B C SUM(C)OVER(PARTITIONBYA)  
\-- -- ------- ------------------------  
h b 3 3  
m a 2 4  
m a 2 4  
n a 3 6  
n b 2 6  
n b 1 6  
x b 3 9  
x b 2 9  
x b 4 9  
  
如果用sum，group by 则只能得到  
A SUM(C)  
\-- ----------------------  
h 3  
m 4  
n 6  
x 9  
无法得到B列值  
  
＝＝＝＝＝

二：开窗函数  
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：  
1：  
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数  
over（partition by deptno）按照部门分区  
2：  
over（order by salary range between 5 preceding and 5 following）  
每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5  
例如:对于以下列  
aa  
1  
2  
2  
2  
3  
4  
5  
6  
7  
9  
  
sum(aa)over（order by aa range between 2 preceding and 2 following）  
得出的结果是  
AA SUM  
\---------------------- -------------------------------------------------------  
1 10  
2 14  
2 14  
2 14  
3 18  
4 18  
5 22  
6 18  
7 22  
9 9  
  
就是说，对于aa=5的一行 ，sum为 5-1<=aa<=5+2 的和  
对于aa=2来说 ，sum=1+2+2+2+3+4=14 ；  
又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；  
  
3：其它：  
over（order by salary rows between 2 preceding and 4 following）  
每行对应的数据窗口是之前2行，之后4行  
4：下面三条语句等效：  
over（order by salary rows between unbounded preceding and unbounded following）  
每行对应的数据窗口是从第一行到最后一行，等效：  
over（order by salary range between unbounded preceding and unbounded following）  
等效  
over(partition by null)