数仓面试——连续登录问题进阶版

最新推荐文章于 2024-07-13 18:10:19 发布

小萝卜算子

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量158

点赞数

分类专栏：数据仓库文章标签：数据库 sql 数据仓库大数据

本文链接：https://blog.csdn.net/xiaoluobutou/article/details/129246152

版权

数据仓库专栏收录该内容

21 篇文章 2 订阅

订阅专栏

Hi, 我是小萝卜算子

一、引入

紧接上一篇文章：数仓面试——连续登录问题

表结构和数据一致

需求有所升级：中间间隔一天，也算连续登录，求出连续4天登录的用户id（因为样例中1001用户连续3天登录，但是升级后，则可视为连续4天登录）

二、思考

由于间隔一天也算连续登录

数仓面试——连续登录问题中方法一/二/三的方式将不再有效，因为过滤条件变成不固定的筛选日期了

方法四依然有效，只需要将flag条件略加修改即可

三、需求：求出连续4天登录的用户id，间隔一天登录也算连续

方法一：相对不灵活的方式

SELECT     
     id
FROM
     (
          SELECT
               id,
               base_dt
          FROM
               (
                    SELECT
                         id,
                         dt,
                         date_sub(base_dt1, dr) base_dt
                    FROM
                         (
                              SELECT
                                   id,
                                   dt,
                                   date_sub(dt, rn) base_dt1,
                                   dense_rank () over (
                                        PARTITION BY id
                                        ORDER BY
                                             date_sub(dt, rn)
                                   ) dr
                              FROM
                                   (
                                        SELECT
                                             id,
                                             dt,
                                             row_number () over (PARTITION BY id ORDER BY dt) rn
                                        FROM
                                             user_login
                                   ) tmp
                         ) tmp1
               ) tmp2
          GROUP BY
               id,
               base_dt
          HAVING
               count(1) >= 4
     ) tmp3
GROUP BY
     id;

详解：

1：利用rown_number，按用户分组，登录时间升序

2：本次登录日期减去步骤一生成自增序列，形成第一道日期基准

3：利用dense_rank，按用户分组，步骤二形成的日期基准升序

4：步骤二的日期基准减去步骤三的自增序列，形成最终的日期基准

5：按步骤四形成的用户和最终日期基准分组，过滤出次数大于等于4的数据

6：按照用户分组去重，获得最终结果

方法二：采用超过两天的登录间隔为分界线分组

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) <= 2,"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) >= 4
) tmp4
GROUP BY
  id;

详解：

1：利用lag（lead），按用户分组，登录时间升序，比较当前日期与上一个登录日期的差

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

3：利用窗口函数max，获取用户登录基准日期（连续登录为0，所以max为前标线）

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

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

方法三：采用过滤掉连续登录的数据

SELECT
  id
FROM
  (
    SELECT
      id,
      dt,
      lag_dt,
      rn,
      cnt,
      CASE
    WHEN cnt = rn
    AND dt_flag = 0 THEN
      rn - lag (rn, 1, 0) over (PARTITION BY id ORDER BY dt) + 1
    ELSE
      rn - lag (rn, 1, 0) over (PARTITION BY id ORDER BY dt)
    END lag_rn
    FROM
      (
        SELECT
          id,
          dt,
          lag_dt,

        IF (datediff(dt, lag_dt) <= 2, 0, 1) dt_flag,
        row_number () over (PARTITION BY id ORDER BY dt) rn,
        count(1) over (PARTITION BY id) cnt
      FROM
        (
          SELECT
            id,
            dt,
            lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
          FROM
            user_login
        ) tmp
      ) tmp1
    WHERE
      dt_flag = 1
    OR cnt = rn
  ) tmp2
WHERE
  lag_rn >= 4
GROUP BY
  id;

详解：

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

2：如果日期差小于等于2，则连续登录，记为0，否则记为1，记为日期基准

3：添加一个row_number和 count 窗口函数

4：取出日期基准为1的数据和最后一条数据

5：比较当前行和前一行rn的差，为连续登录的天数（最后一行特殊处理）

6：根据用户去重，获得结果

方法四：采用sum分组

SELECT
  id
FROM
  (
    SELECT
      id,
      base
    FROM
      (
        SELECT
          id,
          dt,
          lag_dt,
          sum(

            IF (datediff(dt, lag_dt) <= 2, 0, 1)
          ) over (PARTITION BY id ORDER BY dt) base
        FROM
          (
            SELECT
              id,
              dt,
              lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt
            FROM
              user_login
          ) tmp
      ) tmp1
    GROUP BY
      id,
      base
    HAVING
      count(1) >= 4
  ) tmp2
GROUP BY
  id;

详解：

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

2：如果日期差小于等于2，则连续登录，记为0，否则记为1，为日期基准

3：利用窗口函数sum，获取用户到当前行的和

4：连续登录用户步骤三求和结果相同（+0）

5：根据用户和步骤三求得的分组基准分组，并过滤出连续登录超过4天的用户

6：根据用户分组去重，获得结果