开窗函数 OVER(PARTITION BY)函数介绍

柔光的暖阳◎ 2022-11-03 15:28 343阅读 0赞

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

–排序，即便值一样，也不会出现重复排序，  
select **ROW\_NUMBER** () over(order by name) as 排序,\* from T2\_TEMP  
–排序，值一样，就重复排序,例如1,1,3,4  
select **RANK**() over (order by name) as 排序,\* from T2\_TEMP  
–排序，值一样，就重复排序,例如1,1,2,2,3,4,5  
select **DENSE\_RANK**() over(order by name) as 排序,\* from T2\_TEMP  
–排序，分成几组  
select **NTILE**(2) over(order by name) as 排序,\* from T2\_TEMP

下面的测试用例数据语句如下：  
**本文使用的是sqlserver 数据库**

create table T2_TEMP(
        NAME nvarchar(10) primary key,
        CLASS nvarchar(10),
        SROCE numeric
    )
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('cfe', '2', 74);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('dss', '1', 95);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('ffd', '1', 95);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('fda', '1', 80);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('gds', '2', 92);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('gf', '3', 99);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('ddd', '3', 99);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('adf', '3', 45);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('asdf', '3', 55);
     
    insert into T2_TEMP (NAME, CLASS, SROCE)
    values ('3dd', '3', 78);

1、over函数的写法：

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、与over()函数结合的函数的介绍

1、查询每个班的第一名的成绩：如下

1 SELECT \* FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2\_TEMP t) t where mm = 1;  
结果为：  
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()，因为如果同班有两个并列第一，row\_number()只返回一个结果。  
1 SELECT \* FROM (select t.name,t.class,t.sroce,row\_number() over(partition by t.class order by t.sroce desc) mm from T2\_TEMP t) t where mm = 1;

结果为：

dss 1 95 1  
gfs 2 92 1  
ddd 3 99 1  
可以看出，本来第一名是两个人的并列，结果只显示了一个。  
2、rank()和dense\_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense\_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

求班级成绩排名：  
1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm 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()l是连续排序，有两个第二名时仍然跟着第三名  
1 select t.name,t.class,t.sroce,dense\_rank() over(partition by t.class order by t.sroce desc) mm 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（）的使用

根据班级进行分数求和  
1 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()的使用

1 select t.name,t.class,t.sroce,first\_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2\_TEMP t;  
2 select t.name,t.class,t.sroce,last\_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2\_TEMP t;  
（[SQL LAST\_VALUE使用问题记录][SQL LAST_VALUE]）  
分别求出第一个和最后一个成绩。

5、sum() over()的使用

1 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;求出班级的总分。

下面还有很多用法，就不一一列举了，简单介绍一下，和上面用法类似：  
　　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() 括号中就是分母。  
percent\_rank() over(partition by … order by …)：

6、over partition by与group by的区别：

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

参考文章：

1.  [开窗函数 OVER(PARTITION BY)函数介绍][OVER_PARTITION BY]
2.  [SqlServer四种排序：ROW\_NUMBER()/RANK()/DENSE\_RANK()/ntile() over()][SqlServer_ROW_NUMBER_RANK_DENSE_RANK_ntile_ over]

[SQL LAST_VALUE]: https://blog.csdn.net/apollokk/article/details/79842990
[OVER_PARTITION BY]: https://blog.csdn.net/cqkxzyi/article/details/86301731
[SqlServer_ROW_NUMBER_RANK_DENSE_RANK_ntile_ over]: https://www.cnblogs.com/SunnyZhu/p/5762898.html