桑基流向图 - HiveSQL准备

老工厂

由于老工厂的app表是每日全量表，因此才有app_type1和app_type2，将“上半月活跃下半月不活跃”，和
“上半月不活跃下半月活跃”分开来写，因为app_type1和app_type2中，都有全量的imei，所以可以用left jpin where t2.imei is null 的方式，找到“上半月活跃下半月不活跃”的用户，反之同理。

而且，app_type1为了找到“上半月活跃下半月不活跃”的使用了某app的用户，注意这里是两个维度，既要是同一个用户，也要是对于同一个app而言。我之前没有针对 t1.appname = t2.appname，就会变成“上半月活跃下半月不活跃”的用户，那么app_type1和app_type2以join，肯定就啥都没有了
（“上半月活跃下半月不活跃”的用户） join （“下半月活跃上半月不活跃”的用户），正好是null。

-- 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') <= ${date-17}
and date = ${date-1}
and userId = '0'
AND finalCountry = "CN"
group by imeimd5,pkgname
) period1
on app_list.apppackage = period1.pkgname
),

-- 下半月活跃
period_table2 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