场景说明
现有如下表:
门店表shop:存储了门店id及所属区域,主键为门店id
门店营业额日报表report_shop_daily:存储了每家门店每天营业额,主键为日期+门店id
活动表activity:存储了每个区域在指定日期内进行的促销活动,主键为活动id
目的:
我需要查询22年到23年每个活动期间内的参与活动门店的总营业额
难点
1.因为活动日期不固定,活动门店不固定,想根据日期+门店去查难以走索引
2.多个活动之间日期冲突或者门店冲突,汇总时要使用大量汇总函数或者union all或者一条一条查
我们开始吧
首先充胖子:通过三张表交叉,生成一个以活动id+日期+门店为基本单位的胖子。请注意,当活动日期冲突时,会生成两条数据,每个活动对应一条数据,这就为我们之后按活动汇总创造了条件。
注:
1.创建日历表请查看本专栏另一篇文章:创建日历表
2.剩余表的建表DDL及测试数据可以拉到本文最后面获取
select a.activity_id, a.activity_name, c.calendar_date, s.shop_id
from activity a
left join calendar c on c.calendar_date between '2022-01-01' and '2023-12-31' and c.calendar_date between a.begin_date and a.end_date
left join shop s on if(a.area = '', 1, s.area = a.area);
查询营业额:因为第一步已经生成了一个以活动id+日期+门店的胖子,下一步只需要通过日期+门店去连report_shop_daily表,可以轻松利用到主键,查询效率杠杠的。并且当活动没开始时,也能生成一条营业额为0的数据。
select a.activity_name, ifnull(sum(rsd.money), 0) as total_money
from activity a
left join calendar c on c.calendar_date between '2022-01-01' and '2023-12-31' and c.calendar_date between a.begin_date and a.end_date
left join shop s on if(a.area = '', 1, s.area = a.area)
left join report_shop_daily rsd on rsd.business_date = c.calendar_date and rsd.shop_id = s.shop_id
group by a.activity_id;
附录:写本篇文章的测试表建表sql
-- 创建门店表
create table shop(shop_id char(8), shop_name varchar(100), area char(4), primary key(shop_id), index idx_area(area));
insert into shop(shop_id, shop_name, area) values
('00010001', '蓝天店', '0001'),
('00010002', '蓝梦店', '0001'),
('00010003', '蓝海店', '0001'),
('00010004', '蓝山店', '0001'),
('00020001', '绿水店', '0002'),
('00020002', '绿山店', '0002');
-- 创建门店营业额日报表并随机生成营业额
create table report_shop_daily(business_date date, shop_id char(8), money decimal(10, 2), primary key(business_date, shop_id));
insert into report_shop_daily(business_date, shop_id, money)
select c.calendar_date, s.shop_id, round(rand()*10000, 2) as money from calendar c, shop s where c.calendar_date between '2022-01-01' and '2022-12-31';
-- 创建活动表,该表area对应参加活动的门店,如果为空字符串则全部门店都参与活动
create table activity(activity_id char(9), activity_name varchar(100), area char(4), begin_date date, end_date date, primary key(activity_id));
insert into activity(activity_id, activity_name, area, begin_date, end_date) values
('2022001', '庆祝国庆', '', '2022-10-01', '2022-10-07'),
('2022002', '新品上市', '0001', '2022-11-01', '2022-11-03'),
('2022003', '老客优惠', '0001', '2022-11-01', '2022-11-03'),
('2023001', '元旦快乐', '', '2023-01-01', '2023-01-03');