image.png
攬收表c --運(yùn)單號(hào)唯一全陨，1個(gè)客戶可以有多個(gè)快遞
表結(jié)構(gòu)：
表.png
思路：統(tǒng)計(jì)某段時(shí)間客戶單量的分布情況，先根據(jù)客戶id分組統(tǒng)計(jì)每個(gè)客戶的單量揣钦。
結(jié)果存儲(chǔ)在臨時(shí)表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;
知道了每個(gè)客戶的單量嫂伞，根據(jù)單量分組規(guī)則钦睡，通過case when在統(tǒng)計(jì)出來的客戶單量結(jié)果里加一個(gè)字段標(biāo)記某行屬于哪個(gè)組,再根據(jù)標(biāo)記字段分組統(tǒng)計(jì)
select
level as '單量',
count(cus_id) as '客戶數(shù)'
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;
此時(shí)，統(tǒng)計(jì)出每個(gè)組的客戶數(shù)量膀估，分組字段為字符串類型幔亥，出來結(jié)果的排序可能不同。
分組字段用數(shù)字類型代替字符串類型察纯，如下：
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 '客戶數(shù)'
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;
