hive3.1.x 窗口函数：lag()和lead() 作者：马育民 • 2021-04-23 19:59 • 阅读：10197 # 介绍 `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 ``` [](https://www.malaoshi.top/upload/pic/hive/Snipaste_2021-11-23_20-41-57.png) # 案例：查询连续登录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 ``` [](https://www.malaoshi.top/upload/pic/hive/20220319_102503.png) ### 第二步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 ``` 执行结果： |name| |----| |lucy| |李雷 | |韩梅梅 | # 查询连续登录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 ``` [](https://www.malaoshi.top/upload/pic/hive/20220319_104315.png) 通过上面查询结果可知，满足登录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 ``` 执行结果： |name| |----| |lucy| 原文出处：http://malaoshi.top/show_1IX2HWD9osCc.html