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

相关文章

中文分词jieba库的使用与实景应用(一)

知识星球:https://articles.zsxq.com/id_fxvgc803qmr2.html 目录 一.定义: 精确模式(默认模式): 全模式: 搜索引擎模式: paddle 模式(基于深度学习的分词模式): 二 自定义词典 三.文本解析   调整词出现的频率 四. 关键词提取 A. 基于TF-IDF算法的关键词提取 B. 基于TextRank算法的关键词提取

水位雨量在线监测系统概述及应用介绍

在当今社会,随着科技的飞速发展,各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中,水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术,其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成,形成了一个完整的闭环系统。 数据采集单元:这是系统的“眼睛”,

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

csu 1446 Problem J Modified LCS (扩展欧几里得算法的简单应用)

这是一道扩展欧几里得算法的简单应用题,这题是在湖南多校训练赛中队友ac的一道题,在比赛之后请教了队友,然后自己把它a掉 这也是自己独自做扩展欧几里得算法的题目 题意:把题意转变下就变成了:求d1*x - d2*y = f2 - f1的解,很明显用exgcd来解 下面介绍一下exgcd的一些知识点:求ax + by = c的解 一、首先求ax + by = gcd(a,b)的解 这个

hdu1394(线段树点更新的应用)

题意:求一个序列经过一定的操作得到的序列的最小逆序数 这题会用到逆序数的一个性质,在0到n-1这些数字组成的乱序排列,将第一个数字A移到最后一位,得到的逆序数为res-a+(n-a-1) 知道上面的知识点后,可以用暴力来解 代码如下: #include<iostream>#include<algorithm>#include<cstring>#include<stack>#in

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

zoj3820(树的直径的应用)

题意:在一颗树上找两个点,使得所有点到选择与其更近的一个点的距离的最大值最小。 思路:如果是选择一个点的话,那么点就是直径的中点。现在考虑两个点的情况,先求树的直径,再把直径最中间的边去掉,再求剩下的两个子树中直径的中点。 代码如下: #include <stdio.h>#include <string.h>#include <algorithm>#include <map>#

【区块链 + 人才服务】可信教育区块链治理系统 | FISCO BCOS应用案例

伴随着区块链技术的不断完善,其在教育信息化中的应用也在持续发展。利用区块链数据共识、不可篡改的特性, 将与教育相关的数据要素在区块链上进行存证确权,在确保数据可信的前提下,促进教育的公平、透明、开放,为教育教学质量提升赋能,实现教育数据的安全共享、高等教育体系的智慧治理。 可信教育区块链治理系统的顶层治理架构由教育部、高校、企业、学生等多方角色共同参与建设、维护,支撑教育资源共享、教学质量评估、

【机器学习】高斯过程的基本概念和应用领域以及在python中的实例

引言 高斯过程(Gaussian Process,简称GP)是一种概率模型,用于描述一组随机变量的联合概率分布,其中任何一个有限维度的子集都具有高斯分布 文章目录 引言一、高斯过程1.1 基本定义1.1.1 随机过程1.1.2 高斯分布 1.2 高斯过程的特性1.2.1 联合高斯性1.2.2 均值函数1.2.3 协方差函数(或核函数) 1.3 核函数1.4 高斯过程回归(Gauss

AI行业应用(不定期更新)

ChatPDF 可以让你上传一个 PDF 文件,然后针对这个 PDF 进行小结和提问。你可以把各种各样你要研究的分析报告交给它,快速获取到想要知道的信息。https://www.chatpdf.com/