SELECT CASE WHEN DATE_FORMAT(event_time, '%H:%i:%s') >= '07:00:00' AND DATE_FORMAT(event_time, '%H:%i:%s') < '09:00:00' THEN '早高峰' WHEN DATE_FORMAT(event_time, '%H:%i:%s') >= '09:00:00' AND DATE_FORMAT(event_time, '%H:%i:%s') < '17:00:00' THEN '工作时间' WHEN DATE_FORMAT(event_time, '%H:%i:%...

WITH a AS(SELECT city, driver_id, ROUND(SUM(grade)/COUNT(order_id), 1) AS avg_grade FROM tb_get_car_record r JOIN tb_get_car_order o USING(order_id) WHERE grade is not null GROUP BY city, driver_id ORDER BY driver_id), b AS ( SELECT city, driver_id, RANK() OVER (PARTITION BY city ORDER BY avg_grade ...

WITH a AS ( SELECT DATE(event_time) AS dt, product_id FROM tb_order_overall JOIN tb_order_detail USING(order_id) JOIN tb_product_info USING(product_id) WHERE shop_id = 901 ) SELECT t.dt, ROUND(COUNT(DISTINCT a.product_id) / t.onsale_cnt, 3) AS sale_rate, ROUND(1 - COUNT(DISTINCT a.product_id) / t.on...

WITH a AS ( SELECT uid, DATE(in_time) AS dt, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn FROM tb_user_log WHERE artical_id = 0 AND sign_in = 1 AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' ), b AS ( SELECT uid, DATE_FORMAT(MIN(dt), "%Y%m" ) AS month, COUNT(*) AS c...

WITH a AS( SELECT uid, in_time, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time DESC) AS rn, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn2 FROM tb_user_log GROUP BY uid, in_time ), b AS (SELECT uid, in_time FROM a WHERE rn = 1 AND in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FRO...

SELECT video_id, ROUND((100 * full_play_rate + 5 * like_cnt+ 3 * comment_cnt + 2 * retweet_cnt) / (no_play_cnt + 1), 0) AS hot_index FROM( SELECT video_id, AVG(timestampdiff(second, start_time, end_time) >= duration) AS full_play_rate, SUM(if_like) AS like_cnt, COUNT(comment_id) AS comment_cnt, S...

WITH a AS ( SELECT tag, DATE_FORMAT(start_time, '%Y-%m-%d' ) AS dt, COUNT(CASE WHEN if_like = 1 THEN l.id END) AS like_cnt, COUNT(CASE WHEN if_retweet = 1 THEN l.id END) AS retweet_cnt FROM tb_user_video_log l JOIN tb_video_info USING(video_id) WHERE DATE_FORMAT(start_time, '%Y-%m-%d') BETWEEN DATE_...

SELECT course_id, course_name, ROUND(100 * COUNT(DISTINCT CASE WHEN timestampdiff(second, in_datetime, out_datetime) >= 10*60 THEN a.user_id END) / COUNT(DISTINCT CASE WHEN if_sign = 1 THEN b.user_id END), 2) AS attend_rate FROM course_tb JOIN behavior_tb b USING(course_id) JOIN attend_tb a USING...

WITH a AS ( SELECT uid, DATE_FORMAT(start_time, '%Y%m' ) AS start_month, MIN(DATE_FORMAT(start_time, '%Y%m')) OVER (PARTITION BY uid) AS first_month FROM exam_record ), b AS ( SELECT start_month, COUNT(DISTINCT uid) AS mau FROM a GROUP BY start_month ), c AS ( SELECT start_month, COUNT(DISTINCT uid)...

难点1:act_month_total 要算所有年份 不止是2021年 所以不能和后面几列一起用where过滤难点2:act_month_total分别求试卷和练习的月份的时候如果只是用两个count月份的count相加会出现重复月份的情况，即使你分别用了两个distinct，一种方法就是再分别建立两个子查询把它们分别查出来再去重，我用的更简便的直接分别两个count distinct相加，然后在他们的和的末尾减去case when两边的月份有重复的算1：相当于两部运算得出这个值COUNT(DISTINCT date_format(r.start_time, '%Y-%m')) + COUNT...

SELECT uid, COUNT(DISTINCT r.id) as exam_cnt, COUNT(DISTINCT p.id) as question_cnt FROM exam_record r LEFT JOIN practice_record p USING(uid) LEFT JOIN examination_info USING(exam_id) LEFT JOIN user_info USING(uid) WHERE uid in ( SELECT uid FROM exam_record r LEFT JOIN practice_record p USING(uid) LE...

WITH a AS( SELECT exam_id, timestampdiff(second,start_time, submit_time) as finish, DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY timestampdiff(second,start_time, submit_time) DESC) as fastrank, DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY timestampdiff(second,start_time, submit_time)) as slo...

因为窗口函数和聚合函数不能在同一个子查询里叠加使用，并且窗口函数的排序结果不能在同一个子查询底部进行where过滤，所以递进的CTE表达式是最方便的： WITH info AS ( SELECT tid, uid, MAX(score) as maxs, MIN(score) as mins FROM ( SELECT DISTINCT tag as tid, r.id, r.uid, r.score FROM examination_info JOIN exam_record r USING(exam_id) ) temp GROUP BY tid, uid ORDER BY tid, ma...

SELECT exam_id, COUNT(DISTINCT uid) as uv, ROUND(SUM(score)/COUNT(DISTINCT r.id),1) as avg_score FROM user_info JOIN exam_record r USING(uid) JOIN examination_info USING(exam_id) WHERE tag = 'SQL' and level > 5 and DAY(release_time) = DAY(submit_time) GROUP BY exam_id ORDER BY uv DESC, avg_score