MySQL 分组后统计 TopN 思路优化

一、表信息

表结构如下:

CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`score` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1746687 DEFAULT CHARSET=utf8;

使用存储过程生成十万条测试数据,该脚本主要创建 100name,每个 name 生成 1000 条不重复的 score 数据:

CREATE PROCEDURE `generate_score_data`()
BEGINDECLARE i INT DEFAULT 0;DECLARE j INT DEFAULT 0;DECLARE name VARCHAR(255);DECLARE score INT;DECLARE score_set VARCHAR(10000) DEFAULT '';WHILE i < 100 DOSET name = CONCAT('name', i);SET j = 0;SET score_set = '';WHILE j < 1000 DOREPEATSET score = FLOOR(RAND() * 5001);UNTIL NOT FIND_IN_SET(score, score_set)END REPEAT;SET score_set = CONCAT(score_set, ',', score);INSERT INTO score (name, score) VALUES (name, score);SET j = j + 1;END WHILE;SET i = i + 1;END WHILE;
END

执行存储过程:

CALL generate_score_data();

在这里插入图片描述

需求:取出每个 namescoretop3 数据出来。

二、TopN 实现思路

2.1 子查询的方式

子查询的方式是最容易想到的也是效率最差的一种方式,也是网上普遍写方式,通过构造一个子查询,在子查询中判断相同 name 下的当前的 score 大于主 score 的数量,如果小于3 则肯定是位于 top3 的数据。

实现如下:

SELECTs1.*
FROMscore s1 
WHERE( SELECT count(*) FROM score s2 WHERE s1.`name` = s2.`name` AND s2.score > s1.score )< 3 
ORDER BYs1.id ASC

运行结果:

在这里插入图片描述

从结果中可以看到花费了 22.66s ,效率着实不高。

2.2 通过 ROW_NUMBER 优化(推荐)

使用窗口函数 ROW_NUMBER() 对每个姓名进行分组,并按照成绩降序进行排序。然后,在外部包装一层选择具有行号小于等于3的记录,这样就可以得到每个组的 top 3 记录。

实现如下:

SELECTs.id,s.`name`,s.score 
FROM( SELECT id, `name`, score, ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY score DESC ) AS row_num FROM score ) AS s 
WHEREs.row_num <= 3
ORDER BYs.id ASC

运行结果:

在这里插入图片描述

可以看出使用该方式,仅 0.222s 就查出了数据。

2.3 通过 RANK() 优化

实现如下:

SELECTs.`name`,s.score 
FROM( SELECT id, `name`, score, RANK() OVER ( PARTITION BY NAME ORDER BY score DESC ) AS rank_num FROM score ) AS s 
WHEREs.rank_num <= 3
ORDER BYs.id ASC

在这个查询中,将 ROW_NUMBER() 函数更改为 RANK() 函数。RANK() 函数在计算排名时会跳过平级项并产生相同的排名值。例如,如果有两个人的成绩都是第一名,它们的排名值都是1

这种方式可以确保在并列排名的情况下,多个人都能被包含在 top 3 中。然而,如果有并列排名的记录超过了 top 3,它们可能会导致结果集超出预期的记录数,因此使用的时候需要注意是否合适。

运行结果:

在这里插入图片描述

从结果上可以看出比 ROW_NUMBER() 快了仅 0.002s

2.4 通过变量的方式

实现如下:

SELECTt.id,t.`name`,t.score 
FROM(SELECTs.*,@rn :=IF(@NAME = s.NAME,@rn + 1,IF( @NAME := NAME, 1, 1 )) AS row_num FROMscore sCROSS JOIN ( SELECT @rn := 0, @NAME := '' ) AS vars ORDER BYs.NAME,s.score DESC ) AS t 
WHEREt.row_num <= 3
ORDER BYt.id ASC

在这个查询中,使用了两个 MySQL 变量 @name@rn 来跟踪当前分组和每个分组中的行号。在内部查询中,对表进行排序,并使用 CROSS JOIN 子句创建了一个包含两个变量的虚拟表。然后,使用 IF() 函数将变量与当前行的姓名进行比较,以确定分组和行号。

这种方法需要对每行都进行比较,因此在大型数据集上可能会更慢,但在分组数较少且每组记录数较多的情况下,它可以实现更快的查询速度。

运行结果:

在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/179325.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【深度学习】pytorch——Tensor(张量)详解

笔记为自我总结整理的学习笔记&#xff0c;若有错误欢迎指出哟~ pytorch——Tensor 简介创建Tensortorch.Tensor( )和torch.tensor( )的区别torch.Tensor( )torch.tensor( ) tensor可以是一个数&#xff08;标量&#xff09;、一维数组&#xff08;向量&#xff09;、二维数组&…

2023-macOS下安装anaconda,终端自动会出现(base)字样,如何取消

2023-macOS下安装anaconda&#xff0c;终端自动会出现(base)字样&#xff0c;如何取消 安装后&#xff0c;我们再打开终端&#xff0c;就会自动出现了&#xff08;base&#xff09; 就会出现这样子的&#xff0c;让人头大&#xff0c; 所以我们要解决它 具体原因是 安装了anac…

DDoS类型攻击对企业造成的危害

超级科技实验室的一项研究发现&#xff0c;每十家企业中&#xff0c;有四家(39%)企业没有做好准备应对DDoS攻击&#xff0c;保护自身安全。且不了解应对这类攻击最有效的保护手段是什么。 由于缺乏相关安全知识和保护&#xff0c;使得企业面临巨大的风险。 当黑客发动DDoS攻击…

Spring Boot:构建下一代Java应用的利器

文章目录 什么是Spring Boot&#xff1f;Spring Boot的主要特性1. 自动配置2. 独立性3. 微服务支持4. 生态系统5. Spring生态系统集成 Spring Boot的优势1. 提高开发效率2. 减少样板代码3. 更好的部署和管理4. 多种部署选项5. 微服务支持 如何开始使用Spring Boot1. 安装Spring…

飞致云及其旗下1Panel项目进入2023年第三季度最具成长性开源初创榜单

2023年10月26日&#xff0c;知名风险投资机构Runa Capital发布2023年第三季度ROSS指数&#xff08;Runa Open Source Startup Index&#xff09;。ROSS指数按季度汇总并公布在代码托管平台GitHub上年化增长率&#xff08;AGR&#xff09;排名前二十位的开源初创公司和开源项目。…

Spring Boot Actuator 漏洞利用

文章目录 前言敏感信息泄露env 泄露配置信息trace 泄露用户请求信息mappings 泄露路由信息heapdump泄露堆栈信息 前言 spring对应两个版本&#xff0c;分别是Spring Boot 2.x和Spring Boot 1.x&#xff0c;因此后面漏洞利用的payload也会有所不同 敏感信息泄露 env 泄露配置信…

开发小程序需要多少钱?

随着移动互联网的快速发展&#xff0c;小程序已经成为了企业、个人创业者获取用户、提升品牌影响力的重要工具。然而&#xff0c;对于许多初次接触小程序的人来说&#xff0c;开发小程序需要多少钱&#xff0c;是他们最关心的问题。 首先我们需要明确的是&#xff0c;开发小程…

图论08-图的建模-状态的表达与理解 - 倒水问题为例

文章目录 状态的表达例题1题解1 终止条件&#xff1a;有一个数位为42 状态的改变&#xff1a;a表示十位数&#xff0c;b表示个位数3 其他设置 例题2 力扣773 滑动谜题JavaC 状态的表达 例题1 从初始的(x&#xff0c;y)状态&#xff0c;到最后变成&#xff08;4&#xff0c;&am…

SOLIDWORKS 2024新功能--SOLIDWORKS Electrical篇

SOLIDWORKS Electrical 对齐零部件 在设计 3D 机柜布局时使用对齐零部件时&#xff0c;可以在图形区域中预览更改。这大大减少了在 3D 机柜布局中对齐 SOLIDWORKS 零部件所需的工作量。对齐零部件 PropertyManager 简化并改进了工作流程。 SOLIDWORKS Electrical 更改多个导…

跨境电商须知| 独立站的特点与痛点有哪些?

独立站的特点与痛点有哪些&#xff1f; 无论是做独立站&#xff0c;还是做亚马逊&#xff0c;都有各自的难点。自己做独立站若要在跨境行业长足发展&#xff0c;既要知道独立站有什么特点&#xff0c;要清楚独立站的痛点并一一克服。了解独立站搭建更多 一、独立站的特点 1、…

全球最杰出的大神程序员们(14位)

一、全球杰出的程序员介绍 一起来认识一下全球最杰出的大神程序员们。可惜没看到国人的面孔&#xff01;&#xff08;排名不分先后&#xff09; 1、Jon Skeet 个人名望&#xff1a;程序技术问答网站 Stack Overflow 总排名第一的大神&#xff0c;每月的问答量保持在 425 个左…

uniapp 编译到模拟器(mumu)

一开始我是用逍遥模拟器&#xff0c;但这个玩意突然不好使了&#xff0c;一直加载卡在这页面 1、下载 官网下载&#xff1a;mumu模拟器12 2、打开mumu多开器&#xff0c;在右上角adb查看端口号 3、打开mumu模拟器 4、打开HBuiderX 工具—设置—运行配置 5、配置电脑的系统…

Selenium安装WebDriver Chrome驱动(含 116/117/118/119/120/)

1、确认浏览器的版本 在浏览器的地址栏&#xff0c;输入chrome://version/&#xff0c;回车后即可查看到对应版本 2、找到对应的chromedriver版本 2.1 114及之前的版本可以通过点击下载chromedriver,根据版本号&#xff08;只看大版本&#xff09;下载对应文件 2.2 116版本…

【前端笔记】ant-design-vue 3.x使用modal.method()自定义content内容小记

在一次编写业务代码时&#xff0c;碰到了一种既想要Modal.success样式&#xff0c;有想要定制其content内容的情况。 大部分情况下&#xff0c;使用Modal.method()这种方式时&#xff0c;可能content内容固定都是字符串&#xff0c;那如果想要做更高级的交互怎么办&#xff1f…

回溯算法(2)--图着色问题和旅行商问题

目录 一、图着色问题 1、算法设计 2、代码 二、旅行商问题 1、概述问题 2、穷举法 3、回溯法 一、图着色问题 1、算法设计 图着色问题&#xff0c;给定图中各个区域的相邻关系&#xff0c;抽象成一个无向图G&#xff08;V,E&#xff09;&#xff0c;给定m种颜色&…

Hadoop PseudoDistributed Mode 伪分布式

Hadoop PseudoDistributed Mode 伪分布式加粗样式 hadoop101hadoop102hadoop103192.168.171.101192.168.171.102192.168.171.103namenodesecondary namenoderecource managerdatanodedatanodedatanodenodemanagernodemanagernodemanagerjob historyjob logjob logjob log 1. …

基于人工势场法的航线规划

GitHub - zzuwz/Artificial-Potential-Field: 2D平面下的人工势场法 GitHub - mellody11/Artificial-Potential-Field: 机器人导航--人工势场法及其改进 matlab2020a可以运行

Selenium元素定位之页面检测技巧

在进行web自动化测试的时候进行XPath或者CSS定位&#xff0c;需要检测页面元素定位是否正确&#xff0c;如果用脚本去检测&#xff0c;那么效率是极低的。 一般网上推选装额外的插件来实现页面元素定位检测 如&#xff1a;firebug。 其实F12开发者工具就能直接在页面上检测元…

linux上重启mysql

1、先关闭 [rootHIS bin]# ./mysqladmin -h 127.0.0.1 -u root -p shutdown 2、 再重启 [rootHIS support-files]# ./mysql.server start

Android开发知识学习——Kotlin基础

函数声明 声明函数要用用 fun 关键字&#xff0c;就像声明类要用 class 关键字一样 「函数参数」的「参数类型」是在「参数名」的右边 函数的「返回值」在「函数参数」右边使用 : 分隔&#xff0c;没有返回值时可以省略 声明没有返回值的函数&#xff1a; fun main(){println…