SQL面试题实战：某电商销售数据分析

2025-1-2

题目描述

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

Brand（品牌表）

bid	name
1	品牌1
...	...

Category（品类表）

cid	name
1	食品
...	...

Monthlysales（月度销量统计表）

month	bid	cid	platform	sales
2019-12-01	1	1	1	1000
...	...	...	...	...

其中，

month：date 类型，存储的是每个月的第一天；
bid：对应 Brand.bid；
cid：对应 Category.cid；
platform：对应不同的电商平台，有 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 monthlysaleswith 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 dtcross join brandcross join categorycross 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_salesfrom monthlysales sjoin 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;
bid|brand_name  |cid|category_name  |platform|total_sales|---|------------|---|---------------|--------|-----------|  1|品牌1       |  1|食品           |       1|      15115|  1|品牌1       |  2|手机           |       1|      15441|  1|品牌1       |  3|服饰           |       1|      14869|  1|品牌1       |  4|图书           |       1|      15516|  1|品牌1       |  5|电脑           |       1|      14971|...

对于 2019 年的判断，最好不要使用 year(month) 函数，因为这样会导致索引失效。

问题 2

查询 2019 年有 5 个以上（包含 5 个）不同 cid 的单月单平台销售额大于等于 1480 的品牌列表，以及对应的不同 cid 数量，输出格式如下：

| bid | brand_name | cid_count |

解析

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

select b.bid, b.name brand_name, count(distinct s.cid) cid_countfrom monthlysales sjoin brand b on (b.bid = s.bid)where s.month between date '2019-01-01' and date '2019-12-01'and s.sales >= 1480group by b.bid, b.namehaving cid_count >= 5;
bid|brand_name  |cid_count|---|------------|---------|  4|品牌4       |        5|

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

问题 3

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

| bid | brand_name | total_sales_p1|

解析

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

select b.bid, b.name brand_name, sum(s.sales) total_sales_p1from monthlysales sjoin brand b on (b.bid = s.bid)where s.month between date '2019-01-01' and date '2019-12-01'and s.platform = 1group by b.bid, b.namehaving total_sales_p1 > 0order by total_sales_p1 desclimit 3;
bid|brand_name  |total_sales_p1|---|------------|--------------|  3|品牌3       |         76464|  1|品牌1       |         75912|  4|品牌4       |         74931|

其中，ORDER BY用于按照累计销售额从高到低进行排序；`LIMIT`用于返回前 3 条记录。

问题 4

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

| bid | brand_name | total_sales_all|

解析

这个问题至少有两种解决办法：基于问题 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_allfrom (  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_p1join (  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_p2on (top3_p1.bid = top3_p2.bid)order by total_sales_all desc;
bid|brand_name  |total_sales_all|---|------------|---------------|  1|品牌1       |         153579|  3|品牌3       |         152819|  4|品牌4       |         150860|

其中，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_allfrom top3 p1 join top3 p2 on (p1.bid = p2.bid)join brand b on (b.bid = p1.bid)where p1.platform = 1and p2.platform = 2;
bid|brand_name  |total_sales_all|---|------------|---------------|  1|品牌1       |         153579|  3|品牌3       |         152819|  4|品牌4       |         150860|

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

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

总结

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

来源：SQL编程思想

THE END

实战 SQL窗口函数：电商平台销售排行榜和飙升榜

<<上一篇

人工智能开发：图像+文本多模态 RAG 全能指南

下一篇>>