目前大数据发展势头强劲，各个公司大数据岗位需求不断上涨，其中数据相关的岗位都逃不开hive/hadoop相关技术，面试中更是屡屡提及。今天我们来看一下一道在大厂频繁出现的面试/笔试题目。

题目：

有一个用户登录表，表名为login_table，记录了用户的ID，求用户最长的连续登录天数以及起始的日期。要求使用hive-sql，不允许使用自定义的UDAF或者MR实现。

user_id	login_date
10000	2020-07-01
10000	2020-07-02
10000	2020-07-05
10000	2020-07-06
10000	2020-07-07
20000	2020-07-01
20000	2020-07-10
20000	2020-07-11
20000	2020-07-12

题目分析：

本题目的数据非常简单，容易理解，难点在怎么判断“连续登录”。我们知道sql的优点是处理行列式，通过简单的代码快速处理行列式的常规操作。什么是常规操作，比如sum、max、min、avg等，通过自定义的聚合函数或者mr当然能快速解决，但在题目中是禁止的，因此只能通过“常规”操作来完成。

通过现有数据不能得出答案，那我们只能通过构造新的列来完成辅助计算。我们注意到连续日期是若干个共差为1的等差数列，我们只需要构造另一个共差为1的等差数列，对应项相减，就可以对每个连续的登陆日期得到一个固定值。这么说有些抽象，我们看一下代码和数据

create table tmp_login_table_1 as 
select    
    user_id,   
    date_format(login_date,'yyyyMMdd') as log_date,   
    row_number() over  (partition by user_id order by login_date) as row_no
from    
    login_table

我们得到的数据是这样的

user_id	login_date	row_no
10000	20200701	1
10000	20200702	2
10000	20200705	3
10000	20200706	4
10000	20200707	5
20000	20200701	1
20000	20200710	2
20000	20200711	3
20000	20200712	4

然后我们再把日期看做一个整形，与row_no相减，得到新表，列diff = cast(login_date as bigint) - rowno

user_id	login_date	row_no	diff
10000	20200701	1	20200700
10000	20200702	2	20200700
10000	20200705	3	20200702
10000	20200706	4	20200702
10000	20200707	5	20200702
20000	20200701	1	20200700
20000	20200710	2	20200708
20000	20200711	3	20200708
20000	20200712	4	20200708

到这里我们发现，所有连续的日期，diff列都是相等的，那么我们可以把diff列作为group key，做简单的sum、max、min操作，即可得到最后的正确答案。

select   
    user_id,  
    diff,  
    sum(1) as continuous_day,  
    max(login_date) as max_login_date,  
    min(login_date) as min_login_date
from   
    tmp_login_table_2
group by   
    user_id,  
    diff

最终得到数据

user_id	continuous_day	max_login_date	min_login_date
10000	2	20200701	20200702
10000	3	20200705	20200707
20000	1	20200701	20200701
20000	3	20200710	20200712

这类问题通常的思路就是通过构造新的列，辅助完成计算。