连续登陆5天及以上的用户

这个面试经典sql题，相信大家都遇到过，下面用窗口函数lead和lag解决这个问题

首先我们了解一下lead和lag的用法：

lag函数：lag(colnum,n,default)

这个函数用于统计窗口内往上第n行的值，其中参数1为列名，参数2为位移数，比如4，即往上数第四行，参数3为默认值，不指定即为NULL

lead函数：lead(column,n,default)

这个函数用于统计窗口内往下第n行的值，其中参数1为列名，参数2为位移数，比如4，即往下数第四行，参数3为默认值，不指定即为NULL

下面介绍一下实际案例

1.第一步先做一下数据准备,用户表和登录表

--用户表

create table test.test_accounts

(

id string,

name string

);

insert into test.test_accounts(id,name)values('001','zhangsan');

insert into test.test_accounts(id,name)values('002','lisi');

insert into test.test_accounts(id,name)values('003','wangwu');

insert into test.test_accounts(id,name)values('004','zhaoliu');

insert into test.test_accounts(id,name)values('005','fengqi');

insert into test.test_accounts(id,name)values('006','qianba');

--登陆表

create table dip_public.test_logins_zy_0806

(

id string,

login_dt string

);

insert into test.test_logins(id,login_dt)values('001','2021-07-06');

insert into test.test_logins(id,login_dt)values('001','2021-07-30');

insert into test.test_logins(id,login_dt)values('001','2021-07-31');

insert into test.test_logins(id,login_dt)values('001','2021-08-01');

insert into test.test_logins(id,login_dt)values('001','2021-08-02');

insert into test.test_logins(id,login_dt)values('001','2021-08-03');

insert into test.test_logins(id,login_dt)values('001','2021-08-04');

insert into test.test_logins(id,login_dt)values('001','2021-08-05');

insert into test.test_logins(id,login_dt)values('002','2021-07-31');

insert into test.test_logins(id,login_dt)values('002','2021-08-01');

insert into test.test_logins(id,login_dt)values('002','2021-08-02');

insert into test.test_logins(id,login_dt)values('003','2021-08-03');

insert into test.test_logins(id,login_dt)values('003','2021-08-04');

insert into test.test_logins(id,login_dt)values('003','2021-08-05');

2.先用lag函数看一下效果

select

id,

login_dt,

lead(login_dt,4) over(partition by id order by login_dt desc) as first_dt

from test.test_logins

3.输出连续登录5天以上的用户 

select

aa.id,

aa.name

from

(select

id,

name

from test.test_accounts

group by

id,

name

) aa

join

(select

id,

login_dt,

lead(login_dt,4) over(partition by id order by login_dt desc) as first_dt

from test.test_logins

) bb

on bb.id=aa.id

where datediff(bb.login_dt,bb.first_dt)>=4

group by

aa.id,

aa.name