菜单 学习猿地 - LMONKEY

 SQL 计算最长连续登录天数

SQL 计算最长连续登录天数

JimAdela profile image JimAdela ・1 min read

课程推荐:PHP开发工程师--学习猿地精品课程

SQL计算最长登录天数
file

计算最长登陆天数主要用两个函数: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
file

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)
file

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
file

SQL 计算最长连续登录天数

标签:技术 group by detail article login tps csdn count number

原文地址:https://www.cnblogs.com/jiaxinwei/p/13936273.html

评论 (0)