现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):
id | uid | nick_name | achievement | level | job | register_time |
---|---|---|---|---|---|---|
1 | 1001 | 牛客1号 | 3100 | 7 | 算法 | 2020-01-01 10:00:00 |
2 | 1002 | 牛客2号 | 2300 | 7 | 算法 | 2020-01-01 10:00:00 |
3 | 1003 | 牛客3号 | 2500 | 7 | 算法 | 2020-01-01 10:00:00 |
4 | 1004 | 牛客4号 | 1200 | 5 | 算法 | 2020-01-01 10:00:00 |
5 | 1005 | 牛客5号 | 1600 | 6 | C++ | 2020-01-01 10:00:00 |
6 | 1006 | 牛客6号 | 2600 | 7 | C++ | 2020-01-01 10:00:00 |
试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
id | exam_id | tag | difficulty | duration | release_time |
---|---|---|---|---|---|
1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
2 | 9002 | C++ | easy | 60 | 2021-09-01 06:00:00 |
3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|
1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
1005 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
1005 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:59 | 84 |
1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 81 |
1002 | 9001 | 2020-09-01 13:01:01 | 2020-09-01 13:41:01 | 81 |
1005 | 9001 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
题目练习记录表practice_record(uid用户ID, question_id题目ID, submit_time提交时间, score得分):
uid | question_id | submit_time | score |
---|---|---|---|
1001 | 8001 | 2021-08-02 11:41:01 | 60 |
1004 | 8001 | 2021-08-02 19:38:01 | 70 |
1004 | 8002 | 2021-08-02 19:48:01 | 90 |
1001 | 8002 | 2021-08-02 19:38:01 | 70 |
1004 | 8002 | 2021-08-02 19:48:01 | 90 |
1006 | 8002 | 2021-08-04 19:58:01 | 94 |
1006 | 8003 | 2021-08-03 19:38:01 | 70 |
1006 | 8003 | 2021-08-02 19:48:01 | 90 |
1006 | 8003 | 2020-08-01 19:38:01 | 80 |
请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数,按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下:
uid | act_month_total | act_days_2021 | act_days_2021_exam | act_days_2021_question |
---|---|---|---|---|
1006 | 3 | 4 | 1 | 3 |
1001 | 2 | 2 | 1 | 1 |
1005 | 1 | 1 | 1 | 0 |
1002 | 1 | 0 | 0 | 0 |
1003 | 0 | 0 | 0 | 0 |
解释:6/7级用户共有5个,其中1006在202109、202108、202008共3个月活跃过,2021年活跃的日期有20210907、20210804、20210803、20210802共4天,2021年在试卷作答区20210907活跃1天,在题目练习区活跃了3天。
输入:
drop table if exists examination_info,user_info,exam_record,practice_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 user_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE practice_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
question_id int NOT NULL COMMENT '题目ID',
submit_time datetime COMMENT '提交时间',
score tinyint 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 user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
(1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
(1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
(1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
(1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
(1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
(1006, '牛客6号', 2600, 7, 'C++', '2020-01-01 10:00:00');
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
(9002, 'C++', 'easy', 60, '2021-09-01 06:00:00'),
(9003, '算法', 'medium', 80, '2021-09-01 10:00:00');
INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-08-01 19:38:01', 80);
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:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1005, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);
输出:
1006|3|4|1|3
1001|2|2|1|1
1005|1|1|1|0
1002|1|0|0|0
1003|0|0|0|0
SELECT
ui.uid,
COUNT(DISTINCT LEFT(s,6)) AS act_month_total,
COUNT(DISTINCT IF(LEFT(s,4)='2021',RIGHT(s,4),null)) AS act_days_2021,
COUNT(DISTINCT IF(LEFT(s,4)='2021' AND tag='e',RIGHT(s,4),null)) AS act_days_2021_exam,
COUNT(DISTINCT IF(LEFT(s,4)='2021' AND tag='p',RIGHT(s,4),null)) AS act_days_2021_question
FROM (
SELECT uid,DATE_FORMAT(submit_time,'%Y%m%d') AS s,'p' tag FROM practice_record pr
UNION ALL
SELECT uid,DATE_FORMAT(start_time,'%Y%m%d') AS s,'e' AS tag FROM exam_record er
)mon
RIGHT JOIN user_info ui
ON ui.uid = mon.uid
WHERE ui.level >5
GROUP BY uid
ORDER BY act_month_total DESC,act_days_2021 DESC;
现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
id | exam_id | tag | difficulty | duration | release_time |
---|---|---|---|---|---|
1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
2 | 9002 | SQL | hard | 60 | 2021-09-01 06:00:00 |
3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 78 |
2 | 1002 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
3 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
4 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:40:01 | 86 |
5 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
6 | 1004 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
7 | 1005 | 9003 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
8 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
9 | 1003 | 9003 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
10 | 1003 | 9002 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
找到每类试卷得分的前3名,如果两人最大分数相同,选择最小分数大者,如果还相同,选择uid大者。由示例数据结果输出如下:
tid | uid | ranking |
---|---|---|
SQL | 1003 | 1 |
SQL | 1004 | 2 |
SQL | 1002 | 3 |
算法 | 1005 | 1 |
算法 | 1006 | 2 |
算法 | 1003 | 3 |
解释:有作答得分记录的试卷tag有SQL和算法,SQL试卷用户1001、1002、1003、1004有作答得分,最高得分分别为81、81、89、85,最低得分分别为78、81、86、40,因此先按最高得分排名再按最低得分排名取前三为1003、1004、1002。
输入:
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, 'SQL', '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:31:00', 78),
(1001, 9001, '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:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1003, 9003, '2021-09-08 12:01:01', '2021-09-08 12:11:01', 40),
(1003, 9002, '2021-09-01 14:01:01', null, null);
输出:
SQL|1003|1
SQL|1004|2
SQL|1002|3
算法|1005|1
算法|1006|2
算法|1003|3
首先筛选出一个各类标签与用户和排名的结果集。
SELECT tag, exam_record.uid,
row_number() over (PARTITION BY tag ORDER BY tag,
MAX(score) DESC,
MIN(score) DESC, exam_record.uid DESC) ranking
FROM exam_record JOIN examination_info
ON exam_record.exam_id = examination_info.exam_id
GROUP BY tag, exam_record.uid;
然后就是从结果集中选出排名不大于 3 的标签、用户 ID
和排名,即 SELECT ... FROM ... WHERE...
。
SELECT tag, uid, ranking
FROM(
SELECT tag, exam_record.uid,
row_number() over (PARTITION BY tag ORDER BY tag,
MAX(score) DESC,
MIN(score) DESC, exam_record.uid DESC) ranking
FROM exam_record JOIN examination_info
ON exam_record.exam_id = examination_info.exam_id
GROUP BY tag, exam_record.uid
) ranktable
WHERE ranking <= 3;
现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
id | exam_id | tag | difficulty | duration | release_time |
---|---|---|---|---|---|
1 | 9001 | SQL | hard | 60 | 2021-09-01 06:00:00 |
2 | 9002 | C++ | hard | 60 | 2021-09-01 06:00:00 |
3 | 9003 | 算法 | medium | 80 | 2021-09-01 10:00:00 |
试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1001 | 9001 | 2021-09-01 09:01:01 | 2021-09-01 09:51:01 | 78 |
2 | 1001 | 9002 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 | 81 |
3 | 1002 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 | 81 |
4 | 1003 | 9001 | 2021-09-01 19:01:01 | 2021-09-01 19:59:01 | 86 |
5 | 1003 | 9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:51 | 89 |
6 | 1004 | 9002 | 2021-09-01 19:01:01 | 2021-09-01 19:30:01 | 85 |
7 | 1005 | 9001 | 2021-09-01 12:01:01 | 2021-09-01 12:31:02 | 85 |
8 | 1006 | 9001 | 2021-09-07 10:01:01 | 2021-09-07 10:21:01 | 84 |
9 | 1003 | 9001 | 2021-09-08 12:01:01 | 2021-09-08 12:11:01 | 40 |
10 | 1003 | 9002 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
11 | 1005 | 9001 | 2021-09-01 14:01:01 | (NULL) | (NULL) |
12 | 1003 | 9003 | 2021-09-08 15:01:01 | (NULL) | (NULL) |
找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息,按试卷ID降序排序。由示例数据结果输出如下:
exam_id | duration | release_time |
---|---|---|
9001 | 60 | 2021-09-01 06:00:00 |
解释:试卷9001被作答用时有50分钟、50分钟、30分1秒、11分钟、10分钟,第二快和第二慢用时之差为50分钟-11分钟=39分钟,试卷时长为60分钟,因此满足大于试卷时长一半的条件,输出试卷ID、时长、发布时间。
输入:
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);
输出:
9001|60|2021-09-01 06:00:00
根据问题将题目进行拆解,首先将每套试卷完成第二快和完成第二慢的用时以及试卷信息统计出来。
SELECT exam_id, duration, release_time
FROM (
SELECT DISTINCT exam_id, duration, release_time,
NTH_VALUE(time_took, 2) OVER (
PARTITION BY exam_id ORDER BY time_took DESC) as max2nd_time,
NTH_VALUE(time_took, 2) OVER (
PARTITION BY exam_id ORDER BY time_took ASC) as min2nd_time
FROM (
SELECT exam_id, duration, release_time,
TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
FROM exam_record JOIN examination_info USING(exam_id)
WHERE submit_time IS NOT NULL
) as exam_record_timetook
) as exam_time_took;
然后筛选出第二快和第二慢用时之差大于试卷时长一半的试卷,即 WHERE max2nd_time - min2nd_time > duration / 2
。
SELECT exam_id, duration, release_time
FROM (
SELECT DISTINCT exam_id, duration, release_time,
NTH_VALUE(time_took, 2) OVER (
PARTITION BY exam_id ORDER BY time_took DESC) as max2nd_time,
NTH_VALUE(time_took, 2) OVER (
PARTITION BY exam_id ORDER BY time_took ASC) as min2nd_time
FROM (
SELECT exam_id, duration, release_time,
TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
FROM exam_record JOIN examination_info USING(exam_id)
WHERE submit_time IS NOT NULL
) as exam_record_timetook
) as exam_time_took
WHERE max2nd_time - min2nd_time > duration / 2
ORDER BY exam_id DESC;
现有试卷作答记录表exam_record(uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分):
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1006 | 9003 | 2021-09-07 10:01:01 | 2021-09-07 10:21:02 | 84 |
2 | 1006 | 9001 | 2021-09-01 12:11:01 | 2021-09-01 12:31:01 | 89 |
3 | 1006 | 9002 | 2021-09-06 10:01:01 | 2021-09-06 10:21:01 | 81 |
4 | 1005 | 9002 | 2021-09-05 10:01:01 | 2021-09-05 10:21:01 | 81 |
5 | 1005 | 9001 | 2021-09-05 10:31:01 | 2021-09-05 10:51:01 | 81 |
请计算在2021年至少有两天作答过试卷的人中,计算该年连续两次作答试卷的最大时间窗days_window,那么根据该年的历史规律他在days_window天里平均会做多少套试卷,按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下:
uid | days_window | avg_exam_cnt |
---|---|---|
1006 | 6 | 2.57 |
解释:用户1006分别在20210901、20210906、20210907作答过3次试卷,连续两次作答最大时间窗为6天(1号到6号),他1号到7号这7天里共做了3张试卷,平均每天3/7=0.428571张,那么6天里平均会做0.428571*6=2.57张试卷(保留两位小数);
用户1005在20210905做了两张试卷,但是只有一天的作答记录,过滤掉。
示例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
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);
输出:
1006|6|2.57
SELECT uid,
COUNT(start_time) exam_cnt,
DATEDIFF(MAX(start_time), MIN(start_time)) + 1 diff_days,
MAX(DATEDIFF(next_start_time, start_time)) + 1 days_window
FROM (
SELECT uid, exam_id, start_time,
lead(start_time) over(
PARTITION BY uid ORDER BY start_time) next_start_time
FROM exam_record
WHERE YEAR(start_time)=2021
) exam_record_lead
GROUP BY uid;
WHERE diff_days > 1
。ROUND(days_window * exam_cnt / diff_days, 2) avg_exam_cnt
。SELECT uid, days_window, ROUND(days_window * exam_cnt / diff_days, 2) avg_exam_cnt
FROM (
SELECT uid,
COUNT(start_time) exam_cnt,
DATEDIFF(MAX(start_time), MIN(start_time)) + 1 diff_days,
MAX(DATEDIFF(next_start_time, start_time)) + 1 days_window
FROM (
SELECT uid, exam_id, start_time,
lead(start_time) over(
PARTITION BY uid ORDER BY start_time) next_start_time
FROM exam_record
WHERE YEAR(start_time)=2021
) exam_record_lead
GROUP BY uid
) exam_record
WHERE diff_days > 1
ORDER BY days_window DESC, avg_exam_cnt DESC;
现有试卷作答记录表exam_record(uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间,为空的话则代表未完成, score:得分):
id | uid | exam_id | start_time | submit_time | score |
---|---|---|---|---|---|
1 | 1006 | 9003 | 2021-09-06 10:01:01 | 2021-09-06 10:21:02 | 84 |
2 | 1006 | 9001 | 2021-08-02 12:11:01 | 2021-08-02 12:31:01 | 89 |
3 | 1006 | 9002 | 2021-06-06 10:01:01 | 2021-06-06 10:21:01 | 81 |
4 | 1006 | 9002 | 2021-05-06 10:01:01 | 2021-05-06 10:21:01 | 81 |
5 | 1006 | 9001 | 2021-05-01 12:01:01 | (NULL) | (NULL) |
6 | 1001 | 9001 | 2021-09-05 10:31:01 | 2021-09-05 10:51:01 | 81 |
7 | 1001 | 9003 | 2021-08-01 09:01:01 | 2021-08-01 09:51:11 | 78 |
8 | 1001 | 9002 | 2021-07-01 09:01:01 | 2021-07-01 09:31:00 | 81 |
9 | 1001 | 9002 | 2021-07-01 12:01:01 | 2021-07-01 12:31:01 | 81 |
10 | 1001 | 9002 | 2021-07-01 12:01:01 | (NULL) | (NULL) |
找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数,按试卷完成数和用户ID降序排名。由示例数据结果输出如下:
uid | exam_complete_cnt |
---|---|
1006 | 3 |
解释:用户1006近三个有作答试卷的月份为202109、202108、202106,作答试卷数为3,全部完成;用户1001近三个有作答试卷的月份为202109、202108、202107,作答试卷数为5,完成试卷数为4,因为有未完成试卷,故过滤掉。
输入:
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
(1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84),
(1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89),
(1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81),
(1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81),
(1006, 9001, '2021-05-01 12:01:01', null, null),
(1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78),
(1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 81),
(1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 81),
(1001, 9002, '2021-07-01 12:01:01', null, null);
输出:
1006|3
问题拆解:
exam_record
表中筛选出用户 ID
、答题开始时间、得分,并按照月份的逆序排列。SELECT uid, start_time, score,
dense_rank() OVER(PARTITION BY uid ORDER BY date_format(start_time, '%Y%m') DESC;
SELECT uid,
COUNT(score) AS exam_complete_cnt
FROM [结果集] WHERE recent_months <= 3;
ID
降序输出,得到最终结果如下。SELECT uid,
COUNT(score) AS exam_complete_cnt
FROM(
SELECT uid, start_time, score,
dense_rank() OVER(PARTITION BY uid ORDER BY date_format(start_time, '%Y%m') DESC)
AS recent_months
FROM exam_record
) recent_table
WHERE recent_months <= 3
GROUP BY uid
HAVING COUNT(score) = COUNT(uid)
ORDER BY exam_complete_cnt DESC, uid DESC;