[大数据]SQL案例学习：连续登录问题

案例需求描述

案例使用的表及初始化数据的脚本附在当前文章最下面。

实现方法一

利用Oracle中的排序窗口函数（ROW_NUMBER：为分区中的每行数据分配一个序列号，序列号从1开始），结合日期差值计算，最后求得用户连续登陆的天数，详细的推理步骤如下：

①查询出一月份各用户的登录信息，将登陆日期格式化为年月日格式，并去重。

select distinct t.user_id, trunc(t.login_time) ymd
from t_login t
where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')

②基于第一步查询出来的记录，使用ROW_NUMBER窗口函数，按照用户id分组并根据登录日期排序

with t1 (user_id,ymd) as (
  select distinct t.user_id, trunc(t.login_time) ymd
  from t_login t
  where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
),

t2 (user_id,ymd,rn) as(
  select user_id,ymd,
  row_number() over (partition by user_id order by ymd)        
  from t1
)
select * from t2; 

③ 观察第二步查询的结果集，可以看出如果登录日期字段的值是连续值，那么日期减去序号的值应该是相等的。根据这个规律，可以得到如下查询结果：

with t1 (user_id,ymd) as (
  select distinct t.user_id, trunc(t.login_time) ymd
  from t_login t
  where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
),

t2 (user_id,ymd,diff_date) as(
  select user_id,ymd,
  ymd-row_number() over (partition by user_id order by ymd) diff_date         
  from  t1
)
select * from t2; 

④基于第三步的结果，根据用户id和相减后的日期进行分组，便可筛选出连续登陆天数>=3的用户信息

综上所有分析，最终的查询SQL如下：

with t1 (user_id,ymd) as (
  select distinct t.user_id, trunc(t.login_time) ymd
  from t_login t
  where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
),

t2 (user_id,ymd,diff_date) as(
  select user_id,ymd,
  ymd-row_number() over (partition by user_id order by ymd) diff_date         
  from  t1
)

select user_id,min(ymd),max(ymd),count(*) days from t2 
group by user_id,diff_date
having count(*) >= 3

实现方法二

利用Oracle中的取值窗口函数（LAG：返回窗口内当前行之前的第N行数据），计算登录日期的偏移量，最后求得用户连续登陆的天数，详细的推理步骤如下：

①查询出一月份各用户的登录信息，将登陆日期格式化为年月日格式，并去重。

select distinct t.user_id, trunc(t.login_time) ymd
from t_login t
where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')

②基于第一步查询出来的结果对登录日期字段使用lag窗口函数，计算当前行前面两行记录的日期（向上偏移2）

③观察第二步查询的结果，以登录日期为2022-01-04这条记录为例，如果2022-01-04之前已连续登陆2天，那么2022-01-04对应的偏移日期应该为2022-01-02。实际的偏移日期为2022-01-01，说明2022-01-04前面的两天不是连续值。

基于这个规律，将登录日期与偏移日期相减，求得差值，如果差值为2，说明当前记录的前面两条记录的登录日期是连续的。

④对第三步的结果进行过滤，筛选出diff为2的记录，即为连续登陆3天的用户信息。

综上所有分析，最终的查询SQL如下：

with t1 (user_id,ymd) as (
  select distinct t.user_id, trunc(t.login_time) ymd
  from t_login t
  where t.login_time >= to_date('2022-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  and t.login_time <= to_date('2022-01-31 23:59:59','yyyy-mm-dd hh24:mi:ss')
),
t2 as (
  select user_id,ymd,lag(ymd,2) over(partition by user_id order by ymd) lag2
  from t1
),
t3 as (
  select user_id,ymd,ymd-lag2 diff
  from t2
)

select user_id,ymd,diff
from t3
where diff = 2

总结

1.案例中用到的两个函数ROW_NUMBER()、LAG()，均为窗口函数，对窗口函数的详细用法，可以参考文章：

SQL窗口函数

2.案例的实现SQL中，使用到了with关键字，在Oracle中with有两个用法：

第一种用法，用来定义临时表，就是本案例中所用的（t1、t2、t3），将查询到的结果放到临时的变量中，方便后面对结果集的使用。

第二种用法，递归查询，详细使用方法参考文章：

Oracle递归的实现

初始化脚本（Oracle）

CREATE TABLE t_login
(user_id int, 
login_time date
);

INSERT INTO t_login VALUES (1, to_date('2022-01-01 08:05:11','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (2, to_date('2022-01-01 10:00:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (3, to_date('2022-01-01 12:13:14','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-01 19:30:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-02 07:59:30','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (2, to_date('2022-01-02 14:00:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (2, to_date('2022-01-03 11:15:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (3, to_date('2022-01-03 16:00:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-04 07:20:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (2, to_date('2022-01-04 07:45:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (3, to_date('2022-01-04 10:30:30','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-05 13:00:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-06 17:18:19','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (1, to_date('2022-01-07 20:00:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO t_login VALUES (2, to_date('2022-01-07 21:00:00','yyyy-mm-dd hh24:mi:ss'));

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖