常见面试题（一）

关键词： sql，面试；

最近身边有不少小伙伴在考虑跳槽的事情，免不了的会遇到一些SQL的面试题。

根据过往的经历和大伙儿的反馈，我们发现问题大都集中于HIVE-SQL中的窗口函数和数据倾斜的应对方案上。于是我们整理归纳了几个问题出来，以供大家参考。

1.连续活跃问题

这个问题有两个变形：

（1）某APP用户活跃记录表active，有uid（用户id）、dt（活跃日期）字段，求出连续出勤3天及以上的用户数

（2）某APP用户活跃记录表active，有uid（用户id）、dt（活跃日期）字段，求每个用户的最大连续活跃天数

以上两个问题要求输出的数据不同，但都是要考察对于窗口函数lead、lag的掌握能力。第二个是在第一个问题基础上的延伸，也可以作为此类问题的通解。我们按照顺序分别来进行解答。

首先，我们构建一个表以供测试说明，形如下图：

考虑到权限问题，这里就不单独新建hive表了，直接在with子句中union出来一个临时表：

WITH active AS
  (SELECT 100 UID,
              '2021-04-01'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 100 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 100 UID,
                        '2021-04-03'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-03'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 105 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-03'dt)

（1）要知道一个用户连续活跃，那么肯定是要对他的所有活跃记录进行排序的。对于active这个表，每天每个活跃用户都会有一条记录，我们将每个用户的活跃记录按日期顺序排列，如果上一条记录的日期与本条记录的日期刚好相差1天，那么这两条记录就是连续的。反过来也就是说，连续出勤的记录之间，日期差值为1，推而广之，如果用户连续出勤了N天，那么这N条记录之中任意相邻的两条都是差1天，而这段记录开始的日期到结束的日期之间的差值则是N-1。如果某用户连续出勤了4天，那么从他第3天出勤的记录往前数第6条记录就刚好是他连续出勤的开始日期，所以，我们从每条记录往前数第6条的日期与本条记录的日期差值刚好是2天的话，就表示这期间没有间断。

我们利用lead获取每一条记录其往前第2条记录的日期，与本条记录日期求差值，如果这个差值等于2，就表明该用户连续出勤了3天及以上。具体计算sql如下所示，可以求得共有3个用户连续活跃了3天及以上。

select
count(DISTINCT uid)
from(
        SELECT UID,
               dt,
               lag(dt,2)over(PARTITION BY UID ORDER BY dt)dt2
        FROM active
        )x
where datediff(dt,dt2)=2

这是从后往前数，同样的利用lag函数可以获取每条记录往后数第2条记录的日期，如果有差值等于2的记录，那么也可以表明用户连续出勤了3天及以上。

（2）有了第一个问题作为铺垫，再来看第二个问题，相信大家应该更容易有思路了。

在这个问题下，没有明确给出要计算连续多少天活跃，而是要求每个用户的最大连续活跃天数。如此一来，我们就不能直接错位相减了，而是需要清晰地找到每一段连续活跃的起止时间点。

所以我们首先要判断每条记录与其相邻的记录之间是否连续，如果不连续则说明这条记录是某一段连续活跃的起点或者终点。

给所有记录标记上是否是断点之后，我们就可以为每条记录去匹配距离他最近的一次起点：

根据上图的子查询，我们就能知道每条活跃记录是从哪天开始连续活跃的了，然后求每个用户UID下所有活跃记录中与其起始日期最大的差值即可：

select
UID,max(datediff(dt,start_dt))days
from(
    select
    UID,dt,max(if(if_continue=0,dt,null))over(PARTITION BY UID ORDER BY dt)start_dt
    from(
        select
        UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue
        from(
                SELECT UID,
                       dt,
                       lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2
                FROM active
                )x
    )y
)z
group by UID

如此，就求得了所有用户的最大连续活跃天数了：