Oracle 查找三天登录用户问题

一、数据准备:

create table logintest(user_id number,log_date date);
insert into logintest values(111,to_date('2021-06-01','yyyy-mm-dd'));
insert into logintest values(111,to_date('2021-06-02','yyyy-mm-dd'));
insert into logintest values(111,to_date('2021-06-03','yyyy-mm-dd'));
insert into logintest values(111,to_date('2021-06-05','yyyy-mm-dd'));
insert into logintest values(111,to_date('2021-06-08','yyyy-mm-dd'));
insert into logintest values(222,to_date('2021-06-01','yyyy-mm-dd'));
insert into logintest values(222,to_date('2021-06-03','yyyy-mm-dd'));
insert into logintest values(222,to_date('2021-06-04','yyyy-mm-dd'));
insert into logintest values(222,to_date('2021-06-06','yyyy-mm-dd'));
insert into logintest values(222,to_date('2021-06-07','yyyy-mm-dd'));
insert into logintest values(333,to_date('2021-06-01','yyyy-mm-dd'));
insert into logintest values(333,to_date('2021-06-02','yyyy-mm-dd'));
insert into logintest values(333,to_date('2021-06-04','yyyy-mm-dd'));
insert into logintest values(333,to_date('2021-06-06','yyyy-mm-dd'));
insert into logintest values(333,to_date('2021-06-07','yyyy-mm-dd'));
commit;

二、实现需求:

        有登陆表logintest, 表中字段：user_id 用户名，log_date 登录日期，查找连续登录三天的用户信息

三、实现思路:

1. 使用开窗函数dense_rank(), 按user_id分组, 根据log_date排序, 得到col_rank字段

2. log_date减去col_rank, 得到衍生日期字段, minus_date

3. 按user_id和minus_date分组计数,  计数 >=3结果即为所求

四、实现代码:

select 
f.user_id
, count(minus_date) cnt
from(
    select 
        user_id
        ,log_date
        ,dense_rank() over (partition by user_id order by log_date) col_rank
        ,log_date - (dense_rank() over (partition by user_id order by log_date)) minus_date
    from logintest
) f
group by f.user_id, f.minus_date
having count(minus_date) >= 3;

五、总结:

        1. having(count())>=n, n可根据需求改变;

        2. 此类问题可以衍生出连续登录天数最多的、每天登录多次要去重等等问题,  其核心思想都是按分类条件下,对不连续不等值序列减去连续不等值序列(或者是不连续等值序列减去连续等值序列)的结果进行聚合计数, 最后再按计数值来筛选条件

        3. 连续序列可以由connect by产生, 也可以由开窗排名函数产生, 也可以自定义数值

        4. 根据不同场景或者不同需求, 斟酌考虑使用连续等值序列或者连续不等值序列, 如下图