sql 开窗函数 目录 窗口函数 1.1 排序窗口函数rank 1.2 rank(), dense_rank(), row_number()区别 1.3 排序截取数据lag(),lead(),ntile(),cume_dist() 1.4 聚合函数作为窗口函数 1.5 over(- – rows between and ) 窗口函数 简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。 开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句 定义的多行记录 开窗函数一般分为两类,聚合开窗函数和排序开窗函数。 简单来说，窗口函数有以下功能： 1）同时具有分组和排序的功能 2）不减少原表的行数 3）语法如下： <窗口函数>的位置，可以放以下两种函数： 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum(). avg(), count(), max(), min()等,rows between…and… 因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。 3）业务需求“在每组内排名”，比如： 排名问题：每个部门按业绩来排名 topN问题：找出每个部门排名前N的员工进行奖励 1.1 排序窗口函数rank 我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分： 1）每个班级内：按班级分组 用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级） 2）按成绩排名 子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。 通过下图，我们就可以理解partiition by（分组）和order by（在组内排序)的作用了。 
group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。 
注意事项： partition子句可是省略，省略就是不指定分组，只是按成绩由高到低进行了排序。但是，这就失去了窗口函数的功能，所以一般不要这么使用。 窗口函数原则上只能写在select子句中 1.2 rank(), dense_rank(), row_number()区别 得到结果： 
从上面的结果可以看出： 函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。 函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。 函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。 1.3 排序截取数据lag(),lead(),ntile(),cume_dist() ：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val ：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val ：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。 ，计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布： 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。 1.4 聚合函数作为窗口函数 聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。 我们来看一下窗口函数是聚合函数时，会出来什么结果： 
如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。 不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算， 这样使用窗口函数有什么用呢？ 聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。 1.5 over(- – rows between and ) rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。 ：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。 ：当前行 ：往前n行数据 ：往后n行数据 ：起点，unbounded preceding 表示从表数据的起点， unbounded following表示到后面的终点 以上为个人经验，希望能给大家一个参考，也希望大家多多
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/72933.html
