招行理财金额频次分段SQL记录

# 招行理财、金额分段、频次分段
select
    amount_num as "金额分段",
    cishu as "频次分段",
    sum(total_amount) as "总交易额",
    sum(total_chengben) as "总成本",
    sum(meiren_bishu) as "总笔数",
    count(F_trans_buyer_user_id) as "总人数"
from
(
    select
        amount_num,
        F_trans_buyer_user_id,
        count(F_trans_buyer_user_id) as meiren_bishu,
        sum(F_trans_amount) as total_amount,
        sum(F_business_cost_amount) as total_chengben,
        case when count(F_trans_buyer_user_id) = 1 then '1次'
             when count(F_trans_buyer_user_id) = 2 then '2次'
             when count(F_trans_buyer_user_id) = 3 then '3次'
             when count(F_trans_buyer_user_id) = 4 then '4次'
             when count(F_trans_buyer_user_id) = 5 then '5次'
             when count(F_trans_buyer_user_id) > 5 then '5次+'
        end as cishu
    from
    (
        select
            F_trans_buyer_user_id,
            F_trans_amount,
            F_business_cost_amount,
            case when F_trans_amount >= 0 and F_trans_amount <= 100000 then '1区间 0~1000'
                 when F_trans_amount > 100000 and F_trans_amount <= 500000 then '2区间 1001~5000'
                 when F_trans_amount > 500000 and F_trans_amount <= 1000000 then '3区间 5001～10000'
                 when F_trans_amount > 1000000 and F_trans_amount <= 3000000 then '4区间 10001~30000'
                 when F_trans_amount > 3000000 and F_trans_amount <= 5000000 then '5区间 3w~5w'
                 when F_trans_amount > 5000000 and F_trans_amount <= 10000000 then '6区间 5w~10w'
                 when F_trans_amount > 10000000 and F_trans_amount <= 50000000 then '7区间 10w~50w'
                 when F_trans_amount > 50000000 and F_trans_amount <= 100000000 then '8区间 50w~100w'
                 when F_trans_amount > 100000000 then '9区间 100w+'
             end as amount_num
        from t_wallet_info
        where F_recvable_recv_bank_id
        in (招行前端编码)
        and F_trans_seller_user_id
        in (理财场景商户UID)
        and F_business_time >= '2019-01-01 00:00:00'
        AND F_business_time <= '2019-07-30 23:59:59'
        AND F_business_type in (5,7,8)
    ) a
    group by amount_num,F_trans_buyer_user_id
) b
group by amount_num,cishu
order by amount_num,cishu;