-- 测试日活表是否数据正常:测试正常
select
date,
distinct_cnt,
growth_cnt
from
(
select
date,
count(*) as distinct_cnt
from
dm_autocar.autocar_mds_user_by_device_distinct
where
date <= '${date}'
group by
date
)t_ug
left join
(
select
p_date,
count(*) as growth_cnt
from
growth.m_device_distinct
where
p_date <= '${DATE}'
and app_name = 'automobile'
group by
p_date
)t_growth
on concat_ws('-',substr(t_ug.date,1,4),substr(t_ug.date,5,2),substr(t_ug.date,7,2))= t_growth.p_date
where distinct_cnt <> growth_cnt
-- 测试分区数是否正常
select
app_name,
count(distinct date) as date_cnt
from
dm_autocar.autocar_mds_user_by_device_distinct
where
date <= '${date}'
group by
app_name
-- 测试新增表是否数据正常:测试正常
select
date,
ug_cnt,
growth_cnt
from
(
select
date,
count(*) as ug_cnt
from
dm_autocar.autocar_mds_activation_device_by_ug
where
date <= '${date}'
group by
date
)t_ug
left join
(
select
p_date,
count(*) as growth_cnt
from
growth.m_device_activation
where
p_date <= '${DATE}'
and app_name = 'automobile'
group by
p_date
)t_growth
on concat_ws('-',substr(t_ug.date,1,4),substr(t_ug.date,5,2),substr(t_ug.date,7,2))= t_growth.p_date
where ug_cnt <> growth_cnt
-- 测试分区数是否正常
select
app_name,
count(distinct date) as date_cnt
from
dm_autocar.autocar_mds_activation_device_by_ug
where
date <= '${date}'
group by
app_name