开窗函数的使用_MySQL 开窗函数

2024年 8月 8日下午2:12 • 激活谷笔记

数据库面试题（一）——开窗函数OVER(PARTITION BY) 　　！！！！！！！！唯有美女，才有动力！！！！！！！！　　
　　目录　　！！！！！！！！唯有美女，才有动力！！！！！！！！　　一、开窗函数的概念：　　二、开窗函数的语法：　　三、开窗函数和聚合函数的区别：　　四、排序开窗函数row_number()、rank()、dense_rank()、ntile()的区别：　　1、首先创建表 students_grades：　　2、插入测试数据：　　3、查询语句如下：　　4、查询结果如下：　　五、聚合开窗函数：　　1、sum(salary) over(partition by city) 　　2、sum(salary) over(order by name) 　　3、sum(salary) over(partition by city order by name) 　　4、其他类似sum()的聚合函数：　　5、first_value(salary)和last_value(salary) 　　6、lead(salary,1,0)和lag(salary,2,9) 　　一、开窗函数的概念：　　开窗函数是计算基于组的某种聚合值，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。　　有两类：一类是排序开窗函数，一类是聚合开窗函数。　　二、开窗函数的语法：　　函数名(列名) over(partition by 列名1 order by 列名2 ) ，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。　　三、开窗函数和聚合函数的区别：　　（1）SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数。　　（2）聚合函数每组只返回一个值，开窗函数每组可返回多个值。　　注：常见主流数据库MSSQLServer、Oracle、DB2等都支持开窗函数，MySQL8.0支持开窗函数，MySQL8.0之前的版本不支持。　　四、排序开窗函数row_number()、rank()、dense_rank()、ntile()的区别：　　看不明白文字描述可以看后面的实操例子： row_number() over()：对相等的值不进行区分，相等的值对应的排名相同，序号从1到n连续。rank() over()：相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。dense_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，则排在第2名（假设仅有1个第二名）的人是第3个人。ntile( n ) over()：可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。　　实操举例：　　1、首先创建表 students_grades：　　2、插入测试数据：　　3、查询语句如下：　　4、查询结果如下：　　
　　五、聚合开窗函数：　　（测试数据在本节最后提供）　　1、sum(salary) over(partition by city) 　　结果如图：　　
　　2、sum(salary) over(order by name) 　　结果如图：　　
　　3、sum(salary) over(partition by city order by name) 　　结果如图：　　
　　每个人的工资按照每个月累计统计数据：　　
　　
4、其他类似sum()的聚合函数：　　count() over(partition by … order by …)：求分组后的总数。　　max() over(partition by … order by …)：求分组后的最大值。　　min() over(partition by … order by …)：求分组后的最小值。　　avg() over(partition by … order by …)：求分组后的平均值。　　5、first_value(salary)和last_value(salary) 　　结果分别如下图：　　
　　
　　6、lead(salary,1,0)和lag(salary,2,9) 　　结果如下图：　　
　　
　　测试数据：

开窗函数的使用_MySQL 开窗函数

相关推荐