select a.prov_nm, --省份
f.year_trade_amt, ---年交易额
a.year_fee_amt, ---年收入
f.bm_trade_amt/e.before_month_days bm_avg_amt, --上月日均交易额
a.bm_fee_amt/e.before_month_days bm_avg_fee,--上月日均收入
f.day_trade_amt,a.day_fee_amt,
a.day_ysr_trade_amt,
a.bm_ysr_trade_amt/e.before_month_days bm_ysr_avg_amt,
b.bm_equity_amt/e.before_month_days bm_avg_equity_amt,
c.day_equity_amt,
d.yj_cnt,
e.day_new_cnt,
e.new30_cnt,
e.new30_trans_cnt
from
(SELECT b.prov_nm,
--sum(a.year_trade_amt)/1000000.000 year_trade_amt,--单位：万元
sum(a.year_fee_amt)/1060000.0000 year_fee_amt,
--sum(a.bm_trade_amt)/1000000.000 bm_trade_amt,
sum(a.bm_fee_amt)/1060000.0000 bm_fee_amt,
--sum(a.day_trade_amt)/1000000.000 day_trade_amt,
sum(a.day_fee_amt)/1060000.0000 day_fee_amt,
sum(a.day_ysr_trade_amt)/1000000.0000 day_ysr_trade_amt,
sum(a.bm_ysr_trade_amt)/1000000.0000 bm_ysr_trade_amt
FROM
(SELECT fee_party,
--sum(cast(t.trade_amt AS bigint)) year_trade_amt,
sum(cast(t.fee_amt AS bigint)) year_fee_amt,
--sum(case when substr(cast(t.bdpms_etl_time as varchar),1,6)=regexp_replace(substr(cast(date_add('month',-1,current_date) as varchar),1,7),'-','') then cast(trade_amt as bigint) end) bm_trade_amt,
sum(case when substr(cast(t.bdpms_etl_time as varchar),1,6)=regexp_replace(substr(cast(date_add('month',-1,date_add('day',-1,current_date)) as varchar),1,7),'-','') then cast(fee_amt as bigint) end) bm_fee_amt,
--sum(case when substr(cast(t.bdpms_etl_time as varchar),1,8)=regexp_replace(substr(cast(date_add('day',-1,current_date) as varchar),1,10),'-','') then cast(trade_amt as bigint) end) day_trade_amt,
sum(case when substr(cast(t.bdpms_etl_time as varchar),1,8)=regexp_replace(substr(cast(date_add('day',-1,current_date) as varchar),1,10),'-','') then cast(fee_amt as bigint) end) day_fee_amt,
sum(case when substr(cast(t.bdpms_etl_time as varchar),1,8)=regexp_replace(substr(cast(date_add('day',-1,current_date) as varchar),1,10),'-','') and FEE_amt<>'0' then cast(trade_amt as bigint) end) day_ysr_trade_amt,
sum(case when substr(cast(t.bdpms_etl_time as varchar),1,6)=regexp_replace(substr(cast(date_add('month',-1,date_add('day',-1,current_date)) as varchar),1,7),'-','') and FEE_amt<>'0' then cast(trade_amt as bigint) end) bm_ysr_trade_amt
FROM ods_feecr_bdpms.t_fee_calculation_zengliang t
WHERE t.bdpms_etl_time >= 20210101000000
AND t.bdpms_etl_time < 20220101000000
AND t.incr_col='UPDATED_AT'
AND t.fee_flag = 'SUCCESS'
AND t.usable_flag = 'USABLE'
AND t.solution_no IN ( 'PS1' ,
'PS11018' ,
'PS11210' ,
'PS11666' ,
'PS11286' ,
'PS11208' ,
'PS10602' ,
'PS10404' ,
'PS10402' ,
'PS11700' ,
'PS11256' ,
'PS11024' ,
'PS10418' ,
'PS11850' ,
'PS11848')
GROUP BY fee_party)
a
LEFT JOIN
(SELECT distinct merchant_id,
prov_nm
FROM cdm_dsj.dim_merchant_basic_info_df
WHERE day_id=cast(date_add('day',-1,current_date) as varchar)) b
ON a.fee_party=b.merchant_id
GROUP BY b.prov_nm)
a
left join
(select b.prov_nm,sum(a.equity_use_amt)/1000000.0000 bm_equity_amt
from
(select merchant_id,sum(equity_use_amt) equity_use_amt
from cdm_dsj.dwd_marketing_use_di
where substr(day_id,1,7)=substr(cast(date_add('month',-1,date_add('day',-1,current_date)) as varchar),1,7)
--and dept_cd='20022'
AND solution_cd in
('PS1' ,
'PS11018' ,
'PS11210' ,
'PS11666' ,
'PS11286' ,
'PS11208' ,
'PS10602' ,
'PS10404' ,
'PS10402' ,
'PS11700' ,
'PS11256' ,
'PS11024' ,
'PS10418' ,
'PS11850' ,
'PS11848')
GROUP BY merchant_id) a
LEFT JOIN
(SELECT merchant_id,
prov_nm
FROM cdm_dsj.dim_merchant_basic_info_df
WHERE day_id=cast(date_add('day',-1,current_date) as varchar)) b
on a.merchant_id=b.merchant_id
GROUP BY b.prov_nm)
b
on a.prov_nm=b.prov_nm
left join
(select b.prov_nm,sum(a.equity_use_amt)/1000000.0000 day_equity_amt
from
(select merchant_id,sum(equity_use_amt) equity_use_amt
from cdm_dsj.dwd_marketing_use_di
where day_id=cast(date_add('day',-1,current_date) as varchar)
--and dept_cd='20022'
AND solution_cd in
('PS1' ,
'PS11018' ,
'PS11210' ,
'PS11666' ,
'PS11286' ,
'PS11208' ,
'PS10602' ,
'PS10404' ,
'PS10402' ,
'PS11700' ,
'PS11256' ,
'PS11024' ,
'PS10418' ,
'PS11850' ,
'PS11848')
GROUP BY merchant_id) a
LEFT JOIN
(SELECT merchant_id,
prov_nm
FROM cdm_dsj.dim_merchant_basic_info_df
WHERE day_id=cast(date_add('day',-1,current_date) as varchar)) b
on a.merchant_id=b.merchant_id
GROUP BY b.prov_nm)
c
on a.prov_nm=c.prov_nm
left join
(select prov_nm, count(distinct fee_party) yj_cnt
from
(select b.prov_nm,a.fee_party
from
(SELECT fee_party,
sum(cast(t.trade_amt AS bigint)) year_trade_amt
FROM ods_feecr_bdpms.t_fee_calculation_zengliang t
WHERE t.bdpms_etl_time >=20210101000000
AND t.incr_col='UPDATED_AT'
AND t.fee_flag = 'SUCCESS'
AND t.usable_flag = 'USABLE'
AND t.solution_no IN ( 'PS1' ,
'PS11018' ,
'PS11210' ,
'PS11666' ,
'PS11286' ,
'PS11208' ,
'PS10602' ,
'PS10404' ,
'PS10402' ,
'PS11700' ,
'PS11256' ,
'PS11024' ,
'PS10418' ,
'PS11850' ,
'PS11848' )
GROUP BY fee_party
order by year_trade_amt desc limit 10000) a
LEFT JOIN
(SELECT merchant_id,
prov_nm
FROM cdm_dsj.dim_merchant_basic_info_df
WHERE day_id=cast(date_add('day',-1,current_date) as varchar)) b
on a.fee_party=b.merchant_id
where a.fee_party not in
(SELECT fee_party
FROM ods_feecr_bdpms.t_fee_calculation_zengliang t
WHERE t.bdpms_etl_time =cast(concat(regexp_replace(cast(date_add('day',-1,CURRENT_DATE) as varchar),'-',''),'000000') as bigint)
AND t.incr_col='UPDATED_AT'
AND t.fee_flag = 'SUCCESS'
AND t.usable_flag = 'USABLE'
AND t.solution_no IN ( 'PS1' ,
'PS11018' ,
'PS11210' ,
'PS11666' ,
'PS11286' ,
'PS11208' ,
'PS10602' ,
'PS10404' ,
'PS10402' ,
'PS11700' ,
'PS11256' ,
'PS11024' ,
'PS10418' ,
'PS11850' ,
'PS11848' )
)
)
group by prov_nm
)
d
on a.prov_nm=d.prov_nm
left join
(SELECT A.prov_nm,
cast(substr(cast(date_add('day',-1,cast(concat(substr(cast(date_add('day',-1,current_date) as varchar),1,7),'-01')as date))as varchar),9,10)as bigint) before_month_days,
count(DISTINCT a.merchant_id) new30_cnt,
count(DISTINCT b.merchant_id) new30_trans_cnt,
count( case when create_tm=cast(date_add('day',-1,current_date) as varchar) then A.merchant_id end ) day_new_cnt
FROM
(SELECT prov_nm,
merchant_id,
substr(cast(create_tm as varchar),1,10) create_tm
FROM cdm_dsj.dim_merchant_basic_info_df
WHERE create_tm>=cast(date_add('day',-30,current_date) as varchar)
AND day_id=cast(date_add('day',-1,current_date) as varchar)
and stat_nm='可用'
and owned_group_cd='11006'
and merchant_id in
(select distinct merchant_id from cdm_dsj.dwd_merchant_stock_agmt_df
where day_id=cast(date_add('day',-1,current_date)as varchar)
and solution_cd in('PS1',
'PS11018',
'PS11210',
'PS11666',
'PS11286',
'PS11208',
'PS10602',
'PS10404',
'PS10402',
'PS11700',
'PS11256',
'PS11024',
'PS10418',
'PS11850',
'PS11848'
)) ) a
LEFT JOIN
(SELECT DISTINCT merchant_id
FROM dm_o2o_bdpms.pmt_merchant_tran_o2o
WHERE day_id>=cast(date_add('day',-30,CURRENT_DATE)AS varchar)) b ON a.merchant_id=b.merchant_id
GROUP BY prov_nm
)
e
on a.prov_nm=e.prov_nm
left join
(select prov_nm, sum(a.year_trade_amt)/1000000.0000 year_trade_amt,
sum(a.bm_trade_amt)/1000000.0000 bm_trade_amt,
sum(a.day_trade_amt)/1000000.0000 day_trade_amt
from
(select merchant_id,
sum(case when substr(day_id,1,4)='2021' then trans_amt end) year_trade_amt,
sum(case when substr(day_id,1,7)=substr(cast(date_add('month',-1,date_add('day',-1,current_date))as varchar),1,7) then trans_amt end) bm_trade_amt,
sum(case when day_id=cast(date_add('day',-1,current_date)as varchar) then trans_amt end) day_trade_amt
from dm_o2o_bdpms.pmt_merchant_tran_o2o
where day_id>='2021-01-01'
group by merchant_id) a
left join
(select merchant_id,prov_nm from cdm_dsj.dim_merchant_basic_info_df
where day_id=cast(date_add('day',-1,current_date)as varchar))b
on a.merchant_id=b.merchant_id
group by b.prov_nm) f
on a.prov_nm=f.prov_nm
京公网安备 11010502036488号