/*位移开窗连续两次的时间差形成新的表t1，从t1中取出uid，按照uid分组，取出时间差的最大值*/ select uid ,days_windows ,round(days_windows * all_avg,2) AS avg_exam_cnt from( select uid ,max(timestampdiff(day,start_time,time_lag))+1 AS days_windows ,count(start_time) / (timestampdiff(day,min(start_time),max(start_time))+1) AS all_avg from( s...

select distinct exam_id ,duration ,release_time from( select er.exam_id /*作答时间timestampdiff(second,start_time,submit_time)第二快*/ ,nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) AS fast_2 ,nth_value(timest...

select tid ,uid ,ranking from( select tag AS tid ,uid ,row_number() over(partition by tag order by max(score) desc,min(score) desc, uid desc) AS ranking from exam_record er inner join examination_info ei on er.exam_id = ei.exam_id group by tag,uid )t where ranking <= 3 /*更加清晰的写法，先group by得出用户在每类t...

/*这种解法是很简洁的，但是在计算活跃总月数时，可能试卷和练习的月份有相同的，但是因为分开计算了，所以会出现重复计算的问题。解决方法：给试卷和练习加上标签 select u.uid count(distinct date_format(er.start_time,'%Y%m'))+count(distinct date_format(pr.submit_time,'%Y%m')) AS act_month_total ,count(distinct case when year(er.start_time)=2021 then date_format(er.start_time,'%Y%m%d...

select t1.uid ,count(distinct er1.id) AS exam_cnt ,count(distinct pr1.id) AS question_cnt from (/*先过滤出红名大佬t1*/ select distinct er.uid from exam_record er left join user_info ui on er.uid = ui.uid left join examination_info ei on er.exam_id = ei.exam_id where tag = 'SQL' and difficulty = 'hard' and l...

select exam_id AS tid ,count(distinct uid) AS uv ,count(*) AS pv from exam_record group by exam_id /*order by exam_id desc ,count(distinct uid) desc ,count(*) desc */ union all select question_id AS tid ,count(distinct uid) AS uv ,count(*) AS pv from practice_record group by question_id order by lef...

select er.exam_id ,count(distinct er.uid) AS uv ,round(avg(er.score),1) AS avg_score from examination_info ei left join exam_record er on ei.exam_id = er.exam_id left join user_info ui on er.uid = ui.uid where tag = 'SQL' and level>5 and date(ei.release_time) = date(er.submit_time) group by er.ex...

select tag ,count(start_time) AS tag_cnt from exam_record er inner join examination_info ei on er.exam_id = ei.exam_id where uid in( select uid from exam_record where month(submit_time) = 9 group by uid having count(submit_time)>=3 ) group by tag order by tag_cnt desc 连接作答信息表和试卷信息表，选择字段tag和开始作答时间...

select uid ,count(case when submit_time is null then 1 else null end) AS incomplete_cnt ,count(case when submit_time is not null then 1 else null end) AS complete_cnt ,group_concat(distinct concat(date(start_time),':',tag) separator ';') AS detail from( select uid ,submit_time ,start_time ,tag from ...

SELECT DATE_FORMAT(submit_time,'%Y%m') AS submit_month, COUNT(*) AS month_q_cnt, ROUND(COUNT(*) / AVG(DAY(LAST_DAY(submit_time))),3) AS avg_day_q_cnt FROM practice_record WHERE DATE_FORMAT(submit_time,'%Y') = '2021' GROUP BY DATE_FORMAT(submit_time,'%Y%m') UNION SELECT '2021汇总' AS submit_month, COUN...

select date_format(submit_time,"%Y%m") as month, round(count(distinct uid,date_format(submit_time,"%Y%m%d"))/count(distinct uid),2) as avg_active_days, count(distinct uid) as mau from exam_record where submit_time is not null and year(submit_time) = 2021 group by month 1.平均活跃天数= 总活跃天数 / 月活人数。关键的是求解总...

/* 将两个表连接起来，SELECT成绩，并且增加一列按照tag分组计算成绩均值，形成新的表T。将T的成绩升序排序，LIMIT 1。 */ WITH T AS( SELECT er.score, AVG(er.score) OVER(PARTITION BY ei.tag) AS avg FROM exam_record er INNER JOIN examination_info ei USING (exam_id) WHERE ei.tag = 'SQL' ) SELECT score AS min_score_over_avg FROM T WHERE score >= avg O...

/*解法一 SELECT ei.tag, ei.difficulty, round(avg(er.score), 1) clip_avg_score FROM ( SELECT *, RANK() OVER (ORDER BY score DESC) de_rk, RANK() OVER (ORDER BY score) as_rk FROM exam_record WHERE score is not null ) AS er INNER JOIN examination_info ei ON er.exam_id = ei.exam_id WHERE er.as_rk != 1 AND e...

REPLACE INTO examination_info (exam_id, tag, difficulty, duration, release_time) VALUES ( 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00' ) 因为试卷id是主键，插入的9003在表中已经存在，所以用INSERT插入是会失败的。REPLACE的语法和INSERT一样，不同点在于REPLACE INTO尝试插入新的数据，根据主键如果发现表中已经存在相同的数据，则删除原有的数据，将新的数据插入，否则就直接插入新数据。