over(partition by)函数的使用_over partition by用法

开窗函数，Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

测试用例：

create table T2_TEMP(
 2     NAME varchar2(10) primary key,
 3     CLASS varchar2(10),
 4     SROCE NUMBER 
 5 )
 6 
 7 insert into T2_TEMP (NAME, CLASS, SROCE)
 8 values ('cfe', '2', 74);
 9 
10 insert into T2_TEMP (NAME, CLASS, SROCE)
11 values ('dss', '1', 95);
12 
13 insert into T2_TEMP (NAME, CLASS, SROCE)
14 values ('ffd', '1', 95);
15 
16 insert into T2_TEMP (NAME, CLASS, SROCE)
17 values ('fda', '1', 80);
18 
19 insert into T2_TEMP (NAME, CLASS, SROCE)
20 values ('gds', '2', 92);
21 
22 insert into T2_TEMP (NAME, CLASS, SROCE)
23 values ('gf', '3', 99);
24 
25 insert into T2_TEMP (NAME, CLASS, SROCE)
26 values ('ddd', '3', 99);
27 
28 insert into T2_TEMP (NAME, CLASS, SROCE)
29 values ('adf', '3', 45);
30 
31 insert into T2_TEMP (NAME, CLASS, SROCE)
32 values ('asdf', '3', 55);
33 
34 insert into T2_TEMP (NAME, CLASS, SROCE)
35 values ('3dd', '3', 78);

1、over（partition by）写法

over（partition by class order by sroce） 按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

2、开窗范围

over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

3、结合其它语句用法：

（1）查询每个班的第一名的成绩

SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) num from T2_TEMP t) where num = 1;

 得到的结果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1

如果存在并列第一的情况，使用row_number()则只返回一个结果。

SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) num from T2_TEMP t) where num = 1;

结果是并列第一的情况下只显示1个
dss      1        95        1  
gfs      2        92        1
ddd      3        99        1 

（2）rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

求班级成绩排名：

select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) num from T2_TEMP t;

dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5

dense_rank()是连续排序，有两个第二名时仍然跟着第三名

select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) num from T2_TEMP t;

dss        1        95        1
ffd        1        95        1
fda        1        80        2 
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4

3、sum()over()

根据班级进行分数求和:

select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

dss        1        95        190  --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

4、first_value() over()和last_value() over()分别求出第一个和最后一个成绩

        count() over(partition by ... order by ...)：求分组后的总数。
　　max() over(partition by ... order by ...)：求分组后的最大值。
　　min() over(partition by ... order by ...)：求分组后的最小值。
　　avg() over(partition by ... order by ...)：求分组后的平均值。
　　lag() over(partition by ... order by ...)：取出前n行数据。　　

　　lead() over(partition by ... order by ...)：取出后n行数据。

　　ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母

5、group by和partition by的区别

group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能，但同时也具有其他的高级功能。