#加上score IS NOT NULL SELECT uid, exam_id, ROUND(AVG(score)) AS avg_score FROM exam_record LEFT JOIN examination_info USING(exam_id) LEFT JOIN user_info USING(uid) WHERE (nick_name REGEXP '^[0-9]+$' OR nick_name REGEXP '^牛客[0-9]+号$') AND tag REGEXP '^C' AND score IS NOT NULL GROUP BY uid,exam_id ORDE...

试卷或题目有一个最近在202109就可以，OR SELECT uid, nick_name, achievement from (SELECT ui.uid, nick_name, achievement, pr.submit_time, er.start_time FROM user_info ui LEFT JOIN exam_record er USING(uid) LEFT JOIN practice_record pr USING(uid) WHERE nick_name LIKE '牛客%号' AND (achievement BETWEEN 1200 AND 2500)) t G...

如果一个人在一条做题记录里的做题时间等于他所有做题时间里最小的一个，那么这条记录就是他首次做题的记录，那么他就是在这个做题时间当月的新用户 SELECT start_month, COUNT(DISTINCT uid) AS mau, SUM(new) AS month_add_uv, MAX(SUM(new)) OVER(ORDER BY start_month) AS max_month_add_uv, SUM(SUM(new)) OVER(ORDER BY start_month) AS cum_sum_uv FROM (SELECT DATE_FORMAT(start_time,'%Y...

SUM() OVER(ORDER BY ) 可以计算累积频数 SELECT exam_id, start_month, month_cnt, SUM(month_cnt) OVER(PARTITION BY exam_id ORDER BY exam_id,start_month) AS cum_exam_cnt FROM (SELECT exam_id, DATE_FORMAT(start_time,'%Y%m') AS start_month, COUNT(start_time) AS month_cnt FROM exam_record WHERE start_time IS NOT N...

最高最低分是针对每个试卷来说，不要考虑不同用户的不同试卷 SELECT uid,exam_id, ROUND(AVG( CASE WHEN score_MAX = score_MIN THEN score ELSE (score-score_MIN)/(score_MAX-score_MIN)*100 END )) AS avg_new_score FROM (SELECT uid,exam_id,score, MAX(score) OVER(PARTITION BY exam_id) AS score_MAX, MIN(score) OVER(PARTITION BY exam_id) AS...

cast(exam_cnt_rank_21 as signed)将字段转换成有符号整数统计一列中符合不同条件的行的数量，用COUNT(IF)比较简便，用WHERE 再连接比较啰嗦 SELECT 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) as rank_delta FROM (SELECT tag, exam_cnt_20, exam_cnt_21,...

注意用DENSE_RANK!! SELECT uid, DATE_FORMAT (start_time, '%Y%m') AS start_month, COUNT(start_time) AS total_cnt, COUNT(submit_time) AS complete_cnt FROM ( SELECT uid, start_time, submit_time, DENSE_RANK() OVER ( PARTITION BY uid ORDER BY DATE_FORMAT (start_time, '%Y%m') DESC ) AS mr FROM exam_record WHE...

SELECT uid, COUNT(submit_time) AS exam_complete_cnt FROM (SELECT uid, submit_time, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) AS rk FROM exam_record) t1 WHERE rk <=3 GROUP BY uid HAVING COUNT(IF(submit_time IS NULL,1,NULL))=0 ORDER BY exam_complete_cnt DESC,u...

用的是start_time不是submit_time SELECT uid, MAX(datediff(ld,rd) +1) AS days_window, round(MAX(datediff(ld,rd) +1) * (COUNT(*)+1)/(datediff(MAX(ld),MIN(rd))+1),2) AS avg_exam_cnt FROM (SELECT uid, DATE(start_time) AS rd, LEAD(DATE(start_time)) OVER(PARTITION BY uid ORDER BY DATE(start_time)) AS ld, start_...

SELECT exam_id, duration, release_time FROM (SELECT exam_id, duration, release_time, time1, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY time1 ASC) AS rk1, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY time1 DESC) AS rk2 FROM (SELECT examination_info.*, start_time, submit_time, TIMESTAMPDIFF(SE...

SELECT tid,uid,ranking FROM (SELECT tid,uid, RANK() OVER(PARTITION BY tid ORDER BY rk1 ASC,rk2 ASC,uid DESC) AS ranking FROM (SELECT tid,uid, RANK() OVER(PARTITION BY tid ORDER BY MAX1 DESC) AS rk1, RANK() OVER(PARTITION BY tid ORDER BY MIN1 DESC) AS rk2 FROM (SELECT tag AS tid,uid, MAX(score) AS MA...

分别写四个查询再结合思路比较简单 SELECT uid, IFNULL(act_month_total,0), IFNULL(act_days_2021,0), IFNULL(act_days_2021_exam,0), IFNULL(act_days_2021_question,0) FROM (SELECT uid, COUNT(DISTINCT month) AS act_month_total FROM (SELECT uid, DATE_FORMAT(er.start_time,' %Y%m') AS month FROM user_info ui LEFT JOIN exam_re...

SELECT level, COUNT(IF(score>80,uid,null)) AS level_cnt FROM exam_record JOIN examination_info USING(exam_id) JOIN user_info USING(uid) WHERE tag = 'SQL' GROUP BY level HAVING level_cnt > 0 ORDER BY level_cnt DESC;

SELECT exam_id, COUNT(distinct uid) AS uv, ROUND(AVG(score),1) AS avg_score FROM exam_record JOIN user_info USING(uid) JOIN examination_info USING(exam_id) WHERE tag ='SQL' AND level > 5 AND DATE(start_time) = DATE(release_time) GROUP BY exam_id ORDER BY uv DESC,avg_score ASC