该方法可用于计算两个相邻日期间的间隔,如可用来计算用户活跃度,看出每个用户多次下单中的每两次之间的时间间隔,用以划分用户(如很活跃,活跃等,),还可以看出用户的大致购买时间间隔,对于超出时间间隔未再次下单的用户(沉睡用户),可想办法进行唤醒等。
1.查看现有数据
select * from emp;
2.通过分析函数lag直接获取上一个记录的信息
select deptno,hiredate,
lag(hiredate) over (partition by deptno order by hiredate asc) as last_day
from emp
order by deptno,hiredate;
如果使用的数据库不支持lag函数怎么办呢?只能靠自己写了
如果支持CTE
with t1 as
(select deptno,hiredate,
row_number() over (partition by deptno order by hiredate asc) as rn —先通过row_number函数创建一个排序标识,然后再通过这个标识做Join
from emp)
select a.deptno,a.hiredate,b.hiredate as last_day
from t1 a
left join t1 b
on a.deptno = b.deptno
and a.rn = b.rn + 1
order by deptno,hiredate;
如果连CTE也没法建,那只能把这个虚拟表写2次了,这样就会执行2次,降低效率
select a.deptno,a.hiredate,b.hiredate as last_day
from (
select deptno,hiredate,
row_number() over (partition by deptno order by hiredate asc) as rn
from emp ) a
left join (
select deptno,hiredate,
row_number() over (partition by deptno order by hiredate asc) as rn
from emp ) b
on a.deptno = b.deptno and a.rn = b.rn + 1
order by deptno,hiredate;
两者其实是一个意思,得到的结果如下: