在实际的生产过程中最常见的分析用户连续登陆的情况,也是sql中最为常见的面试题---用户连续登录问题
假设我们有一张表,user,里有字段uid(用户id),long_time(登录时间)
表中数据如下:
现在我们要查询他连续登录了N天,
首先我们需要对用户登录的时间进行一个distinct的去重(由于我这边数据没有重复,就不进行去重操作了哈),如果需要去重的话只需要前面加distinct就ok了哈
我们使用CTE语法进行操作:
1,首先使用row_number窗口函数对用户数据进行排序
select uid,long_time,
row_number() over (partition by uid order by long_time) rk
from ceshi;
2,使用date_sub函数计算long_time一列加上rk后生成一列新的sub_date(我就随便命名了叫 day),假设字段中,同一用户的day相同,则说明是连续登录情况
with tb1 as (
select uid,long_time,
row_number() over (partition by uid order by long_time) rk
from ceshi)
select *,date_sub(long_time,interval rk DAY )day
from tb1;
3,按照uid和day进行分组聚合,取出uid,count(*)进行统计连续登录天数,最终的代码文件
with tb1 as (
select uid,long_time,
row_number() over (partition by uid order by long_time) rk
from ceshi),
tb2 as (
select *,date_sub(long_time,interval rk DAY )day
from tb1)
select uid,count(*)
from tb2 group by uid,day;
最终统计出连续登录的用户登录的次数