题解 | #快递量区间分布#

此题，考点就是聚合函数+判断函数，之前2道题都是直接出了要判断的列，这题是要由你自己求出来.
1.先求出，每位用户的单量 2.在求出每个用户的单量属于哪个范围的 3.在求哪个范围内的用户个数

方法一:虚拟表+聚合函数+判断求出(if)
with tiaojian as (
select 
客户ID,
count(distinct 运单号) as 订单数
from 快递揽收表
where 
date_format(创建日期,"%Y%m")=202005
group by 客户ID
)


select 
t.单量,
count(客户ID) as 客户数
from(
select 
客户ID,
if(订单数 between 0 and 5 ,"0-5",if(订单数 between 6 and 10,"6-10",if(订单数 between 11 and 20,"11-20","20以上"))) as 单量
from tiaojian
) as t
group by t.单量
order by t.单量


方法二:虚拟表+聚合函数+判断求出(case when then end)
with tiaojian as (
select 
客户ID,
count(distinct 运单号) as 订单数
from 快递揽收表
where 
date_format(创建日期,"%Y%m")=202005
group by 客户ID
)


select
t.单量,
count( 客户ID) as "客户数"
from(
select 
客户ID,
case when 订单数 between 0 and 5 then "0-5"
     when 订单数 between 6 and 10 then "6-10"
     when 订单数 between 11 and 20 then "11-20"
     when 订单数>20 then "20以上" end 单量
from tiaojian
) as t 
group by t.单量
order by t.单量