实现lead，lag窗口函数功能脚本

yipiantian

已于 2023-10-12 09:49:31 修改

阅读量383

点赞数

分类专栏： hive 文章标签：数据库 sql

于 2022-07-29 16:05:12 首次发布

本文链接：https://blog.csdn.net/yipiantian/article/details/126057971

版权

hive 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

lead:实现窗口向下移动n行（即下面的行往上移）

lag:实现窗口向上移动n行（即上面的行往下移）

案例脚本：

with t as
(
    select '10001' as uid,'2021-08-10 10:18:31' as dateline,'url3' as page
    union all
    select '10001' as uid,'2021-08-10 10:01:26' as dateline,'url1' as page
    union all
    select '10001' as uid,'2021-08-10 10:09:48' as dateline,'url2' as page
    union all
    select '10002' as uid,'2021-08-10 09:38:31' as dateline,'url3' as page
    union all
    select '10002' as uid,'2021-08-10 09:21:26' as dateline,'url1' as page
    union all
    select '10002' as uid,'2021-08-10 09:19:48' as dateline,'url2' as page
)
select
t.*
,lead(dateline) over(partition by uid sort by dateline) as dateline_lead
,lag(dateline) over(partition by uid sort by dateline) as dateline_lag
from t

业务含义：

用户在各页面访问的起止时间以及停留时长等。

例1：sql实现类似功能（本例实现向上移动1行）

with t as
(
    select '10001' as uid,'2021-08-10 10:18:31' as dateline,'url3' as page
    union all
    select '10001' as uid,'2021-08-10 10:01:26' as dateline,'url1' as page
    union all
    select '10001' as uid,'2021-08-10 10:09:48' as dateline,'url2' as page
    union all
    select '10002' as uid,'2021-08-10 09:38:31' as dateline,'url3' as page
    union all
    select '10002' as uid,'2021-08-10 09:21:26' as dateline,'url1' as page
    union all
    select '10002' as uid,'2021-08-10 09:19:48' as dateline,'url2' as page
)
,t1 (
    select
     uid,
     dateline,
     row_number() over(distribute by uid sort by dateline asc) as rank,
     page
   from t
)
select
t1.uid,
t1.dateline,
t1.page,
t2.dateline as dateline_end
from t1 left join t1 as t2 on t1.uid=t2.uid and t1.rank+1=t2.rank
;

数据集t展示如下：

移动后的数据集展示如下：

例2：A表转B表(区别上例)

with t as
(
    select '10001' as uid,'2021-08-10 10:18:31' as dateline,'url3' as page
    union all
    select '10001' as uid,'2021-08-10 10:01:26' as dateline,'url3' as page
    union all
    select '10001' as uid,'2021-08-10 10:09:48' as dateline,'url2' as page
    union all
    select '10001' as uid,'2021-08-10 10:10:03' as dateline,'url2' as page
    union all
    select '10002' as uid,'2021-08-10 09:38:31' as dateline,'url1' as page
    union all
    select '10002' as uid,'2021-08-10 09:21:26' as dateline,'url1' as page
    union all
    select '10002' as uid,'2021-08-10 09:19:48' as dateline,'url1' as page
    union all
    select '10002' as uid,'2021-08-10 09:20:48' as dateline,'url1' as page
)
,t1 (
    select
     uid,
     page,
     dateline,
     row_number() over(partition by uid,page order by dateline asc) as rank
   from t
)
select
t1.uid,
t1.page,
t1.dateline,
t2.dateline as dateline_end
from t1 left join t1 as t2 on t1.uid=t2.uid and t1.page=t2.page and t2.rank-t1.rank=1
order by 1,2,3
;