10 篇文章 2 订阅

# 二、解题思路

						ranking_d2 - rankingd1 = 1 （1）
ranking_d3 - rankingd2 = 1 （2）


						dates_d2 - date2_d1 = 1  （3）


						dates_d3 - date2_d2 = 2  （4）


		（ranking_d2 - dates_d2） - （ranking_d1 - dates_d1）= 0


		ranking_d2 - dates_d2 = ranking_d1 - dates_d1，即diff1 = diff2


(2）-（4）有：

		（ranking_d3 - dates_d3） - （ranking_d2 - dates_d2）= -1 ≠0


		ranking_d2 - dates_d2 ≠ ranking_d1 - dates_d1，即 diff1 ≠ diff2


（1）求排名ranking，间隔天数dates,以及排名-天数得到diff

select *, datediff(now(), sigindate)-1267 as dates,
row_number() over(partition by userid order by sigindate desc) as ranking,
(datediff(now(), sigindate)-1267 - row_number() over(partition by userid order by sigindate desc) ) as diff
from t;


（2）对diff进行计数，即求出用户的所有连续活跃天数

select userid,diff, count(diff) as 'diff计数'
from (
select *, datediff(now(), sigindate)-1267 as dates,
row_number() over(partition by userid order by sigindate desc) as ranking,
(datediff(now(), sigindate)-1267 -
row_number() over(partition by userid order by sigindate desc) ) as diff
from t
) as t1
group by userid, diff;


（3）求diff计数的最大值，即求用户的最大活跃天数

select userid, max(diff计数) as '最大活跃天数'
from (
select userid,diff, count(diff) as 'diff计数'
from (
select *, datediff(now(), sigindate)-1267 as dates,
row_number() over(partition by userid order by sigindate desc) as ranking,
(datediff(now(), sigindate)-1267 - row_number() over(partition by userid order by sigindate desc) ) as diff
from t
) as t1
group by userid, diff
) as t2
group by userid;


select userid,diff, count(diff) as 'diff计数'
from (
select userid,diff, count(diff) as 'diff计数'
from (
select *, (datediff(now(), sigindate)-1267 -
row_number() over(partition by userid order by sigindate desc) ) as diff
from t
) as t1
group by userid, diff
) as t2
group by userid;

• 6
点赞
• 2
评论
• 32
收藏
• 一键三连
• 扫一扫，分享海报

08-26 1万+
04-06 7535
03-01 3948
11-30 1975
07-19 2393
01-25 520
09-26 1512
09-27 3930
11-01 8542