如何将年薪从十五万到五十万系列之连续登录问题

嗨，大家好，我是小萝卜算子。（微信公众号：数据仓库践行者。感谢关注）
目前正在带领小伙伴们阅读spark源码，有兴趣的小伙伴可以私信我，获取大数据进阶的相关视频哦。

1、简介

连续登录问题，是一个经典sql，本文从易到难，简单拓展，有更好方法的同学，欢迎私下交流

2、表结构（去过重的）

CREATE TABLE `user_login`(

  `id` int COMMENT '用户主键', 

  `dt` string COMMENT '登录日期')

3、表数据

user_login.id		user_login.dt

1001				2021-12-12

1002				2021-12-12

1001				2021-12-13

1001				2021-12-14

1001				2021-12-16

1002				2021-12-16

1001				2021-12-19

1002				2021-12-17

1001				2021-12-20

4、需求： 求出连续3天登录的用户id

4.1 解决方案一：自关联

其主要原理可以这样理解

1：因为是固定的3天，所以可以采用主键自关联方法，过滤条件为副表ul2的登录时间在主表登录时间和主表登录时间减去两天的区间内
2：然后根据主表的id,和登录时间分组，分组后数量正好是3天的，说明连续三天时间都有登录
3：根据步骤二的结果去重，获取结果

下面看一下实现过程

SELECT
  tmp2.id
FROM
(
  SELECT
    tmp.id,
    tmp.dt
  FROM
  (
    SELECT
      ul1.id,
      ul1.dt
    FROM
      user_login ul1
    INNER JOIN 
      user_login ul2 
    ON ul1.id = ul2.id
    WHERE
      ul2.dt BETWEEN date_sub(ul1.dt, 2) AND ul1.dt
  ) tmp
  GROUP BY
    tmp.id,
    tmp.dt
  HAVING
    count(1) = 3
) tmp2
group by 
tmp2.id

4.2 解决方案二：巧用窗口函数row_number

其主要原理可以这样理解

1：利用row_number 提供一个自增序列

2：如果前后连续，那么登录日期减去步骤一提供的序列号结果相等

3：根据步骤二提供的日期基准，和用户id分组，过滤出大于等于3的结果

4：根据步骤三的结果去重，获取结果

下面看一下实现过程

SELECT
  tmp2.id
FROM
(
  SELECT
    id,
    date_sub(dt, rn) dt_line
  FROM
  (
    SELECT
      id,
      dt,
      row_number () over (PARTITION BY id ORDER BY dt) rn
    FROM
      user_login
  ) tmp
  GROUP BY
  id,
  date_sub(dt, rn)
  HAVING
    count(1) >= 3
) tmp2
group by 
tmp2.id

4.3 解决方案三：采用窗口函数 lag(lead)

其主要原理可以这样理解

1：因为是固定的3天，所以可以采用lag(lead)方法，求出前面第二个的日期与当前的日期差

2：根据步骤一的结果，判断如果日期差等于2，则是连续登录

3：根据步骤二的结果过滤分组，获取结果

下面看一下实现过程

SELECT
  tmp2.id
FROM
(
  SELECT
    id,
    IF (datediff(dt, lag_dt) = 2, 1, 0) flag
  FROM
  (
    SELECT
    id,
    dt,
    lag (dt, 2, '0000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
    FROM
    user_login
  ) tmp
) tmp2
WHERE
  tmp2.flag = 1
GROUP BY
  tmp2.id

4.4 解决方案四：比对相邻数据日期，可扩展为组别划分

其主要原理可以这样理解

1：利用lag（lead） 比较当前日期与上一次登录日期的差

2：如果日期差为1，则连续登录，记为0，否则记录当前登录日期

3：利用窗口函数max，获取用户登录基准日期

4：根据基准日期分组，过滤出大于等于3天的数据

5：根据步骤四结果去重，获得最后结果

下面看一下实现过程

SELECT
  id
FROM
(
  SELECT
    id,
    max_dt_line
  FROM
  (
    SELECT
      id,
      dt,
      lag_dt,
      max(dt_line) over (PARTITION BY id ORDER BY dt) max_dt_line
    FROM
    (
      SELECT
        id,
        dt,
        lag_dt,
        IF (datediff(dt, lag_dt) = 1,"0",dt) dt_line
      FROM
      (
        SELECT
          id,
          dt,
          lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
         FROM
          user_login
      ) tmp
    ) tmp1
  ) tmp2
  GROUP BY
  id,
  max_dt_line
  HAVING
  count(1) >= 3
) tmp4
GROUP BY
  id;

5、拓展

连续登录问题，其实可扩展为划分组别的问题

如果是时间不固定，比如求每个用户最大连续登录天数，那么方法一和三就失效了

如果仅仅是题目要求，求固定连续登录天数，那么个人更倾向于方法三

方法四的扩展性更强，聪明的你想到了吗。。。。