统计2021-06-14日的每个访客的每个会话的浏览页面数和每个页面的浏览时长
确定好每个会话的数据
1.访客是没有登录的用户,使用设备id(mid)作为唯一标识
2.用户访问某一个页面,连续点击多个超链接,到会话断开,是为一个会话
3.一个会话的第一个页面的上页id为空,可以用来判断页面是否为会话开始页
select mid_id,last_page_id,page_id,ts,during_time,
if(last_page_id is null,ts,null) session_start_point
from dwd_page_log where dt='2021-06-14'
利用窗口函数给每条数据增加一个会话id
concat(mid_id,'-',last_value(session_start_point,true)
over(partition by mid_id order by ts)) session_id
last_value(field,boolean) over():取分区内最后一条数据,方向从下往上,默认为false
- last_value(field,false)
- last_value(field,true)
利用会话id进行分组统计
select
mid_id,
session_id,
count(*) page_count,
sum(during_time) during_time
from
(
select
mid_id,last_page_id,page_id,ts,during_time,
session_start_point,
concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
from
(
select
mid_id,last_page_id,page_id,ts,during_time,
if(last_page_id is null,ts,null) session_start_point
from dwd_page_log
where dt='2021-06-14'
) t1
) t2
group by mid_id,session_id;