SQL(进阶实战04)

已于 2022-05-18 09:07:07 修改
阅读量656 收藏 3
点赞数 2
分类专栏： mysql 文章标签： sql 数据库 database
于 2022-05-17 14:09:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44464850/article/details/124820306
版权

1.试卷完成数同比2020年的增长率及排名变化

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602021-01-01 10:00:00
29002C++hard802021-01-01 10:00:00
39003算法hard802021-01-01 10:00:00
49004PYTHONmedium702021-01-01 10:00:00

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

iduidexam_idstart_timesubmit_timescore
1100190012020-08-02 10:01:012020-08-02 10:31:0189
2100290012020-04-01 18:01:012020-04-01 18:59:0290
3100190012020-04-01 09:01:012020-04-01 09:21:5980
5100290012021-03-02 19:01:012021-03-02 19:32:0020
8100390012021-05-02 12:01:012021-05-02 12:31:0198
13100390012020-01-02 10:01:012020-01-02 10:31:0189
9100190022020-02-02 12:01:012020-02-02 12:20:0199
10100290022021-02-02 12:01:012020-02-02 12:43:0181
11100190022020-01-02 19:01:012020-01-02 19:59:0169
16100290022020-02-02 12:01:01
17100290022020-03-02 12:11:01
18100190022021-05-05 18:01:01
4100290032021-01-20 10:01:012021-01-20 10:10:0181
6100190032021-04-02 19:01:012021-04-02 19:40:0189
15100290032021-01-01 18:01:012021-01-01 18:59:0290
7100490042020-05-02 12:01:012020-05-02 12:20:0199
12100190042021-09-02 12:11:01
14100290042020-01-01 12:11:012020-01-01 12:31:0183

请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

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

tagexam_cnt_20exam_cnt_21growth_rateexam_cnt_rank_20exam_cnt_rank_21rank_delta
SQL32-33.3%121

解释：2020年上半年有3个tag有作答完成的记录，分别是C++、SQL、PYTHON，它们被做完的次数分别是3、3、2，做完次数排名为1、1（并列）、3；

2021年上半年有2个tag有作答完成的记录，分别是算法、SQL，它们被做完的次数分别是3、2，做完次数排名为1、2；具体如下：

tagstart_yearexam_cntexam_cnt_rank
C++202031
SQL202031
PYTHON202023
算法202131
SQL202122

因此能输出同比结果的tag只有SQL，从2020到2021年，做完次数3=>2，减少33.3%（保留1位小数）；排名1=>2，后退1名。

示例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, '2020-01-01 10:00:00'),
  (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-08-02 10:01:01', '2020-08-02 10:31:01', 89),
(1002, 9001, '2020-04-01 18:01:01', '2020-04-01 18:59:02', 90),
(1001, 9001, '2020-04-01 09:01:01', '2020-04-01 09:21:59', 80),
(1002, 9003, '2021-01-20 10:01:01', '2021-01-20 10:10:01', 81),
(1002, 9001, '2021-03-02 19:01:01', '2021-03-02 19:32:00', 20),
(1001, 9003, '2021-04-02 19:01:01', '2021-04-02 19:40:01', 89),
(1004, 9004, '2020-05-02 12:01:01', '2020-05-02 12:20:01', 99),
(1003, 9001, '2021-05-02 12:01:01', '2021-05-02 12:31:01', 98),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1001, 9004, '2021-09-02 12:11:01', null, null),
(1003, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9004, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9003, '2021-01-01 18:01:01', '2021-01-01 18:59:02', 90),
(1002, 9002, '2020-02-02 12:01:01', null, null),
(1002, 9002, '2020-03-02 12:11:01', null, null),
(1001, 9002, '2021-05-05 18:01:01', null, null);
输出

SQL|3|2|-33.3%|1|2|1

思路
1.找到所有tag在2020，2021上半年的完成数
2.计算growth_rate，并生成各tag完成数排名
3.取出所有需要的字段，并计算排名变化（2021-2020年，无需取绝对值）
4.筛选出2020和2021年均有完成记录的tag，并按题目要求排序
题解
方式一：
SELECT #第三步：取出所有需要的字段，并计算排名变化（2021-2020年，无需取绝对值）
  tag,
  exam_cnt_20,
  exam_cnt_21,
  growth_rate,
  exam_cnt_rank_20,
  exam_cnt_rank_21,
  CAST(exam_cnt_rank_21 as SIGNED) - CAST(exam_cnt_rank_20 AS SIGNED) rank_delta
FROM (
SELECT #第二步：计算growth_rate，并生成各tag完成数排名
  tag,
  exam_cnt_20,
  exam_cnt_21,
  IFNULL(
      CONCAT(
      ROUND((exam_cnt_21 - exam_cnt_20)/exam_cnt_20 * 100, 1),'%'),0) growth_rate,
  	  RANK() OVER (ORDER BY exam_cnt_20 DESC) exam_cnt_rank_20,
  	  RANK() OVER (ORDER BY exam_cnt_21 DESC) exam_cnt_rank_21
  FROM (
  SELECT #第一步：找到所有tag在2020，2021上半年的完成数
    tag,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') 
           BETWEEN '2020-01' AND '2020-06', 1, 0)) exam_cnt_20,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') 
           BETWEEN '2021-01' AND '2021-06', 1, 0)) exam_cnt_21
  FROM exam_record
  LEFT JOIN examination_info ei USING(exam_id)
  GROUP BY 1) t1
) t2
# 第四步：筛选出2020和2021年均有完成记录的tag，并按题目要求排序
WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0 
ORDER BY 4 desc, 6 desc

方式二：
	select 
	tag,
	exam_cnt_20,
	exam_cnt_21,
	concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),"%") 	growth_rate,
		exam_cnt_rank_20,
		exam_cnt_rank_21,
	cast(exam_cnt_rank_21 as SIGNED)-cast(exam_cnt_rank_20 as SIGNED) rank_delta
	from
	(
	select #根据去年获取下一年的试卷次数和排名
	start_year,
	tag,
	exam_cnt as exam_cnt_20,
	lead(exam_cnt)over(partition by tag order by start_year) exam_cnt_21,
	exam_cnt_rank as exam_cnt_rank_20,
	lead(exam_cnt_rank)over(partition by tag order by start_year)exam_cnt_rank_21
	from 
		(
	select #每类试卷2020/2021完成数及排名统计
	start_year,
	tag,
	exam_cnt,
	rank()over(partition by start_year order by exam_cnt desc) as exam_cnt_rank
	from 
		(
		select #获取每年上半年试卷次数
		date_format(start_time,"%Y") start_year,
		tag,
		count(*)exam_cnt
		from examination_info
		inner join exam_record 
		using(exam_id)
		where score is not null and date_format(start_time,"%m")<=6
		group by tag,date_format(start_time,"%Y")
		)t
		group by tag,start_year
		)t1
	)t2
	WHERE exam_cnt_21 IS NOT NULL
	ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;
拓展
Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR[(N)] 字符型 
DATE  日期型
DATETIME  日期和时间型
DECIMAL  float型
SIGNED  int
TIME  时间型

示例1：
作用: 四舍五入
SELECT CAST('9.0' AS DECIMAL) FROM DUAL; -- 9

SELECT CAST('9.5' AS DECIMAL) FROM DUAL; -- 10

SELECT CAST('9.6' AS DECIMAL) FROM DUAL; -- 10

SELECT CAST('9.3' AS DECIMAL) FROM DUAL; -- 9

2.对试卷得分做min-max归一化

现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002C++hard802020-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
6100390012020-01-02 12:01:012020-01-02 12:31:0168
9100190012020-01-02 10:01:012020-01-02 10:31:0189
1100190012020-01-01 09:01:012020-01-01 09:21:5990
12100290022021-05-05 18:01:01(NULL)(NULL)
3100490022020-01-01 12:01:012020-01-01 12:11:0160
2100390022020-01-01 19:01:012020-01-01 19:30:0175
7100190022020-01-02 12:01:012020-01-02 12:43:0181
10100290022020-01-01 12:11:012020-01-01 12:31:0183
4100390022020-01-01 12:01:012020-01-01 12:41:0190
5100290022020-01-02 19:01:012020-01-02 19:32:0090
11100290042021-09-06 12:01:01(NULL)(NULL)
8100190052020-01-02 12:11:01(NULL)(NULL)

在物理学及统计学数据计算时，有个概念叫min-max标准化，也被称为离差标准化，是对原始数据的线性变换，使结果值映射到[0 - 1]之间。

转换函数为：

img

请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

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

uidexam_idavg_new_score
1001900198
100390010
1002900288
1003900275
1001900270
100490020

解释：高难度试卷有9001、9002、9003；

作答了9001的记录有3条，分数分别为68、89、90，按给定公式归一化后分数为：0、95、100，而后两个得分都是用户1001作答的，因此用户1001对试卷9001的新得分为(95+100)/2≈98（只保留整数部分），用户1003对于试卷9001的新得分为0。最后结果按照试卷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_bin;

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, 'C++', 'hard', 80, '2020-01-01 10:00:00'),
  (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00'),
  (9005, 'WEB', 'hard', 80, '2020-01-01 10:00:00'),
  (9006, 'PYTHON', 'hard', 80, '2020-01-01 10:00:00'),
  (9007, 'web', 'hard', 80, '2020-01-01 10:00:00'),
  (9008, 'Web', 'medium', 70, '2020-01-01 10:00:00'),
  (9009, 'WEB', 'medium', 70, '2020-01-01 10:00:00'),
  (9010, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
	
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75),
(1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60),
(1003, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90),
(1002, 9002, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 90),
(1003, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 68),
(1001, 9002, '2020-01-02 12:01:01', '2020-01-02 12:43:01', 81),
(1001, 9005, '2020-01-02 12:11:01', null, null),
(1001, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),
(1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
(1002, 9004, '2021-09-06 12:01:01', null, null),
(1002, 9002, '2021-05-05 18:01:01', null, null);
说明：分数归一化

假设集合A 收入的最小值和最大值分别为 12,000 美元和 98,000 美元。我们想将收入映射到范围 [1, 100]。那么73,600 美元映射到多少？
在这里插入图片描述

思路
1.统计每次高难度试卷被完成的分数以及该卷的最高最低分
2.关联试卷作答表和试卷信息表:exam_record JOIN examination_info USING(exam_id)
3.筛选做完了高难度的记录：WHERE score IS NOT NULL AND difficulty="hard"
4.按用户ID和试卷ID分组：GROUP BY uid, exam_id
5.计算归一化后分数，需分情况讨论：
如果最高分和最低分相等，即只有一个分值：直接返回原分值
否则归一化后取整：ROUND(AVG((score - min_score) / (max_score - min_score) * 100))
题解
方式一：
select 
uid,
exam_id,
if(is_score=1,mix_score,
   round(avg(((score-mix_score)/(max_score-mix_score))*100),0)) avg_new_score
from exam_record
inner join
(
select  exam_id, min(score) mix_score,max(score) max_score, if(min(score)=max(score),1,0) is_score
from exam_record 
inner join examination_info 
using(exam_id)
where difficulty='hard' and score IS NOT NULL
group by exam_id
) t1
using(exam_id)
where score is not null
group by uid,exam_id
order by exam_id,avg_new_score desc



方式二：
SELECT uid,exam_id,
ROUND(SUM(max_min)/COUNT(max_min),0) avg_new_score
FROM (
	SELECT exam_id,uid,score,
	IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min/*如果某试卷只有一个得分*/
	FROM (
		SELECT uid,a.exam_id,score,
		MIN(score) OVER(PARTITION BY exam_id) min_x, #求每类试卷的得分最小值
		MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值
		FROM exam_record a
		LEFT JOIN examination_info b ON a.exam_id=b.exam_id
		WHERE difficulty='hard' #难度为'hard'
		AND score IS NOT NULL #分数不为空
		)t1
		
	)t2

GROUP BY exam_id,uid
ORDER BY exam_id,avg_new_score DESC;#按照试卷ID升序，评价分降序排序

3.每份试卷每月作答数和截止当月的作答总数

现有试卷作答记录表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
2100290012020-01-20 10:01:012020-01-20 10:10:0189
3100290012020-02-01 12:11:012020-02-01 12:31:0183
4100390012020-03-01 19:01:012020-03-01 19:30:0175
5100490012020-03-01 12:01:012020-03-01 12:11:0160
6100390012020-03-01 12:01:012020-03-01 12:41:0190
7100290012020-05-02 19:01:012020-05-02 19:32:0090
8100190022020-01-02 19:01:012020-01-02 19:59:0169
9100490022020-02-02 12:01:012020-02-02 12:20:0199
10100390022020-02-02 12:01:012020-02-02 12:31:0168
11100190022020-02-02 12:01:012020-02-02 12:43:0181
12100190022020-03-02 12:11:01(NULL)(NULL)

请输出每份试卷每月作答数和截止当月的作答总数。

示例1
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);

输出

9001|202001|2|2
9001|202002|1|3
9001|202003|3|6
9001|202005|1|7
9002|202001|1|1
9002|202002|3|4
9002|202003|1|5

思路
1.求每份试卷每个月的作答数，要以exam_id，开始作答时间的月份进行分组。直接获取试卷ID、作答月份。
2.对每组开始作答时间计数得到每份试卷每个月的作答数
3.以试卷分组，月份递增获取每份试卷每个月的累计和。sum() over() as cum_exam_cnt
题解
方式一：
select 
    exam_id,
    start_month, 
    month_cnt,
    SUM(month_cnt) over(
    partition by exam_id
    order by start_month) as cum_exam_cnt
    from 
    (
    select exam_id,
        DATE_FORMAT(start_time, "%Y%m")as start_month,
        count(start_time) month_cnt
    FROM exam_record
    group by exam_id, DATE_FORMAT(start_time, "%Y%m")-- 每份试卷被作答月份
    )t

方式二：
	SELECT DISTINCT
    exam_id,
    DATE_FORMAT(start_time,'%Y%m') start_month,
    count(start_time) over(
        partition by exam_id,DATE_FORMAT(start_time,'%Y%m')
    ) month_cnt,
    count(start_time) over(
        partition by exam_id
        order by DATE_FORMAT(start_time,'%Y%m') 
    ) cum_exam_cnt
FROM
    exam_record

4. 每月及截止当月的答题情况

现有试卷作答记录表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
2100290012020-01-20 10:01:012020-01-20 10:10:0189
3100290012020-02-01 12:11:012020-02-01 12:31:0183
4100390012020-03-01 19:01:012020-03-01 19:30:0175
5100490012020-03-01 12:01:012020-03-01 12:11:0160
6100390012020-03-01 12:01:012020-03-01 12:41:0190
7100290012020-05-02 19:01:012020-05-02 19:32:0090
8100190022020-01-02 19:01:012020-01-02 19:59:0169
9100490022020-02-02 12:01:012020-02-02 12:20:0199
10100390022020-02-02 12:01:012020-02-02 12:31:0168
11100190022020-01-02 19:01:012020-02-02 12:43:0181
12100190022020-03-02 12:11:01(NULL)(NULL)

请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。
由示例数据结果输出如下：

start_monthmaumonth_add_uvmax_month_add_uvcum_sum_uv
2020012222
2020024224
2020033024
2020051024
说明
month1001100210031004
20200111
2020021111
202003111
2020051

由上述矩阵可以看出，2020年1月有2个用户活跃（mau=2），当月新增用户数为2；

2020年2月有4个用户活跃，当月新增用户数为2，最大单月新增用户数为2，当前累积用户数为4。

实例1
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);
输出

202001|2|2|2|2
202002|4|2|2|4
202003|3|0|2|4
202005|1|0|2|4

思路
1.统计每月用户第一次出现的月份 min()over()
2.统计用户作答的次数和最大的用户作答数 
count(distinct uid) as mau,
count(distinct case when start_month=first_month then uid else null end)as month_add_uv	
3.计算用户作答月份最大的数和截止当月的累积用户数 
max(month_add_uv)over(order by start_month) max_month_add_uv,
sum(month_add_uv)over(order by start_month) cum_sum_uv
题解

    select 
    start_month,mau,month_add_uv,
    max(month_add_uv)over(order by start_month) max_month_add_uv,
    sum(month_add_uv)over(order by start_month) cum_sum_uv
    from (
    select 
        start_month,
        count(distinct uid) as mau,
        count(distinct case
              when start_month=first_month then uid
              else null end) as month_add_uv	
    from (
    select 
    uid,
    date_format(start_time,"%Y%m")as start_month,
    min(date_format(start_time,"%Y%m"))
    over(partition by uid 
    order by date_format(start_time,"%Y%m"))first_month
    from exam_record
    )t
    group by start_month
)t1

5.统计有未完成状态的试卷的未完成数和未完成率

现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分），数据如下：

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-09-02 12:01:01(NULL)(NULL)

请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate。由示例数据结果输出如下：

exam_idincomplete_cntcomplete_rate
900110.333

解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，因此未完成数为1，未完成率为0.333（保留3位小数）

示例1
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),
(1001, 9001, '2021-09-02 12:01:01', null, null);
题解
select exam_id,
sum(if(submit_time is null,1,0))incomplete_cnt,
round(sum(if(submit_time is null,1,0))/count(exam_id),3) complete_rate
from exam_record
group by exam_id
having incomplete_cnt>0
林园857
关注
  • 2
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
牛客SQL141——试卷完成数同比2020年的增长率及排名变化
weixin_51350949的博客
03-27 148
请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。1.先计算2020,2021年上半年每一类试卷。
MYSQL学习笔记（5）——聚合窗口函数
A133333的博客
03-22 2223
题目复盘——聚合窗口函数1 SQL33 对试卷得分做min-max归一化 问题分析 （1）高难度试卷——exam_record left join examination_info on…where difficulty=‘hard’ （2）得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]—— 聚合窗口函数找每个exam_id试卷内的最大最小值： select uid,exam_id, score,min(score)over(partition by exam_id) min_
写一些简单的SQL
m0_67864787的博客
11-02 188
SELECT AVG(tc.chengji) 语文,AVG(tc1.chengji) 数学,AVG(tc2.chengji) 英语 FROM t_kecheng tk。SELECT ts.id,TS.stuname,tc1.chengji 语文,tc2.chengji 数学,tc3.chengji 英语,平均值。SUM(IF(tc.kechengid=1,tc.chengji,0)) as '语文',SUM(IF(tc.kechengid=2,tc.chengji,0)) as '数学',
SQL 进阶挑战篇
withoutfear的博客
02-26 1636
01增删改操作 插入记录 SQL1 插入记录（一） 现在有两个用户的作答记录详情如下： 用户1001在2021年9月1日晚上10点11分12秒开始作答试卷9001，并在50分钟后提交，得了90分； 用户1002在2021年9月4日上午7点1分2秒开始作答试卷9002，并在10分钟后退出了平台。 请用一条语句将这两条记录插入表中。 INSERT INTO exam_record VALUES (null, 1001, 9001, '2021-09-01 22:11:12', '2021-09-.
SQL33 对试卷得分做min-max归一化
weixin_48744808的博客
02-27 1183
SQL33 对试卷得分做min-max归一化 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）： 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）： 在物理学及统计学数据计算时，有个概念叫min-max标准化，也被称为离差标准化，是对原始数据的线性变
MySQL数据库进阶实战 完整版PDF.pdf
05-07
内容概要：《冬季实战营第三期：MySQL数据库进阶实战》是阿里云云起实验室推出的一个云上学习活动，旨在帮助开发者通过最佳实践快速实践和学习云上技能。本电子手册共包含四个实战章节，涵盖了MySQL数据库的快速部署...
MS SQL入门-进阶-实战培训.pdf
10-30
1.3 SQL 管理工具介绍   1.3.1 SQL server配置管理器   1.3.2 SQL server网络配置   1.3.3 连接SQL server服务器   1.3.4 服务器属性配置   1.3.5 命令行下的SQL管理工具   2 设计与管理数据库和对象  ...
数据库MySQL进阶实战
03-31
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下产 品。MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS ... 基本 SQL 语句的使用
牛客网站数据库SQL实战题目
最新发布
05-17
题目特点： 多样性： 题目涵盖了广泛的SQL知识点，包括基本查询、复杂查询、聚合函数、子查询、联接（JOIN）、视图、存储...初学者可以通过简单题目打牢基础，进阶用户可以挑战高难度题目，提升解决复杂问题的能力。
hive进阶强化必备
08-23
个人总结的hive快速提升能力的快捷训练方式供参考
Sql之面试题总结
qq_42720463的博客
03-04 2274
1. 每月及截止当月的答题情况 【题目】： 现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分） 请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数： 【解题思路】： Keywords：活跃用户数、新增用户数、截止当月单月新增最大、截止当月的累积用户数 活跃用户数：每月有提交记录的用户 COUNT(DISTIN
Mysql窗口函数 (知识点梳理+题目解析+面试实战）(四万字长文，一文读懂，建议收藏后食用)
weixin_45532984的博客
12-19 2806
窗口函数作为Mysql 8的新特性以及面试笔试的重点，掌握并且进行来练习是非常有必要的。本文章详细介绍窗口函数的方方面面，包含知识点梳理，简单案例，题目解析和面试实战，阅读需要约一定的时间，建议收藏后食用。其中在解题部分更是包含了题目分析和要求梳理，解体解答和答案总和，题目由易到难，更包含牛客会员类型题目，其中解体解答花费了很大心思。毫不夸张地说，弄懂本篇文章，可以应付基本所有的面试笔试，尤其是数据开发岗和数据分析岗。
牛客-SQL篇-SQL进阶挑战-06其他常用操作-空值处理-SQL145统计有未完成状态的试卷的未完成数和未完成率
SunshineLu2019的博客
11-27 362
解释：试卷9001有3次被作答的记录，其中两次完成，1次未完成，因此未完成数为1，未完成率为0.333（保留3位小数）id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',start_time datetime NOT NULL COMMENT '开始时间',exam_id int NOT NULL COMMENT '试卷ID',submit_time datetime COMMENT '提交时间',score tinyint COMMENT '得分'
【Kay】SQL窗口函数题目
m0_54115636的博客
03-07 295
一、每类试卷得分前3名 现有试卷信息表examination_info 试卷作答记录表exam_record 请找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者 实例结果： 代码如下： SELECT te.tid,uid,ranking FROM( SELECT tag AS tid, uid, ROW_NUMBER() OVER(PARTITION BY tag .
ORACLE计算同比环比
热门推荐
起止洺的博客
07-06 1万+
同比环比概念和公式 同比: 同比一般情况下是今年第n月与去年第n月比 环比: 表示连续2个统计周期（比如连续两月）内的量的变化比。 同比增长率=（本期数-同期数）/|同期数|×100% 环比增长率=（本期数-上期数）/上期数×100% 需求: 计算2020年01月到2020年2月, 每个月的本期值和环比和同比率 数据库 表和数据准备 CREATE TABLE test ( "ID" NUMBER NOT NULL , "VALUE" NUMBER , "YEARMONTH" VARCHAR2(6
oracle 同比增长率计算
有花堪折直须折，莫待无花空折枝。
03-19 2532
同比计算公式：同比增长率=(本年-上年)/上年*100% 例如计算某个东西的绝对量的年度同比增长率 NVL (ROUND (NORM_VLAUE / LAG (NORM_VLAUE) OVER (ORDER BY NORM_NAME, "YEAR") * 100,2) - 100,'0') 计算公式： ROUND (NORM_VLAUE / LAG (NORM_VLAUE) OVE...
oracle 月增长率,oracle同比增长怎么算,同比增长率怎么算？
weixin_28736355的博客
04-13 1291
同比就是个上一阶段相比环比就是。。。还是举例子吧， 今年6月和去年6月对比就是环比同比增长的意思：比如你有10000元，存进了银行，过了一段时间，他变成11000元，相比原来增长了10%，这就是同比增长。　同比增长计算公式 　　同比增长率=(本期数－同期数)÷同期数×100%参考资料：自己理解环比发展速度=本期数÷上期数×100% 。环比增长率=(本期数-上期数)/上期数×100%。环比，表示连续...
oracle计算占比和环比增长率案例
。
05-12 2868
根据科室、时间维度分组， 对不同科室人数、科室总金额进行统计汇总，以及计算科室不同药品的使用情况 如：各药品的占比、环比增长率等等
sql 中 exists和 in如何选择
znanj的博客
09-09 861
如果外部查询是“大”和内部查询是“小”，IN是一般效率比EXISTS好。 如果外部查询是“小”和内部查询是“大”，那么EXISTS是相当有效的。
spark机器学习进阶实战 pdf
11-03
《Spark机器学习进阶实战》是一本关于使用Spark框架进行高级机器学习实践的教程。它涵盖了许多Spark的高级特性和机器学习算法，可以帮助读者更深入地理解和应用这两个领域。 这本书首先介绍了Spark的基本概念和编程...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值