开窗函数 OVER(PARTITION BY)函数介绍 开窗函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 –排序，即便值一样，也不会出现重复排序， 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 下面的测试用例数据语句如下： 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) 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) 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; 分别求出第一个和最后一个成绩。 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虽然也具有分组功能，但同时也具有其他的高级功能。
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/25199.html
