某电商销售数据分析 SQL 面试题解析

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net


文章目录

        题目描述
        问题 1
            解析
        问题 2
            解析
        问题 3
            解析
        问题 4
            解析
        总结

本文基于微信群里的一个问题,感谢这位朋友提供的分享。

题目描述

假设某电商销售数据有如下几张表:

在这里插入图片描述

其中,

month:date 类型,存储的是每个月的第一天;
bid:对应 Brand.bid;
cid:对应 Category.cid;
paltform:对应不同的电商平台,有 2 种:1,2;
sales:销售额;
主键为 (month, paltform, bid, cid),一行数据对应一个月一个平台一个 bid 的一个 cid 的销售额。

基于上面的描述,我们可以创建以下示例表和模拟数据(使用 MySQL 数据库):

create table brand(bid int auto_increment primary key, name varchar(50));
insert into brand(name) values (‘品牌1’);
insert into brand(name) values (‘品牌2’);
insert into brand(name) values (‘品牌3’);
insert into brand(name) values (‘品牌4’);
insert into brand(name) values (‘品牌5’);

create table category(cid int auto_increment primary key, name varchar(10));
insert into category(name) values (‘食品’);
insert into category(name) values (‘手机’);
insert into category(name) values (‘服饰’);
insert into category(name) values (‘图书’);
insert into category(name) values (‘电脑’);

create table monthlysales(month date, bid int, cid int, platform int,sales int);
alter table monthlysales add constraint pk_monthlysales primary key(month, platform, bid, cid);

insert into monthlysales
with recursive dt as (
select date ‘2019-01-01’ as v
union all
select v + interval ‘1’ month from dt where v < date ‘2019-12-01’
),
platform(pid) as (
values row(1), row(2)
)
select v, bid, cid, pid, 1000 + round((rand(1) * 500))
from dt
cross join brand
cross join category
cross join platform;

为了生成示例数据,我们使用了递归形式的通用表表达式(WITH子句),相关语法可以参考这篇文章。另外,rand(1) 函数确保了结果的可复现。
问题 1

对于指定的 cid 范围(cid 列表:1,3,5),查询 2019 年每个平台上每个 bid 对应每个 cid 的累计销售额,输出格式如下:
在这里插入图片描述

请写出 SQL。
解析

这个题目比较简单,就是按照品牌、品类以及平台分组再加上 sum 函数统计销售额;同时关联其他表获取品牌和品类名称。

select b.bid, b.name brand_name, c.cid, c.name category_name, s.platform, sum(s.sales) total_sales
from monthlysales s
join brand b on (b.bid = s.bid)
join category c on (c.cid = s.cid)
where b.bid in (1, 3, 5)
and s.month between date ‘2019-01-01’ and date ‘2019-12-01’
group by b.bid, b.name, c.cid, c.name, s.platform;

bidbrand_namecidcategory_nameplatformtotal_sales
1品牌11食品115115
1品牌12手机115441
1品牌13服饰114869
1品牌14图书115516
1品牌15电脑114971

对于 2019 年的判断,最好不要使用 year(month) 函数,因为这样会导致索引失效。另外,关于各种数据库中的分组汇总和聚合函数可以参考这篇文章。
问题 2

查询 2019 年有 5 个以上(包含 5 个)不同 cid 的单月单平台销售额大于等于 1480 的品牌列表,以及对应的不同 cid 数量,输出格式如下:
在这里插入图片描述

解析

这个问题和上面的问题差不多,主要是按照品牌分组统计;但是在分组统计之后,还需要过滤一下统计的数量。

select b.bid, b.name brand_name, count(distinct s.cid) cid_count
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.sales >= 1480
group by b.bid, b.name
having cid_count >= 5;

bidbrand_namecid_count
4品牌45

其中,count() 函数用于统计各种品牌单月在单平台上不同 cid 的数量;having 最终返回数量大于等于 5 的品牌。
问题 3

查询 2019 年只在平台 1 上有销售额的品牌中(排除平台为 2 时销售累计额大于 0 的品牌),平台 1 累计销售额最大的 Top 3 品牌以及对应的销售额,输出格式如下:
在这里插入图片描述

解析

同样是先进行分组统计,获取每个品牌在平台 1 上的累计销售额;然后加上排序和数量限定操作返回 Top 3 结果。

select b.bid, b.name brand_name, sum(s.sales) total_sales_p1
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 1
group by b.bid, b.name
having total_sales_p1 > 0
order by total_sales_p1 desc
limit 3;

bidbrand_nametotal_sales_p1
3品牌376464
1品牌175912
4品牌474931

其中,ORDER BY用于按照累计销售额从高到低进行排序;LIMIT用于返回前 3 条记录。
问题 4

查询 2019 年在两个平台中分别同时都能进入销售额 Top 3 的品牌以及对应的全平台累计销售额,输出格式如下:
在这里插入图片描述

解析

这个问题至少有两种解决办法:基于问题 3 中的解决方法分别获取平台 1 和平台 2 上的 Top 3 品牌,然后使用一个连接查询;或者使用 MySQL 8.0 中的窗口函数。

第一种方法的实现如下:

select top3_p1.bid, top3_p1.brand_name, top3_p1.total_sales_p1 + top3_p2.total_sales_p2 as total_sales_all
from (
select b.bid, b.name brand_name, sum(s.sales) total_sales_p1
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 1
group by b.bid, b.name
having total_sales_p1 > 0
order by total_sales_p1 desc
limit 3) top3_p1
join (
select b.bid, b.name brand_name, sum(s.sales) total_sales_p2
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 2
group by b.bid, b.name
having total_sales_p2 > 0
order by total_sales_p2 desc
limit 3) top3_p2
on (top3_p1.bid = top3_p2.bid)
order by total_sales_all desc;

bidbrand_nametotal_sales_all
1品牌1153579
3品牌3152819
4品牌4150860

其中,top3_p1 代表了平台 1 上的 Top 3 品牌和累计销售额;op3_p2 代表了平台 2 上的 Top 3 品牌和累计销售额。

如果使用的 MySQL 8.0 版本,我们可以利用窗口函数计算排名:

with sales2019 as (
select bid, platform, sum(sales) sales
from monthlysales
where month between date ‘2019-01-01’ and date ‘2019-12-01’
group by bid, platform
),
top3 as (
select * from (
select bid, platform, sum(sales) over (partition by bid) total_sales_all,
rank() over (partition by platform order by sales desc) as rk
from sales2019
) t where rk <= 3
)
select p1.bid, b.name brand_name, p1.total_sales_all
from top3 p1
join top3 p2 on (p1.bid = p2.bid)
join brand b on (b.bid = p1.bid)
where p1.platform = 1
and p2.platform = 2;

bidbrand_nametotal_sales_all
1品牌1153579
3品牌3152819
4品牌4150860

其中,sales2019 代表了 2019 年各种品牌在不同平台上的销售额;top3 基于 sales2019 计算不同平台上的 Top 3 品牌和累计销售额;这里使用了两个窗口函数(OVER子句是窗口函数的标识),sum() 用于获取品牌的累计销售额,rank() 用于计算不同平台上的销售额排名。

除此之外,WITH语句比第一种方法语义上更加清晰;而且从性能角度来说也会更好,因为它只需要访问 monthlysales 表一次。
总结

总的来说,这几道题目都是高频的 SQL 数据分析问题。依次考察了GROUP BY分组加聚合函数、HAVING过滤和DISTINCT去重、ORDER BY排序加上LIMIT子句实现 Top-N 排行榜,以及窗口函数的使用。