介绍

lag() 和 lead() 分析函数可以在一次查询中 取出同一字段的 前N行的数据(Lag) 和 后N行的数据(Lead) 作为独立的列

应用场景

一般用于时间段内的统计查询，如：连续登录10天的用户，连续访问同一网页、商品、视频的用户等，使用这 2个 函数会减轻工作量

lead

语法：

lead(colName,N,defaultValue)

参数：

• colName：取哪一列的值
• N：向后偏移 N 行
• defaultValue：如果取不到返回的默认值

例子

查询用户登录时间和下一次登录时间

建表

create external table login_log(
    name string,
    login_time string
) row format delimited fields terminated by ',';

数据

李雷,2021-01-01
李雷,2021-01-02
李雷,2021-01-03
李雷,2021-01-05
韩梅梅,2021-01-01
韩梅梅,2021-01-02
韩梅梅,2021-01-03
韩梅梅,2021-01-04
韩梅梅,2021-01-06
lucy,2021-01-01
lucy,2021-01-02
lucy,2021-01-03
lucy,2021-01-04
lucy,2021-01-05

注意：不能有重复的日期

上传文件

load data local inpath '/program/login_log.txt' into table login_log

分析

• lead() 取后1行数据

• partition by 根据 name 分区

• order by：在一个分区内，根据 login_time 排序，取下一行记录（即：下一次登录时间），取不到就取0

注意：不能有重复的日期，如果有重复的日期，会完全错乱

sql

select name,login_time,lead(login_time,1,0) over (partition by name order by login_time) as next_login_time
from login_log

案例：查询连续登录2天的用户

分析

• 通过日期函数 date_add()， 计算 登录日期+1，就是 第二天

• 如果 第二天 等于 上面sql的 next_login_time ，就表示连续 2 天登录

第一步sql

select name,login_time,
    date_add(login_time,1) next_day,
    lead(login_time,1,0) over( PARTITION by name order by login_time) as next_login_time
    from login_log

第二步sql

with tbl as (
    select name,login_time,
    date_add(login_time,1) next_day,
    lead(login_time,1,0) over( PARTITION by name order by login_time) as next_login_time
    from login_log 
)
select  DISTINCT name from tbl where next_day= next_login_time

执行结果：

查询连续登录5天的用户

第一步 sql

select name,login_time,
    date_add(login_time,4) next4_day,
    lead(login_time,4,0) over( PARTITION by name order by login_time) as next_login_time
    from login_log

通过上面查询结果可知，满足登录5天的只要 lucy

第二步sql

with tbl as (
    select name,login_time,
    date_add(login_time,4) next4_day,
    lead(login_time,4,0) over( PARTITION by name order by login_time) as next_login_time
    from login_log
)
select DISTINCT name from tbl where next4_day= next_login_time

执行结果：

原文出处：/show_1IX2HWD9osCc.html