SQL数据准备-桑吉图

长短视频流入流出计算
之前写了好久都写错了没有数据。。。

-- 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')