[大数据]Hive练习题之连续n天

数据源

guid01,2018-02-28
guid01,2018-03-01
guid01,2018-03-02
guid01,2018-03-04
guid01,2018-03-05
guid01,2018-03-06
guid01,2018-03-07
guid02,2018-03-01
guid02,2018-03-02
guid02,2018-03-03
guid02,2018-03-06

建表

create table if not exists test.tb_login (uid string, dt string)
row format delimited fields terminated by ",";

创建并导入数据

vim /doit/tb_login
load data local inpath "/doit/tb_login" into table test.tb_login;

需求:
求连续3天以上登录(不包含3天)的用户以及首次登录日期和最后登录日期,以及登录天数

解法:
第一步: 使用窗口函数,按照用户分组,登录时间排序, 依次打上标号

SELECT
 uid,
 dt,
 ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) rn
FROM
 tb_login

第二步: 使用date_sub函数, 将登录时间与标号进行相减

SELECT
 uid,
 dt,
 DATE_SUB(dt,rn) dt_dif
FROM
 (SELECT
  uid,
  dt,
  ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) rn
 FROM
  tb_login)v1

第三步:
使用groupby对用户和相减后的日期分组, 使用min max函数找出用户所在时间段的首次登录时间和最后登录时间, 使用count统计出登录的次数, 使用having筛选登录次数大于3的数据

SELECT
 uid,
 MIN(dt) start_time,
 MAX(dt) end_time,
 COUNT(dt) times
FROM
 (SELECT
  uid,
  dt,
  DATE_SUB(dt,rn) dt_dif
 FROM
  (SELECT
   uid,
   dt,
   ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt) rn
  FROM
   tb_login) v1) v2
GROUP BY uid,dt_dif
HAVING times > 3