等待下一个秋

  • Spark
  • Flink
  • Hive
  • 数据仓库
  • ClickHouse
  • 收徒弟
  • Java
    • Spring
    • Mybatis
    • SpringBoot
    • 面试题
  • Python
    • Python基础
    • 爬虫
    • Numpy
    • matplotlib
    • Flask
  • 技术杂谈
    • Linux知识
    • Docker
    • Git教程
    • Redis教程
    • mysql
    • 前端
    • R语言
    • 机器学习
  • 关于我
  • 其它
    • 副业挣钱
    • 资料下载
    • 资料文档
专注于Hadoop/Spark/Flink/Hive/数据仓库等
关注公众号:大数据技术派,获取更多学习资料。
  1. 首页
  2. Hive
  3. 正文

Hive 分析函数lead、lag实例应用

2020年12月17日 5242点热度 0人点赞 0条评论

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

这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率,其中over()表示当前查询的结果集对象,括号里面的语句则表示对这个结果集进行处理。

1 LEAD

与LAG相反,LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

参数1为列名,参数2为往下第n行(可选,默认为1),参数3为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)

场景

用户Peter在浏览网页,在某个时刻,Peter点进了某个页面,过一段时间后,Peter又进入了另外一个页面,如此反复,那怎么去统计Peter在某个特定网页的停留时间呢,又或是怎么统计某个网页用户停留的总时间呢?

create table test.user_log(
    userid string,
    time string,
    url string
) row format delimited fields terminated by '\t';

使用load命令将如下测试数据导入:

Peter   2015-10-12 01:10:00 url1
Peter   2015-10-12 01:15:10 url2
Peter   2015-10-12 01:16:40 url3
Peter   2015-10-12 02:13:00 url4
Peter   2015-10-12 03:14:30 url5
Marry   2015-11-12 01:10:00 url1
Marry   2015-11-12 01:15:10 url2
Marry   2015-11-12 01:16:40 url3
Marry   2015-11-12 02:13:00 url4
Marry   2015-11-12 03:14:30 url5

数据说明:Peter 2015-10-12 01:10:00 url1 ,表示Peter在2015-10-12 01:10:00进入了网页url2,即记录的是进入网页的时间。

分析

要计算Peter在页面url1停留的时间,需要用进入页面url2的时间,减去进入url1的时间,即2015-10-12 01:15:10这个时间既是离开页面url1的时间,也是开始进入页面url2的时间。

获取用户在某个页面停留的起始与结束时间:

select userid,
time stime,
lead(time) over(partition by userid order by time) etime,
url 
from test.user_log;

stime就是进入页面时间,etime就是离开页面时间,结果是这样的:

Marry   2015-11-12 01:10:00 2015-11-12 01:15:10 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:16:40 url2
Marry   2015-11-12 01:16:40 2015-11-12 02:13:00 url3
Marry   2015-11-12 02:13:00 2015-11-12 03:14:30 url4
Marry   2015-11-12 03:14:30 NULL    url5
Peter   2015-10-12 01:10:00 2015-10-12 01:15:10 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:16:40 url2
Peter   2015-10-12 01:16:40 2015-10-12 02:13:00 url3
Peter   2015-10-12 02:13:00 2015-10-12 03:14:30 url4
Peter   2015-10-12 03:14:30 NULL    url5

用etime减去stime,然后按照用户分组累加就是,每个用户访问的总时间了。

select userid,
time stime,
lead(time) over(partition by userid order by time) etime,
UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),'yyyy-MM-dd HH:mm:ss')- UNIX_TIMESTAMP(time,'yyyy-MM-dd HH:mm:ss') period,
url 
from test.user_log;

这里展示出了stime(开始时间),etime(离开时间),period(停留时长),url(页面地址),结果:

Marry   2015-11-12 01:10:00 2015-11-12 01:15:10 310 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:16:40 90  url2
Marry   2015-11-12 01:16:40 2015-11-12 02:13:00 3380    url3
Marry   2015-11-12 02:13:00 2015-11-12 03:14:30 3690    url4
Marry   2015-11-12 03:14:30 NULL    NULL    url5
Peter   2015-10-12 01:10:00 2015-10-12 01:15:10 310 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:16:40 90  url2
Peter   2015-10-12 01:16:40 2015-10-12 02:13:00 3380    url3
Peter   2015-10-12 02:13:00 2015-10-12 03:14:30 3690    url4
Peter   2015-10-12 03:14:30 NULL    NULL    url5
  • 这里有空的情况,也就是没有获取到离开时间,这要看实际业务怎么定义了,如果算到23点,太长了。

2 Lag

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)可以用来做一些时间的维护,如上一次登录时间。

场景

用户Peter在浏览网页,在某个时刻,Peter点进了某个页面,过一段时间后,Peter又进入了另外一个页面,如此反复,那怎么去统计Peter在某个特定网页的停留时间呢,又或是怎么统计某个网页用户停留的总时间呢?

create table test.user_log(
    userid string,
    time string,
    url string
) row format delimited fields terminated by '\t';

使用load命令将如下测试数据导入:

Peter   2015-10-12 01:10:00 url1
Peter   2015-10-12 01:15:10 url2
Peter   2015-10-12 01:16:40 url3
Peter   2015-10-12 02:13:00 url4
Peter   2015-10-12 03:14:30 url5
Marry   2015-11-12 01:10:00 url1
Marry   2015-11-12 01:15:10 url2
Marry   2015-11-12 01:16:40 url3
Marry   2015-11-12 02:13:00 url4
Marry   2015-11-12 03:14:30 url5

数据说明:Peter 2015-10-12 01:10:00 url1 ,表示Peter在2015-10-12 01:10:00进入了网页url2,即记录的是进入网页的时间。

select userid,
time etime,
lag(time, 1, '1970-01-01 00:00:00') over(partition by userid order by time) stime,
url 
from test.user_log;

这里etime是结束时间,stime是开始时间,结果:

Marry   2015-11-12 01:10:00 1970-01-01 00:00:00 url1
Marry   2015-11-12 01:15:10 2015-11-12 01:10:00 url2
Marry   2015-11-12 01:16:40 2015-11-12 01:15:10 url3
Marry   2015-11-12 02:13:00 2015-11-12 01:16:40 url4
Marry   2015-11-12 03:14:30 2015-11-12 02:13:00 url5
Peter   2015-10-12 01:10:00 1970-01-01 00:00:00 url1
Peter   2015-10-12 01:15:10 2015-10-12 01:10:00 url2
Peter   2015-10-12 01:16:40 2015-10-12 01:15:10 url3
Peter   2015-10-12 02:13:00 2015-10-12 01:16:40 url4
Peter   2015-10-12 03:14:30 2015-10-12 02:13:00 url5

计算总时间,只需要用结束时间 - 开始时间,然后分组累加即可。

select userid,
UNIX_TIMESTAMP(time, 'yyyy-MM-dd HH:mm:ss') - 
UNIX_TIMESTAMP(lag(time, 1, '1970-01-01 00:00:00') over(partition by userid order by time), 'yyyy-MM-dd HH:mm:ss'),
url 
from test.user_log;

结果

Marry   1447290600  url1
Marry   310 url2
Marry   90  url3
Marry   3380    url4
Marry   3690    url5
Peter   1444612200  url1
Peter   310 url2
Peter   90  url3
Peter   3380    url4
Peter   3690    url5

因为有两个我将默认值置为了1970-01-01,所以算出来比较大,实际工作中需要按照实际情况处理。

标签: 大数据
最后更新:2022年1月22日

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

打赏 点赞
< 上一篇
下一篇 >

文章评论

取消回复

等待下一个秋

待我代码写成,便娶你为妻!专注于Hadoop/Spark/Flink/Hive/数据仓库等,关注公众号:大数据技术派,获取更多学习资料。

搜一搜
微信
最新 热点 随机
最新 热点 随机
深入理解ClickHouse跳数索引 ClickHouse主键索引最佳实践 Java和Python操作Clickhouse ChatGPT 注册教程 ChatGPT可以做什么 ClickHouse 自定义分区键
Hive的安装 Redis 数据类型 Matplotlib 艺术家教程 Redis 性能测试 Redis HyperLogLog Hadoop面试题总结(四)——YARN
标签聚合
挣钱 大数据 mysql 数据仓库 R语言 Python 算法 Hive 书籍 Java Flink Redis
文章归档
  • 2023年4月
  • 2023年3月
  • 2023年2月
  • 2022年12月
  • 2022年11月
  • 2022年9月
  • 2022年7月
  • 2022年6月
  • 2022年5月
  • 2022年4月
  • 2022年3月
  • 2022年2月
  • 2022年1月
  • 2021年12月
  • 2021年11月
  • 2021年10月
  • 2021年9月
  • 2021年8月
  • 2021年6月
  • 2021年5月
  • 2021年4月
  • 2021年3月
  • 2021年2月
  • 2021年1月
  • 2020年12月
  • 2020年11月
  • 2020年10月
  • 2020年9月
  • 2020年8月
  • 2020年7月
  • 2020年5月
  • 2020年4月
  • 2020年1月
  • 2019年9月
  • 2019年8月
  • 2019年7月
  • 2019年6月
  • 2019年5月
  • 2019年4月
  • 2019年3月
  • 2019年1月
  • 2018年12月
  • 2017年5月

©2022 ikeguang.com. 保留所有权利。

鄂ICP备2020019097号-1

鄂公网安备 42032202000160号