lead函数在统计页面浏览时长中的应用

1. 创建页面浏览记录表

create table if not exists page_view
(

    ,page_id                   string       comment '页面id'
    ,page_status               string       comment '浏览状态:1:退出;0:进入'
    ,ux_event_time             bigint       comment '发生时间(毫秒)'
)
comment '页面进出明细表'
partitioned by (pt_d  string  comment '天分区')
row format delimited
fields terminated by '\001'
lines terminated by '\n'
stored as orc
;

2.使用lead函数+开窗函数进行统计浏览时长

select
  page_id
  ,sum(if(status_in = '0' and status_out = '1', status_out - status_in,0)) page_view_all_time --只计算符合进入--退出这种顺序的时间
from 
(
select
    page_id
   ,ux_event_time                                                               as in_time       --进入时间
   ,page_status                                                                 as status_in     --进入状态值
   ,lead(ux_event_time,1) over (partition by page_id order by ux_event_time asc)   as out_time      --退出时间
   ,lead(page_status,1) over (partition by page_id order by ux_event_time asc)     as status_out    --退出状态值
from page_view
where pt_d = '20211230'
) t
group by page_id

3.结果

3.1 第一段sql部分结果

3.2 最终结果