--比较数据 模板
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;