Hive_语法_连续N天登陆

Sql方式实现连续N天登陆

构造测试数据

create table dwd.login_log as
select 1 as user_id, "2020-01-01" as login_date
union all
select 1 as user_id, "2020-01-02" as login_date
union all
select 1 as user_id, "2020-01-07" as login_date
union all
select 1 as user_id, "2020-01-08" as login_date
union all
select 1 as user_id, "2020-01-09" as login_date
union all
select 1 as user_id, "2020-01-10" as login_date
union all
select 2 as user_id, "2020-01-01" as login_date
union all
select 2 as user_id, "2020-01-02" as login_date
union all
select 2 as user_id, "2020-01-04" as login_date

如果日期格式不规范，可以将其转换为标准格式

create table dwd.login_log as
select user_id,to_date(from_unixtime(UNIX_TIMESTAMP(login_date,'yyyy-MM-dd'))) as login_date
from tmp.login_log; -- tmp库为原始数据

1.使用lag&lead+datediff窗口函数

• 比如求连续三天登陆，可以将当天上一条数据和下一条数据都拿到，然后保证now-lag=lead-now=1即可;
• 如果是连续多天，可以取更多的数据，或者将数据全部更改为lag或者lead函数;
• datediff(date1, date2) - Returns the number of days between date1 and date2

select user_id 
from