驾驶舱表日常测试

-- 测试日活表是否数据正常：测试正常
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