pandas实现hive的lag和lead函数

lag和lead

该函数的格式如下:

• 第一个参数为列名，
• 第二个参数为往上第n行（可选，默认为1），
• 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

lag

lag(字段名,N，默认值) over(partition by 分组字段 order by 排序字段 排序方式) 

lead

lead(字段名,N，默认值) over(partition by 分组字段 order by 排序字段 排序方式)

案例:

select
  cookieid,
  createtime,
  url,
  row_number() over (partition by cookieid order by createtime) as rn,
  LAG(createtime,1,'1970-01-01 00:00:00') over (partition by cookieid order by createtime) as last_1_time,
  LAG(createtime,2) over (partition by cookieid order by createtime) as last_2_time
from cookie.cookie4

select
  cookieid,
  createtime,
  url,
  row_number() over (partition by cookieid order by createtime) as rn,
  LEAD(createtime,1,'1970-01-01 00:00:00') over (partition by cookieid order by createtime) as next_1_time,
  LEAD(createtime,2) over (partition by cookieid order by createtime) as next_2_time
from cookie.cookie4;