select start_month , count(distinct uid) mau , count(distinct new_uid) month_add_uv , max(count(distinct new_uid)) over(order by start_month) max_month_add_uv , sum(count(distinct new_uid)) over(order by start_month) cum_sum_uv from ( select uid , date_format(start_time, '%Y%m') start_month -- 判断是否为...

select * -- 窗口函数累积求和 , sum(month_cnt) over(partition by exam_id order by exam_id, start_month) cum_exam_cnt from ( -- 聚合算总数 select exam_id , date_format(start_time, '%Y%m') start_month , count(start_time) month_cnt from exam_record group by exam_id , date_format(start_time, '%Y%m') ) t ;

select uid , exam_id , round(avg(new_score), 0) avg_new_score from ( select uid , exam_id -- 归一化 , if(min_score = max_score, score, (score - min_score) * 100 / (max_score - min_score)) new_score from ( select a.uid , a.exam_id , a.score -- 统计每个exam_id的最高分和最低分 , min(a.score) over(partition by a.exam_...

select tag , exam_cnt_20 , exam_cnt_21 , growth_rate , exam_cnt_rank_20 , exam_cnt_rank_21 -- MYSQL中当两个字段相减时，如果其中一个或两个字段的类型的unsigned无签名类型，如果想减的值小于0则会报错（BIGINT UNSIGNED value is out of range） , if(exam_cnt_rank_21 >= exam_cnt_rank_20, exam_cnt_rank_21 - exam_cnt_rank_20, - (exam_cnt_rank_20 - exam...

select uid , start_month , count(start_time) total_cnt , count(submit_time) complete_cnt from ( select a.uid , date_format(a.start_time, '%Y%m') start_month , a.start_time , a.submit_time , dense_rank() over(partition by a.uid order by date_format(a.start_time, '%Y%m') desc) dt_rk , e.rate_rk from e...

select t1.uid -- 相邻最大时间间隔 , max(datediff(t1.lead_1_time, t1.start_time)) + 1 days_window -- 答题总数 * 总时间间隔 / 相邻最大时间间隔 , round(count(t1.start_time) * (max(datediff(t1.lead_1_time, t1.start_time)) + 1) / (datediff(max(t1.start_time), min(t1.start_time)) + 1), 2) avg_exam_cnt from ( -- lead: 向前推移窗口函数 sel...

select exam_id -- 考虑Group By是否允许SELECT非聚合列 , duration , release_time from ( select a.exam_id , b.duration , b.release_time , timestampdiff(minute, a.start_time, a.submit_time) ans_time , row_number() over(partition by a.exam_id order by timestampdiff(minute, a.start_time, a.submit_time) desc) rk1 , ...

select tid , uid , ranking from ( select b.tag tid , a.uid -- 聚合排序方式 , row_number() over(partition by b.tag order by max(a.score) desc, min(a.score) desc, a.uid desc) ranking from exam_record a left join examination_info b on a.exam_id = b.exam_id group by b.tag , a.uid ) t1 where ranking <= 3 ;

select a.uid , count(distinct act_month) act_month_total , count(distinct if(year(act_time) = 2021, act_day, NULL)) act_days_2021 , count(distinct if(year(act_time) = 2021 and tag = 'exam', act_day, NULL)) act_days_2021_exam , count(distinct if(year(act_time) = 2021 and tag = 'practice', act_day, NU...

select t1.uid , t1.exam_cnt , ifnull(t2.question_cnt, 0) question_cnt from -- 考试次数 ( select uid, count(submit_time) exam_cnt from exam_record where year(submit_time) = 2021 group by uid ) t1 left join -- 练习次数 ( select uid, count(submit_time) question_cnt from practice_record where year(submit_time) ...

select uid , sum(if(a.submit_time is null, 1, 0)) incomplete_cnt , sum(if(a.submit_time is null, 0, 1)) complete_cnt , group_concat(distinct concat(date_format(a.start_time, '%Y-%m-%d'), ':', b.tag) order by a.start_time separator ';') detail from exam_record a left join examination_info b on a.exam...