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()
大多数人都以为是才智成就了科学家,他们错了,是品格。---爱因斯坦