比来身边有不少小伙伴在推敲跳槽的工作，免不了的会碰到一些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
如斯，就求得了所有效户的最大年夜持续活泼天数了：
无论是第一种照样第二种，重要都是考察窗口函数的应用。?练控制窗口函数，并灵活应用，此类问题就不在话下了。
更多SQL标题正在整顿傍边，持续更新ing……
[声明]本文版权归原作者所有，内容为作者小我不雅点，转载目标在于传递更多信息，如涉及作品内容、版权等问题，可接洽本站删除，感谢。
更多内容可存眷微信"大众,"号：成都CDA数据分析师。
