当前位置: 首页 > news >正文

牛客网SQL进阶128:未完成试卷数大于1的有效用户

官网链接:

未完成试卷数大于1的有效用户_牛客题霸_牛客网现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, st。题目来自【牛客题霸】icon-default.png?t=N7T8https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286?tpId=240&tqId=2183007&ru=%2Fpractice%2F45a87639110841b6950ef6a12d20175f&qru=%2Fta%2Fsql-advanced%2Fquestion-ranking&sourceUrl=

0 问题描述

     基于试卷作答记录表exam_record、试卷信息表examination_info , 统计2021年每个未完成试卷作答数大于1的有效用户的数据(有效用户指完成试卷作答数至少为1且未完成数小于5)

1 数据准备

drop table if exists examination_info;
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;drop table if exists exam_record;
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, '2020-01-01 10:00:00'),(9002, 'SQL', 'easy', 60, '2020-02-01 10:00:00'),(9003, '算法', 'medium', 80, '2020-08-02 10:00:00');INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

 

2 数据分析

step1:  获取各用户的tag,start_time及未完成标记和已完成标记,如果该作答记录交卷了则已完成标记为1,未完成标记为0,否则相反:if(submit_time is null, 1, null) as incomplete

代码如下:

select  er.uid,ei.tag,er.start_time,-- incomplete 未提交试卷的标记if(er.submit_time is null, 1, null) as incomplete,-- complete已提交试卷的标记if(er.submit_time is not null, 1,null) as complete
from exam_record er
left join examination_info ei on er.exam_id =ei.exam_id
where year(er.start_time)=2021

 step2:  

  • 用户分组:group by uid,统计:未完成试卷作答数incomplete_cnt 、已完成试卷作答数complete_cnt_cnt
  • 筛选出有效用户:having complete_cnt >= 1 and  incomplete_cnt >1 and incomplete_cnt <5
  • 对于每条作答tag,用符号 ":" 来拼接 日期字段和tag字段:concat_ws(':', date(start_time), tag); 对于一个人(组内)的多条作答,需去重distinct concat_ws(':', date(start_time), tag);

  • group_concat ( 要连接的字段  [separator '分隔符'])

最终代码如下:

SELECT uid,-- 未完成试卷的作答数count(incomplete) as incomplete_cnt,-- 已完成试卷的作答数count(complete) as complete_cnt,-- distinct concat_ws(':', date(start_time), tag)  as cw-- group_concat ( cw  separator ';')group_concat(distinct concat_ws(':', date(start_time), tag) separator ';') as detail
from (SELECT er.uid,ei.tag,er.start_time,-- incomplete 未提交试卷的标记if(er.submit_time is null, 1, null) as incomplete,-- complete已提交试卷的标记if(er.submit_time is not null, 1,null) as completefrom exam_record erleft join examination_info  ei on er.exam_id =ei.exam_idwhere year(er.start_time)=2021
) as t1
group by uid
-- 有效用户:完成试卷的作答数至少为1 and 未完试卷的作答数小于5且大于1
having complete_cnt >= 1 and incomplete_cnt >1 and  incomplete_cnt <5
order by incomplete_cnt desc;

3 小结

   本案例涉及到:if条件判断+count,即条件聚合。 此外还涉及到date日期函数、concat_ws、group_concat函数的使用

concat_ws(带分隔符的字符串连接函数)

  • 语法:concat_ws(string SEP, string A ,string B.......)
  • 返回值:string
  • 说明:返回输入字符串连接后的结果,SEP表示各个字符串的分隔符
  • 举例:select  concat_ws('|','ad','cv','op') ;---> ad|cv|op

group_concat函数

  • 语法:group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator '分隔符'])
  • 说明:将group by产生的同一个分组中的值连接起来,返回一个字符串结果
  • 参数解释:distinct排除重复值; 如果需要对结果中的值进行排序,可以使用order by子句;separator '分隔符':是拼接符号,默认为逗号
  • 返回值:string
  • sql举例:
#--- 对buyer字段进行分组,把去除重复冗余的spending字段的值打印在同一行,'+'加号分隔
select buyer,group_concat(distinct spending separator '+') 
from spend
group by buyer;

相关文章:

牛客网SQL进阶128:未完成试卷数大于1的有效用户

官网链接&#xff1a; 未完成试卷数大于1的有效用户_牛客题霸_牛客网现有试卷作答记录表exam_record&#xff08;uid用户ID, exam_id试卷ID, st。题目来自【牛客题霸】https://www.nowcoder.com/practice/46cb7a33f7204f3ba7f6536d2fc04286?tpId240&tqId2183007&ru%2…...

GitHub的使用操作

记得看目录哦&#xff01; 1. 创建仓库2. 下载desktop3. 把创建的库克隆到本地4. 文件拷贝到本地仓库![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/7171ac6c4ca14e3b8d22717121f79c9e.png)5. 在网址后面加/compare进行比较6. 给系统添加功能 1. 创建仓库 2. 下载…...

智慧公厕管理软件

随着城市化的不断推进&#xff0c;城市公共设施逐渐完善&#xff0c;其中智慧公厕的建设也在不断提速。智慧公厕作为城市基础设施的重要组成部分&#xff0c;对城市卫生水平提升有着不可忽视的作用。而智慧公厕管理软件更是智慧公厕管理的基础&#xff0c;是公共厕所智慧化管理…...

【30秒看懂大数据】数据中台

知幽科技是一家专注企业数字/智化&#xff0c;围绕数据价值应用的一站式数智化解决方案的咨询公司&#xff0c;也包括了为企业提供定制化数据培训&#xff0c;力求做企业最好的数智化决策伙伴。 点击上方「蓝字」关注我们 30秒看懂大数据专栏 让您在有限的碎片化时间&#xf…...

【UI自动化测试技术】自动化测试研究:Python+Selenium+Pytest+Allure,详解UI自动化测试,了解元素交互的常用方法(精)(三)

导言 在之前的文章里&#xff0c;我们一起学习了定位方式、等待机制等内容。相信通过之前的学习&#xff0c;你已经掌握了WEB自动化的一些入门知识&#xff0c;具备了编写代码的一些基础知识和能力。这篇文章&#xff0c;让我们一起学习一下模拟键盘事件。 在实际的项目当中&a…...

GPT-4带来的思想火花

GPT-4能够以其强大的生成能力和广泛的知识储备激发出众多思想火花。它能够在不同的情境下生成新颖的观点、独特的见解和富有创意的解决方案&#xff0c;这不仅有助于用户突破思维定势&#xff0c;还能促进知识与信息在不同领域的交叉融合。 1.GPT-4出色的创新思考和知识整合能…...

使用倒模耳机壳UV村脂胶液制作舞台监听耳返入耳式耳机壳有哪些优点?

使用倒模耳机壳UV树脂胶液制作舞台监听耳返入耳式耳机壳有很多优点&#xff0c;具体如下&#xff1a; 高音质表现&#xff1a;通过倒模工艺制作的耳机壳能够更好地贴合耳朵&#xff0c;减少声音散射和反射&#xff0c;提高声音的清晰度和质感。这对于舞台监听来说非常重要&…...

html从零开始8:css3新特性、动画、媒体查询、雪碧图、字体图标【搬代码】

css3新特性 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><meta http-equiv"X-UA-Compatible" content"IEedge"><meta name"viewport" content"widthdevice-width, …...

HAL库 STM32驱动W25QXX驱动例程

HAL库 STM32驱动W25QXX驱动例程 &#x1f4cd;驱动程序参考&#xff1a;《STM32CubeMX | 基于STM32使用HAL库W25Q128驱动程序》&#x1f511; 驱动方式&#xff1a;硬件SPI方式和SPI DMA方式。&#x1f516;适用于&#xff1a;W25X系列/Q系列芯片:W25Q80、W25Q16、W25Q32、 W25…...

C#入门及进阶|数组和集合(九):Stack类

在C#中&#xff0c;通过类Stack来封装对栈的操作&#xff0c;使得对栈的操作变得非常简单和容易理解。 栈是按照“后进先出”的原则来操作元素。 栈集合常用的属性和方法: 属性说明Count获取 Stack 中包含的元素数。方法说明Peek返回位于栈顶部的对象但不将其移除。Po…...

算法训练day31贪心算法理论基础Leetcode455分发饼干376摆动序列53最大子序和

贪心算法理论基础 文章链接 代码随想录 (programmercarl.com) 说实话贪心算法并没有固定的套路。最好用的策略就是举反例&#xff0c;如果想不到反例&#xff0c;那么就试一试贪心吧。 面试中基本不会让面试者现场证明贪心的合理性&#xff0c;代码写出来跑过测试用例即可&…...

Java与JavaScript同源不同性

Java是目前编程领域使用非常广泛的编程语言&#xff0c;相较于JavaScript&#xff0c;Java更被人们熟知。很多Java程序员想学门脚本语言&#xff0c;一看JavaScript和Java这么像&#xff0c;很有亲切感&#xff0c;那干脆就学它了&#xff0c;这也间接的帮助了JavaScript的发展…...

【JavaEE】spring boot快速上手

SpringBoot快速上手 文章目录 SpringBoot快速上手Maven会出现的一个官方bug创建完项目之后常用的的三个功能依赖管理Maven仓库中央仓库本地仓库国内源配置私服 springboot项目创建什么是springspring boot项目的创建Hello Worldweb服务器 SpringMVC什么是SpringWebMVC什么是MVC…...

【数据结构】16 二叉树的定义,性质,存储结构(以及先序、后序、中序遍历)

二叉树 一个二叉树是一个有穷的结点集合。 它是由根节点和称为其左子树和右子树的两个不相交的二叉树组成的。 二叉树可具有以下5种形态。 性质 一个二叉树第i层的最大结点数为 2 i − 1 2^{i-1} 2i−1, i ≥ 1 i \geq 1 i≥1 每层最大结点可以对应完美二叉树&#xff08;…...

GPT SOVITS项目 一分钟克隆 (文字输出)

步骤流程&#xff1a;&#xff08;首先使用UVR 提取人声文件&#xff0c;然后按下面步骤进行&#xff09; 注意这里提交的音频是参考的音频...

python34-Python列表和元组之加法

列表和元组支持加法运算&#xff0c;加法的和就是两个列表或元组所包含的元素的总和。 需要指出的是&#xff0c;列表只能和列表相加;元组只能和元组相加;元组不能直接和列表相加。 如下代码示范了元组和列表的加法运算。 # !/usr/bin/env python# -*- coding: utf-8 -*-# T…...

不做程序员了(转岗半年后对程序员岗位的思考)

不做程序员了&#xff08;转岗半年后对程序员岗位的思考&#xff09; 前言 好久没有更新了&#xff0c;已经久到CSDN的小编来问我为什么不更了。原因是我半年前转岗了&#xff0c;不再做程序员了&#xff0c;由程序员变为了产品经理。废话不多说&#xff0c;换个视角来给大家…...

DS:八大排序之直接插入排序、希尔排序和选择排序

创作不易&#xff0c;感谢三连支持&#xff01;&#xff01; 一、排序的概念及运用 1.1 排序的概念 排序&#xff1a;所谓排序&#xff0c;就是使一串记录&#xff0c;按照其中的某个或某些关键字的大小&#xff0c;递增或递减的排列起 来的操作。稳定性&…...

【MySQL】-21 MySQL综合-8(MySQL默认值+MySQL非空约束+MySQL查看表中的约束)

MySQL默认值MySQL非空约束MySQL查看表中的约束 MySQL默认值在创建表时设置默认值约束在修改表MySQL默认值在创建表时设置默认值约束在修改表时添加默认值约束删除默认值约束删除默认值约束 MySQL非空约束在创建表时设置非空约束在修改表时添加非空约束删除非空约束 MySQL查看表…...

力扣hot3--并查集+哈希

第一想法是排个序然后遍历一遍&#xff0c;but时间复杂度就超啦 并查集居然与哈希结合了&#xff08;&#xff09; 已经好久没用过并查集了&#xff0c;&#xff0c;&#xff0c;我们用哈希表f_node中来记录原结点的父节点&#xff0c;其中key是原结点&#xff0c;value是父节点…...

Java 语言特性(面试系列2)

一、SQL 基础 1. 复杂查询 &#xff08;1&#xff09;连接查询&#xff08;JOIN&#xff09; 内连接&#xff08;INNER JOIN&#xff09;&#xff1a;返回两表匹配的记录。 SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.dept_id; 左…...

VB.net复制Ntag213卡写入UID

本示例使用的发卡器&#xff1a;https://item.taobao.com/item.htm?ftt&id615391857885 一、读取旧Ntag卡的UID和数据 Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click轻松读卡技术支持:网站:Dim i, j As IntegerDim cardidhex, …...

MySQL 隔离级别:脏读、幻读及不可重复读的原理与示例

一、MySQL 隔离级别 MySQL 提供了四种隔离级别,用于控制事务之间的并发访问以及数据的可见性,不同隔离级别对脏读、幻读、不可重复读这几种并发数据问题有着不同的处理方式,具体如下: 隔离级别脏读不可重复读幻读性能特点及锁机制读未提交(READ UNCOMMITTED)允许出现允许…...

C++中string流知识详解和示例

一、概览与类体系 C 提供三种基于内存字符串的流&#xff0c;定义在 <sstream> 中&#xff1a; std::istringstream&#xff1a;输入流&#xff0c;从已有字符串中读取并解析。std::ostringstream&#xff1a;输出流&#xff0c;向内部缓冲区写入内容&#xff0c;最终取…...

三体问题详解

从物理学角度&#xff0c;三体问题之所以不稳定&#xff0c;是因为三个天体在万有引力作用下相互作用&#xff0c;形成一个非线性耦合系统。我们可以从牛顿经典力学出发&#xff0c;列出具体的运动方程&#xff0c;并说明为何这个系统本质上是混沌的&#xff0c;无法得到一般解…...

《C++ 模板》

目录 函数模板 类模板 非类型模板参数 模板特化 函数模板特化 类模板的特化 模板&#xff0c;就像一个模具&#xff0c;里面可以将不同类型的材料做成一个形状&#xff0c;其分为函数模板和类模板。 函数模板 函数模板可以简化函数重载的代码。格式&#xff1a;templa…...

怎么让Comfyui导出的图像不包含工作流信息,

为了数据安全&#xff0c;让Comfyui导出的图像不包含工作流信息&#xff0c;导出的图像就不会拖到comfyui中加载出来工作流。 ComfyUI的目录下node.py 直接移除 pnginfo&#xff08;推荐&#xff09;​​ 在 save_images 方法中&#xff0c;​​删除或注释掉所有与 metadata …...

Proxmox Mail Gateway安装指南:从零开始配置高效邮件过滤系统

&#x1f49d;&#x1f49d;&#x1f49d;欢迎莅临我的博客&#xff0c;很高兴能够在这里和您见面&#xff01;希望您在这里可以感受到一份轻松愉快的氛围&#xff0c;不仅可以获得有趣的内容和知识&#xff0c;也可以畅所欲言、分享您的想法和见解。 推荐&#xff1a;「storms…...

python爬虫——气象数据爬取

一、导入库与全局配置 python 运行 import json import datetime import time import requests from sqlalchemy import create_engine import csv import pandas as pd作用&#xff1a; 引入数据解析、网络请求、时间处理、数据库操作等所需库。requests&#xff1a;发送 …...

《Docker》架构

文章目录 架构模式单机架构应用数据分离架构应用服务器集群架构读写分离/主从分离架构冷热分离架构垂直分库架构微服务架构容器编排架构什么是容器&#xff0c;docker&#xff0c;镜像&#xff0c;k8s 架构模式 单机架构 单机架构其实就是应用服务器和单机服务器都部署在同一…...