#两种情况吧，只有一条数据的不需要标准化 # 并且需要先归一化，再计算均值 select tmp2.uid ,tmp2.exam_id ,round(avg(tmp2.s),0) as gs from(# 计算归一化后的数值 select tmp1.* ,if(tmp1.cnt=1, tmp1.score,(tmp1.score-tmp1.min_s)/(tmp1.max_s-tmp1.min_s)*100) as s from(#准备数据和字段 select er.* ...

#构建20年和21年的数据集 + 数据连接后进行计算 with t1 as ( select ei.tag ,count(submit_time) as cnt ,rank() over(order by count(submit_time) desc) as rk from examination_info ei join exam_record er using(exam_id) where substr(er.submit_time,1,10) between'2020-01-01' and '2020-06-30'...

select tmp1.uid ,count(submit_time) as cnt from(#按照用户的做题时间新排序 select * ,DENSE_RANK() over(partition by uid order by date_format(start_time,'%Y-%m') desc) as rk from exam_record )tmp1 where tmp1.rk<=3 #筛选近满足近三个月的数据 group by tmp1.uid having(count(tmp1.start...

#首先这个题就是** #按条件计算公式 select tmp2.uid ,max(tmp2.diff_d) as days_window ,round(max(tmp2.cnt)/max(tmp2.max_d)*max(tmp2.diff_d),2) as avg_exam_cnt from(#筛选满足条件的用户where select tmp1.uid ,tmp1.diff ,timestampdiff(day,substr(tmp1.first_d,1,10),substr(tmp1.last_d,1,1...

#timestampdiff + 窗口函数row_number() 正序+倒序排列 + where + group by + max -min #数据连接 SELECT ei.exam_id ,ei.duration ,ei.release_time from examination_info ei join (#筛选每个用户正序和倒序第二的时间（where）并利用groupby 计算差值 select tmp2.exam_id ,max(tmp2.dt) - min(tmp2.dt) as diff from(#...

解题思路： 1、根据题目，需要对每个用户，每门考试下钻，并统计最值；(group by两个字段+min()+max()) 2、排序：窗口函数排序，需要对科目进行分组，并对最高分、最低分和账号三个字段进行排序，用来区分并列情况;(rank() over(partition by order by) 3、筛选：筛选各自科目中的前三名即可;(where) tmp2.tag ,tmp2.uid ,tmp2.rk from(#rank() over窗口函数进行排序,三个字段需要排序 SELECT tmp1.* ,rank() over(pa...

题目难点： 1、总活动月数，并非21年活动月数；(两种时间的统计方式） 2、试卷并不一定包含试题，两项是独立的；(两个表的拼接） 3、每个用户，left join；(左连接+聚合操作） 思路如下： 1、首先整理数据：分别筛选 满足条件用户的试卷情况和试题情况，并进行数据拼接 涉及到的函数：union all-数据拼接；where满足条件用户；left join-试卷情况 这里需要注意一下，on和where对于条件处理的先后顺序 2、聚合操作：统计每个用户的数据，必然是聚合操作group by 3、统计条件：条件判断+统计 涉及到的函数：substr-日期格式判断；if-条件判断；distinc...

构建临时表：筛选满足的用户信息 + 用户完成试卷数 + 用户完成试题数 最后要注意需要 左连接 + ifnull的处理 with t1 as ( #先筛选出满足的用户 select ui.uid from examination_info ei join exam_record er on ei.exam_id=er.exam_id and ei.tag='SQL' and ei.difficulty='hard' join user_info ui on ui.uid=er.uid and ui.level=7 where s...

#timestampdiff()对时间差进行计算 with t1 as ( SELECT e.* ,TIMESTAMPDIFF(SECOND,e.start_time,e.submit_time)/60 as t ,i.duration ,i.difficulty from exam_record e join examination_info i using(exam_id) ) ,t2 as( #满足条件一的用户 select t1.uid ,'activity1...

select i.exam_id ,r.uid ,r.score from examination_info i join exam_record r using(exam_id) where i.tag='SQL' and substr(i.release_time,1,10)=substr(r.start_time,1,10) ) select t1.exam_id ,count(distinct t1.uid) as uv ,round(avg(t1.score),1) as avg_score f...

with t1 as ( select uid from exam_record where submit_time is not null group by uid having(count(submit_time)/count(DISTINCT substr(submit_time,1,7))>=3) ) select i.tag ,count(r.exam_id) as tag_cnt from examination_info i join exam_record r using(exam_id) where...

#先创建满足要求的用户id with t1 as ( select tmp1.* from(select uid ,exam_id ,substr(start_time,1,10) as ti ,count(exam_id) over(partition by uid) as cnt ,count(submit_time) over(partition by uid) as com_cnt ,count(if(submit_time is null,1,null)) over(partiti...

窗口函数的拓展 1、avg() over() select tmp1.score from(SELECT r.* ,avg(r.score) over (partition by i.tag) as av from examination_info i left join exam_record r on i.exam_id=r.exam_id where i.tag='SQL' )tmp1 where tmp1.score>=tmp1.av order by tmp1.score limit 1

count典型题目 1、count(*) 不论null都进行统计； 2、count(1) 不论null都进行统计； 3、count(字段) 只统计不为 null的数据 4、count(distinct ) 去重统计 SELECT count(*) as total_pv ,count(submit_time) as complete_pv ,count(distinct if(submit_time is not null,exam_id,null)) as complete_exam_cnt from exam_record