SQL查询--揽收表统计客户快递单量分布情况

image.png

揽收表c --运单号唯一，1个客户可以有多个快递
表结构：

表.png

思路：统计某段时间客户单量的分布情况，先根据客户id分组统计每个客户的单量。
结果存储在临时表cc中

select cus_id,count(cus_id) as num
from c
where create_date >= '2020-05-01' and create_date <= '2020-05-31'
group by cus_id;

知道了每个客户的单量，根据单量分组规则，通过case when在统计出来的客户单量结果里加一个字段标记某行属于哪个组,再根据标记字段分组统计

select 
    level as '单量',
    count(cus_id) as '客户数'
from
    (select 
        cus_id,
        num,
        case 
              when num<=5 then '0-5'
              when num<=10 then '6-10'
              when num<=20 then '11-20'
              else '20以上'
        end as level
    from cc) a
group by level;

此时，统计出每个组的客户数量，分组字段为字符串类型，出来结果的排序可能不同。
分组字段用数字类型代替字符串类型，如下：

select 
    case level
          when 1 then '0-5'
          when 2 then '6-10'
          when 3 then '11-20'
          else '20以上'
    end as '单量',
    count(cus_id) as '客户数'
from
    (select 
        cus_id,
        num,
        case 
              when num<=5 then 1
              when num<=10 then 2
              when num<=20 then 3
              else 4
        end as level
    from cc) a
group by level
order by level;