大数据pyspark sql分析函数lead()和lag()

1 功能

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值

2 语法

lag(col, offset=1, default=None)

col 被对比的字段

offset 偏移量

default 默认值

3 不多说，直接上案例

session_window = Window.partitionBy("user_id", "sponsor_id").orderBy(functions.col("event_time").asc())
    diff_df = df.withColumn("lead", functions.lead("event_time", 1).over(session_window))\
        .withColumn("lag", functions.lag("event_time", 1).over(session_window))\
        .filter("user_id is not null and user_id !='' and sponsor_id is not null")\
        .select("page", "sponsor_id", "user_id", "event_time", "lead","lag")

运行效果

4 结论

很明显，lead是往后偏移量，lag是往前一行偏移