正规的彩票

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

 更新时间: 2020年06月26日 11:42:40   转载 作者: 懂点IT的耿小厨  
这篇文章主要给大家介绍了关于MySQL8.0窗口函数入门实践及总结的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者使用MySQL8.0具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧

前言

mysql8.0zhiqian,zuoshujupaimingtongjidengxiangdangtongku,yinweimeiyouxiangoracle、sql server 、postgresqldengqitashujukuneiyangdechuangkouhanshu。dansuizhemysql8.0zhongxinzenglechuangkouhanshuzhihou,zhenduizheileitongjijiuzaiyebushishile,benwenjiuyichangyongdepaixushilijieshaomysqldechuangkouhanshu。

1、准备工作

chuangjianbiaojiceshishuju

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

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

genjumeimenkechengdefenshuconggaodaodijinxingpaiming,cishi,huichuxianfenshuxiangtongshizenmechulidewenti,xiamianjiugenjubutongdechuangkouhanshulaichulibutongchangjingdexuqiu

ROW_NUMBER

youjieguokeyikanchu,fenshuxiangtongshianzhaoxuehaoshunxujinxingpaiming

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

weilerangfenshuxiangtongshipaimingyexiangtong,zekeyishiyongdense_rankhanshu,jieguoruxia:

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_rankdejieguoshifenshuxiangtongshipaimingxiangtongle,danshixiayigemingcishijinjiezheshangyigemingcide,ruguo2gebingliededi1zhihou,xiayigezhengguidecaipiaoxiangshidi3ming,zekeyishiyongrankhanshushixian

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)

zheiyangjiushixianlegezhongpaixuxuqiu。

NTILE

ntilehanshudezuoyongshiduimeigefenzupaiminghou,zaijiangduiyingfenzufenchengngexiaozu,liru

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、窗口函数小结

正规的彩票mysqlzhonghaiyouxuduoqitadechuangkouhanshu,benwenliejuyixie,dajiakeyizixingceshi

类别 函数 说明
排序 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

fanhuimeigefenzuzhongpaimingdindeduiyingziduan(huobiaodashi)dezhi,danxiaoyundexingduiyingdezhishinull

正规的彩票mysqlzhongzhuyaodechuangkouhanshuxianzongjiezheimeduo,jianyihaishidedongshoushijianyifan。lingwai,mysql5.7jizhiqianbanbendepaixufangshideshixianhenduorenyizongjie,yejianyishicaoyifan。

总结

正规的彩票daocizheipianguanyumysql8.0chuangkouhanshurumenshijianjizongjiedewenzhangjiujieshaodaozheile,gengduoxiangguanmysql8.0chuangkouhanshushijianneirongqingsousuojiaobenzhijiayiqiandewenzhanghuojixuliulanxiamiandezhengguidecaipiaoxiangguandewenzhangxiwangdajiayihouduoduozhichijiaobenzhijia!

您可能感兴趣的文章:

正规的彩票相关的文章

  • 多次执行mysql_fetch_array()的指针归位问题探讨

    多次执行mysql_fetch_array()的指针归位问题探讨

    多次执行mysql_fetch_array(),在第二次执行的时候,如果不加处理,就不会输出任何内容,这种情况下只需要对循环指针进行复位即可,感兴趣的朋友可以了解下啊,或许对你有所帮助
    2012-01-01
  • MySQL学习第四天 Windows 64位系统下使用MySQL

    MySQL学习第四天 Windows 64位系统下使用MySQL

    MySQL学习第四天教大家如何在Windows 64位下使用MySQL,即使用命令行方式完成操作MySQL正规的彩票,感兴趣的小伙伴们可以参考一下
    2015-05-05
  • 解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)

    解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)

    有次同事提出开发使用的mysql数据库连接很慢,因为正规的彩票的mysql开发数据库是单独一台机器部署的,所以认为可能是网络连接问题导致的。
    2010-07-07
  • 基于Mysql的Sequence实现方法

    基于Mysql的Sequence实现方法

    下面小编就为大家带来一篇基于Mysql的Sequence实现方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
    2016-09-09
  • 解决MySQL server has gone away错误的方案

    解决MySQL server has gone away错误的方案

    在本篇文章里小编给大家分享的是一篇关于MySQL server has gone away错误的解决办法,有需要的朋友们可以参考下。
    2019-02-02
  • MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法

    MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法

    这篇文章主要介绍了MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
    2018-12-12
  • mysql优化取随机数据慢的方法

    mysql优化取随机数据慢的方法

    mysql取随机数据慢,怎么办?下面小编与大家一起来看看mysql取随机数据慢优化的过程。
    2012-11-11
  • Mac下mysql 5.7.13 安装配置方法图文教程

    Mac下mysql 5.7.13 安装配置方法图文教程

    这篇文章主要介绍了Mac下mysql 5.7.13 安装配置方法图文教程,内容很详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下
    2016-03-03
  • mysql日志滚动

    mysql日志滚动

    日志滚动解决日志文件过大问题,比如正规的彩票开启了general_log,这个日志呢是记录mysql正规的彩票器上面所运行的所有sql语句;比如正规的彩票开启了mysql的慢查询
    2013-01-01
  • 开源MySQL高效数据仓库解决方案:
Infobright详细介绍

    开源MySQL高效数据仓库解决方案: Infobright详细介绍

    这篇文章主要介绍了开源MySQL高效数据仓库解决方案: Infobright详细介绍,本文讲解了Infobright特征、Infobright的价值、Infobright的适用场景、与MySQL对比等内容,需要的朋友可以参考下
    2014-03-03

最新评论

正规的彩票_正规彩票平台app下载[首页最快线路√] 彩票官网_彩票官网app|网站首页 彩票平台_彩票平台快三-[平台最快线路√] 彩票app下载_彩票app下载官网下载快三_官方入口 彩票平台_彩票平台app-专注彩票门户 彩票官网_彩票官网app下载网址-HOME 彩票平台_彩票平台登录-老品牌最信誉 彩票app_彩票app客户端下载>> 首页 彩票平台_彩票平台免费送彩金18-点击进入>! 彩票平台_彩票平台官网〖2020购彩首选〗 彩票app_彩票app彩票投注平台-Welcome