做网站好还是做微信小程序好,微信营销成,网站关键字在哪设置,不关网站备案开窗函数——排序函数
开窗函数与其他函数的区别是,它不是关联其他表查询,而是在一张表内根据我们的想法自定义的规则分组后对我们组内的数据进行检索和计算。我们自定义的规则所分的组#xff0c;就如同整张表的一个个小窗口#xff0c;因此我们开出一个个小窗口并对这些小…开窗函数——排序函数
开窗函数与其他函数的区别是,它不是关联其他表查询,而是在一张表内根据我们的想法自定义的规则分组后对我们组内的数据进行检索和计算。我们自定义的规则所分的组就如同整张表的一个个小窗口因此我们开出一个个小窗口并对这些小窗口进行的操作就叫做开窗函数。 开窗函数分为排序函数和聚合函数。
一、排序函数
1.三个排序函数
句型row_number()的排序函数
order_func OVER([PARTITION BY expression] ORDER BY clase)其中order_func是指排序函数包括 row_number(),rank(),dense_rank(),ntile(); 他们会为每一行返回一个序值。 PARTITION BY 是我们自定义的分组策略如果不用该函数会把其余select 语句包括where、having、group by的运行结果做为窗口进行排序ORDER BY 是排序的方法即每个窗口内根据哪个字段排序不能缺省。 例如:我们需要对一个包含4个班级的16个同学的成绩表进行以班为单位的排序我们可以这样排序
select st_name,st_class,st_score,row_number() over(partition by st_class order by score) as 名次
from score_tab;但有时候我们的业务需求我们排序但不以表中的字段排。而我们又不能没有ORDER BY语句我们应该怎么办呢是的开发者也考虑到了
select st_name,st_class,st_score,row_number() over(partition by st_class order by (select 0)) as 名次
from score_tab;当然这并不是所有sql家族成员都是这么设计的比如hive的方法就略有不同
select st_name,st_class,st_score,row_number() over(partition by st_class order by 0) as 序值
from score_tab;直接用0并不用select 0; 当然这种排序也是有弊端的就拿我们的学习成绩为例假如同一个窗口内的两个同学分数相同我们按这种方法排可能他们一个是第二名一个是第三名这样对孩子就太不公平了。于是我们又有了另外两种排序方式 rank()和dense_rank() 三者的区别是
row_number() 对窗口每一行记录输出一个序值同一窗口不会有重复的充值,无论排序字段值是否相等都是1,2,3,4;
rank() 对窗口内每一行记录输出一个序值字段值相同的则输出相同序值下一充值为非密集排序如1224;
dense_rank() 对窗口内每一行记录输出一个充值字段值相同的则输出相同序值下一序值为密集排序如:1,2,2,3;2.数据均分分组函数–NTILE()
NTILE()的功能是进行均分分组其参数是我们要分组的数量比如我们把学生平均分为四组
select st_name,, st_class,,st_score,NTILE(4) OVER(ORDER BY st_score) as 分组
from score_tab;结果是按照成绩排列为1234组部分数据如下
st_namest_classst_score分组a1681c4691r3721d4851
如果我们按班级为单位把每一个班的成绩平均分为高、下两种评级则
select st_name,, st_class,,st_score,CASE NTILE(2) OVER(PARTITION BY st_class ORDER BY st_score) when 1 then 低when 1 then 高END AS ‘level’
from score_tab;st_namest_classst_scorelevelp469低g475低e487高j492高
总结
一般我们在使用排序函数的时候我们不会同时使用distinct因为我们在给记录进行排序时一般会忽略同值记录事实上我们执行如下语法其中的distinct并没有起作用不会去重只是增加了资源的开销
SELECT DISTINCT st_score,ROW_NUMBER() OVER(PARTITION BY st_class ORDER BY st_score) AS 排名
FROM score_tab;如果我们业务真实需要去重后再编序值请用过滤条件GROUP BY,如
SELECT st_score,ROW_NUMBER() OVER(PARTITION BY st_class ORDER BY st_score) AS 排名
FROM score_tab
GROUP BY st_score;相关替代方案–子查询
在开窗函数没有发布之前我们进行窗口操作都是使用子查询进行的其原理为:先查出该组内比当前成绩低的个数再加上1,就是该学生在该组的序值如
select st_name,st_class,st_score--开窗函数方式,row_number() over(partition by st_class order by score) as 开窗序值名次--子查询(SELECT COUNT(1)1 FORM st_score s2WHERE s2.st_classa1.st_class AND s2.st_scores1.st_score) as 子查询名次
from score_tab s1;--如果是密集排序我们只需把count()的参数改为排序字段即可去重如
select st_name,st_class,st_score--开窗函数方式,row_number() over(partition by st_class order by score) as 开窗序值名次--子查询(SELECT COUNT(st_score)1 FORM st_score s2WHERE s2.st_classa1.st_class AND s2.st_scores1.st_score) as 子查询名次
from score_tab s1;
