ORACLE查询各个用户最大连续登陆天数

ORACLE查询各个用户最大连续登陆天数

一、数据准备

执行如下查询，可以看到login_record表数据，user_id为用户标识，fdate为用户登陆日期。

select * from login_record order by user_id,fdate;

二、数据去重

把同一用户在同一天的登陆记录去重，并按用户和日期排序（这里也可以不用排序，不影响最终结果，主要是为了方便理解数据）

with 
t1 as(
  select 
    distinct user_id,fdate 
  from login_record order by user_id,fdate
)
select * from t1;

三、对去重后的数据编号

分别对每个用户的登陆记录按照日期（升序）进行编号（xh字段）

with 
t1 as(
  select 
    distinct user_id,fdate
  from login_record order by user_id,fdate
),
t2 as(
  select 
    user_id,fdate,
    row_number() over(partition by user_id order by fdate) as xh
  from t1
)
select * from t2;

四、得到连续标识=登陆日期-编号值

计算每条记录的 连续标识=登陆日期-该记录的编号值，连续标识相同则用户登陆日期连续
说明：如果第三步日期是降序，这里需要计算：登陆日期+编号值

with 
t1 as(
  select 
    distinct user_id,fdate
  from login_record order by user_id,fdate
),
t2 as(
  select 
    user_id,fdate,
    row_number() over(partition by user_id order by fdate) as xh
  from t1
),
t3 as(
  select 
    user_id,fdate,xh,fdate-xh as lxbz
  from t2
)
select * from t3;

五、根据用户id和连续标识进行分组统计

这里使用group by函数对user_id、lxbz进行分组，并统计每组记录数量（这里的数量就是用户连续登陆的天数）

with 
t1 as(
  select 
    distinct user_id,fdate
  from login_record order by user_id,fdate
),
t2 as(
  select 
    user_id,fdate,
    row_number() over(partition by user_id order by fdate) as xh
  from t1
),
t3 as(
  select 
    user_id,fdate,xh,fdate-xh as lxbz
  from t2
),
t4 as(
  select 
    user_id,lxbz,count(*) as lxts
  from t3 group by user_id,lxbz order by user_id
)
select * from t4;

六、最后使用聚合函数max计算每个用户最大连续登陆天数

这里用group by分组函数对user_id进行分组，查询每组最大lxts的记录，对应的值则为该用户最大连续登陆天数

with 
t1 as(
  select 
    distinct user_id,fdate
  from login_record order by user_id,fdate
),
t2 as(
  select 
    user_id,fdate,
    row_number() over(partition by user_id order by fdate) as xh
  from t1
),
t3 as(
  select 
    user_id,fdate,xh,fdate-xh as lxbz
  from t2
),
t4 as(
  select 
    user_id,lxbz,count(*) as lxts
  from t3 group by user_id,lxbz order by user_id
),
t5 as(
  select 
    user_id,max(lxts) 最大连续登录天数
  from t4 group by user_id order by user_id
)
select * from t5;