oracle数据分析函数_oracle表分析语句

SQL窗口函数详解–什么是窗口函数？数据分析师面试题必备 　　

　　1.什么是“窗口”？ 　　如果看了上一篇文章“MySQL中group by分组和聚合函数”的话，应该理解了什么是分组和聚合，聚合函数可以理解为对一组值执行计算并返回单一的值的一类函数，它借助group by子句，根据“by”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。这里的“组”、“小区域”其实就是“窗口”，换了个说法而已。

　　对于聚合函数来说，”窗口“是固定的，就是每一个组。比如我想知道每个学生的平均成绩，指定的组就是每个学生的”s_id”，聚合函数在这个s_id划定的窗口内对所有记录进行计算。这是静态窗口，窗口内的记录相互关联，窗口外的记录彼此无关。 　　但是实际业务中我们往往需要处理排名、累加、移动平均、取前N个这样的需求，每个记录的排名需要动态计算它在所有数据的位置；累加需要动态计算前面所有记录的和；移动平均要滑动计算；取前N个也是需要计算排名。这时候就需要动态窗口或者叫滑动窗口。 　　2.什么是“窗口函数”？ 　　“窗口函数”是动态处理数据的一类函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。 　　“窗口函数”由两部分组成，第一部分是“分析函数”，第二部分是“窗口函数”，语法如下： 　　其中第二部分over(）才是“窗口函数”，只不过因为它的特点大家都这样叫，就默认为这个语法结构就是“窗口函数”。over(）的作用是划定窗口，分析函数的作用是在窗口内对记录进行计算。 　　2.1 over(） 　　先讲窗口函数over(），它的括号内有三个参数： 　　2.1.1 “partition by” 　　表示的是分组或者分区，可以理解为group by，但是地位不同，如果partition by和group by后的字段相同，partition by就会失效。因为group by先分组（划分窗口），如果字段相同则partition by就没有可分的了。

　　如何只是简单的聚合计算，比如求和、求平均，partition by和group by的实质是一样的，不同在于group by聚合计算后合并记录，而partition by聚合计算后不合并记录，官方参考手册里解释over()窗口不会对查询的行组折叠为单个输出行，而是为为每一行生成一个结果。如果想达到和group by一样的效果，前面加上distinct关键字进行去重。

　　2.1.2 “order by” 　　表示排序，前面说到的排名、累加都有排序的操作在里面，意思就是根据什么条件排名、根据什么条件累加。order by后面可以跟一个字段也可以跟多个字段，哪个写在前面先排序哪个。排序的方式有“asc（升序）”和”desc（降序）”控制。 　　2.1.3 rows between “开始的行” between “结束的行” 　　这里就是控制窗口大小的地方，这个范围是随着数据的变化而变化的，写法有很多种，代表着不同的含义： 　　为了更好理解，下面列举几个例子： 　　再用一张动态图来演示计算累加的动态窗口是怎么变化的：

　　结合前面与groupby分组同时处理一组值的区别，窗口函数会对每一条记录扫描，生成动态窗口，再进行计算。这里累加的窗口范围写法就是“rows between unbounded preceding and current row”，一般默认不写。 　　小结：以上就是三个参数的详解。这三个参数是可以自由组合使用以实现不同业务需求。 　　如果一个都不用，over后面的括号是空的，则所有记录就是一个窗口（未分组），前面的分析函数就是聚合函数的作用，整体聚合； 　　如果只用partition by则相当于group by分组聚合； 　　如果只用order by则可以实现整体数据累加计算； 　　如果只用rows between就可以实现窗口滑动、移动平均； 　　如果用partition by和order by实现分组累加计算； 　　如果用partition by和rows between实现分组移动平均计算； 　　2.2 分析函数 　　搞清楚窗口之后，就可以解释与窗口函数一起使用的分析函数： 　　官方手册里可以查到所有可与窗口函数搭配使用的函数：12.21.2 窗口函数概念和语法，这里对经常使用进行分类举例说： 　　2.2.1 聚合函数（括号内加列名） 　　一般情况下，常用的AVG()，COUNT()，GROUP_CONCAT()，MAX()，MIN()，STD()，SUM()，VARIANCE()都可以作为分析函数。 　　2.2.2 排名函数（括号内什么都不写） 　　ROW_NUMBER()：按照字段值排序后生成一个连续不重复的排名，比如1、2、3、4、5；如果记录数相同的话，原始数据谁在前面就先排名，最后的排名就是记录数。 　　RANK()：按照字段值排序后生成一个不连续且会重复的排名，比如1、2、2、4、5；排名会断掉，最后的排名就是记录数。 　　DENSE_RANK()：按照字段值排序后生成一个连续且会重复的排名，比如1、2、2、3、4；排名不会断掉，但是最后的排名小于记录数。 　　2.2.3 偏移函数 　　LEAD(col,n,default)：用于统计窗口内往后第几行值。第一个参数为列名，第二个参数为往后第n行（可选，默认为1），第三个参数为默认值（当往后第n行为NULL时候，取默认值，如不指定，则为NULL）。 　　可以解决上一次登录、上一次购买等连续问题，非常实用。通过这个案例来加深理解：连续60天畅销产品怎么找？SQL相邻问题之连续登录的四种解法 　　LAG(col,n,default)：用于统计窗口内往前第几行值。第一个参数为列名，第二个参数为往前第n行（可选，默认为1），第三个参数为默认值（当往前第n行为NULL时候，取默认值，如不指定，则为NULL）。 　　FIRST_VALUE(列名)：用于找出统计窗口内的第一个值。 　　LAST_VALUE(列名)：用于找出统计窗口内的最后一个值。 　　NTH_VALUE()：用于找出统计窗口内的第N个值。 　　2.2.4 占比函数 　　CUME_DIST()：小于等于当前值的行数占分组内总行数的比例，相当于求累加分布函数。比如，统计小于等于当前薪水的人数，所占总人数的比例。 　　PERCENT_RANK()：分组内当前行的RANK值减1占分组内总行数减1的比例，也就是排名百分比。 　　NTILE()：按照记录数分区并排名编号，分区的方式是按百分比平均分成多少份，比如分成五份，编号1,2,3,4,5；间接可视作是排名。 　　3.窗口函数的使用 　　Oracle和SQL server很早就有了窗口函数，但是MySQL在8.0版本才开始引进窗口函数，在这之前都是使用自定义变量的方法处理排名、累加等问题。 　　因为窗口函数是计算函数不是筛选，只能对where和group by子句筛选后的结果进行操作，所以窗口函数只能写在select子句中，不能像聚合函数那样放在having后面做筛选条件。 　　窗口函数内不可以使用distinct关键字去重，但是在最外层可以使用。 　　MySQL中不支持窗口函数的嵌套，即窗口函数内字段名嵌套一个窗口函数。 　　over()作为窗口，如果需要重复使用多次，可以使用WINDOW子句在使用窗口的函数中单独指定窗口并起别名，然后在各函数的OVER子句内引用窗口别名。



　　4.典型案例 　　4.1 排名 　　4.1.1 所有成绩进行排名（全局排名） 　　三种不同的排名函数结果不同。

　　4.1.2 每科成绩排名

　　在全局排名的基础上增加了partition by分组，就实现了组内排名。 　　4.1.3 按成绩从高到低把学生分为1,2,3组

　　把学生按成绩分为三个组，第一组成绩高，第二组成绩中等，第三组成绩较低。通过括号内的数字控制分成几个组。 　　4.1.4 查询每个学科成绩最高（低）的学生

　　4.2 累加 　　4.2.1 按学号对学生总成绩进行累加（全局累加）

　　4.2.2 对每个学生按照学科成绩进行累加（分组累加）

　　4.3 移动平均 　　4.3.1 查询3日移动平均值

　　4.4 前N个/前百分比 　　4.4.1 查询各个成绩在全班所有成绩的占比

　　4.4.2 查询每科成绩第一名和最后一名成绩

　　4.4.3 查询每科成绩第三名

　　4.5上一个连续记录 　　4.5.1 查询每个学生的上（下）一个同学的学号

　　4.5.2 查询每个学生每科成绩比上一门高多少百分比（求同比）

　　5.聚合函数和窗口函数的联系与区别 　　窗口函数与聚合函数的功能相似，两者都可以对指定数据窗口进行统计分析，但窗口函数与聚合函数又有所区别，窗口函数可以为每行数据进行一次计算，因为窗口函数指定了数据窗口大小，这个数据窗口大小可能会随着行的变化而滑动变化，可以在这个滑动窗口里进行计算并返回一个值。而聚合函数只返回一行，因为它只能对分组下的所有数据进行统计。 　　聚合函数相当于是group by和order by关键字的封装，而窗口函数是动态分组和order by的封装。 　　另外，在使用聚合函数时，与分组列无关的列不可以出现在SELECT关键字下，如果想要把除了分组列之外的其他明细数据和聚合值同时提取，聚合函数是实现不了的 ，而窗口函数就可以方便地实现这一点。比如我想在一行同时呈现某个产品的销量和所有产品的销量汇总sum，以便于计算某个产品占所有销量的百分比，这个时候窗口函数就可以非常完美地支持。 　　以上就是窗口函数的全部内容，需要注意到的知识点都囊括进来，还有些例子没有完全展开，后续会慢慢补充。 　　最后欢迎大家我，我是拾陆，搜索“二八Data”，更多技术干货持续奉献。