【牛客刷题-SQL进阶挑战】NO5.窗口函数
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
- 前言
前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
1 🌈 专用窗口函数
🚀 SQL27 每类试卷得分前3名
📖 examination_info 表结构
📖 exam_record表结构
🚀 题目描述现有试卷信息表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 | 1001 | 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大者。由示例数据结果输出如下：+--------+------+----+| tag | uid | rk |+--------+------+----+| 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);🍌🍌 答案select tag,uid,rkfrom ( select tag, uid, rank() over ( partition by tag order by tag,max(score) desc,min(score) desc,uid desc) as rk from exam_record er left join examination_info ei on er.exam_id = ei.exam_id group by tag,uid) as twhere rk<=3;
🚀 SQL28 第二快/慢用时之差大于试卷时长一半的试卷
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述现有试卷信息表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:12: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降序排序。由示例数据结果输出如下：解释：试卷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);🍌🍌 答案SELECT distinct exam_id, duration, release_timeFROM (SELECT exam_id, duration, release_time, sum(case when rank1= 2 then costtime when rank2= 2 then -costtime else 0 end ) as sub from(SELECT exam_id,duration, release_time,TIMESTAMPDIFF(minute,start_time, submit_time) as costtime,row_number() over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time, submit_time) desc) as rank1,row_number() over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time, submit_time) asc) as rank2FROM exam_recordJOIN examination_info USING (exam_id) where submit_time is not null ) a group by exam_id ) b where sub * 2 >= duration order by exam_id desc;
🚀SQL29 连续两次作答试卷的最大时间窗
📖 exam_record 表结构
🚀 题目描述现有试卷作答记录表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做了两张试卷，但是只有一天的作答记录，过滤掉。🚀 建表语句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);🍌🍌 答案SELECT uid,max(day1)+1 days_window,round((count(*)/(sum(day1)+1))*(max(day1)+1),2) avg_exam_cntfrom(select uid,date1,lag1,datediff(date1,lag1) day1from(select uid,date(start_time) date1,lag(date(start_time),1,date(start_time)) over(partition by uid order by start_time) lag1from exam_record where uid in(select uid from exam_record where year(start_time)=2021group by uid having count(distinct date(start_time))>1) and year(start_time)=2021) a) bgroup by uidorder by days_window desc,avg_exam_cnt desc;备注：按最大时间窗和平均做答试卷套数倒序排序，保留两位小数
🚀 SQL30 近三个月未完成试卷数为0的用户完成情况
📖 exam_record 表结构
🚀 题目描述现有试卷作答记录表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);select a.uid, count(a.start_time) as exam_complete_cntFROM( select uid, start_time, submit_time, DENSE_RANK() over(partition by uid order by date_format(start_time, '%Y%m') desc) as time_rank from exam_record) as awhere a.time_rank <= 3group by a.uidhaving count(a.start_time) = count(a.submit_time)order by exam_complete_cnt desc, uid desc;
🚀 SQL31 未完成率较高的50%用户近三个月答卷情况
📖 user_info 表结构
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：+----+------+---------------+-------------+-------+--------+---------------------+| id | uid | nick_name | achievement | level | job | register_time|+----+------+---------------+-------------+-------+--------+---------------------+| 1 | 1001 | 牛客1 | 3200 | 7 | 算法 | 2020-01-01 10:00:00 || 2 | 1002 | 牛客2号| 2500 | 6 | 算法 | 2020-01-01 10:00:00 || 3 | 1003 | 牛客3号♂ | 2200 | 5 | 算法 | 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 | 2020-01-01 10:00:00 || 2 | 9002 | SQL | hard|80 | 2020-01-01 10:00:00 || 3 | 9003 | 算法 | hard|80 | 2020-01-01 10:00:00 || 4 | 9004 | PYTHON | medium |70 | 2020-01-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 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 90 || 2 | 1002 | 9001 | 2020-01-20 10:01:01 | NULL | NULL || 3 | 1002 | 9001 | 2020-02-01 12:11:01 | NULL | NULL || 4 | 1003 | 9001 | 2020-03-01 19:01:01 | NULL | NULL || 5 | 1001 | 9001 | 2020-03-01 12:01:01 | NULL | NULL || 6 | 1002 | 9001 | 2020-03-01 12:01:01 | 2020-03-01 12:41:01 | 90 || 7 | 1002 | 9001 | 2020-05-02 19:01:01 | 2020-05-02 19:32:00 | 90 || 8 | 1001 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:59:01 | 69 || 9 | 1001 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:20:01 | 99 || 10 | 1002 | 9002 | 2020-02-02 12:01:01 | NULL | NULL || 11 | 1002 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:43:01 | 81 || 12 | 1002 | 9002 | 2020-03-02 12:11:01 | NULL | NULL || 13 | 1001 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 || 14 | 1001 | 9002 | 2020-01-01 12:11:01 | NULL | NULL || 15 | 1002 | 9001 | 2020-01-01 18:01:01 | 2020-01-01 18:59:02 | 90 || 16 | 1002 | 9003 | 2020-05-06 12:01:01 | NULL | NULL || 17 | 1001 | 9002 | 2020-05-05 18:01:01 | NULL | NULL |+----+------+---------+---------------------+---------------------+-------+请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。由示例数据结果输出如下：+------+-------------+-----------+--------------+| uid | start_month | total_cnt | complete_cnt |+------+-------------+-----------+--------------+| 1002 | 202002 | 3 | 1 || 1002 | 202003 | 2 | 1 || 1002 | 202005 | 2 | 1 |+------+-------------+-----------+--------------+解释：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。🚀 建表语句drop table if exists examination_info,user_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 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 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', 3200, 7, '算法', '2020-01-01 10:00:00'), (1002, '牛客2号', 2500, 6, '算法', '2020-01-01 10:00:00'), (1003, '牛客3号♂', 2200, 5, '算法', '2020-01-01 10:00:00');INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'SQL', '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-01-01 09:01:01', '2020-01-01 09:21:59', 90),(1002, 9001, '2020-01-20 10:01:01', null, null),(1002, 9001, '2020-02-01 12:11:01', null, null),(1003, 9001, '2020-03-01 19:01:01', null, null),(1001, 9001, '2020-03-01 12:01:01', null, null),(1002, 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),(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),(1002, 9002, '2020-02-02 12:01:01', null, null),(1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),(1002, 9002, '2020-03-02 12:11:01', null, null),(1001, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89),(1001, 9002, '2020-01-01 12:11:01', null, null),(1002, 9001, '2020-01-01 18:01:01', '2020-01-01 18:59:02', 90),(1002, 9003, '2020-05-06 12:01:01', null, null),(1001, 9002, '2020-05-05 18:01:01', null, null);# 第一步，先找出未完成率前50%高的用户ID，注意这里需要的sql试卷with rote_tab as (select t.uid,t.f_rote,row_number()over(order by t.f_rote desc,uid) as rank2,count(t.uid)over(partition by t.tag)as cntfrom (select er.uid,ef.tag,(sum(if(submit_time is null,1,0))/count(start_time)) as f_rotefrom exam_record er left join examination_info ef on ef.exam_id=er.exam_id where tag='SQL' group by uid ) t)select #第四步，分用户和月份进行数据统计；同时需要注意，统计的试卷数是所有类型的，不是之前仅有SQL类型 uid ,start_month ,count(start_time) as total_cnt ,count(submit_time) as complete_cntfrom (select # 第三步，利用窗口函数对每个用户的月份进行降序排序，以便找出最近的三个月； uid ,start_time ,submit_time ,date_format(start_time,'%Y%m') as start_month ,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as rank3from exam_record where uid in (select distinct er.uid from exam_record er left join user_info uf on uf.uid=er.uid where er.uid in (select uid from rote_tab where rank2<=round(cnt/2,0)) and uf.level in (6,7)) # 第二步，进一步找出满足等级为6或7的用户ID) t2where rank3<=3group by uid,start_monthorder by uid,start_month;
🚀 SQL32 试卷完成数同比2020年的增长率及排名变化
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：+----+---------+--------+------------+----------+---------------------+| id | exam_id | tag | difficulty | duration | release_time |+----+---------+--------+------------+----------+---------------------+| 1 | 9001 | SQL | hard|60 | 2020-01-01 10:00:00 || 2 | 9002 | C++ | hard|80 | 2020-01-01 10:00:00 || 3 | 9003 | 算法 | hard|80 | 2020-01-01 10:00:00 || 4 | 9004 | PYTHON | medium |70 | 2020-01-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 | 2020-08-02 10:01:01 | 2020-08-02 10:31:01 | 89 || 2 | 1002 | 9001 | 2020-04-01 18:01:01 | 2020-04-01 18:59:02 | 90 || 3 | 1001 | 9001 | 2020-04-01 09:01:01 | 2020-04-01 09:21:59 | 80 || 4 | 1002 | 9003 | 2021-01-20 10:01:01 | 2021-01-20 10:10:01 | 81 || 5 | 1002 | 9001 | 2021-03-02 19:01:01 | 2021-03-02 19:32:00 | 20 || 6 | 1001 | 9003 | 2021-04-02 19:01:01 | 2021-04-02 19:40:01 | 89 || 7 | 1004 | 9004 | 2020-05-02 12:01:01 | 2020-05-02 12:20:01 | 99 || 8 | 1003 | 9001 | 2021-05-02 12:01:01 | 2021-05-02 12:31:01 | 98 || 9 | 1001 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:20:01 | 99 || 10 | 1002 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:43:01 | 81 || 11 | 1001 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:59:01 | 69 || 12 | 1001 | 9004 | 2021-09-02 12:11:01 | NULL | NULL || 13 | 1003 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 || 14 | 1002 | 9004 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 | 83 || 15 | 1002 | 9003 | 2021-01-01 18:01:01 | 2021-01-01 18:59:02 | 90 || 16 | 1002 | 9002 | 2020-02-02 12:01:01 | NULL | NULL || 17 | 1002 | 9002 | 2020-03-02 12:11:01 | NULL | NULL || 18 | 1001 | 9002 | 2021-05-05 18:01:01 | NULL | NULL |+----+------+---------+---------------------+---------------------+-------+请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。由示例数据结果输出如下：+------+-------------+-------------+-------------+------------------+------------------+------------+| tag | exam_cnt_20 | exam_cnt_21 | growth_rate | exam_cnt_rank_20 | exam_cnt_rank_21 | rank_delta |+------+-------------+-------------+-------------+------------------+------------------+------------+| SQL | 3 | 2 | -33.3% | 1 | 2 | 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);🍌🍌 答案with info_2020 as (select ei.tag, count(er.score) as exam_cnt ,rank() over(order by count(er.score) desc) as exam_cnt_rankfrom exam_record as erright join examination_info as eion er.exam_id=ei.exam_idwhere year(er.start_time)=2020 and month(er.start_time)<=6group by ei.taghaving count(er.score)>0),info_2021 as (select ei.tag, count(er.score) as exam_cnt ,rank() over(order by count(er.score) desc) as exam_cnt_rankfrom exam_record as erright join examination_info as eion er.exam_id=ei.exam_idwhere year(er.start_time)=2021 and month(er.start_time)<=6group by ei.taghaving count(er.score)>0),exam_year_info as (select i20.tag, i20.exam_cnt as exam_cnt_20, i21.exam_cnt as exam_cnt_21,i20.exam_cnt_rank as exam_cnt_rank_20, i21.exam_cnt_rank as exam_cnt_rank_21from info_2020 as i20join info_2021 as i21on i20.tag=i21.tag)select tag, exam_cnt_20, exam_cnt_21,concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100, 1), '%') as growth_rate,exam_cnt_rank_20, exam_cnt_rank_21, (cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed)) as rank_deltafrom exam_year_infoorder by growth_rate desc, exam_cnt_rank_21 desc;
2 🌈 聚合窗口函数
🚀 SQL33 对试卷得分做min-max归一化
📖 examination_info 表结构
📖 exam_record 表结构
🚀 题目描述现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：+----+---------+--------+------------+----------+---------------------+| id | exam_id | tag | difficulty | duration | release_time |+----+---------+--------+------------+----------+---------------------+| 1 | 9001 | SQL | hard|60 | 2020-01-01 10:00:00 || 2 | 9002 | C++ | hard|80 | 2020-01-01 10:00:00 || 3 | 9003 | 算法 | hard|80 | 2020-01-01 10:00:00 || 4 | 9004 | PYTHON | medium |70 | 2020-01-01 10:00:00 || 5 | 9005 | WEB | hard|80 | 2020-01-01 10:00:00 || 6 | 9006 | PYTHON | hard|80 | 2020-01-01 10:00:00 || 7 | 9007 | web | hard|80 | 2020-01-01 10:00:00 || 8 | 9008 | Web | medium |70 | 2020-01-01 10:00:00 || 9 | 9009 | WEB | medium |70 | 2020-01-01 10:00:00 || 10 | 9010 | SQL | medium |70 | 2020-01-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 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 90 || 2 | 1003 | 9002 | 2020-01-01 19:01:01 | 2020-01-01 19:30:01 | 75 || 3 | 1004 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:11:01 | 60 || 4 | 1003 | 9002 | 2020-01-01 12:01:01 | 2020-01-01 12:41:01 | 90 || 5 | 1002 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:32:00 | 90 || 6 | 1003 | 9001 | 2020-01-02 12:01:01 | 2020-01-02 12:31:01 | 68 || 7 | 1001 | 9002 | 2020-01-02 12:01:01 | 2020-01-02 12:43:01 | 81 || 8 | 1001 | 9005 | 2020-01-02 12:11:01 | NULL | NULL || 9 | 1001 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 || 10 | 1002 | 9002 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 | 83 || 11 | 1002 | 9004 | 2021-09-06 12:01:01 | NULL | NULL || 12 | 1002 | 9002 | 2021-05-05 18:01:01 | NULL | NULL |+----+------+---------+---------------------+---------------------+-------+在物理学及统计学数据计算时，有个概念叫min-max标准化，也被称为离差标准化，是对原始数据的线性变换，使结果值映射到[0 - 1]之间。请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。由示例数据结果输出如下：+------+---------+------+| uid | exam_id | sc1 |+------+---------+------+| 1001 | 9001 | 98 || 1003 | 9001 | 0 || 1002 | 9002 | 88 || 1003 | 9002 | 75 || 1001 | 9002 | 70 || 1004 | 9002 | 0 |+------+---------+------+解释：高难度试卷有9001、9002、9003；作答了9001的记录有3条，分数分别为68、89、90，按给定公式归一化后分数为：0、95、100，而后两个得分都是用户1001作答的，因此用户1001对试卷9001的新得分为(95+100)/2≈98（只保留整数部分），用户1003对于试卷9001的新得分为0。最后结果按照试卷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_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);🍌🍌 答案select uid,exam_id,round(avg((score-min_score)/(max_score-min_score)*100)) sc1from(SELECT ex.uid,ex.exam_id,score,max(score) over(partition by ex.exam_id ) max_score,min(score) over(partition by ex.exam_id ) min_scorefrom exam_record ex join examination_info e on ex.exam_id=e.exam_idwhere difficulty='hard' and score is not null) awhere max_score!=min_score group by uid,exam_idunion select uid,exam_id,score sc1from(SELECT ex.uid,ex.exam_id,score,max(score) over(partition by ex.exam_id ) max_score,min(score) over(partition by ex.exam_id ) min_scorefrom exam_record ex join examination_info e on ex.exam_id=e.exam_idwhere difficulty='hard' and score is not null) bwhere max_score=min_scoreorder by exam_id,sc1 desc;
🚀 SQL34 每份试卷每月作答数和截止当月的作答总数
📖 exam_record 表结构
🚀 题目描述现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：请输出每份试卷每月作答数和截止当月的作答总数。由示例数据结果输出如下：+---------+-------------+-----------+--------------+| exam_id | start_month | month_cnt | cum_exam_cnt |+---------+-------------+-----------+--------------+| 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 |+---------+-------------+-----------+--------------+解释：试卷9001在202001、202002、202003、202005共4个月有被作答记录，每个月被作答数分别为2、1、3、1，截止当月累积作答总数为2、3、6、7。🚀 建表语句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);🍌🍌 答案SELECT exam_id, DATE_FORMAT(start_time,'%Y%m') start_month, COUNT(DATE_FORMAT(start_time,'%Y%m')) AS month_cnt, SUM(COUNT(DATE_FORMAT(start_time,'%Y%m'))) over (partition by exam_id order by DATE_FORMAT(start_time,'%Y%m'))cum_exam_cntFROM exam_recordGROUP BY exam_id, start_monthORDER BY exam_id, start_month;
🚀 SQL35 每月及截止当月的答题情况
📖 exam_record 表结构
🚀 题目描述现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：+----+------+---------+---------------------+---------------------+-------+| id | uid | exam_id | start_time | submit_time | score |+----+------+---------+---------------------+---------------------+-------+| 1 | 1001 | 9001 | 2020-01-01 09:01:01 | 2020-01-01 09:21:59 | 90 || 2 | 1002 | 9001 | 2020-01-20 10:01:01 | 2020-01-20 10:10:01 | 89 || 3 | 1002 | 9001 | 2020-02-01 12:11:01 | 2020-02-01 12:31:01 | 83 || 4 | 1003 | 9001 | 2020-03-01 19:01:01 | 2020-03-01 19:30:01 | 75 || 5 | 1004 | 9001 | 2020-03-01 12:01:01 | 2020-03-01 12:11:01 | 60 || 6 | 1003 | 9001 | 2020-03-01 12:01:01 | 2020-03-01 12:41:01 | 90 || 7 | 1002 | 9001 | 2020-05-02 19:01:01 | 2020-05-02 19:32:00 | 90 || 8 | 1001 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:59:01 | 69 || 9 | 1004 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:20:01 | 99 || 10 | 1003 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:31:01 | 68 || 11 | 1001 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:43:01 | 81 || 12 | 1001 | 9002 | 2020-03-02 12:11:01 | NULL | NULL |+----+------+---------+---------------------+---------------------+-------+请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。由示例数据结果输出如下：+--------+-----+--------------+------------------+------------+| stime | mau | month_add_uv | max_month_add_uv | cum_sum_uv |+--------+-----+--------------+------------------+------------+| 202001 | 2 | 2 | 2 | 2 || 202002 | 4 | 2 | 2 | 4 || 202003 | 3 | 0 | 2 | 4 || 202005 | 1 | 0 | 2 | 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(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);🍌🍌 答案SELECT stime,mau,if(month_add is not null,month_add,0) as month_add_uv,max(if(month_add is not null, month_add,0)) over (order by stime) as max_month_add_uv,sum(if(month_add is not null, month_add,0)) over (order by stime) as cum_sum_uvFROM (SELECT stime, count(distinct uid) as mau FROM (SELECT uid,DATE_FORMAT(start_time,'%Y%m') as stime FROM exam_record) a GROUP BY stime) cLEFT JOIN(SELECT ntime, count(distinct uid) as month_add FROM (SELECT uid,min(DATE_FORMAT(start_time,'%Y%m')) as ntime from exam_record GROUP BY uid) b GROUP BY ntime) dON c.stime=d.ntime;
