大厂数据开发必考SQL题

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

题目：

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

题目分析：

本题目的数据非常简单，容易理解，难点在怎么判断“连续登录”。我们知道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

我们得到的数据是这样的

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

到这里我们发现，所有连续的日期，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

最终得到数据

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