SQL实例

# 理财场景 ACS 和 百盈
select
    dt as "日期",
    ACS_DaE_cnt as "理财_ACS_笔数",
    round(ACSDaE_amount/1000000,2) as "理财_ACS_金额(万元)",
    concat(round(ACSDaE_zb*100,2),'%') as "理财_ACS_金额占比",
    round(((daE_b2c+daE_duiSi+daE_kuanJie+daE_daiKou+daE_b2b+daE_qiTa)-ACS_DaE_cost)/100,2) as "理财_ACS_估算节约成本(元)",
    BY_DaE_cnt as "理财_百盈_笔数",
    round(BYDaE_amount/1000000,2) as "理财_百盈_金额(万元)",
    concat(round(BYDaE_zb*100,2),'%') as "理财_百盈_金额占比",
    round(((BYdaE_b2c+BYdaE_duiSi+BYdaE_kuanJie+BYdaE_daiKou+BYdaE_b2b+BYdaE_qiTa)-BY_DaE_cost)/100,2) as "理财_百盈_估算节约成本(元)",
    concat(round(round((ACSDaE_amount+BYDaE_amount)/total_amount,4)*100,2),'%') as "理财_ACS+百盈金额占比"
from (
    select
        dt,
        total_amount,
        ACS_DaE_cnt,
        ACSDaE_amount,
        round(ACSDaE_zb,4) as ACSDaE_zb,
        BY_DaE_cnt,
        BYDaE_amount,
        round(BYDaE_zb,4) as BYDaE_zb,
        ACS_DaE_cost,
        BY_DaE_cost,
        case when b2c_amount=0 then 0 else round(ACSDaE_amount*round(b2c_amount/(total_amount-ACSDaE_amount),8)*round(b2c_cost/b2c_amount,8),0) end as daE_b2c,
        case when duiSi_amount=0 then 0 else round(ACSDaE_amount*round(duiSi_amount/(total_amount-ACSDaE_amount),8)*round(duiSi_cost/duiSi_amount,8),0) end as daE_duiSi,
        case when kuaiJie_amount=0 then 0 else round(ACSDaE_amount*round(kuaiJie_amount/(total_amount-ACSDaE_amount),8)*round(kuaiJie_cost/kuaiJie_amount,8),0) end as daE_kuanJie,
        case when daiKou_amount=0 then 0 else round(ACSDaE_amount*round(daiKou_amount/(total_amount-ACSDaE_amount),8)*round(daiKou_cost/daiKou_amount,8),0) end as daE_daiKou,
        case when b2b_amount=0 then 0 else round(ACSDaE_amount*round(b2b_amount/(total_amount-ACSDaE_amount),8)*round(b2b_cost/b2b_amount,8),0) end as daE_b2b,
        case when qiTa_amount=0 then 0 else round(ACSDaE_amount*round(qiTa_amount/(total_amount-ACSDaE_amount),8)*round(qiTa_cost/qiTa_amount,8),0) end as daE_qiTa,
        case when b2c_amount=0 then 0 else round(BYDaE_amount*round(b2c_amount/(total_amount-BYDaE_amount),8)*round(b2c_cost/b2c_amount,8),0) end as BYdaE_b2c,
        case when duiSi_amount=0 then 0 else round(BYDaE_amount*round(duiSi_amount/(total_amount-BYDaE_amount),8)*round(duiSi_cost/duiSi_amount,8),0) end as BYdaE_duiSi,
        case when kuaiJie_amount=0 then 0 else round(BYDaE_amount*round(kuaiJie_amount/(total_amount-BYDaE_amount),8)*round(kuaiJie_cost/kuaiJie_amount,8),0) end as BYdaE_kuanJie,
        case when daiKou_amount=0 then 0 else round(BYDaE_amount*round(daiKou_amount/(total_amount-BYDaE_amount),8)*round(daiKou_cost/daiKou_amount,8),0) end as BYdaE_daiKou,
        case when b2b_amount=0 then 0 else round(BYDaE_amount*round(b2b_amount/(total_amount-BYDaE_amount),8)*round(b2b_cost/b2b_amount,8),0) end as BYdaE_b2b,
        case when qiTa_amount=0 then 0 else round(BYDaE_amount*round(qiTa_amount/(total_amount-BYDaE_amount),8)*round(qiTa_cost/qiTa_amount,8),0) end as BYdaE_qiTa
    from (
        select
            dt,
            sum(total_amount) as total_amount,
            sum(case when fenlei='ACSDaE' then num else 0 end) as ACS_DaE_cnt,
            sum(case when fenlei='ACSDaE' then total_amount else 0 end) as ACSDaE_amount,
            sum(case when fenlei='ACSDaE' then total_cost else 0 end) as ACS_DaE_cost,
            sum(case when fenlei='ACSDaE' then total_amount else 0 end)/sum(total_amount) as ACSDaE_zb,
            sum(case when fenlei='BYDaE' then num else 0 end) as BY_DaE_cnt,
            sum(case when fenlei='BYDaE' then total_amount else 0 end) as BYDaE_amount,
            sum(case when fenlei='BYDaE' then total_cost else 0 end) as BY_DaE_cost,
            sum(case when fenlei='BYDaE' then total_amount else 0 end)/sum(total_amount) as BYDaE_zb,
            sum(case when fenlei='b2c' then total_amount else 0 end) as b2c_amount,
            sum(case when fenlei='b2c' then total_cost else 0 end) as b2c_cost,
            sum(case when fenlei='duiSi' then total_amount else 0 end) as duiSi_amount,
            sum(case when fenlei='duiSi' then total_cost else 0 end) as duiSi_cost,
            sum(case when fenlei='kuaiJie' then total_amount else 0 end) as kuaiJie_amount,
            sum(case when fenlei='kuaiJie' then total_cost else 0 end) as kuaiJie_cost,
            sum(case when fenlei='daiKou' then total_amount else 0 end) as daiKou_amount,
            sum(case when fenlei='daiKou' then total_cost else 0 end) as daiKou_cost,
            sum(case when fenlei='b2b' then total_amount else 0 end) as b2b_amount,
            sum(case when fenlei='b2b' then total_cost else 0 end) as b2b_cost,
            sum(case when fenlei='qiTa' then total_amount else 0 end) as qiTa_amount,
            sum(case when fenlei='qiTa' then total_cost else 0 end) as qiTa_cost
        from (
            select
                to_char(F_business_time, 'YYYY-MM-DD') as dt,
                fenlei,
                count(*) as num,
                sum(F_trans_amount) as total_amount,
                sum(F_business_cost_amount) as total_cost
            from (
                select
                    F_business_time,
                    F_recvable_bank_user_id,
                    F_trans_amount,
                    F_business_cost_amount,
                    F_channel_class,
                    case when F_channel_class=0 and F_recvable_bank_user_id=5259 then 'duiSi'
                         when F_channel_class=0 and F_recvable_bank_user_id!=5259 then 'b2c'
                         when F_channel_class=1 then 'kuaiJie'
                         when F_channel_class=2 and F_recvable_bank_user_id=5259 then 'duiSi'
                         when F_channel_class=2 and F_recvable_bank_user_id!=5259 then 'daiKou'
                         when F_channel_class=3 then 'b2b'
                         when F_recvable_bank_user_id=8999 then 'ACSDaE'
                         when F_recvable_bank_user_id=999 then 'BYDaE'
                         else 'qiTa' end as fenlei
                from (
                    select
                        F_business_time,
                        F_recvable_bank_user_id,
                        F_trans_amount,
                        F_business_cost_amount,
                        F_channel_class
                    from (
                        SELECT
                            F_business_time,
                            F_recvable_bank_user_id,
                            F_trans_amount,
                            F_business_cost_amount
                        FROM wallet.t_wallet_info
                        WHERE F_trans_seller_user_id in (
                            select a.F_sp_user_id
                            from fn.t_scenario_sp_map a
                            left join fn.t_scenarios b on a.F_sp_scenario=b.F_flag::int
                            where b.F_flag::int in (10000,10001,10002,10003,20000,20001,20002,30000,30001,70000,70001,70002,70003)
                            and f_sp_id<>'united credit'
                        )
                        and F_business_time >= '2020-08-01'
                        AND F_business_time < '2020-09-01'
                        AND F_business_type in (5,6,7)
                        AND F_trans_type NOT IN (17)
                        AND F_recvable_bank_user_id<>202
                    ) t1 left join (select min(F_channel_class) as F_channel_class,F_channel_code
                    from fn.t_reserve_bank_account_channel_details group by F_channel_code) t2
                    on t1.F_recvable_bank_user_id=t2.F_channel_code
                ) t3
            ) t4 group by fenlei,dt order by dt
        ) t5 group by dt order by dt
    ) t6 order by dt
) t7 order by dt;

# 信贷线下转账交易情况
select
    dt as "日期",
    ACS_DaE_cnt as "ACS_笔数",
    round(ACSDaE_amount/1000000,2) as "ACS_金额(万元)",
    concat(round(ACSDaE_zb*100,2),'%') as "ACS_金额占比",
    round(((daE_b2c+daE_duiSi+daE_kuanJie+daE_daiKou+daE_b2b+daE_qiTa)-ACS_DaE_cost)/100,2) as "ACS_估算节约成本(元)",
    JHXD_DaE_cnt as "建行小贷_笔数",
    round(JHXDDaE_amount/1000000,2) as "建行小贷_金额(万元)",
    concat(round(JHXDDaE_zb*100,2),'%') as "建行小贷_金额占比",
    round(((JHXDDaE_b2c+JHXDDaE_duiSi+JHXDDaE_kuanJie+JHXDDaE_daiKou+JHXDDaE_b2b+JHXDDaE_qiTa)-JHXD_DaE_cost)/100,2) as "建行小贷_估算节约成本(元)",
    concat(round(round((ACSDaE_amount+JHXDDaE_amount)/total_amount,4)*100,2),'%') as "ACS+建行小贷金额占比"
from (
    select
        dt,
        total_amount,
        ACS_DaE_cnt,
        ACSDaE_amount,
        round(ACSDaE_zb,4) as ACSDaE_zb,
        JHXD_DaE_cnt,
        JHXDDaE_amount,
        round(JHXDDaE_zb,4) as JHXDDaE_zb,
        ACS_DaE_cost,
        JHXD_DaE_cost,
        case when b2c_amount=0 then 0 else round(ACSDaE_amount*round(b2c_amount/(total_amount-ACSDaE_amount),8)*round(b2c_cost/b2c_amount,8),0) end as daE_b2c,
        case when duiSi_amount=0 then 0 else round(ACSDaE_amount*round(duiSi_amount/(total_amount-ACSDaE_amount),8)*round(duiSi_cost/duiSi_amount,8),0) end as daE_duiSi,
        case when kuaiJie_amount=0 then 0 else round(ACSDaE_amount*round(kuaiJie_amount/(total_amount-ACSDaE_amount),8)*round(kuaiJie_cost/kuaiJie_amount,8),0) end as daE_kuanJie,
        case when daiKou_amount=0 then 0 else round(ACSDaE_amount*round(daiKou_amount/(total_amount-ACSDaE_amount),8)*round(daiKou_cost/daiKou_amount,8),0) end as daE_daiKou,
        case when b2b_amount=0 then 0 else round(ACSDaE_amount*round(b2b_amount/(total_amount-ACSDaE_amount),8)*round(b2b_cost/b2b_amount,8),0) end as daE_b2b,
        case when qiTa_amount=0 then 0 else round(ACSDaE_amount*round(qiTa_amount/(total_amount-ACSDaE_amount),8)*round(qiTa_cost/qiTa_amount,8),0) end as daE_qiTa,
        case when b2c_amount=0 then 0 else round(JHXDDaE_amount*round(b2c_amount/(total_amount-JHXDDaE_amount),8)*round(b2c_cost/b2c_amount,8),0) end as JHXDDaE_b2c,
        case when duiSi_amount=0 then 0 else round(JHXDDaE_amount*round(duiSi_amount/(total_amount-JHXDDaE_amount),8)*round(duiSi_cost/duiSi_amount,8),0) end as JHXDDaE_duiSi,
        case when kuaiJie_amount=0 then 0 else round(JHXDDaE_amount*round(kuaiJie_amount/(total_amount-JHXDDaE_amount),8)*round(kuaiJie_cost/kuaiJie_amount,8),0) end as JHXDDaE_kuanJie,
        case when daiKou_amount=0 then 0 else round(JHXDDaE_amount*round(daiKou_amount/(total_amount-JHXDDaE_amount),8)*round(daiKou_cost/daiKou_amount,8),0) end as JHXDDaE_daiKou,
        case when b2b_amount=0 then 0 else round(JHXDDaE_amount*round(b2b_amount/(total_amount-JHXDDaE_amount),8)*round(b2b_cost/b2b_amount,8),0) end as JHXDDaE_b2b,
        case when qiTa_amount=0 then 0 else round(JHXDDaE_amount*round(qiTa_amount/(total_amount-JHXDDaE_amount),8)*round(qiTa_cost/qiTa_amount,8),0) end as JHXDDaE_qiTa
    from (
        select
            dt,
            sum(total_amount) as total_amount,
            sum(case when fenlei='ACSDaE' then num else 0 end) as ACS_DaE_cnt,
            sum(case when fenlei='ACSDaE' then total_amount else 0 end) as ACSDaE_amount,
            sum(case when fenlei='ACSDaE' then total_cost else 0 end) as ACS_DaE_cost,
            sum(case when fenlei='ACSDaE' then total_amount else 0 end)/sum(total_amount) as ACSDaE_zb,
            sum(case when fenlei='JHXDDaE' then num else 0 end) as JHXD_DaE_cnt,
            sum(case when fenlei='JHXDDaE' then total_amount else 0 end) as JHXDDaE_amount,
            sum(case when fenlei='JHXDDaE' then total_cost else 0 end) as JHXD_DaE_cost,
            sum(case when fenlei='JHXDDaE' then total_amount else 0 end)/sum(total_amount) as JHXDDaE_zb,
            sum(case when fenlei='b2c' then total_amount else 0 end) as b2c_amount,
            sum(case when fenlei='b2c' then total_cost else 0 end) as b2c_cost,
            sum(case when fenlei='duiSi' then total_amount else 0 end) as duiSi_amount,
            sum(case when fenlei='duiSi' then total_cost else 0 end) as duiSi_cost,
            sum(case when fenlei='kuaiJie' then total_amount else 0 end) as kuaiJie_amount,
            sum(case when fenlei='kuaiJie' then total_cost else 0 end) as kuaiJie_cost,
            sum(case when fenlei='daiKou' then total_amount else 0 end) as daiKou_amount,
            sum(case when fenlei='daiKou' then total_cost else 0 end) as daiKou_cost,
            sum(case when fenlei='b2b' then total_amount else 0 end) as b2b_amount,
            sum(case when fenlei='b2b' then total_cost else 0 end) as b2b_cost,
            sum(case when fenlei='qiTa' then total_amount else 0 end) as qiTa_amount,
            sum(case when fenlei='qiTa' then total_cost else 0 end) as qiTa_cost
        from (
            select
                to_char(F_business_time, 'YYYY-MM-DD') as dt,
                fenlei,
                count(*) as num,
                sum(F_trans_amount) as total_amount,
                sum(F_business_cost_amount) as total_cost
            from (
                select
                    F_business_time,
                    F_recvable_bank_user_id,
                    F_trans_amount,
                    F_business_cost_amount,
                    F_channel_class,
                    case when F_channel_class=0 and F_recvable_bank_user_id=5259 then 'duiSi'
                         when F_channel_class=0 and F_recvable_bank_user_id!=5259 then 'b2c'
                         when F_channel_class=1 then 'kuaiJie'
                         when F_channel_class=2 and F_recvable_bank_user_id=5259 then 'duiSi'
                         when F_channel_class=2 and F_recvable_bank_user_id!=5259 then 'daiKou'
                         when F_channel_class=3 then 'b2b'
                         when F_recvable_bank_user_id=8999 then 'ACSDaE'
                         when F_recvable_bank_user_id=8998 then 'JHXDDaE'
                         else 'qiTa' end as fenlei
                from (
                    select
                        F_business_time,
                        F_recvable_bank_user_id,
                        F_trans_amount,
                        F_business_cost_amount,
                        F_channel_class
                    from (
                        SELECT
                            F_business_time,
                            F_recvable_bank_user_id,
                            F_trans_amount,
                            F_business_cost_amount
                        FROM wallet.t_wallet_info
                        WHERE F_trans_seller_user_id in (
                            select a.F_sp_user_id
                            from fn.t_scenario_sp_map a
                            left join fn.t_scenarios b on a.F_sp_scenario=b.F_flag::int
                            where b.F_flag::int in (40000,40001,40002)
                            and f_sp_id<>'united credit'
                        )
                        and F_business_time >= '@{date('yyyy-MM-01','-1','days')}@'
                        AND F_business_time < '@{date('yyyy-MM-dd','+0','days')}@'
                        AND F_business_type in (5,6,7)
                        AND F_trans_type NOT IN (17)
                        AND F_recvable_bank_user_id<>202
                    ) t1 left join (select min(F_channel_class) as F_channel_class,F_channel_code
                    from fn.t_reserve_bank_account_channel_details group by F_channel_code) t2
                    on t1.F_recvable_bank_user_id=t2.F_channel_code
                ) t3
            ) t4 group by fenlei,dt order by dt
        ) t5 group by dt order by dt
    ) t6 order by dt
) t7 order by dt;