电商面试题：如何分析复杂业务？
Last updated
Was this helpful?
Last updated
Was this helpful?
Was this helpful?
【题目】
某公司在不同的电商平台上都有店铺，下面是该公司的销售数据表 (每个表有一行示例数据)。
现在需要解决的业务问题是：
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 品牌表 as a
inner join 月销售统计表 as c
on a.品牌号 = c.品牌号
inner join 品类表 as b
on b.品类号 = c.品类号;把这个查询结果当做临时表
2. 筛选条件：品类号列表（12，33，45，99，1001），2019 年
使用 where 子句筛选出符合条件的数据
select *
from 临时表
where 品类号 in(12，33，45，99，1001)
and year(月份) = 2019;查询结果
3. 每个电商平台上每个品牌号对应每个品类号的累计销售额
看到 "每个"，要想到《猴子 从零学会 sql》里见过的这类问题要用分组汇总。
select sum(销售额) as 销售额
from 临时表
where 品类号 in(12，33，45，99，1001)
and year(月份) = 2019
group by 电商平台,品牌号,品类号;查询结果
把临时表的 sql 套入上面就得到最终的 sql：
select a.品牌号,品牌名,b.品类号,品类名,电商平台,sum(销售额) as 销售额
from 品牌表 as a
inner join 月销售统计表 as c
on a.品牌号 = c.品牌号
inner join 品类表 as b
on b.品类号 = c.品类号
where 品类号 in(12，33，45，99，1001)
and year(月份)= 2019
group by a.品牌号,品牌名,b.品类号,品类名,电商平台;查询结果
问题 2: 查询 2019 年有 5 个以上 (含 5 个) 不同品类号的单月单平台销售额大于等于 10000 的品牌列表，及对应的不同品类号数量，输出格式如下
使用逻辑树分析方法将复杂问题变简单：
（1）查询结果：品牌号、品牌名、品类数量
（2）筛选条件：2019 年
（3）不同品类号的单月单平台的销售额
（4）5 个以上 (含 5 个) 不同品类号
（5）销售额大于等于 10000
1. 查询结果：品牌号、品牌名、品类数量
查询结果涉及到 2 张表需要用到多表查询，用哪种联结呢？
因为题目要求查询所有符合条件的品牌号，而品牌表包含了所有品牌号，所有需要以 " 品牌表 " 进行左联结，保留左边表 (品牌表) 里的全部数据。
select *
from 品牌表 as a
left join 月销售统计表 as c
on a.品牌号 = c.品牌号;查询结果
把上面查询结果当做临时表。
2. 筛选条件：2019 年
select *
from 临时表
where year(月份) = 2019;查询结果
3. 不同品类号的单月单平台的销售额
也就是每个品类号每个月每个平台，涉及到 “每个” 需要用到分组汇总。
select sum(销售额)
from 临时表
where year(月份) = 2019
group by 品类号,月份,电商平台;查询结果
4.5 个以上 (含 5 个) 不同品类号
需要用 count，distinct 筛选出符合条件的数据。
select sum(销售额) as 销售额
from 临时表
where year(月份) = 2019
group by 品类号,月份,电商平台
having count(distinct 品类号) >= 5;查询结果
5. 销售额大于等于 10000
select sum(销售额) as 销售额
from 临时表
where year(月份) = 2019
group by 品类号,月份,电商平台
having count(distinct 品类号) >= 5
and 销售额>= 10000;查询结果
输出格式如下：
把临时表的 sql 套入上面就得到最终的 sql：
select a.品牌号,品牌名,count(distinct 品类号) as 品类号数量
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号
where year(月份) = 2019
group by a.品牌号,品牌名,月份,电商平台
having count(distinct 品类号) >= 5
and 销售额>= 10000;查询结果
问题 3: 查询 2019 年只在电商平台 1 上有销售额的品牌中（即排除电商平台为 2 时销售额累计大于 0 的品牌），电商平台 1 的累计销售额最大的 Top30 个品牌及对应的销售额，输出格式如下
使用逻辑树分析方法，将复杂问题拆解为简单问题：
（1）查询结果：品牌号，品牌名，平台 1 总销售额
（2）筛选条件：2019 年
（3）只在电商平台 1 上有销售额的品牌 (即平台 2 的累计销售额为 0)
（4）电商平台 1 的累计销售额最大的 Top30 个品牌及对应的销售额
1. 查询结果：品牌号，品牌名，平台 1 总销售额
输出的表中有品牌号，品牌名和销售额，所有需要用到联结，联结品牌表和月销售统计表，联结方式和问题 2 相同
select *
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号;把查询结果当做临时表。
2. 筛选条件：2019 年
select *
from 临时表
where year(月份) = 2019;查询结果
3. 只在电商平台 1 上有销售额的品牌 (即平台 2 的累计销售额为 0)
需要用 group by 对品牌号，电商平台分组，用 having 筛选出平台为 2 且销售额为 0 的品牌号作为子查询
select a.品牌号
from 临时表
where year(月份) = 2019
group by a.品牌号,电商平台
having 电商平台 = 2 and sum(销售额) = 0;查询结果
4. 电商平台 1 的累计销售额最大的 Top30 个品牌及对应的销售额
观察输出格式要求。
可以看出需要要用 group by 对品牌号，品牌名，电商平台分组，
用 having 和 in 筛选出平台为 1 且在平台 2 销售额为 0 的 Top30 个品牌号，品牌名及对应的销售额。
并用 order by 对销售额排序, 并用 limit 取前 30 项
select a.品牌号,sum(销售额) as '平台1总销售额'
from 临时表
where year(月份) = 2019
group by a.品牌号,电商平台
having 电商平台 = 1 and a.品牌号 in (子查询)
order by sum(销售额) desc
limit 30;查询结果
观察输出格式要求：
最终答案：
select a.品牌号,品牌名,sum(销售额) as '平台1总销售额'
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号
where year(月份) = 2019
group by a.品牌号,品牌名,电商平台
having 电商平台 = 1 and a.品牌号 in (
select a.品牌号
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号
where year(月份) = 2019
group by a.品牌号,电商平台
having 电商平台 = 2 and sum(销售额) = 0
)
order by sum(销售额) desc
limit 30;查询结果
问题 4: 查询 2019 年在两个电商平台中分别同时都能进入销售额 Top 50 的品牌及对应的全电商平台累计销售额，输出格式如下
使用逻辑树分析方法，将复杂问题拆解为简单问题：
（1）查询结果：品牌号，品牌名，全平台累计销售额
（2）筛选条件：2019 年
（3）在两个电商平台中分别同时都能进入销售额 Top 50 的品牌
（4）品牌对应的全电商平台累计销售额
1. 查询结果：品牌号，品牌名，全平台累计销售额
输出的表中有品牌号，品牌名和销售额，所有需要用到联结，联结品牌表和月销售统计表，联结方式和问题 2，问题 3 相同
select *
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号;查询结果
2. 筛选条件：2019 年
where 子句限制条件
select *
from 品牌表 as a left join 月销售统计表 as c
on a.品牌号 = c.品牌号
where year(月份) = 2019;查询结果
3. 在两个电商平台中分别同时都能进入销售额 Top 50 的品牌
1）因为月销售统计表中同品牌号同平台包含了不同的品类号
所有应该用 group by 按品牌号和电商平台分组，计算每个品牌在每个电商平台的累计销售额
select 品牌号,电商平台,sum(销售额) as 总销售额
from 月销售统计表
where year(月份) = 2019
group by 品牌号,电商平台;查询结果
将上述结果作为临时表，表名为：销售统计表
2）分别同时都能进入销售额 Top 50 的品牌：
这是《猴子 从零学过 sql》里讲过的 top N 问题。
先从简单的入手：查询每个平台销售额最高的两个品牌（即对平台进行分组，对销售额进行排序），套用之前讲过的 top N 问题模板。
#TOP N问题SQL模板
select *
from (
select*,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as ranking
from 表名) as a
where ranking <= N;对应这个案例就是：
select *
from (
select*,
row_number() over (partition by 电商平台
order by 总销售额 desc) as ranking
from 销售统计表) as a
where ranking <=2;那么在两个平台内都进入 TOP2 的品牌号呢？
从上图可以看出，很显然是品牌号是 158。因此我们需要对品牌号分组，用 count 对品牌号进行计数，大于或等于 2 则该品牌同时在两个平台均进入 TOP2
select 品牌号,sum(总销售额) as 平台总销售额
from (
select*,
row_number() over (partition by 电商平台
order by 总销售额 desc) as ranking
from 销售统计表) as a
where ranking <=2
group by 品牌号
having count(电商平台) >= 2;3）现在可以知道如何求在两个平台内都进入 TOP50 的品牌号了，只需把 ranking <= 2 改为 ranking <= 50 即可
select 品牌号,sum(总销售额) as 全平台累计销售额
from (
select*,
row_number() over (partition by 电商平台
order by 总销售额 desc) as ranking
from 销售统计表) as a
where ranking <=50
group by 品牌号
having count(电商平台) >= 2;查询结果
仍然将此表作为临时表，表名为：前五十品牌号
4. 品牌对应的全电商平台累计销售额
因为输出格式中有品牌名，所以需要联结品牌表和前五十品牌号，因为输出的是进入 TOP50 的品牌号，所以需要以 "前五十品牌表" 进行右联结，保留右边表 (前五十品牌表) 里的全部数据。
求出品牌号为前五十品牌号的品牌号、品牌、全平台累计销售额：
select a.品牌号,a.品牌名,全平台累计销售额
from 品牌表 as a right join 前五十品牌号 as b
on a.品牌号 = b.品牌号
where a.品牌号 in (b.品牌号)
group by a.品牌号,a.品牌名;查询结果
最终答案：
with 销售统计表(品牌号,电商平台,总销售额)
as
(select 品牌号,电商平台,sum(销售额) as 总销售额
from 月销售统计表
where year(月份) = 2019
group by 品牌号,电商平台),
前五十品牌号 as
(select 品牌号,sum(总销售额) as 全平台累计销售额
from (
select *,
row_number() over(partition by 电商平台
order by 总销售额 desc) as ranking
from 销售统计表) as a
where ranking <=50
group by 品牌号
having count(电商平台) >= 2)
select a.品牌号,a.品牌名,全平台累计销售额
from 品牌表 as a right join 前五十品牌号 as b
on a.品牌号 = b.品牌号
where a.品牌号 in (b.品牌号)
group by a.品牌号,a.品牌名;查询结果
【本题考点】
1）考查对字符串的编程能力。
2）考查多表联结，TOPN 问题。
3）考查思维能力，面对多个条件和多个表，如何用逻辑树分析方法理清思路，解决问题。
4）top n 问题的应用变化
【举一反三】
本题的模拟数据可以在公众号（猴子聊人物）对话框回复 “资料”，在下面网盘路径中获取到
推荐：如何从零学会 sql？
