一、业务背景及口径说明
假设现在有下方这两张表(sales和dim_date),我们需要通过它们计算销售额同比增长率。
第一张表是 sales(销售表),记录日期和日期对应的销售额;
第二张表是dim_date(日期维表),记录日期对应的可比日期(这里是去年同月同天)。
口径如下:
当 日 销 售 额 同 比 增 长 率 = 当 日 销 售 额 − 去 年 同 月 同 天 销 售 额 去 年 同 月 同 天 销 售 额 ∗ 100 % 当日销售额同比增长率=\frac{当日销售额 - 去年同月同天销售额}{去年同月同天销售额} *100\% 当日销售额同比增长率=去年同月同天销售额当日销售额−去年同月同天销售额∗100%
那么我们该怎么写 sql 来计算这个同比增长率呢?
二、计算逻辑
计算逻辑:
- 先用销售表关联日期维表,获得每天的可比日期(如2020-01-03的可比日期为2019-01-03),再用上述结果表关联销售表,得到可别日期对应的销售额;
- 根据统计口径可直接求得。
2.1 MySQL实战
2.1.1 数据导入
-- DDL: sales
drop table if exists test.sales;
create table test.sales (
report_date date comment '日期'
, sales_amt double comment '销售额'
) engine=innodb default charset=utf8
;
-- Insert Data
insert into test.sales(
report_date
, sales_amt
)
values('2020-01-03', '1000')
, ('2020-01-02', '800')
, ('2019-01-03', '900')
, ('2019-01-02', '1100')
;
-- DDL: dim_date
create table test.dim_date (
report_date date comment '日期'
, date_yoy date comment '去年同月同天'
) engine=innodb default charset=utf8
;
-- Insert Data
insert into test.dim_date(
report_date
, date_yoy
)
values('2020-01-03', '2019-01-03')
, ('2020-01-02', '2019-01-02')
;
2.1.1 计算逻辑实现
select a.report_date
, 100* (a.sales_amt - c.sales_amt) / c.sales_amt '可比增长率'
from test.sales a
left join test.dim_date b on a.report_date = b.report_date
left join test.sales c on b.date_yoy = c.report_date