SQL题:未完成率较高的50%用户近三个月答卷情况

2024-06-21 11:04

本文主要是介绍SQL题:未完成率较高的50%用户近三个月答卷情况,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

SQL题:未完成率较高的50%用户近三个月答卷情况

这是一道牛客网上SQL进阶图库中的一道困难题目,个人花了近两个小时才通过所有用例。之所以想记录下来是因为这道题算是一个很考验基本功的题目,也不乏一些SQL中的技巧。下面我们逐步分析:

描述

现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):

iduidnick_nameachievementleveljobregister_time
11001牛客1号32007算法2020-01-01 10:00:00
21002牛客2号25006算法2020-01-01 10:00:00
31003牛客3号22005算法2020-01-01 10:00:00

试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002SQLhard802020-01-01 10:00:00
39003算法hard802020-01-01 10:00:00
49004PYTHONmedium702020-01-01 10:00:00

试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-01 09:01:012020-01-01 09:21:5990
15100290012020-01-01 18:01:012020-01-01 18:59:0290
13100190012020-01-02 10:01:012020-01-02 10:31:0189
2100290012020-01-20 10:01:01
3100290012020-02-01 12:11:01
5100190012020-03-01 12:01:01
6100290012020-03-01 12:01:012020-03-01 12:41:0190
4100390012020-03-01 19:01:01
7100290012020-05-02 19:01:012020-05-02 19:32:0090
14100190022020-01-01 12:11:01
8100190022020-01-02 19:01:012020-01-02 19:59:0169
9100190022020-02-02 12:01:012020-02-02 12:20:0199
10100290022020-02-02 12:01:01
11100290022020-02-02 12:01:012020-02-02 12:43:0181
12100290022020-03-02 12:11:01
17100190022020-05-05 18:01:01
16100290032020-05-06 12:01:01

请统计SQL试卷上未完成率较高的50%用户中,6级和7级用户在有试卷作答记录的近三个月中,每个月的答卷数目和完成数目。按用户ID、月份升序排序。

由示例数据结果输出如下:

uidstart_monthtotal_cntcomplete_cnt
100220200231
100220200321
100220200521

解释:各个用户对SQL试卷的未完成数、作答总数、未完成率如下:

uidincomplete_cnttotal_cntincomplete_rate
1001370.4286
1002480.5000
1003111.0000

1001、1002、1003分别排在1.0、0.5、0.0的位置,因此较高的50%用户(排位<=0.5)为1002、1003;

1003不是6级或7级;

有试卷作答记录的近三个月为202005、202003、202002;

这三个月里1002的作答题数分别为3、2、2,完成数目分别为1、1、1。

###解法:

这道题看起来很复杂,需要我们划分多个步骤,进行多次SQL嵌套才能完成。

**步骤一.**首先需要统计各个用户对SQL试卷的未完成数、作答总数、未完成率。其中需要确保试卷是SQL试卷。需要注意的是,这一步需要考虑多增加一列未完成率排名,排名应该使用开窗函数。SQL写法如下:

select exam_record.uid,
sum(case when submit_time is null then 1 else 0 end)  incomplete_cnt,
count(1) total_cnt, 
round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4) incomplete_rate ,
user_info.level,
row_number() over(order by round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4)) r
from  exam_record 
inner join user_info 
on user_info.uid = exam_record.uid 
inner join examination_info
on exam_record.exam_id = examination_info.exam_id
where examination_info.tag = 'SQL'
group by  exam_record.uid
order by  incomplete_rate

下一步则根据上一步所得出的数据筛选出哪些用户未完成率排在前50%且是6级或7级用户,加上将上一步SQL所得出的表命名为表a,可写如下sql进行筛选:

select  a.uid   from a
where  r >= (select floor(count(distinct uid)/2)  from exam_record) + 1 and  (a.level = 6 or a.level = 7)

此时我们就得出了应该被算入最终统计结果的所有用户uid。

**步骤二.**下一步需要考虑统计用户近三个月的总答题数和完成数。此时需要注意的是需要选出近三个月,因而至少需要一次针对不同用户uid和start_month的排序。代码如下:

select  exam_record.uid,
date_format(exam_record.start_time,"%Y%m")  start_month,
count(1) over(partition by exam_record.uid, date_format(exam_record.start_time,"%Y%m"))  total_cnt, 
sum(case when exam_record.submit_time is null then 0 else 1 end)  over(partition by exam_record.uid , date_format(exam_record.start_time,"%Y%m")) complete_cnt,
dense_rank() over(partition by exam_record.uid  order by date_format(exam_record.start_time,'%Y%m') desc)  x
from  exam_record

上段代码包含了复杂的开窗,其实主要是针对不同用户uid和start_month进行聚合,统计当月的答题总数total_cnt和当月的总完成数complete_cnt。需要注意的是,我们添加了一次排序使用的是dense_rank()进行排序,目的是同时达到筛选前三个月的数据和去重。将上一个SQL所得出的表命名为表t,SQL写法如下:

select  t.uid,t.start_month,t.total_cnt, t.complete_cnt
from t
where t.x <= 3
group by t.uid,t.start_month,t.total_cnt, t.complete_cnt
order by t.uid,t.start_month

以上代码很重要,同时达到去重和选取固定行数的目的,是重要的SQL技巧。

**步骤三.**下面我们将以上两个步骤的所有代码结合起来,得出最终的解:

select  t.uid,t.start_month,t.total_cnt, t.complete_cnt   /*除去下面注释部分所标注的内容都是步骤二所完成查询*/
from (
select  exam_record.uid,
date_format(exam_record.start_time,"%Y%m")  start_month,
count(1) over(partition by exam_record.uid , date_format(exam_record.start_time,"%Y%m"))  total_cnt, 
sum(case when exam_record.submit_time is null then 0 else 1 end)  over(partition by exam_record.uid , date_format(exam_record.start_time,"%Y%m")) complete_cnt,
dense_rank() over(partition by exam_record.uid  order by date_format(exam_record.start_time,'%Y%m') desc)  x
from  exam_record
where exam_record.uid in (     /*这里对uid的筛选其实主要是从步骤一中得出的结果中筛选*/
select  a.uid   from 
(
select exam_record.uid,
sum(case when submit_time is null then 1 else 0 end)  incomplete_cnt,
count(1) total_cnt, 
round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4) incomplete_rate ,
user_info.level,
row_number() over(order by round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4)) r
from  exam_record 
inner join user_info 
on user_info.uid = exam_record.uid 
inner join examination_info
on exam_record.exam_id = examination_info.exam_id
where examination_info.tag = 'SQL'
group by  exam_record.uid
order by  incomplete_rate
)  a
where  r >= (select floor(count(distinct uid)/2)  from exam_record) + 1 and  (a.level = 6 or a.level = 7)
)
) t
where t.x <= 3
group by t.uid,t.start_month,t.total_cnt, t.complete_cnt
order by t.uid,t.start_month

比较复杂,详细查看前两步,才能看懂最终结合的逻辑。

这篇关于SQL题:未完成率较高的50%用户近三个月答卷情况的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1081042

相关文章

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页(deeppagination)却成为了性能优化的一个挑战,在本文中,我们将... 目录引言:深分页,真的只是“翻页慢”那么简单吗?一、背景介绍二、深分页的性能问题三、业务场景分析四、

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案(适合跨库实时查询)1. 方案概述2. 环境要求3.