数仓经典SQL题-细说如何解决连续问题(大厂宠儿SQL)

一、概述

对于初级、中级的数据工程师来说，面试逃不了的就是sql能力的考察，甚至许多高级工程师也逃脱不了现场手撕sql的命运，而连续问题又是所有企业都喜欢考察的一类问题，特别是中大厂，什么连续下单、连续登录、连续签到等等，绕来绕其实都是本质都是连续问题的变形。

为什么企业喜欢问连续问题，因为连续问题考察范围比较广比如，窗口函数中的lag函数，row_number()，sum()over(order by) 等各种函数，分组聚合，去重以及相关数据处理的技巧等，不管我们选取那种方法，连续问题都是比较复杂的一类问题，对于一个面试者的综合能力考察非常全面。

二、解题思路

我们思考一下什么是连续呢，如果给出一份数据，我们怎么才能很"直接"出连续的内容呢？是算出上一数据的日期？还是算出与上一数据差值？还是算出每个是否与上一数据是否连续标志字段呢？其实这些思路都可以，当然解答连续问题也有多种思路，我们就列举几种最常用的解法。

思路1：利用等差数列(两个等差数列的差是常数列)

这种方法是最常用的算的一种，就是在特定的分组下，将连续的内容赋值相同的分组，特定分组是指连续的主体，假如判断用户是否连续登录，则这个特定分组是每个用户，这个是分厂好理解的，连续分组赋值相同的分组ID，连续的行分配相同的的ID值，该ID在不同的连续组之间不同。解题步骤：

1）筛选出所有用户的登录时间，按照用户id去重；

2）用rank对每个用户的日期打序号；

3）用登录时间减去打的序号 得出“等差时间”，如果连续登录，连续登录的这几天的等差时间一样；

4）对“用户+等差时间”分组求和，取最，我们下面就列举大值 会存在先连续登录N天，然后断几天，再继续连续N天以上登录，所以按照按照这个分组求和会有多个和。

5）按照“用户”分组取最大值即为用户的最大登录天数。

例如：查询至少连续三天下单的用户

select user_id
       ,dt_flag
       ,count(*)  as order_cnt
 from ( select user_id
               ,order_dt
               ,date_sub(order_dt,row_number() over(partition by user_id order by order_dt)) as dt_flag
          from  ds_hive.t_order
       ) t3
   group by user_id,dt_flag
   having order_cnt>=3

此方法是适应场景最多的一种接替思路，也是大家必须掌握的方法。

思路2：本次登录日期后的第N天日期等于本次登录后的第N次登录日期

本次登录日期的第二天 VS 按照用户id分区，按照登陆日期排序，取下一次登陆时间(取不到就为'9999-12-31'),两个时间相等即可

1）用date_sub计算连续登录N天后的日期，用开窗函数lead(colName，N，defautValue)得出实际N次登录后的日期。

2）判断两个日期是否相等 只要用户的一条登录记录满足该用户即为连续登录N天的用户。

例如：求连续三天登陆

select distinct user_id from (  
  select  
    user_id,  
    login_date,  
    date_add(login_date,2) as nextday,  
    lead(login_date,2,'9999-12-31') over (partition by user_id order by login_date) as nextlogin  
from user_login) as t1 where nextday = nextlogin;

思路3：自关联

自相关理解就比较容易了，就是自己关自己，然后产生笛卡尔积进行运算，但是如果数据量大的话，产生的笛卡尔积，数据会爆炸性的增加，查询时间很久，所以不推荐数据量大的情况。

例如：求出连续3天登录的用户id；

select
  t1.id,
  t1.dt
from
  (
    select
      ul1.id,
      ul1.dt
    from
      user_login ul1
      inner join user_login ul2 on t3.id = ul2.id
    where
      ul2.dt between date_sub(t3.dt, 2)
      and t3.dt
  ) t1
group by
  t1.id,
  t1.dt
having
  count(1) = 3
) t12
group by
  t12.id

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

思路4：累积求和法

1）用lag(colName，1，defautValue) 取用户的上一次登录日期，用datediff计算两个日期的差值，如果差值<N记为0 否则记为1 即为断点。

2）使用sum()over() 统计截止到当前日期的标记值，同一组的合计值一样，合计值即为组号。

3）根据用户和组号分组聚合，最大和最小连续登录日期的差值即为该用户的最大跨N天连续登录天数。

例如：计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。

select
  user_id,
  max(days) + 1 as con_days
from
  (
    select
      user_id,
      flag,
      datediff(max(time), min(time)) as days
    from(
        select
          user_id,
          time,
          sum(if(flag > 2, 1, 0)) over (
            partition by user_id
            order by
              time
          ) as flag
        from
          (
            select
              user_id,
              time,
              datediff(
                time,
                lag(time, 1, '1970-01-01') over (
                  partition by user_id
                  order by
                    time
                )
              ) as flag
            from
              continues_time
          ) t1
      ) t2
    group by
      user_id,
      flag
  ) t4
group by
  user_id;

三、企业高频面试题

1.普通难度连续：

（字节面试题）有一张用户签到表，标记每天用户是否签到（说明：该表包含所有用户所有工作日的出勤记录） ，包含三个字段：

日期【fdate】、用户id【fuser_id】、用户当天是否签到【fis_sign_in：0否1是】

问题1：请计算截至当前每个用户已经连续签到的天数（输出表仅包含当天签到的所有用户，计算其连续签到天数）;

问题2：请计算每个用户历史以来最大的连续签到天数（输出表为用户签到表中所有出现过的用户，计算其历史最大连续签到天数）。 

2.中等难度连续

（腾讯大数据面试题）有一张股票的价格表stock_data有3个字段，分别是：股票代码(stock_code),日期(trade_date)，收盘价格(closing_price) ，请找出满足连续5天以上（含）每天上涨超过5%的股票,并给出连续满足天数及开始和结束日期。

备注：不考虑停牌或其他情况，仅仅关注每天连续5天上涨超过5%的股票。

3.高等难度连续

有用户签到记录表，t_coin_signin,记录用户当天是否完成签到的情况,计算出每个用户的每个月获得的金币数量；

签到领金币规则如下：

1. 用户签到获得1金币；

2. 如用户连续签到3天则第三天获得2金币，如用户连续签到7天则第7天获得5金币；

3. 连续签到7天后连续天数重置，每月签到天数重置。

感兴趣的小伙伴来挑战一下各个大厂的面试题啦。

涤生大数据往期精彩推荐