面试官：“如何用 SQL 查询每个用户最大连续登录日期？”

原创 Maxim 大数据的奇妙冒险 2022-12-06 08:30

收录于合集

#sql1个

#面试3个

#hive8个

#大数据15个

前言：“如何用 SQL 查询每个用户最大连续登录日期？”

这道题是数据开发面试经常问到的题目，因为其比较考验候选人的 SQL 能力，今天就来简单讲讲其思路。

样例数据如下 login_log：

user_id	login_time
1	2022-11-28
1	2022-12-01
1	2022-12-02
1	2022-12-03
2	2022-12-01
2	2022-12-04

(1) 先开个窗

不了解或不熟悉开窗函数的，可以看下这篇：

通俗易懂：窗口函数 | 全是案例，看完不懂算我输

select user_id, login_time,row_number() over(partition by user_id order by login_time) num from login_log;

开完窗的结果如下：

user_id	login_time	num
1	2022-11-28	1
1	2022-12-01	2
1	2022-12-02	3
1	2022-12-03	4
2	2022-12-01	1
2	2022-12-04	2

(2) 利用等差数列的特性

若是连续登录，login_time - num 则相等。

因此可以这样写：

select t.user_id,     t.login_time,     date_sub(login_time, INTERVAL t.num DAY) date_rsltfrom (    select user_id, login_time,     row_number() over(partition by user_id order by login_time) num     from login_log) t;

注：INTERVAL 关键字可以用于计算时间间隔， date_sub(login_time, INTERVAL t.num DAY)表示登录时间减去 num 天；若 DAY 改成 HOUR 表示减去 num 小时。

以上 SQL 执行后得到：

user_id	login_time	date_rslt
1	2022-11-28	2022-11-27
1	2022-12-01	2022-11-29
1	2022-12-02	2022-11-29
1	2022-12-03	2022-11-29
2	2022-12-01	2022-11-30
2	2022-12-04	2022-12-02

(3) 分组后可获得结果

select a.user_id,    a.date_rslt,    count(1) as cntfrom(    select         t.user_id,         t.login_time,        date_sub(login_time, INTERVAL t.num DAY) date_rslt    from         (        select user_id,         login_time,         row_number() over(partition by user_id order by login_time) num         from login_log        ) t) agroup by a.user_id, a.date_rslt; 

以上就是全部内容啦，想学习更多大数据相关知识，关注大数据的奇妙冒险。

点赞关注不迷路，转载请注明出处。

大数据的奇妙冒险

分享大数据相关知识，关注我，一起经历大数据的奇妙冒险

34篇原创内容

公众号