草庐IT

记一次有意思的 SQL 实现 → 分组后取每组的第一条记录

青石路 2023-03-28 原文

开心一刻

　　今天，朋友气冲冲的走到我面前

　　朋友：我不是谈了个女朋友，谈了三个月嘛，昨天我偷看她手机，你猜她给我备注什么

　　我：备注什么？

　　朋友：舔狗 2 号！

　　我一听，气就上来了，说道：走，找她去，这婆娘确实该骂，臭不要脸的

　　朋友拉住我，劝到：哎哎，不是去骂她，是找她理论，叫她改成舔狗1号，是我先来的！

　　我：滚，我不认识你

需求背景

　　环境

　　 MySQL 版本：8.0.27

　　有四张表：业务信息表、任务表、业务任务表、任务执行日志表

CREATE TABLE `t_business` (
  `business_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '业务id',
  `business_name` VARCHAR(100) NOT NULL COMMENT '业务名',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '备注',
  `create_user` BIGINT(20) NOT NULL COMMENT '创建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最终修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最终修改时间',
  PRIMARY KEY (`business_id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务信息';

CREATE TABLE `t_task` (
  `task_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '任务id',
  `task_name` VARCHAR(100) NOT NULL COMMENT '业务名',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '备注',
  `create_user` BIGINT(20) NOT NULL COMMENT '创建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最终修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最终修改时间',
  PRIMARY KEY (`task_id`) USING BTREE
) ENGINE=InnoDB COMMENT='任务信息';

CREATE TABLE `t_business_task` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `business_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '业务id',
  `task_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '任务id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='业务任务关系';

CREATE TABLE `t_task_exec_log` (
  `log_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志id',
  `task_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '任务id',
  `exec_status` VARCHAR(50) NOT NULL COMMENT '执行状态, 失败：fail，成功：success',
    `data_date` DATE NOT NULL COMMENT '数据日期',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '备注',
  `create_user` BIGINT(20) NOT NULL COMMENT '创建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最终修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最终修改时间',
  PRIMARY KEY (`log_id`) USING BTREE
) ENGINE=InnoDB COMMENT='任务执行日志';
View Code

　　它们关系如下

　　一个业务下有多个任务，一个任务又可以属于不同的业务；同个业务下，一个任务最多关联一次

　　任务每执行一次就会生成一条执行日志；执行日志的数据日期  小于等于 任务执行的当前日期，比如昨天执行的任务的数据日期可以是前天的

　　四张表的数据量分别如下

　　需求

　　按业务分页，每个业务可以展开显示关联的任务信息以及任务最新的执行成功信息

　　任务最新的执行成功信息：状态成功，数据日期最大的那条执行日志信息；如果数据日期一致，则取最终修改时间最大的

　　后端返回的 JSON 数据类似如下

实现方式

　　先分页查业务和任务，再根据任务id循环查最新的执行成功信息

　　1、关联查询业务和任务

　　　　如果查询条件带任务信息（任务ID，任务名），那么 t_business 需要关联 t_business_task 、 t_task 来查

　　　　因为这三张表的数据量都比较小，联表查没什么问题

　　2、根据上一步查到的 task_id 集逐个去查 t_task_exec_log 

　　　　 SQL 类似如下

　　　　可以建个组合索引 idx_status_task_date_modify(exec_status,task_id,data_date,modify_time) 

　　3、将第 1、2 步的数据进行组合

　　　　将任务的最新执行成功信息添加到任务信息中

　　逻辑非常清晰，代码实现起来也非常简单

　　但是，一个任务id就查一次数据库，这显然是有很大性能问题的（一般，公司的开发规范内都会有一条：禁止循环查数据库）

　　先分页查业务和任务，再根据任务id批量查最新的执行成功信息

　　1、关联查询业务和任务

　　2、根据第 1 步查到的任务id集批量查 t_task_exec_log 

　　　　因为这是多个任务一起查，也就没法用 LIMIT 1 了

　　　　那如何查出每个任务的最新执行成功的那一条记录了？

　　　　这里也就对应了文章的标题：分组后取每组的第 1 条记录

　　　　实现方式其实有很多，我这里提供一种，如下

　　　　结合索引 idx_status_task_date_modify(exec_status,task_id,data_date,modify_time) ，查询速度还行

　　　　大家细看这个 SQL ，是不是发现了有意思的东西：GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC)

　　　　是不是知识盲区，是不是有点东西？

　　　　 GROUP_CONCAT 语法 GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR sep); 

　　3、将第 1、2 步的数据进行组合

　　新增任务最新执行成功记录表

　　一般而言，大数据量的日志表是不参与复杂查询的，所以单独拎出来一个表，专门记录任务最新执行成功信息

　　一个任务最多只有一条记录，不存在则直接插入表中，存在则根据 data_date DESC,modify_time DESC 与表中记录做比较，看是否需要进行表中记录更新

　　因为一个任务最多只有一条记录，那么 t_task_latest_exec_log 的数据量是 小于等于  t_task 的数据量的，也就是说数据量不大

　　那么用一个 SQL 就可以实现业务（直接联表 t_business 、 t_business_task 、 t_task 、 t_task_latest_exec_log ）

　　然后在后端代码中进行数据格式的处理，返回前端需要的格式

　　新增表后，其初始数据该如何导入了？

总结

　　1、大家写 SQL 的时候，一定要多结合执行计划来写

　　　　神奇的 SQL 之 MySQL 执行计划 → EXPLAIN，让我们了解 SQL 的执行过程！

　　2、 t_task_latest_exec_log 初始数据的导入

　　　　其实比较简单， 如下所示

INSERT INTO t_task_latest_exec_log(task_id,data_date,create_user,create_time,modify_user,modify_time)
SELECT t2.task_id, t2.data_date, t2.create_user, t2.create_time, t2.modify_user,t2.modify_time FROM (
    SELECT SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC),',',1) log_id
    FROM t_task_exec_log
    WHERE exec_status = 'success'
    GROUP BY task_id
) t INNER JOIN t_task_exec_log t2 ON t.log_id = t2.log_id;
View Code

　　　　一定要去执行，你会发现大惊喜！

　　3、多和同事沟通，多和需求方沟通

　　　　多和同事沟通，集思广益，说不定就找到合适的解决方案了

　　　　多和需求方沟通，多谈谈个人的见解，也许需求改动一丢丢，但我们实现却容易很多

　　4、留疑

　　　　1、分组后如何取前 N 条

　　　　2、分组后如何取倒数 N 条

rarrSQLspanstylecolorMySQL

有关记一次有意思的 SQL 实现 → 分组后取每组的第一条记录的更多相关文章

  1. ruby-on-rails - 按天对 Mongoid 对象进行分组 - 2

    在控制台中反复尝试之后，我想到了这种方法，可以按发生日期对类似activerecord的(Mongoid)对象进行分组。我不确定这是完成此任务的最佳方法，但它确实有效。有没有人有更好的建议，或者这是一个很好的方法？#eventsisanarrayofactiverecord-likeobjectsthatincludeatimeattributeevents.map{|event|#converteventsarrayintoanarrayofhasheswiththedayofthemonthandtheevent{:number=>event.time.day,:event=>ev

  2. ruby - 使用 Vim Rails，您可以创建一个新的迁移文件并一次性打开它吗？ - 2

    使用带有Rails插件的vim，您可以创建一个迁移文件，然后一次性打开该文件吗？textmate也可以这样吗？ 最佳答案 你可以使用rails.vim然后做类似的事情::Rgeneratemigratonadd_foo_to_bar插件将打开迁移生成的文件，这正是您想要的。我不能代表textmate。 关于ruby-使用VimRails，您可以创建一个新的迁移文件并一次性打开它吗？，我们在StackOverflow上找到一个类似的问题： https://sta

  3. ruby - 如何每月在 Heroku 运行一次 Scheduler 插件？ - 2

    在选择我想要运行操作的频率时，唯一的选项是“每天”、“每小时”和“每10分钟”。谢谢!我想为我的Rails3.1应用程序运行调度程序。 最佳答案 这不是一个优雅的解决方案，但您可以安排它每天运行，并在实际开始工作之前检查日期是否为当月的第一天。 关于ruby-如何每月在Heroku运行一次Scheduler插件？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/8692687/

  4. ruby - 如何根据特征实现 FactoryGirl 的条件行为 - 2

    我有一个用户工厂。我希望默认情况下确认用户。但是鉴于unconfirmed特征，我不希望它们被确认。虽然我有一个基于实现细节而不是抽象的工作实现，但我想知道如何正确地做到这一点。factory:userdoafter(:create)do|user,evaluator|#unwantedimplementationdetailshereunlessFactoryGirl.factories[:user].defined_traits.map(&:name).include?(:unconfirmed)user.confirm!endendtrait:unconfirmeddoenden

  5. 华为OD机试用Python实现 -【明明的随机数】 2023Q1A - 2

    华为OD机试题本篇题目：明明的随机数题目输入描述输出描述：示例1输入输出说明代码编写思路最近更新的博客华为od2023|什么是华为od，od薪资待遇，od机试题清单华为OD机试真题大全，用Python解华为机试题|机试宝典【华为OD机试】全流程解析+经验分享,题型分享,防作弊指南华为o

  6. 基于C#实现简易绘图工具【100010177】 - 2

    C#实现简易绘图工具一.引言实验目的:通过制作窗体应用程序(C#画图软件),熟悉基本的窗体设计过程以及控件设计,事件处理等,熟悉使用C#的winform窗体进行绘图的基本步骤,对于面向对象编程有更加深刻的体会.Tutorial任务设计一个具有基本功能的画图软件**·包括简单的新建文件,保存,重新绘图等功能**·实现一些基本图形的绘制,包括铅笔和基本形状等,学习橡皮工具的创建**·设计一个合理舒适的UI界面**注明:你可能需要先了解一些关于winform窗体应用程序绘图的基本知识,以及关于GDI+类和结构的知识二.实验环境Windows系统下的visualstudio2017C#窗体应用程序三.

  7. MIMO-OFDM无线通信技术及MATLAB实现（1）无线信道：传播和衰落 - 2

     MIMO技术的优缺点优点通过下面三个增益来总体概括：阵列增益。阵列增益是指由于接收机通过对接收信号的相干合并而活得的平均SNR的提高。在发射机不知道信道信息的情况下，MIMO系统可以获得的阵列增益与接收天线数成正比复用增益。在采用空间复用方案的MIMO系统中，可以获得复用增益，即信道容量成倍增加。信道容量的增加与min(Nt,Nr)成正比分集增益。在采用空间分集方案的MIMO系统中，可以获得分集增益，即可靠性性能的改善。分集增益用独立衰落支路数来描述，即分集指数。在使用了空时编码的MIMO系统中，由于接收天线或发射天线之间的间距较远，可认为它们各自的大尺度衰落是相互独立的，因此分布式MIMO

  8. 【Java入门】使用Java实现文件夹的遍历 - 2

    遍历文件夹我们通常是使用递归进行操作，这种方式比较简单，也比较容易理解。本文为大家介绍另一种不使用递归的方式，由于没有使用递归，只用到了循环和集合，所以效率更高一些！一、使用递归遍历文件夹整体思路1、使用File封装初始目录，2、打印这个目录3、获取这个目录下所有的子文件和子目录的数组。4、遍历这个数组，取出每个File对象4-1、如果File是否是一个文件，打印4-2、否则就是一个目录，递归调用代码实现publicclassSearchFile{publicstaticvoidmain(String[]args){//初始目录Filedir=newFile("d:/Dev");Datebeg

  9. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  10. ruby - 在 Ruby 中创建按公共(public)键值分组的新哈希 - 2

    假设我有一个在Ruby中看起来像这样的哈希:{:ie0=>"Hi",:ex0=>"Hey",:eg0=>"Howdy",:ie1=>"Hello",:ex1=>"Greetings",:eg1=>"Goodday"}有什么好的方法可以将它变成如下内容:{"0"=>{"ie"=>"Hi","ex"=>"Hey","eg"=>"Howdy"},"1"=>{"ie"=>"Hello","ex"=>"Greetings","eg"=>"Goodday"}} 最佳答案 您要求一个好的方法来做到这一点，所以答案是:一种您或同事可以在六个月后理解

随机推荐