oracle连续天数的计算

注：技术交流可以加我VX：k-loop，昵称：默读者。

--1，创建一个用户登录表
 

create table tmp_ma(
u_id   varchar2(20) ,
login_day date
);
comment on table tmp_ma is '用户登录表';
comment on column tmp_ma.u_id is '用户id';
comment on column tmp_ma.login_day is '登录日期';

--2，插入测试数据,用户A在20190601-201906-04，连续登陆4天，用户A在20190606-201906-07，连续登陆2天。

 

insert into tmp_ma(u_id,login_day) values('A',date'2019-06-01');
insert into tmp_ma(u_id,login_day) values('A',date'2019-06-02');
insert into tmp_ma(u_id,login_day) values('A',date'2019-06-03');
insert into tmp_ma(u_id,login_day) values('A',date'2019-06-04');
insert into tmp_ma(u_id,login_day) values('A',date'2019-06-06');
insert into tmp_ma(u_id,login_day) values('A',date'2019-06-07');

--3，根据用户id分组且以登陆日期升序排序，插入临时表中

create table o_tmp_ma_01
as
select 
m.u_id --用户id
,m.login_day --登录日期
,row_number()over(partition by m.u_id order by m.login_day asc) rn --排序
from tmp_ma m  
;

select 
u_id --用户id
,login_day --登录日期
,rn --排序
from o_tmp_ma_01 
;

 

查询结果如下：

U_ID	LOGIN_DAY	RN
A	2019/6/1	1
A	2019/6/2	2
A	2019/6/3	3
A	2019/6/4	4
A	2019/6/6	5
A	2019/6/7	6

--4,以用户id，登录日期-排序分组，计算连续登陆天数,插入临时表

--分析：如果登陆日期连续，那么登录日期-排序是相同的，所以要以这个差分组。

create table o_tmp_ma_02
as
select 
u_id --用户id
,login_day-rn compare_day --比较日期
,count(*) continuity_days --连续登陆天数
from o_tmp_ma_01 
group by u_id,login_day-rn
;

select
u_id --用户id
,compare_day --比较日期
,continuity_days --连续登陆天数
from o_tmp_ma_02
;

查询结果如下：

U_ID	COMPARE_DAY	CONTINUITY_DAYS
A	2019/6/1	2
A	2019/5/31	4

 

--5,以用户id分组，比较日期升序排序，计算每个比较日期的连续登陆天数，以及累计登陆天数，插入临时表

create table o_tmp_ma_03
as
select 
u_id --用户id
,compare_day --比较日期
,continuity_days --连续登陆天数
,sum(continuity_days) over(partition by u_id order by compare_day asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_continuity_days --累计的登陆天数
,row_number()over(partition by u_id order by compare_day asc) rn --排序
from o_tmp_ma_02
;

select
u_id --用户id
,compare_day --比较日期
,continuity_days --连续登陆天数
,cumulative_continuity_days --累计的登陆天数
,rn --排序
from o_tmp_ma_03
;

查询结果如下：

U_ID	COMPARE_DAY	CONTINUITY_DAYS	CUMULATIVE_CONTINUITY_DAYS	RN
A	2019/5/31	4	4	1
A	2019/6/1	2	6	2

 

--6,计算出用户的两个连续登陆区间内的连续登陆天数，累计登陆天数，连续登陆的开始时间，以及连续登陆的结束时间，插入临时表

create table o_tmp_ma_04
as
select
u_id  --用户id
,compare_day  --比较日期
,continuity_days    --连续登陆天数
,cumulative_continuity_days --累计的登陆天数
,compare_day+cumulative_continuity_days-continuity_days+1 continuity_frist_day --连续登陆的第一天
,compare_day+cumulative_continuity_days continuity_last_day --连续登陆的最后一天
,rn
from o_tmp_ma_03
;

 

select 
u_id  --用户id
,compare_day  --比较日期
,continuity_days    --连续登陆天数
,cumulative_continuity_days --累计的登陆天数
,continuity_frist_day --连续登陆的第一天
,continuity_last_day --连续登陆的最后一天
,rn
from o_tmp_ma_04
;

查询结果如下：

U_ID	COMPARE_DAY	CONTINUITY_DAYS	CUMULATIVE_CONTINUITY_DAYS	CONTINUITY_FRIST_DAY	CONTINUITY_LAST_DAY	RN
A	2019/5/31	4	4	2019/6/1	2019/6/4	1
A	2019/6/1	2	6	2019/6/6	2019/6/7	2

--查询最早的一段连续登陆的情况

select 
u_id  --用户id
,compare_day  --比较日期
,continuity_days    --连续登陆天数
,cumulative_continuity_days --累计的登陆天数
,continuity_frist_day --连续登陆的第一天
,continuity_last_day --连续登陆的最后一天
,rn
from o_tmp_ma_04
where rn=1
;

查询结果如下：

U_ID	COMPARE_DAY	CONTINUITY_DAYS	CUMULATIVE_CONTINUITY_DAYS	CONTINUITY_FRIST_DAY	CONTINUITY_LAST_DAY	RN
A	2019/5/31	4	4	2019/6/1	2019/6/4	1

分析：用户A最早一段连续登陆情况是，20190601第1天登陆，连续登陆了4天，最后一次连续登陆的时间是20190604，这段连续时间内，累计登陆了4天。

--查询最近的一段连续登陆的情况
--将第5步里面的，row_number()over(partition by u_id order by compare_day asc) rn 改成
--row_number()over(partition by u_id order by compare_day desc) rn  即可。

select 
u_id  --用户id
,compare_day  --比较日期
,continuity_days    --连续登陆天数
,cumulative_continuity_days --累计的登陆天数
,continuity_frist_day --连续登陆的第一天
,continuity_last_day --连续登陆的最后一天
,rn
from o_tmp_ma_04
where rn=1
;

查询结果如下：

U_ID	COMPARE_DAY	CONTINUITY_DAYS	CUMULATIVE_CONTINUITY_DAYS	CONTINUITY_FRIST_DAY	CONTINUITY_LAST_DAY	RN
A	2019/6/1	2	6	2019/6/6	2019/6/7	1

分析：用户A最近一段连续登陆情况是，20190601到20190607日之间，这7天内，离现在最近的一段连续登陆情况是，20190606登陆，，到20190607日，连续登陆2天。这7天累计有6天登陆。