MySQL8.0窗口函数入门实践及总结_Mysql

来源:脚本之家  责任编辑:小易  

看你提示是mysql安装路径下的data文件夹已经存在了,你把mysql服务关了,net stop mysql然后把data文件夹删除,重新运行命令net start mysqlmysqld-initialize执行完成之后,会打印 root 用户的初始默认密码,在执行输出结果里面有一段:[Note][MY-010454][Server]A temporary password is generated for root@localhost:fwefhwi你需要记住这个密码,登录之后可以修改www.zgxue.com防采集请勿采集本网。

前言

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

打开两个图档,点窗口\\层叠;然后点窗口,分别点图档,进行图档切换,算不算,好像没有其他的了;

1、准备工作

主要是默认端口相同,他们会冲突,如果不调整,只能启动一个服务。另外,安装的时候要注意不同版本的应用(EXE文件)位置、数据位置(DATA文件夹)、配置文件(MY.CNF、MY.INI)、以及驱动代码

创建表及测试数据

网页链接 配置环境变量(目的是为了避免在CMD窗口下操作时反复切换路径) 在Path下添加 D:\\Program Files\\mysql-8.0.11-winx64\\bin 注意:data文件夹是初始化数据库之后才有的,my-default.ini

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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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

(5)净出口函数从nx=50-0.05y变为nx=40-0.05y时的均衡收入: y=c+i+g+nx=0.8y-10+60+50+40-0.05y=0.75y+140 解得y=560,即均衡收入为560。净出口余额:nx=40-0.05y=40-0.05×560=40

2、统计每门课程分数的排名

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

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)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)

3、窗口函数小结

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及之前版本的排序方式的实现很多人已总结,也建议实操一番。

总结

到此这篇关于MySQL8.0窗口函数入门实践及总结的文章就介绍到这了,更多相关MySQL8.0窗口函数实践内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

$mysqli->query("SELECT answer FROM FAQ WHEREquestion="hello"")这句里面出错了,修改的办法有2种:第一把双引号内部的双引号转义:1mysqli->query("SELECT answer FROM FAQ WHEREquestion=\\"hello\\"");第二种把双引号变成单引号:1mysqli->query("SELECT answer FROM FAQ WHEREquestion='hello'");内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql8.0使用窗口函数解决排序问题
  • 多次执行mysql_fetch_array()的指针归位问题探讨
  • mysql学习第四天 windows 64位系统下使用mysql
  • 解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)
  • 基于mysql的sequence实现方法
  • 解决mysql server has gone away错误的方案
  • mysql的时间差函数timestampdiff、datediff的用法
  • mysql优化取随机数据慢的方法
  • mac下mysql 5.7.13 安装配置方法图文教程
  • mysql日志滚动
  • 开源mysql高效数据仓库解决方案:infobright详细介绍
  • mysql8.0中使用sql查询语句会报错,这是为什么?
  • 请教MySQL高手一个“MySQL8.0.11安装过程”中的报错解决方案是什么?
  • mysql 已经升级到8.0.15了,为什么使用group by加cube函数还是出错?
  • matlab 8.0 editor窗口如何放入主窗口(和command window一起)之中?
  • ug8.0怎么实现多个窗口啊,不是布局
  • 为什么安装了mysql80mysql5.0不能用了?
  • win10装MySQL8.0卡在starting the sever是什么原因造成的?如何解决?
  • 假定某经济社会的消费函数c=30+0.8y,净税收即总税收减去转移支付后的金额t=50,投资i=60
  • 会SQL Server 如何快速的掌握MYSQL 8.0 ,他们之间有什么不通
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql8.0使用窗口函数解决排序问题多次执行mysql_fetch_array()的指针归位问题探讨mysql学习第四天 windows 64位系统下使用mysql解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)基于mysql的sequence实现方法解决mysql server has gone away错误的方案mysql的时间差函数timestampdiff、datediff的用法mysql优化取随机数据慢的方法mac下mysql 5.7.13 安装配置方法图文教程mysql日志滚动开源mysql高效数据仓库解决方案:infobright详细介绍mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql——修改root密码的4种方法mysql日期数据类型、时间类型使用mysql update语句的用法详解mysql 的case when 语句使用说明如何将excel文件导入mysql数据库mysql性能优化案例 - 覆盖索引分享win7下mysql5.5安装图文教程基于mysql数据库的数据约束实例及五种完整什么是blob,mysql blob大小配置介绍mysql中如何使用正则表达式查询通过hsodbc访问mysql的实现步骤win10安装mysql5.7.18winx64 启动服务器失mysql的case when语句的几个使用实例mysql 8.0.11安装教程图文解说
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved