visitlog表结构如下:
visitdate userid age
2019-02-11 test_1 23
2019-02-11 test_2 19
2019-02-11 test_3 39
2019-02-11 test_1 23
2019-02-11 test_3 39
2019-02-11 test_1 23
2019-02-12 test_2 19
2019-02-13 test_1 23
2019-02-15 test_2 19
2019-02-16 test_2 19
本题难点为:连续两天访问记录的用户,需要用到 lead()函数
lead(col,n,DEFAULT) 用于统计窗口内往下第n行值
col为列名, n为可选参数,往下n行,默认为1; 参数3为默认值(当往上第n行为null的时候,取默认值,如不指定,则为null),需配合开窗函数使用。
select visitdate,userid,age,lead(visitdate,1,'1970-01-01') over(partition by userid order by visitdate) as time from visitlog
最终sql如下所示:
select count(distinct t1.userid) ,avg(t1.age)
from(
select visitdate,userid,age,
lead(visitdate,1,'1970-01-01') over(partition by userid order by visitdate) as time
from visitlog
) t1
where datediff(t1.time,t1.visitdate)=1;
代码解释:使用lead函数多出来的time列为计算的基准列,通过datediff()函数可以计算出一个类似于flag的值,该值如果为1,即可表示该用户为连续两天访问的活跃用户。
考虑到用户可能是在一段时间内,两次满足活跃用户条件的单一用户,所以这里加上distinct作为区分。