【Spark数仓项目】需求六：构建设备会话维表

火之高兴

发布于 2024-07-25 15:42:14

500

发布于 2024-07-25 15:42:14

文章被收录于专栏：大数据应用技术

构建设备会话维表

需求说明

本需求继续针对dwd.event_log_detail表深度开发，完成对dws.mall_app_session_agr表（设备会话维表）的构建。本次需求更接近业务，查询结果可用于数据报表呈现使用。以下是需求结果字段案例：

分区日	设备ID	会话ID	起始时间	结束时间	访问页数	入口页	退出页	是否跳出会话
	d01	s01
	d01	s02

字段口径介绍

session_start_time(会话开始时间): 一个设备在一次会话中最小的时间戳
session_end_time  (会话结束时间)：一个设备在一次会话中最大的时间戳
page_count        (会话访问页数): 访问页面事件中获取properties里面url页面，然后去重的结果
enter_page        (入口页): 一次会话中第一次产生访问页面事件对应的url，就是入口页
leave_page        (退出页): 一次会话中最后一次产生访问页面事件对应的url，就是退出页
is_jump_session   (是否跳出会话):如果在一次会话中，没有访问页面事件，或者访问页面事件只出现1次，则是为跳出会话，是跳出会话记录为1，否则记录为0

根据以上口径进行开发，此口径通常是运营人员根据业务需求制定，由我们根据dwd层用户明细数据进行开发。

建表语句

create table dws.mall_app_session_agr(
   deviceid   		   string,  -- 设备编号
   sessionid  		   string,  -- 会话编号
   session_start_time  string, 	-- 会话开始时间
   session_end_time    string, 	-- 会话结束时间
   page_count          int, 	-- 会话访问页数
   enter_page          string, 	-- 入口页
   leave_page          string, 	-- 退出页
   is_jump_session     int      -- 是否跳出会话
)partitioned by(dt string)

此表中大多是和维度相关的字段，我们主要基于设备和会话，会话是我们在先前需求中所计算出的粒度更细的新会话。后六个字段即为我们分析所求。

查询语句

insert overwrite table dws.mall_app_session_agr
partition(dt='2023-06-22')
select deviceid,newsessionid,min(`timestamp`)  session_start_time,max(`timestamp`)   session_end_time,
          count(distinct if(eventid='pageView',properties['url'],null)),
           split(min( if(eventid = 'pageView',concat( `timestamp`,'_',properties['url']),null) ),'_')[1],
           split(max( if(eventid = 'pageView',concat( `timestamp`,'_',properties['url']),null) ),'_')[1],
           if(sum(if(eventid='pageView',1,0))<=1,1,0) 
from dwd.event_log_detail where dt = '2023-06-22'
group by deviceid,newsessionid

此代码中处理访问入口页面和出口页面的方法是用拼接时间戳在网页url前的方式，然后分组排序求出最大和最小，再将拼接好的时间戳和url用split分开即可。

详细代码需求分析(另一种解法)

Step 1

这是我们基于设备和会话构建mall_app_session_agr表需要的字段。

SELECT deviceid,newsessionid,eventid,properties, `timestamp` ,dt
from  dwd.event_log_detail

查询结果：

deviceid	newsessionid	eventid	properties	timestamp	dt
YOBBRQZUHWBP	aadhsbss-0	login	{"pageId":"sch0847","refUrl":"/schools/sch0014.html","url":"/schools/sch0847.html"}	1687393815036	2023-06-22
YOBBRQZUHWBP	aadhsbss-0	login	{"pageId":"sea0878","refUrl":"/contacts/con0457.html","url":"/search/sea0878.html"}	1687393834684	2023-06-22
YOBBRQZUHWBP	aadhsbss-0	promotionShow	{"pageId":"job0611","promotionId":"002","refUrl":"/contacts/con0753.html","url":"/jobs/job0611.html"}	1687393838222	2023-06-22
YOBBRQZUHWBP	aadhsbss-0	ColumnClick	{"columnId":"009","pageId":"stu0053","refUrl":"/jobs/job0992.html","url":"/students/stu0053.html"}	1687393855619	2023-06-22
YOBBRQZUHWBP	aadhsbss-0	pageView	{"pageId":"sea0049","refUrl":"/courses/azkaban/c034.html","url":"/search/sea0049.html"}	1687393870196	2023-06-22
YOBBRQZUHWBP	aadhsbss-0	login	{"pageId":"sch0675","refUrl":"/search/sea0864.html","url":"/schools/sch0675.html"}	1687393877608	2023-06-22

结果解释： deviceid和newsessionid是我们明细表中已有的。eventid字段中包括pageView,promotionShow,adShow等。在本需求中，我们需要的是pageView字段的数据。该条类型的数据是网页的访问记录事件，其他的字段口径都是基于此条目筛选所得。 properties字段中存储的是Map类型的KV值，我们可以从中拿到每次会话访问的网页URL。

Step 2

select deviceid,newsessionid,
	properties['url'] url,
	min(`timestamp`) over(PARTITION by deviceid ,newsessionid) mins,
	max(`timestamp`) over(PARTITION by deviceid ,newsessionid) maxs
from dwd.event_log_detail where dt = '2023-06-22' and eventid = 'pageView'

查询结果：

deviceid	newsessionid	url	mins	maxs
AAAOENQALRSV	fahzotnn-0	/schools/sch0905.html	1687393830674	1687394052014
AAAOENQALRSV	fahzotnn-0	/schools/sch0036.html	1687393830674	1687394052014
AABKFUZHJPRO	ccshlgfr-0	/students/stu0879.html	1687393883613	1687393883613
AAIOUXZTHHLF	akuzgghe-0	/teachers/tea0488.html	1687393951846	1687393957635

每个newsessionid下的url已经查找到，并且我们通过分组聚合的方式，将每个设备id分区下的最小时间戳和最大时间错都已经计算出。其中最小时间戳和最大时间错对应的URL就是访问入口页面和访问出口页面。

Step 3

select deviceid,newsessionid,
	min(`timestamp`) session_start_time,
	max(`timestamp`) session_end_time,
	min(if(`timestamp` = mins,url,null)) as enter_page,
	max(if(`timestamp` = maxs,url,null)) as leave_page
from (
	select deviceid,newsessionid,
		properties['url'] url,
		min(`timestamp`) over(PARTITION by deviceid ,newsessionid) mins,
		max(`timestamp`) over(PARTITION by deviceid ,newsessionid) maxs,
		`timestamp`
	from dwd.event_log_detail where dt = '2023-06-22' and eventid = 'pageView'
)t1
group by deviceid, newsessionid

现在我们通过子查询的方式，增加对timestamp取最大和最小确定开始和结束时间的字段，然后判断子查询内分区开窗最小的时间戳是否有url，如果有，就是进入页，如果没，就返回空，反之求退出页相同。

deviceid	newsessionid	session_start_time	session_end_time	enter_page	leave_page
AAAOENQALRSV	fahzotnn-0	1687393830674	1687394052014	/schools/sch0036.html	/schools/sch0905.html
AABKFUZHJPRO	ccshlgfr-0	1687393883613	1687393883613	/students/stu0879.html	/students/stu0879.html
AAIOUXZTHHLF	akuzgghe-0	1687393951846	1687393957635	/teachers/tea0488.html	/search/sea0142.html

Step 4

select deviceid,newsessionid,
	min(`timestamp`) session_start_time,
	max(`timestamp`) session_end_time,
	count(*) as page_count,
	min(if(`timestamp` = mins,url,null)) as enter_page,
	max(if(`timestamp` = maxs,url,null)) as leave_page,
	if(sum(if(eventid='pageView',1,0))<=1,1,0) as is_jump_session
from (
	select deviceid,newsessionid,
		properties['url'] url,
		min(`timestamp`) over(PARTITION by deviceid ,newsessionid) mins,
		max(`timestamp`) over(PARTITION by deviceid ,newsessionid) maxs,
		`timestamp`,
		eventid
	from dwd.event_log_detail where dt = '2023-06-22' and eventid = 'pageView'
)t1
group by deviceid, newsessionid

完善最终需求，加入了page_count和is_jump_session。没什么难的，是否跳出会话的意思是只要网页中有一个记为1次的pageView，说明这个pageView打开又被关闭，是一次跳出记录。所以我们统计这样的弹出页面，标记为1.

deviceid	newsessionid	session_start_time	session_end_time	page_count	enter_page	leave_page	is_jump_session
AAAOENQALRSV	fahzotnn-0	1687393830674	1687394052014	2	/schools/sch0036.html	/schools/sch0905.html	0
AABKFUZHJPRO	ccshlgfr-0	1687393883613	1687393883613	1	/students/stu0879.html	/students/stu0879.html	1
AAIOUXZTHHLF	akuzgghe-0	1687393951846	1687393957635	2	/teachers/tea0488.html	/search/sea0142.html	0

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-07-25，如有侵权请联系 cloudcommunity@tencent.com 删除

开发