老工厂
由于老工厂的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