电商面试题：如何分析复杂业务？

 

【题目】

某公司在不同的电商平台上都有店铺，下面是该公司的销售数据表(每个表有一行示例数据)。

现在需要解决的业务问题是：

1.对于指定品类号范围（品类号列表：12，33，45，99，1001），查询2019年每个电商平台上每个品牌号对应每个品类号的累计销售额，输出格式如下

2.查询2019年有5个以上(含5个)不同 品类号 的 单月单平台销售额大于等于10000的品牌列表，及对应的不同品类号 数量，输出格式如下

3.查询2019年只在电商平台1上有销售额的品牌中（即排除电商平台为2时销售额累计大于0的品牌），电商平台1的累计销售额最大的Top30个品牌及对应的销售额，输出格式如下

4.查询2019年在两个电商平台中分别同时都能进入销售额Top 50的品牌及对应的全电商平台累计销售额，输出格式如下

​

【解题步骤】

问题1：对于指定品类号范围（品类号列表：12，33，45，99，1001），查询2019年每个电商平台上每个品牌号对应每个品类号的累计销售额，输出格式如下

使用逻辑树分析方法，将复杂问题拆解为简单问题：

（1）查询结果：品牌名，品类名，电商平台、销售额

（2）筛选条件：品类号列表（12，33，45，99，1001），2019年

（3）每个电商平台上每个品牌号对应每个品类号的累计销售额

1.查询结果：品牌名，品类名，电商平台、销售额

这些列分别来自3个不同的表，需要多表联结

select*from品牌表asainnerjoin月销售统计表ascona.品牌号 = c.品牌号innerjoin品类表asbonb.品类号 = c.品类号;

把这个查询结果当做临时表

2.筛选条件：品类号列表（12，33，45，99，1001），2019年

使用where子句筛选出符合条件的数据

select*from临时表where品类号in(12，33，45，99，1001)andyear(月份) =2019;

查询结果

3.每个电商平台上每个品牌号对应每个品类号的累计销售额

看到"每个"，要想到《猴子 从零学会sql》里见过的这类问题要用分组汇总。

selectsum(销售额)as销售额from临时表where品类号in(12，33，45，99，1001)andyear(月份)=2019groupby电商平台,品牌号,品类号;

查询结果

把临时表的sql套入上面就得到最终的sql：

selecta.品牌号,品牌名,b.品类号,品类名,电商平台,sum(销售额)as销售额from品牌表asainnerjoin月销售统计表ascona.品牌号 = c.品牌号innerjoin品类表asbonb.品类号 = c.品类号where品类号in(12，33，45，99，1001)andyear(月份)=2019groupbya.品牌号,品牌名,b.品类号,品类名,电商平台;

查询结果

问题2:查询2019年有5个以上(含5个)不同品类号的单月单平台销售额大于等于10000的品牌列表，及对应的不同品类号数量，输出格式如下

使用逻辑树分析方法将复杂问题变简单：

（1）查询结果：品牌号、品牌名、品类数量

（2）筛选条件：2019年

（3）不同品类号的单月单平台的销售额

（4）5个以上(含5个)不同品类号

（5）销售额大于等于10000

1.查询结果：品牌号、品牌名、品类数量

查询结果涉及到2张表需要用到多表查询，用哪种联结呢？

因为题目要求查询所有符合条件的品牌号，而品牌表包含了所有品牌号，所有需要以"品牌表"进行左联结，保留左边表(品牌表)里的全部数据。

select*from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号;

查询结果

把上面查询结果当做临时表。

2.筛选条件：2019年

select*from临时表whereyear(月份) =2019;

查询结果

3.不同品类号的单月单平台的销售额

也就是每个品类号每个月每个平台，涉及到“每个”需要用到分组汇总。

selectsum(销售额)from临时表whereyear(月份) =2019groupby品类号,月份,电商平台;

查询结果

4.5个以上(含5个)不同品类号

需要用count，distinct筛选出符合条件的数据。

selectsum(销售额)as销售额from临时表whereyear(月份) =2019groupby品类号,月份,电商平台havingcount(distinct品类号) >=5;

查询结果

5.销售额大于等于10000

selectsum(销售额)as销售额from临时表whereyear(月份) =2019groupby品类号,月份,电商平台havingcount(distinct品类号) >=5and销售额>=10000;

查询结果

输出格式如下：

把临时表的sql套入上面就得到最终的sql：

selecta.品牌号,品牌名,count(distinct品类号)as品类号数量from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号whereyear(月份) =2019groupbya.品牌号,品牌名,月份,电商平台havingcount(distinct品类号) >=5and销售额>=10000;

查询结果

问题3:查询2019年只在电商平台1上有销售额的品牌中（即排除电商平台为2时销售额累计大于0的品牌），电商平台1的累计销售额最大的Top30个品牌及对应的销售额，输出格式如下

使用逻辑树分析方法，将复杂问题拆解为简单问题：

（1）查询结果：品牌号，品牌名，平台1总销售额

（2）筛选条件：2019年

（3）只在电商平台1上有销售额的品牌(即平台2的累计销售额为0)

（4）电商平台1的累计销售额最大的Top30个品牌及对应的销售额

1.查询结果：品牌号，品牌名，平台1总销售额

输出的表中有品牌号，品牌名和销售额，所有需要用到联结，联结品牌表和月销售统计表，联结方式和问题2相同

select*from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号;

把查询结果当做临时表。

2.筛选条件：2019年

select*from临时表whereyear(月份) =2019;

查询结果

3.只在电商平台1上有销售额的品牌(即平台2的累计销售额为0)

需要用group by对品牌号，电商平台分组，用having筛选出平台为2且销售额为0的品牌号作为子查询

selecta.品牌号from临时表whereyear(月份) =2019groupbya.品牌号,电商平台having电商平台 =2andsum(销售额) =0;

查询结果

4.电商平台1的累计销售额最大的Top30个品牌及对应的销售额

观察输出格式要求。

可以看出需要要用group by对品牌号，品牌名，电商平台分组，

用having和 in 筛选出平台为1且在平台2销售额为0的Top30个品牌号，品牌名及对应的销售额。

并用order by对销售额排序,并用limit取前30项

selecta.品牌号,sum(销售额)as'平台1总销售额'from临时表whereyear(月份) =2019groupbya.品牌号,电商平台having电商平台 =1anda.品牌号in(子查询)orderbysum(销售额)desclimit30;

查询结果

观察输出格式要求：

最终答案：

selecta.品牌号,品牌名,sum(销售额)as'平台1总销售额'from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号whereyear(月份) =2019groupbya.品牌号,品牌名,电商平台having电商平台 =1anda.品牌号in(selecta.品牌号from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号whereyear(月份) =2019groupbya.品牌号,电商平台having电商平台 =2andsum(销售额) =0)orderbysum(销售额)desclimit30;

 查询结果

问题4:查询2019年在两个电商平台中分别同时都能进入销售额Top 50的品牌及对应的全电商平台累计销售额，输出格式如下

使用逻辑树分析方法，将复杂问题拆解为简单问题：

（1）查询结果：品牌号，品牌名，全平台累计销售额

（2）筛选条件：2019年

（3）在两个电商平台中分别同时都能进入销售额Top 50的品牌

（4）品牌对应的全电商平台累计销售额

1.查询结果：品牌号，品牌名，全平台累计销售额

输出的表中有品牌号，品牌名和销售额，所有需要用到联结，联结品牌表和月销售统计表，联结方式和问题2，问题3相同

select*from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号;

查询结果

2.筛选条件：2019年

where子句限制条件

select*from品牌表asaleftjoin月销售统计表ascona.品牌号 = c.品牌号whereyear(月份) =2019;

查询结果

3.在两个电商平台中分别同时都能进入销售额Top 50的品牌

1）因为月销售统计表中同品牌号同平台包含了不同的品类号

所有应该用group by 按品牌号和电商平台分组，计算每个品牌在每个电商平台的累计销售额

select品牌号,电商平台,sum(销售额)as总销售额from月销售统计表whereyear(月份) =2019groupby品牌号,电商平台;

查询结果

将上述结果作为临时表，表名为：销售统计表

2）分别同时都能进入销售额Top 50的品牌：

这是《猴子 从零学过sql》里讲过的top N问题。

先从简单的入手：查询每个平台销售额最高的两个品牌（即对平台进行分组，对销售额进行排序），套用之前讲过的top N问题模板。

#TOP N问题SQL模板select*from(select*,row_number()over(partitionby要分组的列名orderby要排序的列名desc)asrankingfrom表名)asawhereranking <= N;

对应这个案例就是：

select*from(select*,row_number()over(partitionby电商平台orderby总销售额desc)asrankingfrom销售统计表)asawhereranking <=2;

那么在两个平台内都进入TOP2的品牌号呢？

从上图可以看出，很显然是品牌号是158。因此我们需要对品牌号分组，用count对品牌号进行计数，大于或等于2则该品牌同时在两个平台均进入TOP2

select品牌号,sum(总销售额)as平台总销售额from(select*,row_number()over(partitionby电商平台orderby总销售额desc)asrankingfrom销售统计表)asawhereranking <=2groupby品牌号havingcount(电商平台) >=2;

3）现在可以知道如何求在两个平台内都进入TOP50的品牌号了，只需把ranking <= 2改为ranking <= 50即可

select品牌号,sum(总销售额)as全平台累计销售额from(select*,row_number()over(partitionby电商平台orderby总销售额desc)asrankingfrom销售统计表)asawhereranking <=50groupby品牌号havingcount(电商平台) >=2;

查询结果

仍然将此表作为临时表，表名为：前五十品牌号

4.品牌对应的全电商平台累计销售额

因为输出格式中有品牌名，所以需要联结品牌表和前五十品牌号，因为输出的是进入TOP50的品牌号，所以需要以"前五十品牌表"进行右联结，保留右边表(前五十品牌表)里的全部数据。

求出品牌号为前五十品牌号的品牌号、品牌、全平台累计销售额：

selecta.品牌号,a.品牌名,全平台累计销售额from品牌表asarightjoin前五十品牌号asbona.品牌号 = b.品牌号wherea.品牌号in(b.品牌号)groupbya.品牌号,a.品牌名;

查询结果

最终答案：

with销售统计表(品牌号,电商平台,总销售额)as(select品牌号,电商平台,sum(销售额)as总销售额from月销售统计表whereyear(月份) =2019groupby品牌号,电商平台),前五十品牌号as(select品牌号,sum(总销售额)as全平台累计销售额from(select*,row_number()over(partitionby电商平台orderby总销售额desc)asrankingfrom销售统计表)asawhereranking <=50groupby品牌号havingcount(电商平台) >=2)selecta.品牌号,a.品牌名,全平台累计销售额from品牌表asarightjoin前五十品牌号asbona.品牌号 = b.品牌号wherea.品牌号in(b.品牌号)groupbya.品牌号,a.品牌名;

查询结果

【本题考点】

1）考查对字符串的编程能力。

2）考查多表联结，TOPN问题。

3）考查思维能力，面对多个条件和多个表，如何用逻辑树分析方法理清思路，解决问题。

4）top n问题的应用变化

【举一反三】

 本题的模拟数据可以在公众号（猴子聊人物）对话框回复“资料”，在下面网盘路径中获取到

推荐：如何从零学会sql？