pandas实现hive的lag和lead函数 以及 first_value和last_value函数

lag和lead VS shift 

该函数的格式如下:

• 第一个参数为列名，
• 第二个参数为往上第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;

窗口函数的pandas实现

pandas中使用shift函数来实现lag/lead函数

import pandas as pd
df=pd.read_csv('c:/Users/WQBin/Desktop/data.csv',engine='python', names=['cookieid','createtime','url'])
df['last_url'] = df.sort_values('createtime').groupby('cookieid')['url'].shift(1)
df['next_url'] = df.sort_values('createtime').groupby('cookieid')['url'].shift(-1)
df.sort_values(by=['cookieid','createtime'])

first_value和 last_value VS first()和last()

• FIRST_VALUE 返回组中数据窗口的第一个值
  • 　　FIRST_VALUE ( [scalar_expression )OVER ( [ partition_by_clause ] order_by_clause )
• LAST_VALUE 返回组中数据窗口的最后一个值
  • 　　 LAST_VALUE ( [scalar_expression )OVER ( [ partition_by_clause order_by_clause )

select 
  cookieid, 
  createtime, 
  url, 
  row_number() over (partition by cookieid order by createtime) as rn, 
  first_value(url) over (partition by cookieid order by createtime) as first1 
from cookie.cookie4;

select 
  cookieid, 
  createtime, 
  url, 
  row_number() over (partition by cookieid order by createtime) as rn, 
  last_value(url) over (partition by cookieid order by createtime) as last1 
from cookie.cookie4;

窗口函数的pandas实现

df.sort_values(['createtime'], ascending=[ 1]).groupby(['cookieid']).first()

df.sort_values(['createtime'], ascending=[ 1]).groupby(['cookieid']).last()