大数据培训连续登录经典面试案例

一、简介

连续登录问题,不管是数据研发、还是数据分析,都是一个经典面试SQL题,本文从易到难,简单拓展,举一反三。

大数据培训连续登录经典面试案例 第1张

二、表结构

注:去过重的。

CREATE TABLE `user_login`(

`id` int COMMENT '用户主键',

`dt` string COMMENT '登录日期')

三、表数据

user_login.id user_login.dt

1001 2021-12-12

1002 2021-12-12

1001 2021-12-13

1001 2021-12-14

1001 2021-12-16

1002 2021-12-16

1001 2021-12-19

1002 2021-12-17

1001 2021-12-20

四、题目: 求出连续3天登录的用户ID

方法一:自关联

SELECT

tmp2.id

FROM

SELECT

tmp.id,

tmp.dt

FROM

SELECT

ul1.id,

ul1.dt

FROM

user_login ul1

INNER JOIN

user_login ul2

ON ul1.id = ul2.id

WHERE

ul2.dt BETWEEN date_sub(ul1.dt, 2) AND ul1.dt

) tmp

GROUP BY

tmp.id,

tmp.dt

HAVING

count(1) = 3

) tmp2

group by

tmp2.id

详解:

1:因为是固定的3天,所以可以采用主键自关联方法,【关注尚硅谷,轻松学IT】过滤条件为副表ul2的登录时间在主表登录时间和主表登录时间减去两天的区间内

2:然后根据主表的id,和登录时间分组,分组后数量正好是3天的,说明连续三天时间都有登录

3:根据步骤二的结果去重,获取结果

方法二:巧用窗口函数row_number

SELECT

tmp2.id

FROM

SELECT

id,

date_sub(dt, rn) dt_line

FROM

SELECT

id,

dt,

row_number () over (PARTITION BY id ORDER BY dt) rn

FROM

user_login

) tmp

GROUP BY

id,

date_sub(dt, rn)

HAVING

count(1) >= 3

) tmp2

group by

tmp2.id

详解:

1:利用row_number 提供一个自增序列

2:如果前后连续,那么登录日期减去步骤一提供的序列号结果相等

3:根据步骤二提供的日期基准,和用户id分组,过滤出大于等于3的结果

4:根据步骤三的结果去重,获取结果

方法三:采用窗口函数 lag(lead)

SELECT

tmp2.id

FROM

SELECT

id,

IF (datediff(dt, lag_dt) = 2, 1, 0) flag

FROM

SELECT

id,

dt,

lag (dt, 2, '0000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt

FROM

user_login

) tmp

) tmp2

WHERE

tmp2.flag = 1

GROUP BY

tmp2.id

详解:

1:因为是固定的3天,所以可以采用lag(lead)方法,求出前面第二个的日期与当前的日期差

2:根据步骤一的结果,判断如果日期差等于2,则是连续登录

3:根据步骤二的结果过滤分组,获取结果

方法四: 比对相邻数据日期,可扩展为组别划分

SELECT

id

FROM

SELECT

id,

max_dt_line

FROM

SELECT

id,

dt,

lag_dt,

max(dt_line) over (PARTITION BY id ORDER BY dt) max_dt_line

FROM

SELECT

id,

dt,

lag_dt,

IF (datediff(dt, lag_dt) = 1,"0",dt) dt_line

FROM

SELECT

id,

dt,

lag (dt, 1, '000-00-00') over (PARTITION BY id ORDER BY dt) lag_dt

FROM

user_login

) tmp

) tmp1

) tmp2

GROUP BY

id,

max_dt_line

HAVING

count(1) >= 3

) tmp4

GROUP BY

id;

详解:

1:利用lag(lead) 比较当前日期与上一次登录日期的差

2:如果日期差为1,则连续登录,记为0,【关注尚硅谷,轻松学IT】否则记录当前登录日期

3:利用窗口函数max,获取用户登录基准日期

4:根据基准日期分组,过滤出大于等于3天的数据

5:根据步骤四结果去重,获得最后结果

五、扩展总结

连续登录问题,其实可扩展为划分组别的问题。

如果是时间不固定,比如求每个用户最大连续登录天数,www.atguigu.com那么方法一和三就失效了。

如果仅仅是题目要求,求固定连续登录天数,那么个人更倾向于方法三。

文章来源于数据仓库与Python大数据

推荐阅读:

大数据开发之数仓建模

大数据开发之数据仓库架构分析

大数据开发 Spark 模块之SparkSQL

大数据开发之Spark入门

九七分享吧所有文章来源于网络收集整理,如有侵权请联系QQ2387153712删除,如果这篇文章对你有帮助或者还不错的请给小编点个小赞(◠‿◠),小编每天整理文章不容易(ಥ_ಥ)!!!

文章版权声明:除非注明,否则均为九七分享吧原创文章,转载或复制请以超链接形式并注明出处。

相关阅读

苹果 iOS/iPadOS 15.4.1 正式版发布

哎呦,被盗哦!周杰伦无聊猿NFT疑被钓鱼,价值超300万

“蔚小理”一季度成绩单:小鹏理想均跨过三万辆门槛,蔚来垫底

最完整的人类基因组序列,今天凌晨公布了!

手机行业不景气了?国产厂商大砍单:1.7亿订单取消

快手或将受益于互联网监管

我国实现首例V频段低轨卫星测控

北京外卖封签上线首日调查:1个封签约2分钱,已大面积推广

iPhone更换微信图标教程,安卓:不就是换个主题?

豆瓣关闭私密小组,壮士断腕为时未晚

台积电将5nm产量提高到15万片/月

京东增持京东物流 为了挽救股价还是应对激烈竞争?

发表评论

表情:
评论列表 (暂无评论,130人围观)

还没有评论,来说两句吧...

取消
微信二维码
微信二维码
支付宝二维码