课程推荐:PHP开发工程师--学习猿地精品课程
SQL计算最长登录天数
计算最长登陆天数主要用两个函数:1.窗口函数row_number()over() 2.date_sub()
1.使用row_number()窗口函数
select UID,loadtime,row_number()over(partition by UID order by loadtime) sort
from user_login
2.使用date_sub()函数
select UID,date_sub(loadtime,sort) as date_group,min(loadtime) as start, max(loadtime) as end ,count(1) as continue_days
(select UID,loadtime,row_number()over(partition by UID order by loadtime) sort
from user_login
) a
group by UID,date_sub(loadtime,sort)
3.以UID分组,取max(continue_days)
select UID,max(continue_days) as maxday
(select UID,date_sub(loadtime,sort) as date_group,min(loadtime) as start, max(loadtime) as end ,count(1) as continue_days
(select UID,loadtime,row_number()over(partition by UID order by loadtime) sort
from user_login
) a
group by UID,date_sub(loadtime,sort)
) b
group by UID
SQL 计算最长连续登录天数
标签:技术 group by detail article login tps csdn count number
© 著作权归作者所有
发表评论