内部场景成本率监控报表

select
    t3.scenario_type as "场景名称",
    t3.amount as "交易金额",
    t3.cnt as "订单量",
    t3.kdj as "客单价",
    concat(round(t3.cost_r*100,4),'%') as "成本率",
    concat(round(t4.cost_r*100,4),'%') as "D-2成本率",
    concat(round((t3.cost_r-t4.cost_r)*100,4),'%') as "D-2增长情况",
    concat(round(t5.cost_r*100,4),'%') as "D-7成本率",
    concat(round((t3.cost_r-t5.cost_r)*100,4),'%') as "D-7增长情况",
    concat(round(t6.cost_r*100,4),'%') as "上月同比成本率",
    concat(round((t3.cost_r-t6.cost_r)*100,4),'%') as "上月同比成本率增长情况"
from (
    select
        scenario_type,
        dt,
        sum(trans_amount) as amount,
        sum(trans_count) as cnt,
        sum(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from (
        SELECT
            scenario_type,
            substr(CAST(business_date as VARCHAR),1,10) as dt,
            SUM(trans_count) as trans_count,
            SUM(trans_amount) as trans_amount,
            SUM(cost_amount) as cost_amount
        FROM (
            select
                case when first_scenario='理财' and second_scenario<>'个人理财' and second_scenario<>'基金' then '理财'
                    when first_scenario='信贷' then '信贷'
                    when first_scenario='理财' and second_scenario='个人理财' then '个人理财'
                    when first_scenario='理财' and second_scenario='基金' then '基金'
                end as scenario_type,
                business_date,
                trans_count,
                trans_amount,
                cost_amount
            from wallet.ads_business_info
            WHERE business_date='@{date('yyyy-MM-dd','-1','days')}@'
            and rp_type='收款'
            and (first_scenario in ('信贷','理财') or second_scenario in ('个人理财','基金'))
        ) t1 group by scenario_type,business_date
        ORDER BY scenario_type,business_date
    ) t2 group by scenario_type,dt
    union all
    select
        '理财',
        substr(CAST(business_date as VARCHAR),1,10) as dt,
        SUM(trans_amount) as amount,
        SUM(trans_count) as cnt,
        SUM(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from wallet.ads_business_info
    WHERE business_date='@{date('yyyy-MM-dd','-1','days')}@'
    and rp_type='收款'
    and first_scenario='理财'
    group by dt
) t3 left join (
    select
        scenario_type,
        dt,
        sum(trans_amount) as amount,
        sum(trans_count) as cnt,
        sum(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from (
        SELECT
            scenario_type,
            substr(CAST(business_date as VARCHAR),1,10) as dt,
            SUM(trans_count) as trans_count,
            SUM(trans_amount) as trans_amount,
            SUM(cost_amount) as cost_amount
        FROM (
            select
                case when first_scenario='理财' and second_scenario<>'个人理财' and second_scenario<>'基金' then '理财'
                    when first_scenario='信贷' then '信贷'
                    when first_scenario='理财' and second_scenario='个人理财' then '个人理财'
                    when first_scenario='理财' and second_scenario='基金' then '基金'
                end as scenario_type,
                business_date,
                trans_count,
                trans_amount,
                cost_amount
            from wallet.ads_business_info
            WHERE business_date='@{date('yyyy-MM-dd','-2','days')}@'
            and rp_type='收款'
            and (first_scenario in ('信贷','理财') or second_scenario in ('个人理财','基金'))
        ) t1 group by scenario_type,business_date
        ORDER BY scenario_type,business_date
    ) t2 group by scenario_type,dt
    union all
    select
        '理财',
        substr(CAST(business_date as VARCHAR),1,10) as dt,
        SUM(trans_amount) as amount,
        SUM(trans_count) as cnt,
        SUM(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from wallet.ads_business_info
    WHERE business_date='@{date('yyyy-MM-dd','-2','days')}@'
    and rp_type='收款'
    and first_scenario='理财'
    group by dt
) t4 on t3.scenario_type=t4.scenario_type
left join (
    select
        scenario_type,
        dt,
        sum(trans_amount) as amount,
        sum(trans_count) as cnt,
        sum(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from (
        SELECT
            scenario_type,
            substr(CAST(business_date as VARCHAR),1,10) as dt,
            SUM(trans_count) as trans_count,
            SUM(trans_amount) as trans_amount,
            SUM(cost_amount) as cost_amount
        FROM (
            select
                case when first_scenario='理财' and second_scenario<>'个人理财' and second_scenario<>'基金' then '理财'
                    when first_scenario='信贷' then '信贷'
                    when first_scenario='理财' and second_scenario='个人理财' then '个人理财'
                    when first_scenario='理财' and second_scenario='基金' then '基金'
                end as scenario_type,
                business_date,
                trans_count,
                trans_amount,
                cost_amount
            from wallet.ads_business_info
            WHERE business_date='@{date('yyyy-MM-dd','-7','days')}@'
            and rp_type='收款'
            and (first_scenario in ('信贷','理财') or second_scenario in ('个人理财','基金'))
        ) t1 group by scenario_type,business_date
        ORDER BY scenario_type,business_date
    ) t2 group by scenario_type,dt
    union all
    select
        '理财',
        substr(CAST(business_date as VARCHAR),1,10) as dt,
        SUM(trans_amount) as amount,
        SUM(trans_count) as cnt,
        SUM(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from wallet.ads_business_info
    WHERE business_date='@{date('yyyy-MM-dd','-7','days')}@'
    and rp_type='收款'
    and first_scenario='理财'
    group by dt
) t5 on t3.scenario_type=t5.scenario_type
left join (
    select
        scenario_type,
        dt,
        sum(trans_amount) as amount,
        sum(trans_count) as cnt,
        sum(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from (
        SELECT
            scenario_type,
            substr(CAST(business_date as VARCHAR),1,10) as dt,
            SUM(trans_count) as trans_count,
            SUM(trans_amount) as trans_amount,
            SUM(cost_amount) as cost_amount
        FROM (
            select
                case when first_scenario='理财' and second_scenario<>'个人理财' and second_scenario<>'基金' then '理财'
                    when first_scenario='信贷' then '信贷'
                    when first_scenario='理财' and second_scenario='个人理财' then '个人理财'
                    when first_scenario='理财' and second_scenario='基金' then '基金'
                end as scenario_type,
                business_date,
                trans_count,
                trans_amount,
                cost_amount
            from wallet.ads_business_info
            WHERE business_date=to_char(date_trunc('day', date '@{date('yyyy-MM-dd','-1','days')}@') + interval '-1 month','yyyy-MM-DD')::date
            and rp_type='收款'
            and (first_scenario in ('信贷','理财') or second_scenario in ('个人理财','基金'))
        ) t1 group by scenario_type,business_date
        ORDER BY scenario_type,business_date
    ) t2 group by scenario_type,dt
    union all
    select
        '理财',
        substr(CAST(business_date as VARCHAR),1,10) as dt,
        SUM(trans_amount) as amount,
        SUM(trans_count) as cnt,
        SUM(cost_amount) as cost,
        round(sum(trans_amount)/sum(trans_count),0) as kdj,
        round(sum(cost_amount)/sum(trans_amount),6) as cost_r
    from wallet.ads_business_info
    WHERE business_date=to_char(date_trunc('day', date '@{date('yyyy-MM-dd','-1','days')}@') + interval '-1 month','yyyy-MM-DD')::date
    and rp_type='收款'
    and first_scenario='理财'
    group by dt
) t6 on t3.scenario_type=t6.scenario_type;