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

相关文章

Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

《Androidkotlin中Channel和Flow的区别和选择使用场景分析》Kotlin协程中,Flow是冷数据流,按需触发,适合响应式数据处理;Channel是热数据流,持续发送,支持... 目录一、基本概念界定FlowChannel二、核心特性对比数据生产触发条件生产与消费的关系背压处理机制生命周期

PostgreSQL的扩展dict_int应用案例解析

《PostgreSQL的扩展dict_int应用案例解析》dict_int扩展为PostgreSQL提供了专业的整数文本处理能力,特别适合需要精确处理数字内容的搜索场景,本文给大家介绍PostgreS... 目录PostgreSQL的扩展dict_int一、扩展概述二、核心功能三、安装与启用四、字典配置方法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Python内置函数之classmethod函数使用详解

《Python内置函数之classmethod函数使用详解》:本文主要介绍Python内置函数之classmethod函数使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 类方法定义与基本语法2. 类方法 vs 实例方法 vs 静态方法3. 核心特性与用法(1编程客

Python函数作用域示例详解

《Python函数作用域示例详解》本文介绍了Python中的LEGB作用域规则,详细解析了变量查找的四个层级,通过具体代码示例,展示了各层级的变量访问规则和特性,对python函数作用域相关知识感兴趣... 目录一、LEGB 规则二、作用域实例2.1 局部作用域(Local)2.2 闭包作用域(Enclos

怎样通过分析GC日志来定位Java进程的内存问题

《怎样通过分析GC日志来定位Java进程的内存问题》:本文主要介绍怎样通过分析GC日志来定位Java进程的内存问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、GC 日志基础配置1. 启用详细 GC 日志2. 不同收集器的日志格式二、关键指标与分析维度1.

Python中re模块结合正则表达式的实际应用案例

《Python中re模块结合正则表达式的实际应用案例》Python中的re模块是用于处理正则表达式的强大工具,正则表达式是一种用来匹配字符串的模式,它可以在文本中搜索和匹配特定的字符串模式,这篇文章主... 目录前言re模块常用函数一、查看文本中是否包含 A 或 B 字符串二、替换多个关键词为统一格式三、提

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式?总结深入剖析一下 mysql 中的 COUNT

Java MQTT实战应用

《JavaMQTT实战应用》本文详解MQTT协议,涵盖其发布/订阅机制、低功耗高效特性、三种服务质量等级(QoS0/1/2),以及客户端、代理、主题的核心概念,最后提供Linux部署教程、Sprin... 目录一、MQTT协议二、MQTT优点三、三种服务质量等级四、客户端、代理、主题1. 客户端(Clien

MySQL 中 ROW_NUMBER() 函数最佳实践

《MySQL中ROW_NUMBER()函数最佳实践》MySQL中ROW_NUMBER()函数,作为窗口函数为每行分配唯一连续序号,区别于RANK()和DENSE_RANK(),特别适合分页、去重... 目录mysql 中 ROW_NUMBER() 函数详解一、基础语法二、核心特点三、典型应用场景1. 数据分