SQL如何分析一家店铺？（分组求和、if）

【面试题】

某店铺的商品信息表中记录了有哪些商品

订单明细表中记录了商品销售的流水；"订单明细表"中的'商品ID' 与"商品信息表"中的'商品ID'一一对应。

问题：计算商品A，商品B，在2019年的总销售额和每月销售额

【解题思路】

我们来分析一下问题，销售额 = 商品单价 * 数量。

其中，商品单价在商品信息表中，商品数量在订单明细表中。这涉及到两个表，要想到《猴子从零学会SQL》里讲过的要用到多表联结。

用多维度拆解分析方法来拆解下问题，销售额需要满足：是商品A和商品B的销售，销售时间是19年，订单状态为已支付。

要分别计算商品A和商品B的销售，需要使用分组统计。因此这个问题可以分成3步，下面来详细说明下每个步骤。

1.多表联结

要用哪种联结呢？

因为要保留订单明细表中所有的数据，因此我们选择以订单明细表为左表，进行左联结。联结键为商品ID。

select *
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID;

2.条件筛选

问题中的限定条件有2个：

1）商品名称为商品A和商品B

2）订单状态为支付

订单明细表中都是2019年数据，所以不需再额外限定。

select *
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 in ('商品A','商品B');

3.分组汇总

1）如何分组

分组要使用到group by子句。本题按照商品名称分组，也就是group by 商品名称。

select *
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 in ('商品A','商品A')
group by 商品名称;

2）计算19年总销售和19年各月总销售

计算19年总销售。单个订单的销售额 = 订单数 * 单价，使用sum(订单数*单价)即可得到19年总销售额。

select 商品名称,
sum(订单数*单价) as YTD
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 in ('商品A','商品A')
group by 商品名称;

 再来计算19年各月总销售。我们先以19年1月销售为例。

select 商品名称,
sum(订单数*单价) as YTD,
sum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月"
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 in ('商品A','商品A')
group by 商品名称;

我们来理解一下上面的sum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月" 这段语句。

筛选结果表t2包含19年所有数据，如果直接求和显然不符题意。要计算1月份的销售额，要在聚合前加上if条件判断函数。

if函数有3个参数,第1个参数进行条件判断，若条件判断结果为真，则返回结果expr1，否则返回结果expr2。

我们使用if函数对支付时间进行判断，如果支付时间为2019-01，那么等于销量*单价，否则为0。 

此时我们发现，21年1月以外的销售值，都变成了0。再对销售求和即可得到19年1月的销售额，即sum(if(substr(支付时间,1,7)= '2021-01',销量*单价,0)) as "1月"。

用同样的方法可以计算出2~12月的销售额。

本题最终的sql如下：

select 商品名称,
sum(订单数*单价) as YTD
sum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月",
sum(if(substr(支付时间,1,7)= '2019-02',销量*单价,0)) as "2月",
sum(if(substr(支付时间,1,7)= '2019-03',销量*单价,0)) as "3月",
sum(if(substr(支付时间,1,7)= '2019-04',销量*单价,0)) as "4月",
sum(if(substr(支付时间,1,7)= '2019-05',销量*单价,0)) as "5月",
sum(if(substr(支付时间,1,7)= '2019-06',销量*单价,0)) as "6月",
sum(if(substr(支付时间,1,7)= '2019-07',销量*单价,0)) as "7月",
sum(if(substr(支付时间,1,7)= '2019-08',销量*单价,0)) as "8月",
sum(if(substr(支付时间,1,7)= '2019-09',销量*单价,0)) as "9月",
sum(if(substr(支付时间,1,7)= '2019-10',销量*单价,0)) as "10月",
sum(if(substr(支付时间,1,7)= '2019-11',销量*单价,0)) as "11月",
sum(if(substr(支付时间,1,7)= '2019-12',销量*单价,0)) as "12月"
from 订单明细表 as a1
left join 商品信息表 as a2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 in ('商品A','商品A')
group by 商品名称;

【本题考点】

1.遇到问题要学会要用多维度拆解分析方法来拆解

2.涉及到多张表要想到使用多表联结。

3.考察分组求和知识点。

4.考察了if函数的灵活使用。

【举一反三】

计算商品A在2019年的总销售额和各地区销售额。

参考答案：

select 商品名称,
sum(订单数*单价) as "总销售",
sum(if(区域 = '华南',销量*单价,0)) as "华南",
sum(if(区域 = '华东',销量*单价,0)) as "华东",
from 订单明细表asa1
left  join 商品信息表 asa2
on a1.商品ID =  a2.商品ID
where 订单状态 = '支付'
and 商品名称 = '商品A'
group by 商品名称;