Hive--窗口函数--lead函数--lag函数--first_value函数--last_value函数

最新推荐文章于 2024-07-29 20:37:12 发布

原创最新推荐文章于 2024-07-29 20:37:12 发布 · 753 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

本文详细介绍了SQL中的LAG和LEAD函数，这两种函数分别用于获取窗口内上一行和下一行的数据；同时介绍了FIRST_VALUE和LAST_VALUE函数，分别用于获取分组内排序后的首个和末尾值。这些函数对于进行复杂的时间序列分析和用户行为分析非常有用。

lag函数主用与统计窗口内向上第几行值。

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 website_url_info;

lead用于统计窗口内向下第几行值。

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 website_url_info;

FIRST_VALUE 取分组内排序后，截止到当前行，第一个值。

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 website_url_info;

LAST_VALUE 取分组内排序后，截止到当前行，最后一个值。

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 website_url_info;