背景
用户可能在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