牛客网SQL进阶137:第二快/慢用时之差大于试卷时长一半的试卷

  官网链接:

第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别,。题目来自【牛客题霸】icon-default.png?t=N7T8https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166?tpId=240

0 问题描述

  • 试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间)
  • 试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分)
  • 试卷信息表examination_info和试卷作答记录表exam_record, 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息,按试卷ID降序排序

1 数据准备

drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',exam_id int UNIQUE NOT NULL COMMENT '试卷ID',tag varchar(32) COMMENT '类别标签',difficulty varchar(8) COMMENT '难度',duration int NOT NULL COMMENT '时长',release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE TABLE exam_record (id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',uid int NOT NULL COMMENT '用户ID',exam_id int NOT NULL COMMENT '试卷ID',start_time datetime NOT NULL COMMENT '开始时间',submit_time datetime COMMENT '提交时间',score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),(9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),(9003, '算法', 'medium', 80, '2021-09-01 10:00:00');INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:51:01', 78),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:59:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9002, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:12:01', 84),
(1003, 9001, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null),
(1005, 9001, '2021-09-01 14:01:01', null, null),
(1003, 9003, '2021-09-08 15:01:01', null, null);

2 数据分析

完整的代码如下:


select distinct exam_id,duration,release_time
from(select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as subfrom(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id)tmp3where sub * 2 >= durationorder by exam_id desc;

上述的解题步骤拆分

step1:求出各试卷的用时之差,并进行正序、逆序排序
step2:求出第二快和第二慢的用时之差,并和试卷规定时长(duration)进行比对

step3:试卷ID降序排序

步骤代码

step1:

selectexam_id,duration,release_time,difftime,--进行正序、逆序排序row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2
from (selecter.exam_id,ei.duration,ei.release_time,--step1:求出各试卷的用时之差timestampdiff,并进行正序、逆序排序timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1;

step2: 使用 case when进行赋值,当rn1 =2 时,代表是第二快的difftime(取正值);当rn2 =2 时,代表是第二慢的difftime(需要取负值); 外层再嵌套sum聚合函数,即得到第二快和第二慢的用时之差sub

select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as sub
from(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id;

step3: sub和试卷规定时长(duration)进行比对,要求:sub * 2 >= duration

select distinct exam_id,duration,release_time
from(select exam_id,duration,release_time,sum(case when rn1 =2 then difftimewhen rn2 =2 then -difftimeelse 0end ) as subfrom(selectexam_id,duration,release_time,difftime,row_number() over(partition  by exam_id order by difftime desc ) as rn1,row_number() over(partition  by exam_id order by difftime ) as rn2from (selecter.exam_id,ei.duration,ei.release_time,timestampdiff(minute,er.start_time,er.submit_time) as difftimefrom exam_record er  join examination_info eion  er.exam_id = ei.exam_idwhere submit_time is not null)tmp1)tmp2group by exam_id)tmp3where sub * 2 >= durationorder by exam_id desc;

3 小结

  上述案例用到的知识点:

(1)timestampdiff函数

timestampdiff: MySQL 中用来计算两个日期或时间之间的差值的函数;

语法:timestampdiff(unit, start_date, end_date)

参数说明:

   unit:差值的单位,可以是second(秒)、minute(分)、hour(小时)、day(天)、week(周)、month(月)、quarter(季度)或 year(年)。
  start_date:表示时间段的起始时间

  end_date:表示时间段的结束时间

(2)row_number() over(partition by ..order by ..desc)窗口函数

(3)sum +case when :条件+聚合

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

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

相关文章

【Flink状态管理(二)各状态初始化入口】状态初始化流程详解与源码剖析

文章目录 1. 状态初始化总流程梳理2.创建StreamOperatorStateContext3. StateInitializationContext的接口设计。4. 状态初始化举例:UDF状态初始化 在TaskManager中启动Task线程后,会调用StreamTask.invoke()方法触发当前Task中算子的执行,在…

Transformer实战-系列教程13:DETR 算法解读

🚩🚩🚩Transformer实战-系列教程总目录 有任何问题欢迎在下面留言 本篇文章的代码运行界面均在Pycharm中进行 本篇文章配套的代码资源已经上传 点我下载源码 1、物体检测 说到目标检测你能想到什么 faster-rcnn系列,开山之作&…

Conda历史版本下载地址和python对应关系

一、前言 因为Conda安装版本问题,带来了很多问题,虽然不能直接确定二者之间的关系,但是安装指定版本的conda,确实是一个比较好的方法。特此记忆。 二、下载地址 下载最新版本:Free Download | Anaconda 下载历史版本&#xff…

Springboot集成flowable工作流

文章目录 概要包依赖问题小结 概要 Spirng boot 集成flowable <spring-boot.version>3.1.5</spring-boot.version>包依赖 <dependency><groupId>org.flowable</groupId><artifactId>flowable-spring-boot-starter</artifactId>&l…

vuecli3 执行 npm run build 打包命令报错:TypeError: file.split is not a function

问题 今天有个项目在打包的时候遇到了一个问题&#xff0c;就是执行 npm run build 命令的时候报错了&#xff0c;如下&#xff1a; 解决 我排查了一下&#xff0c;模拟代码如下&#xff1a;在打包的时候用了 MinChunkSizePlugin const webpack require("webpack"…

c++设计模式之装饰器模式

作用 为现有类增加功能 案例说明 class Car { public:virtual void show()0; };class Bmw:public Car { public:void show(){cout<<"宝马汽车>>"<<endl;} };class Audi:public Car { public:void show(){cout<<"奥迪汽车>>&q…

5G NR 信道号计算

一、5G NR的频段 增加带宽是增加容量和传输速率最直接的方法&#xff0c;目前5G最大带宽将会达到400MHz&#xff0c;考虑到目前频率占用情况&#xff0c;5G将不得不使用高频进行通信。 3GPP协议定义了从Sub6G(FR1)到毫米波(FR2)的5G目标频谱。 其中FR1是5G的核心频段&#xff0…

戴上HUAWEI WATCH GT 4,解锁龙年新玩法

春节将至&#xff0c;华为WATCH GT 4作为一款颜值和实力并存的手表&#xff0c;能为节日增添了不少趣味和便利。无论你是钟情于龙年表盘或定制属于自己的表盘&#xff0c;还是过年用来抢红包或远程操控手机拍全家福等等&#xff0c;它都能成为你的“玩伴”。接下来&#xff0c;…

STM32单片机基本原理与应用(四)

直流电机驱动控制原理 1、电机正反转控制 在STM32中&#xff0c;直流电机的正反转控制主要通过改变电机输入电源的极性来实现。当电机的电压极性发生变化时&#xff0c;电机的旋转方向也会相应改变。在硬件电路中&#xff0c;可以通过继电器或晶体管等电子开关来切换电机的电源…

vscode连接ssh报错

关于vscode更新版本至1.86后&#xff0c;导致无法连接服务器问题的记录 原因&#xff1a;vscode1.86更新了对glibc的要求&#xff0c;需要最低2.28版本&#xff0c;导致各种旧版本的linux发行版&#xff08;比如最常见的centos 7&#xff09;都无法用remote-ssh来连接了&#…

springboot微信小程序uniapp学习计划与日程管理系统

基于springboot学习计划与日程管理系统&#xff0c;确定学习计划小程序的目标&#xff0c;明确用户需求&#xff0c;学习计划小程序的主要功能是帮助用户制定学习计划&#xff0c;并跟踪学习进度。页面设计主要包括主页、计划学习页、个人中心页等&#xff0c;然后用户可以利用…

LiteFlow规则引擎框架

LiteFlow规则引擎框架 Hi&#xff0c;我是阿昌&#xff0c;今天介绍一个规则引擎框架&#xff0c;LiteFlow&#xff1b; 一、前言 那首先得知道什么是规则引擎&#xff1f;规则引擎是 一种用于自动化处理业务规则的软件组件。 在软件行业中&#xff0c;规则引擎通常用于解决…

Python中HTTP隧道的基本原理与实现

HTTP隧道是一种允许客户端和服务器之间通过中间代理进行通信的技术。这种隧道技术允许代理服务器转发客户端和服务器之间的所有HTTP请求和响应&#xff0c;而不需要对请求或响应内容进行任何处理或解析。Python提供了强大的网络编程能力&#xff0c;可以使用标准库中的socket和…

深度学习入门笔记(八)可以不断思考的模型:RNN与LSTM

8.1 循环神经网络RNN 之前学到的 CNN 和全连接&#xff0c;模型的输入数据之间是没有关联的&#xff0c;比如图像分类&#xff0c;每次输入的图片与图片之间就没有任何关系&#xff0c;上一张图片的内容不会影响到下一张图片的结果。但在自然语言处理领域&#xff0c;这就成了…

RabbitMQ高可用架构涉及常用功能整理

RabbitMQ高可用架构涉及常用功能整理 1. rabbitmq的集群模式2. 镜像模式高可用系统架构和相关组件3. rabbitmq的核心参数3.1 镜像策略3.2 新镜像同步策略3.3 从节点晋升策略3.4 主队列选择策略 4. rabbitmq常用命令4.1 常用基础命令4.1.1 服务管理4.1.2 用户管理4.1.3 角色管理…

MacOS上怎么把格式化成APFS的U盘或者硬盘格式化回ExFAT?

一、问题 MacOS在更新MacOS Monterey后或者更高系统后发现&#xff0c;格式U盘或者硬盘只有4个APFS选项&#xff0c;那么我们该如何将APFS格式成ExFAT&#xff1f; 二、解答 将APFS的U盘或者硬盘拓展成MacOS的拓展格式即可&#xff0c;操作步骤如下 1、电脑接入U盘或者硬盘 2…

项目02《游戏-11-开发》Unity3D

基于 项目02《游戏-10-开发》Unity3D &#xff0c; 任务&#xff1a;飞行坐骑 首先创建脚本&#xff0c; 绑定脚本&#xff0c; using UnityEngine; public class Dragon : MonoBehaviour{ [SerializeField] private float speed 10f; public Transfo…

RCE(命令执行)知识点总结最详细

description: 这里是CTF做题时常见的会遇见的RCE的漏洞知识点总结。 如果你觉得写得好并且想看更多web知识的话可以去gitbook.22kaka.fun去看&#xff0c;上面是我写的一本关于web学习的一个gitbook&#xff0c;当然如果你能去我的github为我的这个项目点亮星星我会感激不尽htt…

uniapp的配置和使用

①安装环境和编辑器 注册小程序账号 微信开发者工具下载 uniapp 官网 HbuilderX 下载 首先先下载Hbuilder和微信开发者工具 &#xff08;都是傻瓜式安装&#xff09;&#xff0c;然后注册小程序账号&#xff1a; 拿到appid&#xff1a; ②简单通过demo使用微信开发者工具和…

Flask基础学习

1.debug、host、port 模式修改 1) debug模式 默认debug模式是off&#xff0c;在修改代码调试过程中需要暂停重启使用&#xff0c;这时可修改on模式解决。 同时在debug模式开启下可看到出错信息。 下面有关于Pycharm社区版和专业版修改debug模式的区别 专业版 社区版&#…