东莞网站建设规范,美食网页设计免费模板,众创空间文化建设网站,子午谷网站建设窗口函数语法结构#xff1a;
分析函数() over(partition by 分组列名 order by 排序列名 rows between 开始位置 and 结束位置)
开窗函数和聚合函数区别#xff1a; 聚合函数会对一组值进行计算并返回一个值#xff0c;常见的比如sum()#xff0c;count()#xff0c;ma…窗口函数语法结构
分析函数() over(partition by 分组列名 order by 排序列名 rows between 开始位置 and 结束位置)
开窗函数和聚合函数区别 聚合函数会对一组值进行计算并返回一个值常见的比如sum()count()max()min() avg()会与group by一起使用。 注意其中只有count不忽略null值COUNT(a)返回非空值的数量。COUNT(*)返回所有行的数量不区分空值。 count01 ROW_NUMBER()
有些场景比如求各个班或各个学科的前几名这时候需要每一组返回多个值就需要开窗函数解决。 比如现在有一部分学生成绩来自不同的班级 求所有人中的前几名学生 首先想到SELECT * FROM temp ORDER BY sroce DESC 这是对分数倒序排序如果想只要前几名名需要生成一个序号所以使用ROW_NUMBER()。不分组
SELECT name,class,score, ROW_NUMBER() OVER(ORDER BY score DESC) mm FROM temp如果对于班级分组加上PARTITION BY class
SELECT name,class,score
FROM ( SELECT name,class,score, RANK() OVER(PARTITION BY class ORDER BY score) mm FROM temp ) a WHERE mm 1;–这里引用博客[窗口函数over](https://blog.csdn.net/qq_55342245/article/details/122300899)的例子
RANK() DENSE_RANK()
进一步如果只要第一名或者最后一名就不能用row_number()因为如果同班有两个并列第一mm1就只返回一个结果。使用RANK() 查询每个班的第一名的成绩
SELECT name,class,score
FROM (SELECT name,class,score, RANK() OVER(PARTITION BY class ORDER BY score DESC) mm FROM TEMP ) a WHERE mm 1;RANK()在处理相同值时序号一样但是会占位排名可能是12225 DENSE_RANK()在处理相同值时序号一样但是不会占位数字连续出现12223
NTILE()
NTILE函数是用来将数据切割成指定数量的桶buckets并将每个数据行分配到对应的桶中。它可用于将数据划分为相等的小切片为每一行分配该小切片的数字序号。
NTILE不支持ROWS BETWEEN比如NTILE(2) OVER(PARTITION BY dept_no ORDER BY salary ROWS BETWEEN 3 PRECEDING - AND CURRENT ROW)。
搬运一些例子便于理解
SELECT name, dept_no, salary,ntile(2) over(order by salary) n1,-- 全局按照salary升序排列数据切成2份ntile(2) over(partition by dept_no order by salary) n2, -- 按照dept_no分组在分组内按照salary升序排列,数据切成2份ntile(3) over(partition by dept_no order by salary) n3 -- 按照dept_no分组在分组内按照salary升序排列,数据切成3份
FROM data;对于第三个例子 NTILE(3) OVER(PARTITION BY dept_no ORDER BY salary)是根据dept_no列进行分组意味着每个部门的数据会独立进行切割成3份。数据在每个分组内根据salary排序然后再将排序后的数据切割成3份并且每份数据的大小相对均匀。 ———————————————— 参考自窗口函数之ntile()函数讲解https://blog.csdn.net/godlovedaniel/article/details/116571187
总结区别
select ROW_NUMBER()over(order by name) as 排序,* from temp — 排序即使值一样也不会重复排序。例如1,2,3,4,5 select RANK()over(order by name) as 排序,* from temp — 排序值一样就重复排序有间隙。例如1,1,3,4 select DENSE_RANK()over(order by name) as 排序,* from temp — 排序值一样就重复排序没有间隙。例如1,1,2,2,3,4,5 select NTILE(2)over(order by name) as 排序,* from temp — 排序分成2组。此函数一般用于取表中前百分之几的数据。例如取数据的前25%就将数据分4组然后字段的条件是等于1。 –这里引用博客[窗口函数over](https://blog.csdn.net/qq_55342245/article/details/122300899)的例子
