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

2024-06-02 14:58

本文主要是介绍Hive 分析函数lead、lag实例应用,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!


 说明
Hive的分析函数又叫窗口函数,在oracle中就有这样的分析函数,主要用来做数据统计分析的。
Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率,其中over()表示当前查询的结果集对象,括号里面的语句则表示对这个结果集进行处理。

函数介绍
LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
参数1为列名,参数2为往上第n行(可选,默认为1),参数3为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)

LEAD
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
参数1为列名,参数2为往下第n行(可选,默认为1),参数3为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)

场景
问题
用户Peter在浏览网页,在某个时刻,Peter点进了某个页面,过一段时间后,Peter又进入了另外一个页面,如此反复,那怎么去统计Peter在某个特定网页的停留时间呢,又或是怎么统计某个网页用户停留的总时间呢?
数据准备
现在用户的行为都被采集了,处理转换到hive数据表,表结构如下:
create table test.user_log(
    userid string,
    time string,
    url string
) row format delimited fields terminated by ',';
记录数据:
+------------------+----------------------+---------------+--+
| user_log.userid  |    user_log.time     | user_log.url  |
+------------------+----------------------+---------------+--+
| 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          |
+------------------+----------------------+---------------+--+

分析步骤
获取用户在某个页面停留的起始与结束时间
select userid,
       time stime,
       lead(time) over(partition by userid order by time) etime,
       url 
  from test.user_log;
结果:
+---------+----------------------+----------------------+-------+--+
| userid  |        stime         |        etime         |  url  |
+---------+----------------------+----------------------+-------+--+
| 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  |
+---------+----------------------+----------------------+-------+--+

计算用户在页面停留的时间间隔(实际分析当中,这里要做数据清洗工作,如果一个用户停留了4、5个小时,那这条记录肯定是不可取的。)
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;
结果:
+---------+----------------------+----------------------+---------+-------+--+
| userid  |        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  |
+---------+----------------------+----------------------+---------+-------+--+

计算每个页面停留的总时间,某个用户访问某个页面的总时间
select nvl(url,'-1') url,
       nvl(userid,'-1') userid,
       sum(period) totol_peroid from (
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
) a group by url, userid with rollup;
结果:
+-------+---------+---------------+--+
|  url  | userid  | totol_peroid  |
+-------+---------+---------------+--+
| -1    | -1      | 14940         |
| url1  | -1      | 620           |
| url1  | Marry   | 310           |
| url1  | Peter   | 310           |
| url2  | -1      | 180           |
| url2  | Marry   | 90            |
| url2  | Peter   | 90            |
| url3  | -1      | 6760          |
| url3  | Marry   | 3380          |
| url3  | Peter   | 3380          |
| url4  | -1      | 7380          |
| url4  | Marry   | 3690          |
| url4  | Peter   | 3690          |
| url5  | -1      | NULL          |
| url5  | Marry   | NULL          |
| url5  | Peter   | NULL          |
+-------+---------+---------------+--+
 

这篇关于Hive 分析函数lead、lag实例应用的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1024291

相关文章

postgresql使用UUID函数的方法

《postgresql使用UUID函数的方法》本文给大家介绍postgresql使用UUID函数的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录PostgreSQL有两种生成uuid的方法。可以先通过sql查看是否已安装扩展函数,和可以安装的扩展函数

MySQL字符串常用函数详解

《MySQL字符串常用函数详解》本文给大家介绍MySQL字符串常用函数,本文结合实例代码给大家介绍的非常详细,对大家学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql字符串常用函数一、获取二、大小写转换三、拼接四、截取五、比较、反转、替换六、去空白、填充MySQL字符串常用函数一、

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

解决1093 - You can‘t specify target table报错问题及原因分析

《解决1093-Youcan‘tspecifytargettable报错问题及原因分析》MySQL1093错误因UPDATE/DELETE语句的FROM子句直接引用目标表或嵌套子查询导致,... 目录报js错原因分析具体原因解决办法方法一:使用临时表方法二:使用JOIN方法三:使用EXISTS示例总结报错原

C++中assign函数的使用

《C++中assign函数的使用》在C++标准模板库中,std::list等容器都提供了assign成员函数,它比操作符更灵活,支持多种初始化方式,下面就来介绍一下assign的用法,具有一定的参考价... 目录​1.assign的基本功能​​语法​2. 具体用法示例​​​(1) 填充n个相同值​​(2)

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更

Redis中Stream详解及应用小结

《Redis中Stream详解及应用小结》RedisStreams是Redis5.0引入的新功能,提供了一种类似于传统消息队列的机制,但具有更高的灵活性和可扩展性,本文给大家介绍Redis中Strea... 目录1. Redis Stream 概述2. Redis Stream 的基本操作2.1. XADD

JSONArray在Java中的应用操作实例

《JSONArray在Java中的应用操作实例》JSONArray是org.json库用于处理JSON数组的类,可将Java对象(Map/List)转换为JSON格式,提供增删改查等操作,适用于前后端... 目录1. jsONArray定义与功能1.1 JSONArray概念阐释1.1.1 什么是JSONA

PostgreSQL中rank()窗口函数实用指南与示例

《PostgreSQL中rank()窗口函数实用指南与示例》在数据分析和数据库管理中,经常需要对数据进行排名操作,PostgreSQL提供了强大的窗口函数rank(),可以方便地对结果集中的行进行排名... 目录一、rank()函数简介二、基础示例:部门内员工薪资排名示例数据排名查询三、高级应用示例1. 每

nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析(结合应用场景)

《nginx-t、nginx-sstop和nginx-sreload命令的详细解析(结合应用场景)》本文解析Nginx的-t、-sstop、-sreload命令,分别用于配置语法检... 以下是关于 nginx -t、nginx -s stop 和 nginx -s reload 命令的详细解析,结合实际应