SQL获取上一条记录的信息

淩亂°似流年 2023-02-25 10:18 36阅读 0赞

该方法可用于计算两个相邻日期间的间隔，如可用来计算用户活跃度，看出每个用户多次下单中的每两次之间的时间间隔，用以划分用户（如很活跃，活跃等，），还可以看出用户的大致购买时间间隔，对于超出时间间隔未再次下单的用户（沉睡用户），可想办法进行唤醒等。

# 1.查看现有数据 #

select * from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]

# 2.通过分析函数lag直接获取上一个记录的信息 #

select deptno,hiredate,
        lag(hiredate) over (partition by deptno order by hiredate asc) as last_day 
    from emp 
    order by deptno,hiredate;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]

# 如果使用的数据库不支持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;

两者其实是一个意思，得到的结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]: /images/20230209/88b678be585443be8b59f22670fdd50e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]: /images/20230209/d9fb0ac7f4a841189c276e58ffc65734.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 2]: /images/20230209/acb29f2e6c944c7d950e749dd223383f.png