牛客网SQL167解题思路

最新推荐文章于 2024-04-20 21:23:35 发布

BiggerData

最新推荐文章于 2024-04-20 21:23:35 发布

阅读量160

点赞数 1

文章标签： java servlet 蓝桥杯

本文链接：https://blog.csdn.net/liutao3233/article/details/126899379

版权

-- 第一步得到符合条件的日期跟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