首页 > 问答 > 关键词  > mysql最新资讯  > 正文

MySQL8.0窗口函数做数据排名统计详细教程

2020-06-29 09:06 · 稿源:数据库干货铺

MySQL8.0新增了窗口函数,大大的方便了做数据排名统计的人,很多朋友还不清楚怎么用MySQL8.0做数据统计排名,下面就来为大家分享一篇心得文章。

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

1、准备工作

创建表及测试数据

mysql>usetestdb;Databasechanged/*创建表*/mysql>createtabletb_score(idintprimarykeyauto_increment,stu_novarchar(10),coursevarchar(50),scoredecimal(4,1),keyidx_stuNo_course(stu_no,course));
QueryOK,0rowsaffected(0.03sec)

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

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

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

ROW_NUMBER

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

mysql>selectstu_no,course,score,row_number()over(partitionbycourseorderbyscoredesc)rn->fromtb_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|+---------+---------+-------+----+36rowsinset(0.00sec)

DENSE_RANK

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

mysql>selectstu_no,course,score,DENSE_RANK()over(partitionbycourseorderbyscoredesc)rn
->fromtb_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|+---------+---------+-------+----+36rowsinset(0.00sec)

RANK

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

mysql>selectstu_no,course,score,rank()over(partitionbycourseorderbyscoredesc)rn
->fromtb_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|+---------+---------+-------+----+36rowsinset(0.01sec)

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

NTILE

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

mysql>selectstu_no,course,score,rank()over(partitionbycourseorderbyscoredesc)rn,NTILE(2)over(partitionbycourseorderbyscoredesc)rn_groupfromtb_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|+---------+---------+-------+----+----------+36rowsinset(0.01sec)

3、窗口函数小结

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

本文转载自微信公众号【数据库干货铺】

举报

  • 相关推荐
  • 大家在看
  • ResumaidPro:定制化简历构建工具,AI辅助,提升求职效率。

    ResumaidPro是一款利用人工智能技术帮助求职者快速定制化简历的在线工具。它通过浏览器插件形式,允许用户在浏览器标签页内直接定制简历,优化简历内容以通过自动应聘筛选系统(ATS)。它使得简历定制化变得简单快捷,节省了求职者宝贵的时间,同时提高了求职成功的几率。产品背景是通过利用技术改善简历编写过程,以适应快速变化的就业市场。产品提供了不同套餐,包括按月或按季度计费,并且提供了免费试用。

  • CheckVisaSlots:实时监测美国签证预约空位

    CheckVisaSlots是一个专注于帮助学生和旅行者监测美国签证预约空位的在线服务。它通过提供实时的签证预约信息,帮助用户及时了解签证预约的可用情况,从而更好地规划他们的签证申请流程。该服务特别适合那些需要及时获取签证信息以安排旅行或教育计划的用户。

  • 星界AI:一站式AI图文创作神器

    星界AI是一款集成了多种AI大模型的图文创作神器,包括ChatGPT、谷歌Gemini、百度文心一言等,提供文案创作、知识问答、教育辅导、灵感启发等功能,以提高工作效率和创造力。

  • 神笔AIPPT系统:一键生成海量模板,支持SaaS多开的全开源系统

    神笔AIPPT系统是一款支持一键生成和海量模板的在线建站工具,采用SaaS模式,允许无限多开。该系统基于thinkphp 6.1 + mysql 5.7 + vue3 + vite5技术栈开发,完全开源,支持定制二次开发,适用于需要快速搭建个性化网站的用户。产品原价2888元,提供详细的安装部署文档和参数配置文档,以及使用过程中的免费指导服务。

  • Vozo:AI视频生成器,一键重写和配音视频。

    Vozo是一个AI视频生成器,它允许用户通过AI提示重写视频脚本、自动配音和唇形同步,从而快速生成新的视频内容。它支持将视频翻译成多种语言,并且可以针对不同的受众群体定制视频内容。Vozo的主要优点包括易用性、高效性以及能够保持原有视频的魅力。

  • 扣子专业版:企业级 AI 开发平台,无需编程技能,快速搭建个性化应用。

    扣子专业版是一款企业级 AI 应用开发平台,旨在帮助用户快速、低门槛地构建个性化的 AI 应用,支持无编程技能的用户使用。该平台拥有 1 万 + 插件的丰富生态,能够构建功能强大的大模型应用,同时支持数据私有化及团队协作,适合各类企业需求。定价灵活,能够满足不同规模的企业使用需求,是推动企业数字化转型的重要工具。

  • Gobi:个性化情绪健康指导APP

    Gobi是一款致力于提供个性化情绪健康指导的应用程序。它由一群对AI充满热情的团队开发,团队成员来自Google、Microsoft、Meta和Scale AI等知名公司。Gobi通过实时生成基于科学的语音指导健康实践,帮助用户管理情绪健康。产品的主要优点包括全天候的人性化健康支持、适应性AI个性化、实时健康实践生成、日常检查和情绪健康跟踪等。

  • DiT-MoE:大规模参数扩散变换器模型

    DiT-MoE是一个使用PyTorch实现的扩散变换器模型,能够扩展到160亿参数,与密集网络竞争的同时展现出高度优化的推理能力。它代表了深度学习领域在处理大规模数据集时的前沿技术,具有重要的研究和应用价值。

  • Picogen AI Image API:AI图像生成API,提供高质量的4K图像生成和编辑功能。

    Picogen AI Image API是一个领先的AI图像生成平台,提供与Midjourney, Stable Diffusion和DALL-E相媲美的高质量图像生成服务。它支持生成高达4K分辨率的图像,并且具备图像合并、背景移除和8K分辨率的图像放大等高级功能。Picogen旨在为数字营销人员、平面设计师、内容创作者等专业人士提供强大的视觉内容创作工具。

  • Colocio AI:一站式在线营销工具,由AI驱动,简化广告和内容创作。

    Colocio AI是一款集成了人工智能技术的在线营销工具,旨在帮助用户创建、评估和自动化在线广告活动。它通过AI技术生成高转化率的广告文案和社交媒体内容,提供无障碍的内容调度功能,以及实时的AI辅助和数据驱动的报告,帮助用户优化广告活动,提高性能。Colocio AI的背景是简化内容和媒体创作流程,专注于为品牌和目标受众量身定制内容,同时释放用户时间,让他们专注于战略规划和与受众互动,推动业务创新和增长。

  • Supermemory.ai:你的个人数字第二大脑

    Supermemory是一个致力于帮助用户组织、搜索和利用保存信息的平台。它提供强大的工具,如搜索引擎、写作助手和画布,旨在成为用户的个人第二大脑。它注重隐私保护,可在任何地方工作,支持自托管,并且价格亲民,提供免费层级。

  • Dialed:个性化AI激励演讲,激发潜能。

    Dialed是一款AI驱动的应用程序,旨在通过个性化的激励演讲来激发用户的潜能。无论是健身、演讲还是日常挑战,Dialed都能提供个性化的激励和启发。产品的主要优点包括个性化的激励演讲、真诚的支持、传奇的演讲定制、多种声音选择、背景音乐以及AI生成的激励图像。此外,Dialed还允许用户将激励演讲分享给朋友和家人。

  • Aphrodite-engine:PygmalionAI的大规模推理引擎

    Aphrodite是PygmalionAI的官方后端引擎,旨在为PygmalionAI网站提供推理端点,并允许以极快的速度为大量用户提供Pygmalion模型服务。Aphrodite利用vLLM的分页注意力技术,实现了连续批处理、高效的键值管理、优化的CUDA内核等特性,支持多种量化方案,以提高推理性能。

  • ICSFSurvey:深入研究大型语言模型的内部一致性和自我反馈

    ICSFSurvey是一个关于大型语言模型内部一致性和自我反馈的调查研究。它提供了对LLMs自我评估和自我更新机制的统一视角,包括理论框架、系统分类、评估方法、未来研究方向等。

  • Stable Audio Open demo:从文本提示生成立体声音频

    Stable Audio Open 是一个能够从文本提示生成长达47秒的立体声音频的技术。它包含三个主要组件:一个将波形压缩到可管理序列长度的自编码器、一个基于T5的文本嵌入用于文本条件、以及一个在自编码器的潜在空间中操作的基于变换的扩散(DiT)模型。该技术在生成音频方面表现出色,能够根据文本提示生成各种类型的音频,如打击乐、电子音乐、自然声音等。

  • Claude Engineer:利用Anthropic的Claude模型辅助软件开发任务的高级命令行界面。

    Claude Engineer是一个高级的命令行界面,它利用Anthropic的Claude 3和Claude 3.5模型的能力,来协助完成广泛的软件开发任务。这个工具无缝地结合了最先进大型语言模型的能力与实用的文件系统操作、网页搜索功能、智能代码分析和执行能力。

  • Aiuni:探索3D虚拟世界,体验梦想家宇宙。

    Aiuni是一个提供3D虚拟世界体验的平台,用户可以在这里创建和探索个性化的3D模型,享受沉浸式的宇宙探索之旅。Aiuni以其创新的3D技术、丰富的互动性和高度的个性化定制,为用户提供了一个全新的虚拟体验空间。

  • Morphic.com:创新故事讲述的未来,使用突破性技术。

    Morphic Studio是一个利用先进人工智能技术,为创意产业带来革命性变化的在线平台。它提供了从构思到最终故事的全面工具,包括画布(Canvas)和视频编辑器,以及Morphic Playground,允许用户通过简单的消息创建资产。Morphic Studio旨在为创意专业人士,如游戏设计师和电影制作人,提供工具,以减少产品上市时间,并提高创作效率。

  • RapidOCR:快速、多语言支持的OCR工具包

    RapidOCR是一个基于ONNXRuntime、OpenVINO和PaddlePaddle的OCR多语言工具包。它将PaddleOCR模型转换为ONNX格式,支持Python/C++/Java/C#等多平台部署,具有快速、轻量级、智能的特点,并解决了PaddleOCR内存泄露的问题。

  • 百度智金・金融智能体:依托大模型技术,助力金融智能化转型,降本增效。

    智金・金融智能体是基于百度领先的大模型技术,结合多年的金融行业知识,专为金融从业人员设计的智能化解决方案。该产品通过智能化的咨询、交易引导和报告生成等功能,旨在提升金融业务的效率与合规性,帮助用户在信贷、投资、监管和保险等多个场景实现数字化转型。定位为金融行业的智能助手,智金・金融智能体通过 AI 技术为客户提供了高效、准确的支持,降低了人力成本并提高了服务质量。其背景源自百度在 AI 领域的深厚积累,价格策略灵活,具体情况可咨询客服。

今日大家都在搜的词: