关键：两次聚合函数的使用！ 一次用于group by，一次用于窗口函数求累积和 select author, month, round(sum(if_follow)/count(start_time),3) as fans_growth_rate, sum(sum(if_follow)) over(partition by author order by month asc) as total_fans from ( select author,start_time, date_format(start_time,'%Y-%m') as month, case when if_follow=2...

select * from ( select tag,dt, sum(sum(if_like)) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d, max(sum(if_retweet)) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d from ( select tag, date_format(start_time,'%Y-%m-%d') as dt, if_like, if_retweet fro...

select level,score_grade,round(num2/num1,3) as ratio from ( select level,score_grade, count(score_grade) as num2 from ( select level, case when score<60 then '差' when score>=60 and score<75 then '中' when score>=75 and score<90 then '良' when score>=90 then '优' end as score_grade fro...

select exam_id, date_format(start_time,'%Y%m') as start_month, count(start_time) as month_cnt, sum(count(start_time)) over(partition by exam_id order by date_format(start_time,'%Y%m')) as cum_month_cnt from exam_record group by exam_id,start_month

需要注意的几个点：  排名变化：rank()返回无符号类型，不能直接减，用cast转换一下。cast(strng as type),type:char(字符型)、DATE(日期型)、DATETIME(日期和时间型)、DECIMAL(float型 SIGNED(int整数型)、TIME(时间型) 排序：对tag类别中的时间排序，不要下意识用partition  select tag,exam_cnt_20,exam_cnt_21, concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') as growth_rate, exam...

一种更少嵌套的sql实现方法 select uid,start_month,total_cnt,complete_cnt from ( select uid, dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent_month, date_format(start_time,'%Y%m') as start_month, count(start_time) as total_cnt, count(submit_time) as complete_cnt from exa...

select exam_id,duration,release_time from examination_info where exam_id in ( select exam_id from ( select exam_id,spend_time_fast from ( select exam_id,spend_time as spend_time_fast, row_number() over(partition by exam_id order by spend_time asc) as ranking_fast, row_number() over(partition by exam...

看了几个题发现对第二个条件至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）发了福利券的处理都是放在where里筛选，其实当然可行，毕竟这道题之后直接group by uid那么不影响uid出现次数。 我就写一下至少一次的满足条件sql写法吧： select uid, 'activity1' as activity from exam_record where year(start_time)=2021 group by uid having count(if(score>=85,1,null))=count(start_time) union ALL sel...

select er.exam_id as exam_id, count(distinct er.uid) as uv, round(avg(score),1) as avg_score from exam_record as er left join examination_info using(exam_id) left join user_info using(uid) where level>5 and tag='SQL' and date(start_time)=date(release_time) group by exam_id order by uv desc,avg_sc...

这题考察窗口函数罢了，了解rank()、dense_rank()、row_number()的用法区别就很简单了。  rank():排序时若存在相同位次记录，则会跳过之后的位次，简单来说一个示例：1，1，3，4，4，6 dense_rank()：不跳过。示例：1，1，2，3，3，4 row_number()：赋予连续唯一的位次。示例：1，2，3，4，5，6  题意很明显，有个关键点是：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。 那么显然，这里该用dense_rank()窗口函数。 select emp_no,birth_date,first_name,las...

select uid, date_format(start_time,'%Y%m') as start_month, count(start_time) as total_cnt, count(submit_time) as complete_cnt from ( -- 6、7级用户在近三个月有试卷作答记录的用户 #uid集合为1001、1002 select uid, start_time, submit_time from ( select er.uid, start_time, submit_time, dense_rank() over(partition by uid order b...