长短视频流入流出计算
之前写了好久都写错了没有数据。。。
-- app流入流出的定义:app1在[-32,-16]活跃 >= 1次,app2不活跃;app1在[-15,0]不活跃 ,app2活跃 >= 2次
-- 上半月活跃
-- 一个imei对应多个app
with period_table1 as (
select
imeimd5,appname,apppackage,tag,active_counts
from (
select appname,apppackage,tag
from miui.long_short_video_lists) app_list
inner join
(
select imeimd5,pkgname,
count(distinct from_unixtime(usageTime, 'yyyyMMdd')) as active_counts
from miui_data.miui_app_middletier_new
lateral view explode(usageDataMap) t as pkgname,act_value
lateral view explode(act_value.recentUsageReportDataMap) r as usageTime,usage
where
from_unixtime(usageTime, 'yyyyMMdd') >= ${
date-33}
and from_unixtime(usageTime, 'yyyyMMdd')