题解 | #连续两次作答试卷的最大时间窗#

连续两次作答试卷的最大时间窗

https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c

1. 分享思路:首先是lead() over() 窗口函数的使用得到每一次日期的下一行日期值,其次利用uid分组得到max(start_time),min(start_time) ,max(datediff(next_time,start_time)) 得到连续两次作答最大的时间窗口,统计年度作答次数总数的时候,需要统计的是count(start_time) 而不是count(distinct start_time) ;


/*
# 3.表间自关联 
select A1.uid ,
       timestampdiff(day,A1.submit_time,B1.submit_time) days_diff1,
       round(A1.avg_exam * timestampdiff(day,A1.submit_time,B1.submit_time),2) avg_exam_cnt
     from (
# 2. 这些人各自完成试卷 的提交时间及对应的rank值 。
        select ER.uid, 
               ER.submit_time, 
              row_number() over(partition by ER.uid order by ER.submit_time) rankA 
            from (     
        #  1. 统计在2021年至少有两天作答过试卷的人  
            select uid 
            from exam_record 
            where year(submit_time) = 2021 
            group by uid 
            having count(distinct date_format(submit_time,'%Y%m%d')) >= 2  
            ) A 
            join exam_record ER on A.uid = ER.uid and year(ER.submit_time) = 2021
            order by ER.uid,ER.submit_time 
       ) A1 
       join 
        (
        select ER.uid, 
            ER.submit_time, 
            row_number() over(partition by ER.uid order by ER.submit_time) rankB 
            from (     
        #  1. 统计在2021年至少有两天作答过试卷的人  
            select uid 
            from exam_record 
            where year(submit_time) = 2021 
            group by uid 
            having count(distinct date_format(submit_time,'%Y%m%d')) >= 2  
            ) A 
            join exam_record ER on A.uid = ER.uid and year(ER.submit_time) = 2021
            order by ER.uid,ER.submit_time 
        ) B1 on A1.uid=B1.uid and A1.avg_exam=B1.avg_exam and B1.rankB-1 = A1.rankA 
        order by A1.uid,days_diff1 desc 
        limit 1 ;
**/

# 重新按照 lead( ,1) over(partition by  order by )  窗口函数书写     

select uid ,
       days_window ,
       round((total*days_window)/days_diff,2) avg_exam_cnt  
       from (
select  uid,
        count(start_time) total ,  #全年作答的总次数
        datediff(max(start_time),min(start_time))+1 days_diff ,  #头尾时间最大时间窗 
        max(datediff(next_time,start_time))+1 days_window   #该年连续两次作答试卷的最大时间窗 
    from (
    select ER.uid, 
           ER.start_time,
           lead(ER.start_time,1) over(partition by ER.uid order by ER.start_time) next_time  # 当下时间的下一次时间 
           from 
     (select uid 
       from exam_record 
      where year(start_time) = 2021 
      group by uid 
      having count(distinct date(submit_time)) >= 2  
      ) A join exam_record ER on A.uid = ER.uid and year(start_time) = 2021 
      order by ER.uid,ER.start_time 
) A1 
     group by A1.uid 
) A2 
order by days_window desc,avg_exam_cnt desc ; 

/*
WITH t2 AS (
SELECT 
	uid,
	COUNT(start_time) total, -- 用户2021年作答的次数
	DATEDIFF(MAX(start_time),MIN(start_time))+1  diff_time, -- 头尾作答时间窗 
	MAX(DATEDIFF(next_time,start_time))+1 days_window -- 最大间隔天数
FROM (
	SELECT uid,start_time,
	LEAD(start_time,1)OVER(PARTITION BY uid ORDER BY start_time) AS next_time -- 第二次作答时间
	FROM exam_record
    WHERE YEAR(start_time)=2021 -- 2021年的数据
	) t1
GROUP BY uid
)
SELECT uid,days_window,ROUND(total* days_window/diff_time,2) avg_exam_cnt
FROM t2
WHERE diff_time>1
ORDER BY days_window DESC,avg_exam_cnt DESC
;
*/

全部评论

相关推荐

06-12 16:23
已编辑
小米_软件开发
点赞 评论 收藏
分享
06-27 12:54
已编辑
门头沟学院 Java
累了,讲讲我的大学经历吧,目前在家待业。我是一个二本院校软件工程专业。最开始选专业是觉得计算机感兴趣,所以选择了他。本人学习计算机是从大二暑假结束开始的,也就是大三开始。当时每天学习,我个人认为Java以及是我生活的一部分了,就这样持续学习了一年半,来到了大四上学期末,大概是在12月中旬,我终于找的到了一家上海中厂的实习,但我发现实习生的工作很枯燥,公司分配的活也不多,大多时间也是自己在自学。就这样我秋招末才找到实习。时间来到了3月中旬,公司说我可以转正,但是转正工资只有7000,不过很稳定,不加班,双休,因为要回学校参加答辩了,同时当时也是心高气傲,认为可以找到更好的,所以放弃了转正机会,回学校准备论文。准备论文期间就也没有投递简历。然后时间来到了5月中旬,这时春招基本也结束了,然后我开始投递简历,期间只是约到了几家下场面试。工资也只有6-7k,到现在我不知道该怎么办了。已经没有当初学习的心劲了,好累呀,但是又不知道该干什么去。在家就是打游戏,boss简历投一投。每天日重一次。26秋招都说是针对26届的人,25怎么办。我好绝望。要不要参加考公、考研、央国企这些的。有没有大佬可以帮帮我。为什么感觉别人找工作都是顺其自然的事情,我感觉自己每一步都在艰难追赶。八股文背了又忘背了又忘,我每次都花很长时间去理解他,可是现在感觉八股、项目都忘完了。真的已经没有力气再去学习了。图片是我的简历,有没有大哥可以指正一下,或者说我应该走哪条路,有点不想在找工作了。
码客明:太累了就休息一下兄弟,人生不会完蛋的
如果实习可以转正,你会不...
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务