使用 SQL 实现同比环比分析

场景描述

在做数据分析时,经常会需要展示同比和环比的数据,可以通过 SQL 来完成同比和环比的计算,再配合数据可视化工具进行展示,每个月都会自动展示当月数据与上月的对比(环比),以及当月数据与去年同期数据的对比(同比);

效果展示

我先看一下将同比、环比的数据放到数据看板上的效果图:

上图是将某公司的销售数据做了同比和环比分析后,通过报表展示出来的效果,包含:线上线下销量的同比环比、各大区的销量月环比。

实现过程

测试数据预览

以下是我们分析的原始数据,数据来源于真实业务记录,所以部分数据进行了遮挡处理;

我们重点关注的数据:

  1. 每条数据代表一单销售业绩;
  2. “类型” 包含线上、线下;
  3. “成交日期” 是分析依据;
  4. “大区” 也是要分析的内容。

使用 SQL 实现同比环比分析

SQL实现过程

我们以 “线上销量的月环比” 做SQL实现步骤讲解

先统计每个月的线上销量

select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date

使用 SQL 实现同比环比分析

我们需要在每个月累计销量 后面 显示一列上月的销量:

select t.date tdate ,t.累计销量 本月销量 ,y.累计销量 上月销量

from

(select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) t

left JOIN

(select DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) y

on t.date = y.date

使用 SQL 实现同比环比分析

最后就可以通过 “本月销量” 和 “上月销量” 计算环比了;

计算公式为:(本月销量 - 上月销量) / 上月销量 *100%

select t.date tdate ,t.累计销量 本月销量 ,y.累计销量 上月销量,
case

when y.累计销量 is null or y.累计销量 = 0 then 0.00

else round((t.累计销量 - y.累计销量) / y.累计销量, 4)

end ratio

from

(select DATE_FORMAT(日期,'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) t

left JOIN

(select DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m') as date, count(*) as 累计销量 from renwu where 类型 = '线上' GROUP BY date) y

on t.date = y.date

计算结果中的 “ratio” 就是计算的环比,这里保留了4位小数,是为了在报表中转化为百分比再保留两位小数。

我们只需要展示最新月份的环比,所以不需要把所有数据都列出来,只需要展示最新月份的一条数据即可,做最后的优化:

使用 SQL 实现同比环比分析

以上是环比计算的过程,同比计算只需要将增加的 “上月数据” 改为 “去年数据“ 即可

”DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 MONTH),'%Y-%m')” 
改为
”DATE_FORMAT(DATE_ADD(日期,INTERVAL 1 YEAR),'%Y-%m')”

展示同比环比

在 DataEase 的仪表板编辑界面,将SQL 的计算数据放到一个指标卡中显示即可,显示类型选择为 百分比显示,效果如下:

使用 SQL 实现同比环比分析

最终数据展示看板

使用 SQL 实现同比环比分析

以上看板展示工具为:​​DataEase​​