牛客网SQL167解题思路

发布于:2022-12-15 ⋅ 阅读:(171) ⋅ 点赞:(0)

  -- 第一步得到符合条件的日期 跟sign_in的值
 SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr 
 FROM tb_user_log 
 WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')

 -- 第二步的想法 对7取模等于0 +6  对7取模等于3 +2
 SELECT uid,dt,sign_in,dr ,
 dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
 FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr 
 FROM tb_user_log 
 WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1

 --  取模求值
 SELECT uid,DATE_FORMAT(dt,'%Y%m') MONTH,
 (CASE WHEN dr2%7=3 THEN 3 WHEN dr2%7=0  THEN 7 ELSE 1 END) sums
 FROM 
  ( SELECT uid,dt,sign_in,dr ,
 dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
 FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr 
 FROM tb_user_log 
 WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1)temp2

--  求最后的值
SELECT uid,MONTH,SUM(sums) coin
FROM  
( SELECT uid,DATE_FORMAT(dt,'%Y%m') MONTH,
 (CASE WHEN dr2%7=3 THEN 3 WHEN dr2%7=0  THEN 7 ELSE 1 END) sums
 FROM 
  ( SELECT uid,dt,sign_in,dr ,
 dense_rank()over(PARTITION BY DATE_SUB(dt,INTERVAL dr DAY),uid ORDER BY dt) dr2
 FROM ( SELECT uid,DATE(in_time) dt,sign_in ,
dense_rank()over(PARTITION BY uid ORDER BY DATE(in_time)) dr 
 FROM tb_user_log 
 WHERE artical_id=0 AND sign_in!=0 AND (in_time>'2021-07-07-00:00:00' AND in_time<'2021-11-01-00:00:00')) temp1)temp2) temp3
 GROUP BY uid,MONTH
 ORDER BY MONTH,uid


网站公告

今日签到

点亮在社区的每一天
去签到