Hive使用窗口函数分时间间隔规划用户会话-lag/sum

最新推荐文章于 2025-09-29 19:56:49 发布

原创最新推荐文章于 2025-09-29 19:56:49 发布 · 1.8k 阅读

2 ·

CC 4.0 BY-SA版权

Jamie

大数据同时被 2 个专栏收录

16 篇文章

订阅专栏

Hive

6 篇文章

订阅专栏

本文介绍了一种基于时间戳和用户行为数据的session划分方法，通过判断用户动作间的时间间隔来确定不同的会话，适用于分析用户在应用中的活跃模式。

背景

用户可能在app上活跃多次，而这多次界定为半个钟，也就是说，假如我7点到8点这段期间使用了app且进行了活跃操作，隔了半个钟后我重新打开了app进行了活跃的操作，假设活跃时间区间为12点到13点，那么，7-8点这段时间我们定为session1，12-13点这段时间我们定为session2

思路

根据时间戳reporttime来针对用户进行排序，获取这段期间的动作时间里线，使用lag进行判别，假设本次动作与上次动作时间间隔操作1800s（半个钟）那么就赋予1作为标识

实现

select
    tdbank_imp_date -- string  partition fields
    ,uin -- string  QQ或微信
    ,module --  string  业务模块
    ,action --  string  用户行为
    ,obj2 --    string  对象2
    ,platform --    string  平台（iOS、Android等）
    ,source --  string  来源
    ,timelong --    bigint  时长
    -- ,timestr -- string  上报时间
    ,reporttime	--	string	数据上报时间戳（后台默认添加，精确到秒，如1556436373）
    ,is_new -- 1 表示新用户，0表示老用户
	,sum(if(reporttime - pre_rt > 1800, 1, 0)) over(partition by uin order by reporttime asc) as session_id
	,pre_rt
from 
(

select
    tdbank_imp_date -- string  partition fields
    ,uin -- string  QQ或微信
    ,module --  string  业务模块
    ,action --  string  用户行为
    ,obj2 --    string  对象2
    ,platform --    string  平台（iOS、Android等）
    ,source --  string  来源
    ,timelong --    bigint  时长
    -- ,timestr -- string  上报时间
    ,reporttime	--	string	数据上报时间戳（后台默认添加，精确到秒，如1556436373）
    ,is_new
	,lag(reporttime, 1,  unix_timestamp()) over(partition by uin order by reporttime asc) as pre_rt
from 
(
select 
    tdbank_imp_date -- string  partition fields
    ,tb1.uin -- string  QQ或微信
    ,module --  string  业务模块
    ,action --  string  用户行为
    ,obj2 --    string  对象2
    ,platform --    string  平台（iOS、Android等）
    ,source --  string  来源
    ,timelong --    bigint  时长
    -- ,timestr -- string  上报时间
    ,reporttime	--	string	数据上报时间戳（后台默认添加，精确到秒，如1556436373）
    ,if(tb2.uin is not null, 1, 0) as is_new -- 1 表示新用户，0表示老用户
from 
(
select 
    tdbank_imp_date -- string  partition fields
    ,uin -- string  QQ或微信
    ,module --  string  业务模块
    ,action --  string  用户行为
    ,obj2 --    string  对象2
    ,platform --    string  平台（iOS、Android等）
    ,source --  string  来源
    ,timelong --    bigint  时长
    -- ,nvl(timestr, reporttime) as timestr -- string  上报时间
    ,nvl(reporttime, timestr) as reporttime	--	string	数据上报时间戳（后台默认添加，精确到秒，如1556436373）


FROM
base_report_tb
WHERE
tdbank_imp_date between  ${YYYYMMDD}00 AND  ${YYYYMMDD}23  
) tb1 left join
(
select
    uin
FROM
    new_users
WHERE ftime = ${YYYYMMDD}

) tb2 on tb1.uin = tb2.uin
) t 
) t2
-- limit 100000



-- is_new -- 是否新用户
-- session_id -- 会话id