SQL快出来，收快递啦(分区，case when 连表)

SQL快出来，收快递啦(分区，case when 连表)

【面试题】

有一张“快递揽收表”，包含3列：运单号、客户id、创建日期。

问题：查询运单创建日期在0501-0531期间不同单量区间的客户分布。最终得出的数据如下：

【解题步骤】

1. 汇总分析

计算0501-0531期间每个客户的单量，涉及到“每个”，要想到​​《猴子 从零学会SQL》​​​里讲过的用​​分组汇总​​来解决这类问题。按“客户id”分组，对“运单号”统计数目得到单量（count），注意要用distinct对运单号去重

select 客户id, count(distinct 运单号) as 单量from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’group by 客户id;

2. 子查询

在上一步的基础上，还无法得到要求的“最终结果”。因为，“最终结果”里面获取的是不同单量区间的客户数量分布。

所以，还要在上一步的基础上​​（作为子查询）​​获取不同客户所处的单量区间。不同区间这种问题怎么办呢？

要想到​​《猴子 从零学会SQL》​​​里讲过的多条件判断，也就是用到​​case语句​​。

select 客户id,单量, (case when 单量 <= 5 then ‘0-5’ when 单量 >= 6 and 单量 <= 10 then ‘6-10’ when 单量 >= 11 and 单量 <= 20 then ‘11-20’ else ‘20以上’ end) as 单量区间from(select 客户id, count(distinct 运单号) as 单量from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’group by 客户id) as t1;

但是和最终结果对比发现，还需要得到客户数，怎么办？

在上一步查询结果的基础上（作为子查询），再使用一次汇总分析，按“单量区间”分组（group by），对客户id汇总得到客户数（count）。

select 单量区间 as 单量, count(distinct 客户id) as 客户数from (select 客户id,单量,case when 单量 <= 5 then ‘0-5’ when 单量 >= 6 and 单量 <= 10 then ‘6-10’when 单量 >= 11 and 单量 <= 20 then ‘11-20’else ‘20以上’ end as 单量区间from(select 客户id,count(distinct 运单号) as 单量from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’group by 客户id) as t1) as t2group by 单量区间;

【本题考点】

1.当遇到“每个”这类问题，要想到用​​分组汇总或者窗口函数 ​​

2.考查对​​子查询​​的灵活使用，嵌套了两次子查询，也就是把上一步查询结果作为子查询

3.考查对常见函数的了解：count()、group by()、distinct、case when，以及它们的组合使用

4.当遇到​​“区间”问题​​的时候，要想到用多条件判断（case语句）解决

5.考察遇到业务问题，如何用​​逻辑树分析方法​​把复杂问题变成矿业解决的子问题

【举一反三】

查询运单创建日期在0501-0531期间不同单量区间的客户占比。

与原答案的区别是：最终查询的结果是客户占比。

所以，需要查询客户总数；然后使用多表查询来计算客户占比。

select count(distinct 客户id) as 客户总数from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’;

将原答案与“客户总数”联结，同时使用concat()函数将小数转化成百分数。

select a.单量, concat(round(a.客户数/b.客户总数,4)*100,’%’) as 客户占比from (select 单量区间 as 单量,count(distinct 客户id) as 客户数from (select 客户id,单量,case when 单量 <= 5 then ‘0-5’ when 单量 >= 6 and 单量 <= 10 then ‘6-10’when 单量 >= 11 and 单量 <= 20 then ‘11-20’else ‘20以上’ end as 单量区间from(select 客户id,count(distinct 运单号) as 单量from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’group by 客户id) as t1) as t2group by 单量区间) as aleft join (select count(distinct 客户id) as 客户总数from 快递揽收表where 创建日期 >= ‘2020-05-01’ and 创建日期 <= ‘2020-05-31’) as b on 1 = 1;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。