数仓工具—Hive实战之最大连续登陆(5)

用户最大连续登陆

说到最大连续登陆我们觉得可能有点奇怪，这名字怎么那么别扭，但是说到连续登陆就不那么别扭了，因为连续登陆其实是可以反应我们的用户粘性的，例如一个月中用户的连续登陆时间是30天，说明了用户每天都登陆的我们的应用，所以连续登陆也是我们数仓中经常计算的一个指标。

接下来我们说一下什么叫最大连续登陆，其实前面举了一个例子一个月连续登陆了30天，其实的情况是比较少的，除非你是国民级应用微信或者是其他软件，否则很难有一个月30天的连续登陆，那这个时候用户的登陆可能是短短续续的，例如一个月可能有多次连续登陆，那么这个时候我们就需要计算最大连续登陆天数了。

还有一点需要说明的是，其实连续登陆只是一个名字而已，我们现在的app 或或者是网页都是30天免密登陆的，其实我们计算是连续在线或者是连续打开而已，关于这一点我们后面不再解释，统一称之为连续登陆。

最大连续登陆只是计算连续登陆的一种场景，我们需要做的是学会这种计算思想，然后将其应用在其他场景，甚至是在没有连续的时候构造连续的场景，从而简化我们的计算逻辑

数据准备

下面我们计算用户过去7天内的连续登陆情况，下面是我们的数据

uid	event_time
+----+-----------+
1	2021-06-10 14:25:67
1	2021-06-10 22:25:67
1	2021-06-11 10:25:67
1	2021-06-13 10:25:67
1	2021-06-13 20:25:67
1	2021-06-14 10:25:67
1	2021-06-15 10:25:67
1	2021-06-16 10:25:67

下面是构建数据集的SQL

with user_log as(
  select
  1 as uid,'2021-06-10 14:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-10 22:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-11 10:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-13 10:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-13 20:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-14 10:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-15 10:25:67' as event_time
  union all
  select
  1 as uid,'2021-06-16 10:25:67' as event_time      
)
select * from user_log

需要注意的是我们这里是演示计算的方式，如果你是在数仓里进行计算你就需要用过去7天的数据进行计算，同理过去30天的最大登陆你就要用过去30天的数据进行计算

连续登陆

首先我们思考一个问题，如何判断连续，其实对于SQL 来说这也是一道经典的模型。如果我们按照用户的访问时间进行排序编号，按照升序

日期	编号	差值(n 天前)
2021-06-10	1	2021-06-09
2021-06-11	2	2021-06-09
2021-06-12	3	2021-06-09
2021-06-14	4	2021-06-10
2021-06-15	5	2021-06-10

为了方便我在上面举了一个例子，假设一个用户在2021-06-10 到2021-06-12 连续登陆了3天，那么它们的差值都是2021-06-09号，但是在2021-06-13 号断了一天，然后又在2021-06-14到2021-06-15连续登陆了2天，它们的差值都是2021-06-10

也就是说连续登陆的天数就等于不同差值的个数，在上面的例子中，用户连续登陆的天数分别是3天(2021-06-09)和2天(2021-06-10)

数据去重

从上面的例子中我们看到数据是需要按照时间去重的，也就是说一天只能保留一条数据

select
  uid，to_date(event_time) as event_date
from 
  user_log
group by
  uid，to_date(event_time)

uid	event_date
+----+-----------+
1	2021-06-10
1	2021-06-11
1	2021-06-13
1	2021-06-14
1	2021-06-15
1	2021-06-16

我们看到10号和13号的重复数据已经被去掉

生成序号

我们在上一步的基础上使用row_number 生成序号

select
  uid,event_date，row_number() over (partition by uid order by event_date ) as rn
from (
  select
    uid，to_date(event_time) as event_date
  from
    user_log
  group by
   uid，to_date(event_time)
)

uid	event_date	rn
+----+-----------+---+
1	2021-06-10	1
1	2021-06-11	2
1	2021-06-13	3
1	2021-06-14	4
1	2021-06-15	5
1	2021-06-16	6

生成差值

我们只需要使用date_sub 函数即可

select
  uid,event_date,rn,date_sub(event_date,rn) as gap
from (
  select
    uid,event_date，row_number() over (partition by uid order by event_date ) as rn
  from (
    select
      uid，to_date(event_time) as event_date
    from
      user_log
    group by
      uid，to_date(event_time)
  )
)
;

uid	event_date	rn	gap
+----+-----------+---+----+
1	2021-06-10	1	2021-06-09
1	2021-06-11	2	2021-06-09
1	2021-06-13	3	2021-06-10
1	2021-06-14	4	2021-06-10
1	2021-06-15	5	2021-06-10
1	2021-06-16	6	2021-06-10

计算连续

计算连续，我们只需要计算统计不同gap 的个数即可，当然这里我们也可以顺带将连续登陆的开始时间计算出来

select
  uid,gap,count(gap) as continuous_day,date_add(gap,min(rn)) as start_day
from(
  select
    uid,event_date,rn,date_sub(event_date,rn) as gap
  from (
    select
      uid,event_date，row_number() over (partition by uid order by event_date ) as rn
    from (
      select
        uid，to_date(event_time) as event_date
      from
        user_log
      group by
        uid，to_date(event_time)
    )
  )
)
group by
  uid,gap
;

uid	gap	continuous_day	start_day
+----+----+---------------+----------+
1	2021-06-09	2	2021-06-10
1	2021-06-10	4	2021-06-13

这个数据结果很好解释，在过去7天 用户1 从2021-06-10号开始连续两天登陆，从2021-06-13开始连续4天登陆

最大连续登陆

有了上面的计算，我们再来理解最大连续登陆的意思就很简单了

uid	gap	continuous_day	start_day
+----+----+---------------+----------+
1	2021-06-09	2	2021-06-10
1	2021-06-10	4	2021-06-13

因为我们看到在过去7天，用户1出现快乐两次连续登陆，第一次是连续2天，第二次是连续4天，所以我们计算最大连续就很简单了，同时我们也可以把最小连续登陆和连续登陆次数计算出来

select
  uid,max(continuous_day) as max_day,min(continuous_day) as min_day,count(gap) as continuous_times
from (
  select
  uid,gap,count(gap) as continuous_day,date_add(gap,min(rn)) as start_day
  from(
    select
    uid,event_date,rn,date_sub(event_date,rn) as gap
    from (
      select
      uid,event_date，row_number() over (partition by uid order by event_date ) as rn
      from (
        select
        uid，to_date(event_time) as event_date
        from
        user_log
        group by
        uid，to_date(event_time)
      )
    )
  )
  group by
  uid,gap
)group by
  uid

uid	max_day	min_day	continuous_times
+----+--------+--------+-----------------+
1	       4        2	          2

总结

最大连续登陆是我们计算比较常用的指标，我们可以将其当成一个经典的SQL模型，后面我们也会陆续总结出其他的模型，你还知道其他的比较经典的SQL 吗？欢迎分享，或者你还知道其他的关于计算连续的指标？

最大连续登陆重要的是计算思想，最大连续才是关键，登陆只是一个场景，要学会举一反三。