本文详细介绍了如何基于dwd.event_log_detail表构建dws.mall_app_session_agr设备会话维表，包括会话开始和结束时间、访问页数、入口页和退出页的计算方法，以及是否跳出会话的判断。通过SQL建表和查询语句，展示了数据处理过程。
构建设备会话维表
需求说明
本需求继续针对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
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
