代码比较模板

--比较数据 模板
select
a.frefundid,a.fstatus,b.fstatus
from
dw_dwd.dwd_jv_ecc_t_sd_tbrefund_fl_d a
join
 dw_dwd.dwd_jv_api_t_sd_tbrefund_fl_d b
on a.frefundid=b.frefundid
where nvl(a.fstatus,'')!=nvl(b.fstatus,'');

select content,dt
from dw_ods.ods_app_tmall_refund_get_topic_iu_h
where  dt >= '2023030520'
--   and  dt <= '2023030705'
and get_json_object(get_json_object(get_json_object(content,'$.refund_get_response'),'$.refund'),'$.refund_id')
='207878187834035415'
order by dt;




--比较数据 模板
select
a.foid,b.foid,a.fadjustfee,b.fadjustfee
from
dw_dwd.dwd_jv_ecc_t_sd_tbsoentry_fl_d a
join
 dw_dwd.dwd_jv_api_t_sd_tbsoentry_fl_d b
on a.foid=b.foid
where nvl(cast(a.fdiscountfee as decimal(10,2)) ,'')!=nvl(cast(b.fdiscountfee as decimal(10,2)),'');



--对比订单明细 脚本全
select sum(if(nvl(cast(a.
fadjustfee          as string),'')=nvl(cast(b.fadjustfee           as string),''),0,1)) as check_1
,sum(if(nvl(cast(a.
fbuyerrate          as string),'')=nvl(cast(b.fbuyerrate           as string),''),0,1)) as check_2
,sum(if(nvl(cast(a.
fcid                as string),'')=nvl(cast(b.fcid                 as string),''),0,1)) as check_3
,sum(if(nvl(cast(a.
fconsigntime        as string),'')=nvl(cast(b.fconsigntime         as string),''),0,1)) as check_4
,sum(if(nvl(cast(a.
fdiscountfee        as string),'')=nvl(cast(b.fdiscountfee         as string),''),0,1)) as check_5
,sum(if(nvl(cast(a.
fendtime            as string),'')=nvl(cast(b.fendtime             as string),''),0,1)) as check_6
,sum(if(nvl(cast(a.
finvoiceno          as string),'')=nvl(cast(b.finvoiceno           as string),''),0,1)) as check_7
,sum(if(nvl(cast(a.
fisoversold         as string),'')=nvl(cast(b.fisoversold          as string),''),0,1)) as check_8
,sum(if(nvl(cast(a.
fitemmealid         as string),'')=nvl(cast(b.fitemmealid          as string),''),0,1)) as check_9
,sum(if(nvl(cast(a.
fitemmealname       as string),'')=nvl(cast(b.fitemmealname        as string),''),0,1)) as check_10
,sum(if(nvl(cast(a.
flogisticscompany   as string),'')=nvl(cast(b.flogisticscompany    as string),''),0,1)) as check_11
,sum(if(nvl(cast(a.
fmodified           as string),'')=nvl(cast(b.fmodified            as string),''),0,1)) as check_12
,sum(if(nvl(cast(a.
fnum                as string),'')=nvl(cast(b.fnum                 as string),''),0,1)) as check_13
,sum(if(nvl(cast(a.
fnumiid             as string),'')=nvl(cast(b.fnumiid              as string),''),0,1)) as check_14
,sum(if(nvl(cast(a.
foid                as string),'')=nvl(cast(b.foid                 as string),''),0,1)) as check_15
,sum(if(nvl(cast(a.
forderfrom          as string),'')=nvl(cast(b.forderfrom           as string),''),0,1)) as check_16
,sum(if(nvl(cast(a.
fouteriid           as string),'')=nvl(cast(b.fouteriid            as string),''),0,1)) as check_17
,sum(if(nvl(cast(a.
fouterskuid         as string),'')=nvl(cast(b.fouterskuid          as string),''),0,1)) as check_18
,sum(if(nvl(cast(a.
fpayment            as string),'')=nvl(cast(b.fpayment             as string),''),0,1)) as check_19
,sum(if(nvl(cast(a.
fpicpath            as string),'')=nvl(cast(b.fpicpath             as string),''),0,1)) as check_20
,sum(if(nvl(cast(a.
fprice              as string),'')=nvl(cast(b.fprice               as string),''),0,1)) as check_21
,sum(if(nvl(cast(a.
frefundid           as string),'')=nvl(cast(b.frefundid            as string),''),0,1)) as check_22
,sum(if(nvl(cast(a.
frefundstatus       as string),'')=nvl(cast(b.frefundstatus        as string),''),0,1)) as check_23
,sum(if(nvl(cast(a.
fsellernick         as string),'')=nvl(cast(b.fsellernick          as string),''),0,1)) as check_24
,sum(if(nvl(cast(a.
fsellerrate         as string),'')=nvl(cast(b.fsellerrate          as string),''),0,1)) as check_25
,sum(if(nvl(cast(a.
fsellertype         as string),'')=nvl(cast(b.fsellertype          as string),''),0,1)) as check_26
,sum(if(nvl(cast(a.
fshippingtype       as string),'')=nvl(cast(b.fshippingtype        as string),''),0,1)) as check_27
,sum(if(nvl(cast(a.
fskuid              as string),'')=nvl(cast(b.fskuid               as string),''),0,1)) as check_28
,sum(if(nvl(cast(a.
fstatus             as string),'')=nvl(cast(b.fstatus              as string),''),0,1)) as check_29
,sum(if(nvl(cast(a.
ftimeoutactiontime  as string),'')=nvl(cast(b.ftimeoutactiontime   as string),''),0,1)) as check_30
,sum(if(nvl(cast(a.
ftitle              as string),'')=nvl(cast(b.ftitle               as string),''),0,1)) as check_31
,sum(if(nvl(cast(a.
ftotalfee           as string),'')=nvl(cast(b.ftotalfee            as string),''),0,1)) as check_32
,sum(if(nvl(cast(a.
fstorecode          as string),'')=nvl(cast(b.fstorecode    as string),''),0,1)) as check_33
from (
select
foid,
fadjustfee         ,
fbuyerrate         ,
fcid               ,
fconsigntime       ,
fdiscountfee       ,
fendtime           ,
finvoiceno         ,
fisoversold        ,
fitemmealid        ,
fitemmealname      ,
flogisticscompany  ,
fmodified          ,
fnum               ,
fnumiid            ,
foid               ,
forderfrom         ,
fouteriid          ,
fouterskuid        ,
fpayment           ,
fpicpath           ,
fprice             ,
frefundid          ,
frefundstatus      ,
fsellernick        ,
fsellerrate        ,
fsellertype        ,
fshippingtype      ,
fskuid             ,
fstatus            ,
ftimeoutactiontime ,
ftitle             ,
ftotalfee          ,
fstorecode
from dw_dwd.dwd_jv_ecc_t_sd_tbsoentry_fl_d
) a
join (
    select foid
         , fadjustfee
         , fbuyerrate
         , fcid
         , fconsigntime
         , fdiscountfee
         , fendtime
         , finvoiceno
         , fisoversold
         , fitemmealid
         , fitemmealname
         , flogisticscompany
         , fmodified
         , fnum
         , fnumiid
         , foid
         , forderfrom
         , fouteriid
         , fouterskuid
         , fpayment
         , fpicpath
         , fprice
         , frefundid
         , frefundstatus
         , fsellernick
         , fsellerrate
         , fsellertype
         , fshippingtype
         , fskuid
         , fstatus
         , ftimeoutactiontime
         , ftitle
         , ftotalfee
         , fstorecode
    from dw_dwd.dwd_jv_api_t_sd_tbsoentry_fl_d
) b on a.
foid = b.foid
;





--对比订单主表 脚本全
select sum(if(nvl(cast(a.
fid                as string),'')=nvl(cast(b.fid                as string),''),0,1)) as check_1
,sum(if(nvl(cast(a.
fcreatetime        as string),'')=nvl(cast(b.fcreatetime        as string),''),0,1)) as check_2
,sum(if(nvl(cast(a.
flastupdatetime    as string),'')=nvl(cast(b.flastupdatetime    as string),''),0,1)) as check_3
,sum(if(nvl(cast(a.
fbuyermessage      as string),'')=nvl(cast(b.fbuyermessage      as string),''),0,1)) as check_4
,sum(if(nvl(cast(a.
fbuyernick         as string),'')=nvl(cast(b.fbuyernick         as string),''),0,1)) as check_5
,sum(if(nvl(cast(a.
fconsigntime       as string),'')=nvl(cast(b.fconsigntime       as string),''),0,1)) as check_6
,sum(if(nvl(cast(a.
fcreated           as string),'')=nvl(cast(b.fcreated           as string),''),0,1)) as check_7
,sum(if(nvl(cast(a.
fdiscountfee       as string),'')=nvl(cast(b.fdiscountfee       as string),''),0,1)) as check_8
,sum(if(nvl(cast(a.
fendtime           as string),'')=nvl(cast(b.fendtime           as string),''),0,1)) as check_9
,sum(if(nvl(cast(a.
fhasbuyermessage   as string),'')=nvl(cast(b.fhasbuyermessage   as string),''),0,1)) as check_10

,sum(if(nvl(cast(a.
fmodified          as string),'')=nvl(cast(b.fmodified          as string),''),0,1)) as check_11
,sum(if(nvl(cast(a.
fpaytime           as string),'')=nvl(cast(b.fpaytime           as string),''),0,1)) as check_12
,sum(if(nvl(cast(a.
fpayment           as string),'')=nvl(cast(b.fpayment           as string),''),0,1)) as check_13
,sum(if(nvl(cast(a.
fpostfee           as string),'')=nvl(cast(b.fpostfee           as string),''),0,1)) as check_14
,sum(if(nvl(cast(a.
freceiveraddress   as string),'')=nvl(cast(b.freceiveraddress   as string),''),0,1)) as check_15
,sum(if(nvl(cast(a.
freceivercity      as string),'')=nvl(cast(b.freceivercity      as string),''),0,1)) as check_16
,sum(if(nvl(cast(a.
freceiverdistrict  as string),'')=nvl(cast(b.freceiverdistrict  as string),''),0,1)) as check_17
,sum(if(nvl(cast(a.
freceivermobile    as string),'')=nvl(cast(b.freceivermobile    as string),''),0,1)) as check_18
,sum(if(nvl(cast(a.
freceivername      as string),'')=nvl(cast(b.freceivername      as string),''),0,1)) as check_19
,sum(if(nvl(cast(a.
freceiverphone     as string),'')=nvl(cast(b.freceiverphone     as string),''),0,1)) as check_20
,sum(if(nvl(cast(a.
freceiverstate     as string),'')=nvl(cast(b.freceiverstate     as string),''),0,1)) as check_21
,sum(if(nvl(cast(a.
freceiverzip       as string),'')=nvl(cast(b.freceiverzip       as string),''),0,1)) as check_22
,sum(if(nvl(cast(a.
fsellermobile      as string),'')=nvl(cast(b.fsellermobile      as string),''),0,1)) as check_23
,sum(if(nvl(cast(a.
fsellername        as string),'')=nvl(cast(b.fsellername        as string),''),0,1)) as check_24
,sum(if(nvl(cast(a.
fstatus            as string),'')=nvl(cast(b.fstatus            as string),''),0,1)) as check_25
,sum(if(nvl(cast(a.
fsteppaidfee       as string),'')=nvl(cast(b.fsteppaidfee       as string),''),0,1)) as check_26
,sum(if(nvl(cast(a.
fsteptradestatus   as string),'')=nvl(cast(b.fsteptradestatus   as string),''),0,1)) as check_27
,sum(if(nvl(cast(a.
ftid               as string),'')=nvl(cast(b.ftid               as string),''),0,1)) as check_28
,sum(if(nvl(cast(a.
ftitle             as string),'')=nvl(cast(b.ftitle             as string),''),0,1)) as check_29
,sum(if(nvl(cast(a.
ftradefrom         as string),'')=nvl(cast(b.ftradefrom         as string),''),0,1)) as check_30
,sum(if(nvl(cast(a.
ftype              as string),'')=nvl(cast(b.ftype
 as string),''),0,1)) as check_31
from (
select
ftid,
fid               ,
fcreatetime       ,
flastupdatetime   ,
fbuyermessage     ,
fbuyernick        ,
fconsigntime      ,
fcreated          ,
fdiscountfee      ,
fendtime          ,
fhasbuyermessage  ,
fmodified         ,
fpaytime          ,
fpayment          ,
fpostfee          ,
freceiveraddress  ,
freceivercity     ,
freceiverdistrict ,
freceivermobile   ,
freceivername     ,
freceiverphone    ,
freceiverstate    ,
freceiverzip      ,
fsellermobile     ,
fsellername       ,
fstatus           ,
fsteppaidfee      ,
fsteptradestatus  ,
ftid              ,
ftitle            ,
ftradefrom        ,
ftype
from dw_dwd.dwd_jv_ecc_t_sd_tbsobill_fl_d
) a
join (
    select ftid
         , fid
         , fcreatetime
         , flastupdatetime
         , fbuyermessage
         , fbuyernick
         , fconsigntime
         , fcreated
         , fdiscountfee
         , fendtime
         , fhasbuyermessage
         , fmodified
         , fpaytime
         , fpayment
         , fpostfee
         , freceiveraddress
         , freceivercity
         , freceiverdistrict
         , freceivermobile
         , freceivername
         , freceiverphone
         , freceiverstate
         , freceiverzip
         , fsellermobile
         , fsellername
         , fstatus
         , fsteppaidfee
         , fsteptradestatus
         , ftid
         , ftitle
         , ftradefrom
         , ftype
    from dw_dwd.dwd_jv_api_t_sd_tbsobill_fl_d
) b on a.
ftid = b.ftid
where a.fcreated<='2023-06-12';

ods_app_tmall_trade_fullinfo_get_topic_iu_h

ods_app_tmall_refund_get_topic_iu_h
insert overwrite table dw_ods.ods_app_tmall_trade_fullinfo_get_topic_iu_h
select content,dt from dw_ods.ods_ynap_dev_trade_fullinfo_get_topic where dt>='2023032317' and dt<='2023032419'
distribute by 50;
select count(1) from dw_ods.ods_app_tmall_refund_get_topic_iu_h where dt>='2023032422';


select count(1) from dw_dwd.dwd_jv_ecc_t_sd_tbsobill_fl_d
union all
select count(1) from dw_dwd.dwd_jv_api_t_sd_tbsobill_fl_d;