大厂高频面试题-连续登录问题-低调大师

大厂高频面试题-连续登录问题

2020-09-04 612

背景

对于数据开发人员来说，手写sql是比较熟悉的了，就有这样一道题，面试时需要手写sql，这就是非常经典的连续登录问题，大厂小厂都爱问，这种题说简单也不简单，说难也不难，关键是要有思路。

真题

hql统计连续登陆的三天及以上的用户

这个问题可以扩展到很多相似的问题：连续几个月充值会员、连续天数有商品卖出、连续打车、连续逾期。

数据提供

 用户ID、登入日期 user01,2018-02-28 user01,2018-03-01 user01,2018-03-02 user01,2018-03-04 user01,2018-03-05 user01,2018-03-06 user01,2018-03-07 user02,2018-03-01 user02,2018-03-02 user02,2018-03-03 user02,2018-03-06

输出字段

+---------+--------+-------------+-------------+--+|   uid   | times  | start_date  |  end_date   |+---------+--------+-------------+-------------+--+

群内讨论

这道题在群里发出后，大家就展开了激烈的讨论：

解决方案

可以看出来，有很多种不同的解决方案。

这里就为大家提供一种比较常见的方案：

建表

create table wedw_dw.t_login_info( user_id string  COMMENT '用户ID',login_date date COMMENT '登录日期')row format delimitedfields terminated by ',';

导数据

hdfs dfs -put /test/login.txt /data/hive/test/wedw/dw/t_login_info/

验证数据

select * from wedw_dw.t_login_info;+----------+-------------+--+| user_id  | login_date  |+----------+-------------+--+| user01   | 2018-02-28  || user01   | 2018-03-01  || user01   | 2018-03-02  || user01   | 2018-03-04  || user01   | 2018-03-05  || user01   | 2018-03-06  || user01   | 2018-03-07  || user02   | 2018-03-01  || user02   | 2018-03-02  || user02   | 2018-03-03  || user02   | 2018-03-06  |+----------+-------------+--+

解决方案

select t2.user_id         as user_id,count(1)           as times,min(t2.login_date) as start_date,max(t2.login_date) as end_datefrom(    select     t1.user_id    ,t1.login_date    ,date_sub(t1.login_date,rn) as date_diff    from    (        select         user_id        ,login_date        ,row_number() over(partition by user_id order by login_date asc) as rn         from        wedw_dw.t_login_info    ) t1) t2group by  t2.user_id,t2.date_diffhaving times >= 3;

结果

+----------+--------+-------------+-------------+--+| user_id  | times  | start_date  |  end_date   |+----------+--------+-------------+-------------+--+| user01   | 3      | 2018-02-28   | 2018-03-02  || user01    | 4      | 2018-03-04  | 2018-03-07  || user02   | 3      | 2018-03-01   | 2018-03-03  |+----------+--------+-------------+-------------+--+

思路

先把数据按照用户id分组，根据登录日期排序

select         user_id        ,login_date        ,row_number() over(partition by user_id order by login_date asc) as rn         from        wedw_dw.t_login_info

+----------+-------------+-----+--+| user_id  | login_date  | rn  |+----------+-------------+-----+--+| user01   | 2018-02-28  | 1   || user01   | 2018-03-01  | 2   || user01   | 2018-03-02  | 3   || user01   | 2018-03-04  | 4   || user01   | 2018-03-05  | 5   || user01   | 2018-03-06  | 6   || user01   | 2018-03-07  | 7   || user02   | 2018-03-01  | 1   || user02   | 2018-03-02  | 2   || user02   | 2018-03-03  | 3   || user02   | 2018-03-06  | 4   |+----------+-------------+-----+--+

2.用登录日期减去排序数字rn，得到的差值日期如果是相等的，则说明这两天肯定是连续的

select     t1.user_id    ,t1.login_date    ,date_sub(t1.login_date,rn) as date_diff    from    (        select         user_id        ,login_date        ,row_number() over(partition by user_id order by login_date asc) as rn         from        wedw_dw.t_login_info    ) t1    ;

+----------+-------------+-------------+--+| user_id  | login_date  |  date_diff  |+----------+-------------+-------------+--+| user01   | 2018-02-28  | 2018-02-27  || user01   | 2018-03-01  | 2018-02-27  || user01   | 2018-03-02  | 2018-02-27  || user01   | 2018-03-04  | 2018-02-28  || user01   | 2018-03-05  | 2018-02-28  || user01   | 2018-03-06  | 2018-02-28  || user01   | 2018-03-07  | 2018-02-28  || user02   | 2018-03-01  | 2018-02-28  || user02   | 2018-03-02  | 2018-02-28  || user02   | 2018-03-03  | 2018-02-28  || user02   | 2018-03-06  | 2018-03-02  |+----------+-------------+-------------+--+

3.根据user_id和日期差date_diff 分组，最小登录日期即为此次连续登录的开始日期start_date，最大登录日期即为结束日期end_date，登录次数即为分组后的count(1)

select t2.user_id         as user_id,count(1)           as times,min(t2.login_date) as start_date,max(t2.login_date) as end_datefrom(    select     t1.user_id    ,t1.login_date    ,date_sub(t1.login_date,rn) as date_diff    from    (        select         user_id        ,login_date        ,row_number() over(partition by user_id order by login_date asc) as rn         from        wedw_dw.t_login_info    ) t1) t2group by  t2.user_id,t2.date_diffhaving times >= 3;

+----------+--------+-------------+-------------+--+| user_id  | times  | start_date  |  end_date   |+----------+--------+-------------+-------------+--+| user01   | 3      | 2018-02-28   | 2018-03-02  || user01    | 4      | 2018-03-04  | 2018-03-07  || user02   | 3      | 2018-03-01   | 2018-03-03  |+----------+--------+-------------+-------------+--+

结束语

以上仅提供了一种解决方案，小伙伴有其他方案的话，可以进群交流哦

本文分享自微信公众号 - 大数据私房菜（datagogogo）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

微信关注我们

原文链接：https://my.oschina.net/u/4631230/blog/4544698

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

阿里面试官：HashMap 熟悉吧？好的，那就来聊聊 Redis 字典吧！

每天早上七点三十，准时推送干货最近，阿粉的一个朋友出去面试，回来跟阿粉抱怨，面试官不按套路出牌，直接打乱了他的节奏。事情是这样的，前面面试问了几个 Java 的相关问题，我朋友回答还不错，接下来面试官就问了一句：看来 Java 基础还不错，Java HashMap 你熟悉吧？我朋友回答。工作经常用，有看过源码。我朋友本来想着，你随便来吧，这个问题之前已经准备好了，随便问吧。谁知道，面试官下面一句：「那好的，我们来聊聊 Redis 字典吧。」直接将他整蒙逼。阿粉的朋友由于没怎么研究过 Redis 字典，所以这题就直接回答不知道了。「当然，如果面试中真不知道，那就回答不了解，直接下一题，不要乱答。」不过这一题，阿粉觉得还是很可惜，其实 Redis 字典基本原理与 HashMap 差不多，那我们其实可以套用这其中的原理，不求回答满分，但是怎么也可以得个及格分吧~ 面试过程真要碰到这个问题，我们可以从下面三个方面回答。数据结构元素增加过程扩容字典数据结构说起字典，也许大家比较陌生，但是我们都知道 Redis 本身提供 KV 查询的方式，这个 KV 就是其实通过底层...

2020-09-04

698

使用SQL窗口函数进行商务数据分析

本文会从一个商务分析案例入手，说明SQL窗口函数的使用方式。通过本文的5个需求分析，可以看出SQL窗口函数的功能十分强大，不仅能够使我们编写的SQL逻辑更加清晰，而且在某种程度上可以简化需求开发。数据准备本文主要分析只涉及一张订单表orders，操作过程在Hive中完成，具体数据如下： --建表CREATETABLEorders(order_idint,customer_idstring,citystring,add_timestring,amountdecimal(10,2));--准备数据INSERTINTOordersVALUES(1,"A","上海","2020-01-0100:00:00.000000",200),(2,"B","上海","2020-01-0500:00:00.000000",250),(3,"C","北京","2020-01-1200:00:00.000000",200),(4,"A","上海","2020-02-0400:00:00.000000",400),(5,"D","上海","2020-02-0500:00:00.000000",250),(5...

2020-09-04

572

发表评论

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。