用 SQL 查询每个用户最大连续登录日期

👉👉👉 哈喽！大家好，我是【大数据的奇妙冒险】的作者 ，具有 Java 以及大数据开发经验，目前是一位大数据领域项目经理。
擅长 Java、大数据开发、项目管理等。持有 PMP 和 系统架构设计师证书，可以说是持证上岗了😀
如果有对【大数据】感兴趣的朋友，欢迎关注 公 众 号【大数据的奇妙冒险】

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

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

样例数据如下 login_log：

(1) 先开个窗

不了解或不熟悉开窗函数的可以点击查看：
面试官：你用过什么窗口函数？

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

开完窗的结果如下：

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

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

因此可以这样写：

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;
1
2
3
4
5
6
7
8
9

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

以上 SQL 执行后得到：

(3) 分组后可获得结果

select a.user_id,
	a.date_rslt,
	count(1) as cnt
from(
	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
) a
group by a.user_id, a.date_rslt; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

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