Hive SQL窗口函数实现页面统计(以腾云天下页面访问为例)

本文主要是介绍Hive SQL窗口函数实现页面统计(以腾云天下页面访问为例),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

埋点数据字段为:

userid,at,sid,pid分别表示用户id,访问时间,sessionId(区分一次启动),页面id

表名为beacon

所有数据均为模拟数据

2018-07-04 11:46:37	2856	efda26adec1c3eb8	h_01
2018-07-04 11:46:47	2856	efda26adec1c3eb8	h_03
2018-07-04 11:46:54	2856	efda26adec1c3eb8	h_02
2018-07-04 11:47:04	2856	efda26adec1c3eb8	h_02
2018-07-04 11:47:39	2856	efda26adec1c3eb8	h_04
2018-07-04 11:47:39	2856	efda26adec1c3eb8	h_09
2018-07-04 11:47:39	2856	efda26adec1c3eb8	h_01
2018-07-04 11:47:39	2856	efda26adec1c3eb8	h_03
2018-07-04 11:48:40	2856	efda26adec1c3eb8	h_07
2018-07-04 12:48:13	2856	b975601de0e1c2fc	h_01
2018-07-04 12:48:40	2856	b975601de0e1c2fc	h_03
2018-07-04 12:49:07	2856	b975601de0e1c2fc	h_02
2018-07-04 12:49:52	2856	b975601de0e1c2fc	h_07
2018-07-04 12:50:02	2856	5f52c96c52c98367	h_01
2018-07-04 12:50:47	2823	5f52c96c52c98367	h_03
2018-07-04 12:51:09	2823	5f52c96c52c98367	h_02

埋点原因无法统计到最后一个页面停留时间

最终可视化效果为如下图所示

无法查看图片可直接去腾云天下官网查看http://doc.talkingdata.com/posts/522

页面停留时间:

需要按sid分组后,访问时间从小到大排序,后一条时间减去前一条时间为上一条数据里页面的停留时间,故需要用到lead函数

1.求页面受访人数,页面受访(次数|比率)

select to_date(at) date,page p,count(1) pv,count(distinct userid) uv 
from tmp 
group by to_date(at),page

  结果如下

比率:需要每个页面的pv/总的pv,这里用窗口函数sum() over()

select t.date,t.p,t.uv,t.pv,round(t.pv/sum(t.pv) over(),3)
from
(
select to_date(at) date,page p,count(1) pv,count(distinct userid) uv 
from tmp 
group by to_date(at),page
) t

结果如下:

2.求受访总时长占比,平均停留时间(使用lead函数)

select to_date(at) date,page p,
lead(page,1,'endpage') over(partition by sid order by unix_timestamp(at)) nextpage,
at at,
lead(at,1,'endat') over(partition by sid order by unix_timestamp(at)) nextat
from tmp;

结果如下:

接下来求所有页面的停留时长,并过滤掉最后一个页面(下个页面为endpage)与页面与下个页面相同的数据

受访总时长占比为:每个页面总的访问时长/所有页面总的访问时间

select p.date date,
p.p page,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/count(1) over(partition by p.p),3) avglen,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.date),3) rate
from
(
select to_date(at) date,
page p,lead(page,1,'endpage') over(partition by sid order by unix_timestamp(at)) nextpage,
at at,
lead(at,1,'endat') over(partition by sid order by unix_timestamp(at)) nextat
from tmp
) p
where p.p!=p.nextpage and p.nextpage!='endpage'

结果如下:

因为使用over(),页面相同的数据都一样,故去重一下

select n.date date,n.page p,n.avglen avg,n.rate rate
from
(
select p.date date,p.p page,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/count(1) over(partition by p.p),3) avglen,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.date),3) rate
from
(
select to_date(at) date,page p,
lead(page,1,'endpage') over(partition by sid order by unix_timestamp(at)) nextpage,
at at,
lead(at,1,'endat') over(partition by sid order by unix_timestamp(at)) nextat
from tmp
) p
where p.p!=p.nextpage and p.nextpage!='endpage'
) n
group by n.date,n.page,n.avglen,n.rate

结果如下:

:

3.求离开应用

select to_date(browsepath.time) date,browsepath.p p,
round(sum(case when browsepath.nextpage='end' then 1 else 0 end)/sum(1),3) lrate
from
(
select at time,page p,
lead(page,1,'end') over(partition by sid order by unix_timestamp(at)) nextpage
from tmp
) browsepath
where browsepath.p!=browsepath.nextpage
group by to_date(browsepath.time),browsepath.p

结果如下:

4.走向

select j.date date,j.p p,
collect_list(concat_ws('_',j.nextpage,j.rate)) l
from
(
select b.date date,b.p p,b.nextpage nextpage,
cast(b.c/sum(b.c) over(partition by b.p) as string) rate
from
(
select to_date(browsepath.time) date,
browsepath.p p,browsepath.nextpage nextpage,count(1) c
from
(
select at time,page p,
lead(page,1,'end') over(partition by sid order by unix_timestamp(at)) nextpage
from tmp
) browsepath
where browsepath.p!=browsepath.nextpage and nextpage!='end'
group by to_date(browsepath.time),browsepath.p,browsepath.nextpage
) b
) j
group by j.date,j.p

结果如下:

接下来就是把sql join一下:

select pu.date,pu.p,pu.uv,pu.pv,len.rate,len.avg,lr.lrate,lr.path
from
(
select leave.date date,leave.p p,leave.lrate lrate,browse.l path
from
(
select to_date(browsepath.time) date,browsepath.p p,
round(sum(case when browsepath.nextpage='end' then 1 else 0 end)/sum(1),3) lrate
from
(
select at time,page p,
lead(page,1,'end') over(partition by sid order by unix_timestamp(at)) nextpage
from tmp
) browsepath
where browsepath.p!=browsepath.nextpage
group by to_date(browsepath.time),browsepath.p
) leave
full join
(
select j.date date,j.p p,collect_list(concat_ws('_',j.nextpage,j.rate)) l
from
(
select b.date date,b.p p,b.nextpage nextpage,
cast(b.c/sum(b.c) over(partition by b.p) as string) rate
from
(
select to_date(browsepath.time) date,browsepath.p p,browsepath.nextpage nextpage,count(1) c
from
(
select at time,page p,
lead(page,1,'end') over(partition by sid order by unix_timestamp(at)) nextpage
from tmp
) browsepath
where browsepath.p!=browsepath.nextpage and nextpage!='end'
group by to_date(browsepath.time),browsepath.p,browsepath.nextpage
) b
) j
group by j.date,j.p
) browse
on leave.date=browse.date and leave.p=browse.p
) lr
join
(
select t.date date,t.p p,concat_ws('_',cast(t.pv as string),
cast(round(t.pv/sum(pv) over(),3) as string)) pv,t.uv uv
from
(
select to_date(at) date,page p,count(1) pv,count(distinct userid) uv 
from tmp 
group by to_date(at),page
) t
) pu
on lr.date=pu.date and lr.p=pu.p
join
(
select n.date date,n.page p,n.avglen avg,n.rate rate
from
(
select p.date date,p.p page,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/count(1) over(partition by p.p),3) avglen,
round(sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.p)/sum(unix_timestamp(p.nextat)-unix_timestamp(p.at)) over(partition by p.date),3) rate
from
(
select to_date(at) date,page p,
lead(page,1,'endpage') over(partition by sid order by unix_timestamp(at)) nextpage,
at at,
lead(at,1,'endat') over(partition by sid order by unix_timestamp(at)) nextat
from tmp
) p
where p.p!=p.nextpage and p.nextpage!='endpage'
) n
group by n.date,n.page,n.avglen,n.rate
) len
on pu.date=len.date and pu.p=len.p;

这就ok啦,有不足的地方欢迎大家评论!

这篇关于Hive SQL窗口函数实现页面统计(以腾云天下页面访问为例)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



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

相关文章

基于Python实现一个图片拆分工具

《基于Python实现一个图片拆分工具》这篇文章主要为大家详细介绍了如何基于Python实现一个图片拆分工具,可以根据需要的行数和列数进行拆分,感兴趣的小伙伴可以跟随小编一起学习一下... 简单介绍先自己选择输入的图片,默认是输出到项目文件夹中,可以自己选择其他的文件夹,选择需要拆分的行数和列数,可以通过

Python中将嵌套列表扁平化的多种实现方法

《Python中将嵌套列表扁平化的多种实现方法》在Python编程中,我们常常会遇到需要将嵌套列表(即列表中包含列表)转换为一个一维的扁平列表的需求,本文将给大家介绍了多种实现这一目标的方法,需要的朋... 目录python中将嵌套列表扁平化的方法技术背景实现步骤1. 使用嵌套列表推导式2. 使用itert

Python使用pip工具实现包自动更新的多种方法

《Python使用pip工具实现包自动更新的多种方法》本文深入探讨了使用Python的pip工具实现包自动更新的各种方法和技术,我们将从基础概念开始,逐步介绍手动更新方法、自动化脚本编写、结合CI/C... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核

在Linux中改变echo输出颜色的实现方法

《在Linux中改变echo输出颜色的实现方法》在Linux系统的命令行环境下,为了使输出信息更加清晰、突出,便于用户快速识别和区分不同类型的信息,常常需要改变echo命令的输出颜色,所以本文给大家介... 目python录在linux中改变echo输出颜色的方法技术背景实现步骤使用ANSI转义码使用tpu

Python使用python-can实现合并BLF文件

《Python使用python-can实现合并BLF文件》python-can库是Python生态中专注于CAN总线通信与数据处理的强大工具,本文将使用python-can为BLF文件合并提供高效灵活... 目录一、python-can 库:CAN 数据处理的利器二、BLF 文件合并核心代码解析1. 基础合

Python使用OpenCV实现获取视频时长的小工具

《Python使用OpenCV实现获取视频时长的小工具》在处理视频数据时,获取视频的时长是一项常见且基础的需求,本文将详细介绍如何使用Python和OpenCV获取视频时长,并对每一行代码进行深入解析... 目录一、代码实现二、代码解析1. 导入 OpenCV 库2. 定义获取视频时长的函数3. 打开视频文

golang版本升级如何实现

《golang版本升级如何实现》:本文主要介绍golang版本升级如何实现问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录golanwww.chinasem.cng版本升级linux上golang版本升级删除golang旧版本安装golang最新版本总结gola

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

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

SpringBoot中SM2公钥加密、私钥解密的实现示例详解

《SpringBoot中SM2公钥加密、私钥解密的实现示例详解》本文介绍了如何在SpringBoot项目中实现SM2公钥加密和私钥解密的功能,通过使用Hutool库和BouncyCastle依赖,简化... 目录一、前言1、加密信息(示例)2、加密结果(示例)二、实现代码1、yml文件配置2、创建SM2工具

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分