SQL处理连续性问题：从经典面试题开始拓展思路

前言：连续登录问题是一个经典的SQL面试题，相信有些同学已经背下答案了，我们先把套路过一下，再拓展开来，希望给大家一点点启发。

一、题目

1、题目常见形式

 1）查询连续登录7天的用户
 2）查询连续登录7天以上的用户

2、数据样例（表名login）

二、思路梳理

1、数据预处理

 首先，比较明确的是，登录时间信息要简化下，每个人每天登录只保留一条记录就好。
代码如下：
注：以下sql代码采用hive sql语法，是工作常用的，面试也会认可

1
2
3
4
5
6
7

select   
    userid  
    , split(login_time, ' ')[0] as login_date  
from login  
group by  
    userid  
    , split(login_time, ' ')[0]  

结果表：

2、如何从语法角度定义连续？

我们知道sql的语法通常是把每一条记录独立看待的，如果想表示不同记录之间的关系，通常有两种办法：
 1）窗口函数（好用但是有局限）
 2）自连接（更万能但是更麻烦）
=> 我们先用窗口函数法做一下，慢慢拓展开来，尝试把问题通用化

3、所谓标准解法

窗口函数法（网上流传的标准做法，需要聪明的小脑袋瓜灵光乍现一下）
显然： 对于某一个用户登录产生的一个连续日期序列，日期 - 序列号 * 天数 = 常数（其等于序列开始的前一天）
Q：这时候有小伙伴要举手了，不就是等差数列嘛，有那么多性质，为啥非找这么一个性质呢？
A：简单来说两个原因：
1）我们需要的是一个序列的共同特征，所谓共同特征最好的体现就是一个共同的特征值
2）好算呀，序列号可以直接用窗口函数row_number()得出(不熟悉窗口函数的同学可以去翻翻教程哈)
此题标准答案
代码如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

select 
    userid
    , login_date
    , date_sub(login_date, rn) as sequence_feature 
from   
(
    select 
        userid
        , login_date
        , row_number() over(partition by userid order by login_date) as rn
    from tmp_user_login_distinct_days
) as a
;

结果表：

有了这个序列特征值，就想算什么算什么啦，注意分组条件：
1）连续登陆天数： group by userid, sequence_feature => count(distinct login_date)
2）连续登录最后一天： group by userid, sequence_feature => max(login_date)

三、题目拓展v1

现在我们面对的需求发生了一点变化，不是连续登录日期问题啦，而是连续操作问题。

1、题目描述

 数据源是一个工单操作日志，id是工单号，userid是操作工号，op_time是操作时间。已知同一个工单可以由多个人流转操作（即A操作几次后可以转给B，B操作几次后可以转给C，以此类推）。我们把同一个人对同一个工单的连续操作视为一个操作周期（或称一个工作量），希望取出所有工单的所有操作周期的最后一次操作时间（即操作周期结束时间）。

2、数据样例（表名operation）

叮叮！给大家思考一下，可以私信我答案，下一篇博文更新我的代码~