👉👉👉 哈喽!大家好,我是【大数据的奇妙冒险】的作者 ,具有 Java 以及大数据开发经验,目前是一位大数据领域项目经理。
擅长 Java、大数据开发、项目管理等。持有 PMP 和 系统架构设计师证书,可以说是持证上岗了😀
如果有对【大数据】感兴趣的朋友,欢迎关注 公 众 号【大数据的奇妙冒险】
前言:“如何用 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_rslt
from
(
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 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;
以上就是全部内容啦,想学习更多大数据相关知识,欢迎关注大数据的奇妙冒险。
点赞关注不迷路,转载请注明出处。