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

相关文章

MySQL 8 中的一个强大功能 JSON_TABLE示例详解

《MySQL8中的一个强大功能JSON_TABLE示例详解》JSON_TABLE是MySQL8中引入的一个强大功能,它允许用户将JSON数据转换为关系表格式,从而可以更方便地在SQL查询中处理J... 目录基本语法示例示例查询解释应用场景不适用场景1. ‌jsON 数据结构过于复杂或动态变化‌2. ‌性能要

postgresql使用UUID函数的方法

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

Python实现终端清屏的几种方式详解

《Python实现终端清屏的几种方式详解》在使用Python进行终端交互式编程时,我们经常需要清空当前终端屏幕的内容,本文为大家整理了几种常见的实现方法,有需要的小伙伴可以参考下... 目录方法一:使用 `os` 模块调用系统命令方法二:使用 `subprocess` 模块执行命令方法三:打印多个换行符模拟

SpringBoot+EasyPOI轻松实现Excel和Word导出PDF

《SpringBoot+EasyPOI轻松实现Excel和Word导出PDF》在企业级开发中,将Excel和Word文档导出为PDF是常见需求,本文将结合​​EasyPOI和​​Aspose系列工具实... 目录一、环境准备与依赖配置1.1 方案选型1.2 依赖配置(商业库方案)二、Excel 导出 PDF

Python实现MQTT通信的示例代码

《Python实现MQTT通信的示例代码》本文主要介绍了Python实现MQTT通信的示例代码,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一... 目录1. 安装paho-mqtt库‌2. 搭建MQTT代理服务器(Broker)‌‌3. pytho

MySQL字符串常用函数详解

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

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

使用zip4j实现Java中的ZIP文件加密压缩的操作方法

《使用zip4j实现Java中的ZIP文件加密压缩的操作方法》本文介绍如何通过Maven集成zip4j1.3.2库创建带密码保护的ZIP文件,涵盖依赖配置、代码示例及加密原理,确保数据安全性,感兴趣的... 目录1. zip4j库介绍和版本1.1 zip4j库概述1.2 zip4j的版本演变1.3 zip4

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java