文章目录
题目描述
问题 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;
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 数量,输出格式如下:
解析
这个问题和上面的问题差不多,主要是按照品牌分组统计;但是在分组统计之后,还需要过滤一下统计的数量。
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;