【hive】计算相邻两行时间戳的间隔（lag、lead）

业务需要，需要计算同一个用户，通过同一个访问渠道，每次访问的时间间隔。

主要要用到的lag和lead分析函数。这边借这个例子，详细展开来讲一下。

一、lag和lead说明

1. lag

lag(col, n, default) over()

• 说明： 用于统计窗口内向上第n行的值，与lead()刚好相反
• 参数1： 为要取值的列名
• 参数2： 为向上第n行，默认值为1，这个值是固定的，不能动态的变化
• 参数3： 为默认值，当向上第n行的值为NULL时，取默认值，如果不指定，则默认值为NULL
   

2. lead

lead(col, n, default) over()

• 说明： 用于统计窗口内向下第n行的值
• 参数1： 为要取值的列名
• 参数2： 为向下第n行，默认值为1，这个值是固定的，不能动态的变化
• 参数3： 为默认值，当向下第n行的值为NULL时，取默认值，如果不指定，则默认值为NULL

3. 用法说明

select 
*, 
lag(col_1, 1, 'none') over(partition by col_2 order by col_3) as lag_col 
from table;

• over()表示 lag()与lead()操作的数据都在over()的范围内，它里面可以使用partition by 语句（用于分组）和 order by 语句（用于排序）。
• partition by col_2 order by col_3 表示以col_2字段进行分组，再以col_3字段进行排序。

二、具体用法

构造原始数据如下，表名为user_visit_table：

使用lag函数，按user_id和visit_channel分组、然后按visit_time排序，获取到相邻的visit_time列，生成一个新的列，命名为time_lag，代码如下：

SELECT
user_id
,visit_channel
,visit_time
,LAG(visit_time) OVER(PARTITION BY user_id, visit_channel ORDER BY visit_time) AS time_lag
FROM user_visit_table;

执行结果如下：

然后再计算visit_time和time_lag的差值，就比较简单了，代码如下： 

SELECT
*
,IF(t.time_lag IS NOT null, unix_timestamp(t.visit_time)-unix_timestamp(t.time_lag), 0) AS time_diff
FROM
(
    SELECT
    user_id
    ,visit_channel
    ,visit_time
    ,LAG(visit_time) OVER(PARTITION BY user_id, visit_channel ORDER BY visit_time) AS time_lag
    FROM user_visit_table 
) t;

执行结果如下：

参考链接：

hive分析函数lead()和lag()的应用_不想起的昵称的博客-CSDN博客_hive lead