理财线下转账交易SQL加合计行

# 理财场景 ACS 和 百盈 union all 加合计行 解决排序问题
select
    *
from (
    (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-09-01'
                            AND F_business_time < '2020-09-21'
                            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)
    union all
    (select
        '合计' 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
            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
                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
                    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-09-01'
                            AND F_business_time < '2020-09-21'
                            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
            ) t5
        ) t6
    ) t7)
) as t8 order by "日期";