case when 简单应用

SQL数据分段统计实战

最新推荐文章于 2024-07-08 11:25:23 发布

原创最新推荐文章于 2024-07-08 11:25:23 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

PostgreSQL 专栏收录该内容

54 篇文章

订阅专栏

本文详细介绍了一种使用SQL进行复杂数据分段统计的方法，包括条件筛选、分组聚合及特定用户行为分析。通过实际案例，展示了如何针对不同交易金额区间进行交易次数与金额的统计，并特别关注了特定用户ID的交易情况。

select
    qujian,
    count(*) as total_cnt,
    sum(F_trans_amount) as total_amount,
    count(case when F_recvable_bank_user_id=8998 then F_recvable_bank_user_id end) as jhxd_cnt,
    sum(case when F_recvable_bank_user_id=8998 then F_trans_amount end) as jhxd_amount
from
(
    select
        F_recvable_bank_user_id,
        F_trans_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 < 200000 then '2区间 1000-2000'
        when F_trans_amount >= 200000 and F_trans_amount < 300000 then '3区间 2000-3000'
        when F_trans_amount >= 300000 and F_trans_amount < 400000 then '4区间 3000-4000'
        when F_trans_amount >= 400000 and F_trans_amount < 500000 then '5区间 4000-5000'
        when F_trans_amount >= 500000 and F_trans_amount < 1000000 then '6区间 5000-10000'
        when F_trans_amount >= 1000000 and F_trans_amount < 1500000 then '7区间 1w-1.5w'
        when F_trans_amount >= 1500000 then '8区间 1.5w+'
        end as qujian
    from wallet.t_wallet_info
    where F_trans_type <> 17
    AND F_recvable_bank_user_id != 202
    and F_trans_seller_user_id
    in (select f_sp_user_id from fn.t_scenario_sp_map where F_sp_scenario in (40000,40001,40002))
    and F_business_time >= '2020-04-01 00:00:00'
    AND F_business_time <= '2020-04-30 23:59:59'
    AND F_business_type in (5,6,7)
) t1
group by qujian
order by qujian;