mysql占⽐函数_MySQL数分：窗⼝函数
本次分享内容为SQL的窗⼝函数，内容主要有以下⼏点：什么是窗⼝函数
如何使⽤窗⼝函数
聚合函数作为窗⼝函数
窗⼝函数的移动平均
什么是窗⼝函数窗⼝函数在和当前⾏相关的⼀组表⾏上执⾏计算。 这相当于⼀个可以由聚合函数完成的计算类型。但不同于常规的聚合函数， 使⽤的窗⼝函数不会导致⾏被分组到⼀个单⼀的输出⾏；⾏保留其独⽴的⾝份。 在后台，窗⼝函数能够访问的不⽌查询结果的当前⾏。
很拗⼝，我们举个例⼦，先新建⼀个score_new，录⼊数据：
INSERT into score (学号，课程号，成绩) VALUES ('0001','0001',90);
周芷若的乳头INSERT into score (学号，课程号，成绩) VALUES ('0001','0002',85);
INSERT into score (学号，课程号，成绩) VALUES ('0001','0003',75);
INSERT into score (学号，课程号，成绩) VALUES ('0002','0001',85);
INSERT into score (学号，课程号，成绩) VALUES ('0002','0002',96);
INSERT into score (学号，课程号，成绩) VALUES ('0002','0003',100);
INSERT into score (学号，课程号，成绩) VALUES ('0003','0001',61);
INSERT into score (学号，课程号，成绩) VALUES ('0003','0002',73);
INSERT into score (学号，课程号，成绩) VALUES ('0003','0003',82);
INSERT into score (学号，课程号，成绩) VALUES ('0004','0001',66);
INSERT into score (学号，课程号，成绩) VALUES ('0004','0002',77);
INSERT into score (学号，课程号，成绩) VALUES ('0004','0003',88);
INSERT into score (学号，课程号，成绩) VALUES ('0009','0003',50)；说明如何⽐较每个学⽣的成绩和在他或她的班级的平均成绩：
-- 如何⽐较每个学⽣的成绩和在他或她的班级的平均成绩SELECT 班级，学号，成绩,AVG(成绩)
OVER (PARTITION BY 班级) AS 班级平均成绩
FROM score_new;
这⾥我们使⽤了新的SQL函数，这个函数就是窗⼝函数。前三输出列直接来⾃表score_new，并有⼀个针对表中的每⼀⾏的输出⾏，第四列将代表所有含有相同的班级的表⾏的平均值作为当前值。
窗⼝函数的调⽤总是包含⼀个OVER⼦句，后⾯直接跟着窗⼝函数的名称和参数。 这是它在语法上区别于普通函数或聚合功能的地⽅。 窗⼝函数基本语法如下：
‹窗⼝函数› over (partition by ‹⽤于分组的列名›
order by ‹⽤于排序的列名›)
⽇常⼯作中，我们可以通过窗⼝函数解决很多问题，⽐如：分组取每组最⼤值、最⼩值，每组最⼤的N条(top N)记录等，我们都可以使⽤窗⼝函数。
接下来我们说⼀下窗⼝函数怎么使⽤吧~
如何使⽤窗⼝函数
接下来，就结合实例，给⼤家介绍⼏种窗⼝函数的⽤法：
1.rank()：返回数据集中每个值的排名。排名值是根据当前⾏之前的⾏数加1，不包含当前⾏。因此，排序的关联值可能产⽣顺序上的空隙。 这个排名会对每个窗⼝分区进⾏计算；
2.dense_rank()：返回⼀组数值中每个数值的排名。这个函数与 rank() ，相似，除了关联值不会产⽣顺序上的空隙；
3.percent_rank()：返回数据集中每个数据的排名百分⽐。结果是根据 (r - 1) / (n - 1) 其中 r 是由 rank() 计算 的当前⾏排名， n 是当前窗⼝分区内总的⾏数；
5.cume_dist()：返回⼀组数值中每个值的累计分布。 结果返回的是按照窗⼝分区下窗⼝排序后的数据集下，当前⾏前⾯包括当前⾏数据的⾏数。因此，排序中任何关联值均会计算成相同的分布值.。
实例来看：
-- 各窗⼝函数SELECT *,古代六艺
rank() over (ORDER BY 成绩 DESC )as ranking,
dense_rank() over (ORDER BY 成绩 DESC )as dense_ranking,
ROUND(percent_rank() over (ORDER BY 成绩 DESC ),2)as percent_ranking,
row_number() over (ORDER BY 成绩 DESC )as row_num,
cume_dist() over (ORDER BY 成绩 DESC )as cume_d
FROM score_new;
得到结果：20⾏数据，2⾏在屏幕外未截图
中国有几个直辖市从上⾯的结果可以看出：rank()函数，例⼦中存在名次是并列的情况时，会占⽤下⼀名次位置。⽐如说，正常排名
1,2,3,4,5,6······但是使⽤rank()函数，结果就是1,2,3,4,4,6······；
dense_rank()函数，例⼦中存在名次是并列的情况时，不会占⽤下⼀名次位置。⽐如说，正常排名1,2,3,4,5,6······但是使⽤dense_rank()函数，结果就是1,2,3,4,4,5······；
percent_rank()函数，例⼦中percent_rank函数是在rank()函数基础上进⾏百分⽐计算排名百分⽐，⽐
如第4名的排名百分⽐ = (r - 1) / (n - 1) = (4 - 1) / (20 - 1) =0.16；
row_number()函数，例⼦中存在名次是并列的情况，但也不会对并列名次考虑，⽐如说，正常排名1,2,3,4,5,6······使⽤
row_number()函数，结果就是1,2,3,4,5,6······；
cume_dist()函数，例⼦中返回的都是落在每个成绩中的累积概率，⽐如说，有20个学⽣成绩每个成绩概率是0.5，但是有存在相同成绩n 个，那这个成绩的概率是0.5*n，那累计概率就是说落在了这个成绩上的概率⾯积，这⾥因为成绩是降序的我们可以理解为成绩>=该成绩的所有概率和。
是不是还⽐较容易呢？⼀定要记着这⼏个函数的区别：
晒伤后皮肤发红怎么办像⼀开始，我们提到，窗⼝函数可以解决分组取每组最⼤值、最⼩值，每组最⼤的N条(top N)记录，下⾯我们举例来看：查每个班级成绩最⾼的2个学⽣成绩数据。
在这之前，我们先想⼀下，查每个班级最⼤成绩的学⽣数据怎么查询？
-- 按班级号分组取成绩最⼤值所在⾏的数据/*⼤⽩话：对班级分组，对分组结果查询最⼤成绩；对每个班级对应的学⽣的成绩⽐较，等于最⼤成绩的便符合条件SELECT MAX(成绩) FROM score_new GROUP BY 班级;SELECT * FROM score_new WHERE 成绩 = ;*/
SELECT *
FROM score_new as a
WHERE 成绩 =
(SELECT MAX(成绩) FROM score_new AS b WHERE a.班级=b.班级 GROUP BY 班级);
接着我们回到开始的问题：查每个班级成绩最⾼的2个学⽣成绩数据：
拆分两步：
1.先按班级分组，并按照成绩降序排列，查看各班成绩排名：
/*先按班级分组，并按照成绩降序排列SELECT *, row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num FROM score_new;*/
SELECT *,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM score_new;
结果：
2.只要我们筛选row_num值<=2即可，我们可以⽤⼦查询：
SELECT *
FROM
(SELECT *,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS row_num
FROM score_new) AS a
WHERE row_num <=2;
这⾥我们使⽤了partition by ，在over⼦句中，partition by 列表指定将⾏划分成组或分区， 组或分区共享相同的partition by表达式的值，意思就是可以分组。但是需要注意的是，partition by与group by 不同：partition by相⽐group by，能够在保留全部数据的基础上对其中某些字段做分组排序；
但group by则只保留参与分组的字段和聚合函数的结果；
聚合函数作为窗⼝函数
窗⼝函数，除了可以使⽤刚才提到的专⽤窗⼝函数外，还可以使⽤聚合函数。
所有聚合函数可以通过添加 OVER ⼦句来作为窗⼝函数使⽤。这些聚合函数会基于当前滑动窗⼝内的数据⾏计算每⼀⾏数据。
我们可以实例来看⼀下：
-- 各窗⼝函数-聚合函数SELECT *,
SUM(成绩) over (PARTITION BY 班级 ORDER BY 成绩 DESC )as score_sum,马景涛老婆
AVG(成绩) over (PARTITION BY 班级 ORDER BY 成绩 DESC )as score_avg,
MIN(成绩) over (PARTITION BY 班级 ORDER BY 成绩 DESC )as score_min,
MAX(成绩) over (PARTITION BY 班级 ORDER BY 成绩 DESC )as score_max
FROM score_new;
我们可以看的到，不管是sum()、avg()，还是min()、max()，他们在窗⼝函数中，都是对⾃⾝记录以及位于⾃⾝记录之前的数据进⾏聚合，求和、求平均、最⼩值、最⼤值。
所以，聚合函数作为窗⼝函数的时候可以在每⼀⾏的数据⾥直观的看到，截⽌到本⾏数据统计数据是多少，也可以看出每⼀⾏数据对整体的影响。
窗⼝函数的移动平均
先说⼀下移动平均数：
若依次得到测定值(x1,x2,x3···,xn)时，按顺序取⼀定个数所做的全部算术平均值。 例如:
那移动平均数有什么⽤呢？我们可以通过移动平均，直观地查看到与相邻数据的平均、求和等统计数据。移动平均可以进⾏即期预测，当产品需求既不快速增长也不快速下降的时候(不存在季节因素)，移动平均能有效的消除预测中的随机波动。
我们⼀起看⼀下如何进⾏窗⼝函数的移动平均吧(平均数为例)~
-- 窗⼝的移动平均select *,
avg(成绩) over (order by 班级 rows 2 preceding )as current_avg
from score_new;
或许⼀时不能看明⽩，我们把前⼏⾏的算法看⼀下：电动剃须刀排行榜
是不是就明⽩了？
在这⾥我们使⽤了rows和preceding这两个关键字，意思就是“之前n⾏的意思”，实例中我们就是“之前2⾏”，也就是与前两⾏⼀起计算算数平均值。
写在最后：
窗⼝函数原则上只能写在select⼦句中。
⼤家在以下场景中，就可以使⽤我们的窗⼝函数哟：① 经典的TopN问题；⽐如像前边我们查每个班级成绩最⾼的2个学⽣成绩数据；
② 经典排名问题：⽐如按班级对学⽣成绩进⾏排名；
③ 在每个组⾥的⽐较问题：⽐如查每个班级⾥成绩⼤于该班级平均成绩的学⽣排名。
本次分享就先到这⾥~⼀定要多多练习才能掌握！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论