有了这些函数，统计分析事半功倍

最新推荐文章于 2022-11-30 19:02:55 发布
最新推荐文章于 2022-11-30 19:02:55 发布
阅读量178 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gjc592/article/details/106977519
版权

MySQL8.0之前，做数据排名统计等相当痛苦，因为没有像Oracle、SQL SERVER 、PostgreSQL等其他数据库那样的窗口函数。但随着MySQL8.0中新增了窗口函数之后，针对这类统计就再也不是事了，本文就以常用的排序实例介绍MySQL的窗口函数并将常用的几个窗口函数进行小结。

01

   准备工作 

创建表及测试数据

mysql> use testdb;
Database changed
/*  创建表 */
mysql> create  table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1)，key idx_stuNo_course(stu_no,course));
Query OK, 0 rows affected (0.03 sec)


mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb_score         |
+------------------+


/* 新增一批测试数据 */
mysql> insert into tb_score(stu_no,course,score)values('2020001','mysql',90),('2020001','C++',85),('2020003','English',100),('2020002','mysql',50),('2020002','C++',70),('2020002','English',99);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020003','mysql',78),('2020003','C++',81),('2020003','English',80),('2020004','mysql',80),('2020004','C++',60),('2020004','English',100);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020005','mysql',98),('2020005','C++',96),('2020005','English',70),('2020006','mysql',60),('2020006','C++',90),('2020006','English',70);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020007','mysql',50),('2020007','C++',66),('2020007','English',76),('2020008','mysql',90),('2020008','C++',69),('2020008','English',86);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020009','mysql',70),('2020009','C++',66),('2020009','English',86),('2020010','mysql',75),('2020010','C++',76),('2020010','English',81);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> insert into tb_score(stu_no,course,score)values('2020011','mysql',90),('2020012','C++',85),('2020011','English',84),('2020012','English',75),('2020013','C++',96),('2020013','English',88);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

02

   统计排名 

根据每门课程的分数从高到低进行排名，此时，会出现分数相同时怎么处理的问题，下面就根据不同的窗口函数来处理不同场景的需求

ROW_NUMBER

由结果可以看出，分数相同时按照学号顺序进行排名

mysql> select stu_no,course,score, row_number()over(partition by course order by score desc ) rn
    -> from tb_score;
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  2 |
| 2020006 | C++     |  90.0 |  3 |
| 2020001 | C++     |  85.0 |  4 |
| 2020012 | C++     |  85.0 |  5 |
| 2020003 | C++     |  81.0 |  6 |
| 2020010 | C++     |  76.0 |  7 |
| 2020002 | C++     |  70.0 |  8 |
| 2020008 | C++     |  69.0 |  9 |
| 2020007 | C++     |  66.0 | 10 |
| 2020009 | C++     |  66.0 | 11 |
| 2020004 | C++     |  60.0 | 12 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  2 |
| 2020002 | English |  99.0 |  3 |
| 2020013 | English |  88.0 |  4 |
| 2020008 | English |  86.0 |  5 |
| 2020009 | English |  86.0 |  6 |
| 2020011 | English |  84.0 |  7 |
| 2020010 | English |  81.0 |  8 |
| 2020003 | English |  80.0 |  9 |
| 2020007 | English |  76.0 | 10 |
| 2020012 | English |  75.0 | 11 |
| 2020005 | English |  70.0 | 12 |
| 2020006 | English |  70.0 | 13 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  3 |
| 2020011 | mysql   |  90.0 |  4 |
| 2020004 | mysql   |  80.0 |  5 |
| 2020003 | mysql   |  78.0 |  6 |
| 2020010 | mysql   |  75.0 |  7 |
| 2020009 | mysql   |  70.0 |  8 |
| 2020006 | mysql   |  60.0 |  9 |
| 2020002 | mysql   |  50.0 | 10 |
| 2020007 | mysql   |  50.0 | 11 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

DENSE_RANK

为了让分数相同时排名也相同，则可以使用DENSE_RANK函数，结果如下：

mysql> select stu_no,course,score, DENSE_RANK()over(partition by course order by score desc ) rn
    -> from  tb_score  ;
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  1 |
| 2020006 | C++     |  90.0 |  2 |
| 2020001 | C++     |  85.0 |  3 |
| 2020012 | C++     |  85.0 |  3 |
| 2020003 | C++     |  81.0 |  4 |
| 2020010 | C++     |  76.0 |  5 |
| 2020002 | C++     |  70.0 |  6 |
| 2020008 | C++     |  69.0 |  7 |
| 2020007 | C++     |  66.0 |  8 |
| 2020009 | C++     |  66.0 |  8 |
| 2020004 | C++     |  60.0 |  9 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  1 |
| 2020002 | English |  99.0 |  2 |
| 2020013 | English |  88.0 |  3 |
| 2020008 | English |  86.0 |  4 |
| 2020009 | English |  86.0 |  4 |
| 2020011 | English |  84.0 |  5 |
| 2020010 | English |  81.0 |  6 |
| 2020003 | English |  80.0 |  7 |
| 2020007 | English |  76.0 |  8 |
| 2020012 | English |  75.0 |  9 |
| 2020005 | English |  70.0 | 10 |
| 2020006 | English |  70.0 | 10 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  2 |
| 2020011 | mysql   |  90.0 |  2 |
| 2020004 | mysql   |  80.0 |  3 |
| 2020003 | mysql   |  78.0 |  4 |
| 2020010 | mysql   |  75.0 |  5 |
| 2020009 | mysql   |  70.0 |  6 |
| 2020006 | mysql   |  60.0 |  7 |
| 2020002 | mysql   |  50.0 |  8 |
| 2020007 | mysql   |  50.0 |  8 |
+---------+---------+-------+----+
36 rows in set (0.00 sec)

RANK

DENSE_RANK的结果是分数相同时排名相同了，但是下一个名次是紧接着上一个名次的，如果2个并列的第1之后，下一个我想是第3名，则可以使用RANK函数实现

mysql> select stu_no,course,score,rank()over(partition by course order by score desc ) rn
    -> from  tb_score;
+---------+---------+-------+----+
| stu_no  | course  | score | rn |
+---------+---------+-------+----+
| 2020005 | C++     |  96.0 |  1 |
| 2020013 | C++     |  96.0 |  1 |
| 2020006 | C++     |  90.0 |  3 |
| 2020001 | C++     |  85.0 |  4 |
| 2020012 | C++     |  85.0 |  4 |
| 2020003 | C++     |  81.0 |  6 |
| 2020010 | C++     |  76.0 |  7 |
| 2020002 | C++     |  70.0 |  8 |
| 2020008 | C++     |  69.0 |  9 |
| 2020007 | C++     |  66.0 | 10 |
| 2020009 | C++     |  66.0 | 10 |
| 2020004 | C++     |  60.0 | 12 |
| 2020003 | English | 100.0 |  1 |
| 2020004 | English | 100.0 |  1 |
| 2020002 | English |  99.0 |  3 |
| 2020013 | English |  88.0 |  4 |
| 2020008 | English |  86.0 |  5 |
| 2020009 | English |  86.0 |  5 |
| 2020011 | English |  84.0 |  7 |
| 2020010 | English |  81.0 |  8 |
| 2020003 | English |  80.0 |  9 |
| 2020007 | English |  76.0 | 10 |
| 2020012 | English |  75.0 | 11 |
| 2020005 | English |  70.0 | 12 |
| 2020006 | English |  70.0 | 12 |
| 2020005 | mysql   |  98.0 |  1 |
| 2020001 | mysql   |  90.0 |  2 |
| 2020008 | mysql   |  90.0 |  2 |
| 2020011 | mysql   |  90.0 |  2 |
| 2020004 | mysql   |  80.0 |  5 |
| 2020003 | mysql   |  78.0 |  6 |
| 2020010 | mysql   |  75.0 |  7 |
| 2020009 | mysql   |  70.0 |  8 |
| 2020006 | mysql   |  60.0 |  9 |
| 2020002 | mysql   |  50.0 | 10 |
| 2020007 | mysql   |  50.0 | 10 |
+---------+---------+-------+----+
36 rows in set (0.01 sec)

这样就实现了各种排序需求。

NTILE

NTILE函数的作用是对每个分组排名后，再将对应分组分成N个小组，例如

mysql> select stu_no,course,score, rank()over(partition by course order by score desc )rn,NTILE(2)over(partition by course order by score desc ) rn_group  from  tb_score;
+---------+---------+-------+----+----------+
| stu_no  | course  | score | rn | rn_group |
+---------+---------+-------+----+----------+
| 2020005 | C++     |  96.0 |  1 |        1 |
| 2020013 | C++     |  96.0 |  1 |        1 |
| 2020006 | C++     |  90.0 |  3 |        1 |
| 2020001 | C++     |  85.0 |  4 |        1 |
| 2020012 | C++     |  85.0 |  4 |        1 |
| 2020003 | C++     |  81.0 |  6 |        1 |
| 2020010 | C++     |  76.0 |  7 |        2 |
| 2020002 | C++     |  70.0 |  8 |        2 |
| 2020008 | C++     |  69.0 |  9 |        2 |
| 2020007 | C++     |  66.0 | 10 |        2 |
| 2020009 | C++     |  66.0 | 10 |        2 |
| 2020004 | C++     |  60.0 | 12 |        2 |
| 2020003 | English | 100.0 |  1 |        1 |
| 2020004 | English | 100.0 |  1 |        1 |
| 2020002 | English |  99.0 |  3 |        1 |
| 2020013 | English |  88.0 |  4 |        1 |
| 2020008 | English |  86.0 |  5 |        1 |
| 2020009 | English |  86.0 |  5 |        1 |
| 2020011 | English |  84.0 |  7 |        1 |
| 2020010 | English |  81.0 |  8 |        2 |
| 2020003 | English |  80.0 |  9 |        2 |
| 2020007 | English |  76.0 | 10 |        2 |
| 2020012 | English |  75.0 | 11 |        2 |
| 2020005 | English |  70.0 | 12 |        2 |
| 2020006 | English |  70.0 | 12 |        2 |
| 2020005 | mysql   |  98.0 |  1 |        1 |
| 2020001 | mysql   |  90.0 |  2 |        1 |
| 2020008 | mysql   |  90.0 |  2 |        1 |
| 2020011 | mysql   |  90.0 |  2 |        1 |
| 2020004 | mysql   |  80.0 |  5 |        1 |
| 2020003 | mysql   |  78.0 |  6 |        1 |
| 2020010 | mysql   |  75.0 |  7 |        2 |
| 2020009 | mysql   |  70.0 |  8 |        2 |
| 2020006 | mysql   |  60.0 |  9 |        2 |
| 2020002 | mysql   |  50.0 | 10 |        2 |
| 2020007 | mysql   |  50.0 | 10 |        2 |
+---------+---------+-------+----+----------+
36 rows in set (0.01 sec)

03

   窗口函数小结 

MySQL中还有许多其他的窗口函数，本文列举一些，大家可以自行测试

 
类别函数说明
排序ROW_NUMBER为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段
DENSE_RANK根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，序号中没有间隙（1,1,2,3这种）
RANK根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，但序号中存在间隙（1,1,3,4这种）
NTILE根据排序字段为每个分组中根据指定字段的排序再分成对应的组
分布PERCENT_RANK计算各分组或结果集中行的百分数等级
CUME_DIST计算某个值在一组有序的数据中累计的分布
前后LEAD返回分组中当前行之后的第N行的值。如果不存在对应行，则返回NULL。比如N=1时，第一名对应的值是第二名的，最后一名结果是NULL
LAG返回分组中当前行之前的第N行的值。如果不存在对应行，则返回NULL。比如N=1时，第一名对应的值是是NUL，最后一名结果是倒数第2的值
首尾中FIRST_VALUE返回每个分组中第一名对应的字段（或表达式）的值，例如本文中可以是第一名的分数、学号等任意字段的值
LAST_VALUE返回每个分组中最后一名对应的字段（或表达式）的值，例如本文中可以是最后一名的分数、学号等任意字段的值
NTH_VALUE

返回每个分组中排名第N的对应字段（或表达式）的值，但小于N的行对应的值是NULL

MySQL中主要的窗口函数先总结这么多，建议还是得动手实践一番。另外，MySQL5.7及之前版本的排序方式的实现很多人已总结，也建议实操一番。

>>>>

近期活动

2020年，Gdevops全球敏捷运维峰会开启了传播前沿技术、助力技术人成长进阶、促进跨界交流合作的第五个年头。依托迄今成功举办的17场大会在分享议题上的精心打磨、在技术圈子里的口碑传播，Gdevops在展开新一年技术巡演中邀请到更顶级的嘉宾阵容，将带来更重磅的科技成果与独家实践。点击链接了解更多情况

2020 Gdevops全球敏捷运维峰会

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

3.  比hive快10倍的大数据查询利器-- presto

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫



码


关

注

gjc592
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL窗口函数及其分析查询
weixin_41540084的博客
09-06 1468
MySQL从版本8.0开始，支持窗口函数。窗口函数允许您以新的，更简单的方式解决查询问题，并具有更好的性能。 假设我们有一个sales表，按员工和财政年度存储销售额，如下所示： CREATE TABLE sales( sales_employee VARCHAR(50) NOT NULL, fiscal_year INT NOT NULL, sale DECIMAL(14,...
【一点分享】Mysql的窗口函数，分析功能从此上一个台阶。
或许能用上的博客
08-19 354
一、理解 Mysql8.0开始支持窗口函数，极大增强了Mysql的统计性能，再也不会被Oracle等鄙视了。Mysql8.0还增加了其他实用的高级性能，关注公众号后续文章进行了解。 1、使用场景 可从两个角度来了解和分辨需要使用窗口函数的场景。 （1）要查询的数据有n组性质相同的数据集（订单表可以按品类分成n组、按日期分成n组等），对每组组内的数据都需要做相同的统计计算。比如各品类的销售额前3名的商品分别是？比如各部门出勤率最好的3位员工是？ （2）符合以下5类窗口函数的代表的统计意义 排序函数
Mysql8.0窗口函数，统计报表so easy
hawk9821的博客
07-29 370
Mysql8.0支持窗口函数 习惯使用orale的程序猿们，mysql8.0支持窗口函数，使用mysql统计各种报表数据将不再麻烦。还未升级mysql的同学们，也可以升级尝试一下。 number() , rank() , dense_rank() CREATE TABLE numbers ( val INT ); INSERT INTO numbers(val) VALUES(1),(2),(2),(3),(4),(4),(5); SELECT val, ROW_NUMBER() OVE
mysql 占比函数_MySQL数分：窗口函数
weixin_39531229的博客
12-20 953
本次分享内容为SQL的窗口函数，内容主要有以下几点：什么是窗口函数如何使用窗口函数聚合函数作为窗口函数窗口函数的移动平均什么是窗口函数窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数， 使用的窗口函数不会导致行被分组到一个单一的输出行；行保留其独立的身份。 在后台，窗口函数能够访问的不止查询结果的当前行。很拗口，我们举个例子，先新建一个...
MYSQL窗口函数
Annabel_CM的博客
07-18 6307
mysql窗口函数从应用场景分析及用法
Excel数据整理与分析以一敌十的SUBTOTAL函数共9
11-19
这些编号对应不同的统计功能，如： - 1（AVERAGE）：平均值 - 2（COUNT）：计数（不包括错误值和逻辑值） - 3（MAX）：最大值 - 4（MIN）：最小值 - 6（PRODUCT）：乘积 - 9（STDEV.P）：基于整个数据集的...
从零开始学Python AI开发系列53-数据统计分析篇-Numpy数组1
08-08
统计分析方面，Numpy提供了`max`和`min`函数来获取一维数组中的最大和最小值，而`argmax`和`argmin`则返回这些值的索引位置。`shape`函数可获取数组的形状信息，如一维数组的长度或矩阵的行数和列数。`dtype`函数则...
外企Excel应用.[函数.Part.I].pdf
11-14
Excel是Microsoft公司开发的一款功能强大的电子表格处理软件，广泛应用于数据处理、统计分析、财务计算等领域。它自1987年第一版发行以来，随着计算机技术的进步，版本不断更新迭代，提供了越来越丰富的功能，而函数...
：探索MATLAB函数库：发现强大功能，事半功倍
![：探索MATLAB函数库：发现强大功能，事半功倍](https://img-blog.csdnimg.cn/20191217112733986.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9...这些函数经过优化，可以
EXCEL函数公式ppt课件.ppt
11-19
总的来说，Excel 2007通过其丰富的功能和改进的界面，极大地提升了用户处理和分析数据的能力，无论是基础的数据管理还是复杂的统计分析，都能游刃有余。掌握这些知识点，将使你在数据处理工作中事半功倍。
MySQL-函数
Ego_Ekko的博客
09-29 514
mysql函数
没有窗口函数，你能很快做出这道MySQL面试题吗？
数据分析与统计学之美
04-25 2745
  这是另外一个面试题目，相对难说有点难度，由于MySQL8.0以前没有“窗口函数”，当你碰到了这个题目，你是否能够很快写出这个答案来呢？ 请使用上述数据，完成如下效果： 建表语句 create table mianshi1 ( id varchar(20), dates varchar(20), v_num int ) charset = utf8; 插入...
Hive窗口函数详解——及3套案例
肥宅小谢的博客
06-18 1158
前言：要先了解窗口函数的结构，over（）才是窗口函数，而avg()、sum()、max()、min()等是与over（）分析函数 一，hive窗口函数语法 首先，avg()、sum()、max()、min()等是分析函数，而over()才是窗口函数 ...
SQL篇-常用窗口函数
qq_27093831的博客
05-09 5705
前言 在我们日常数据分析中，常常遇到要在组内排名、组内取topn等等需求，例如下面的业务需求： 排名问题：每个品牌的商品按销售额来排名 topN问题：找出每个品牌排名前N的商品 面对这类需求，就需要使用sql的高级功能窗口函数了。窗口函数是个神奇的存在，可以把多行代码变一行处理，把复杂的子查询变简单。让我们来一起了解窗口函数吧！ 1、什么是窗口函数 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），作用于一个数据集合。窗口函数的一个概念就是当前
presto结合grouping、grouping set实现统计宽表
qq_36800511的博客
11-21 496
presto统计宽表
MySQL窗口函数
ZJH
11-27 3774
个人认为跟上面的3.1滑窗筛选单位ROWS和RANGE属于一个包含关系，单位可以选择ROWS行、RANGE范围，都可以筛选一定的范围内，但比较死板，不够灵活，3.2就是这个升级版，足够灵活CURRENT ROW ：当前行UNBOUNDED PRECEDING ：当前行上侧所有行UNBOUNDED FOLLOWING ：当前行下侧所有行expr PRECEDING：当前行上侧expr行(expr可以是数字，也可以是表达式)
MySql窗口函数详解-超级细
最新发布
啊哈的博客
11-30 1465
mysql窗口函数详解 ROW_NUMBER()OVER() RANK()OVER() DENSE_RANK()OVER() PERCENT_RANK()OVER() NTH_VALUE()OVER() MAX()OVER() MIN()OVER() AVG()OVER() SUM()OVER() COUNT()OVER() CUME_DIST()OVER() FIRST_VALUE()OVER()、LAST_VALUE()OVER() LAG()OVER() LEAD()OVER()
R语言统计计算函数全解析
这些函数涵盖了数据处理、统计分析和图形绘制等基本功能。 - 例如，`c()`用于连接或合并对象，`seq()`用于生成序列，`mean()`计算平均值，`sd()`计算标准差，`summary()`提供数据集的基本统计信息。 2. **数据类型...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值