数据比对通用SQL模版

-- ********************************************************************--
-- author:xujinchen
-- create time:2023-06-14 17:51:04
-- ********************************************************************--
with ods_dc as (
    select pt,
           count(*) as cnt
    from ${database1}.${tablename1}
    where pt between '${begin_date}' and '${end_date}'
    group by pt
),
ods as (
    select pt,
           count(*) as cnt
    from ${database1}.${tablename1}
    where pt between '${begin_date}' and '${end_date}'
    group by pt
),
anl as (
    select ods_dc.pt as ods_dc_pt,
           ods_dc.cnt as ods_dc_cnt,
           ods.pt as ods_pt,
           ods.cnt as ods_cnt
    from ods_dc
    full outer join ods on ods_dc.pt=ods.pt
)

select ods_dc_pt,
       ods_pt,
       ods_dc_cnt,
       ods_cnt,
       case when ods_dc_cnt>ods_cnt then 1 else 0 end as flag
from anl
where ods_dc_cnt is NULL or ods_cnt is NULL or ods_dc_cnt!=ods_cnt;

　　比对数据量差异，返回有差异的分区。