实现同比、环比计算的 N 种姿势

2022 年 5 月 11 日
本文字数：2253 字
阅读完需：约 7 分钟

在做数据分析时，我们会经常听到同比、环比同比的概念。各个企业和组织在发布统计数据时，通常喜欢用同比、环比来和之前的历史数据进行比较，用来说明数据的变化情况。例如，统计局公布 2022 年 1 月份 CPI 同比增长 0.9%，环比增长 0.6%。

实际中，在基于数据库的数据分析场景中，环比和同比是典型的复杂计算场景之一，特别是在 Oracle 等商业数据库的分析函数出现之前。以 MySQL 为例，在 8.0 版本中才引入了 Lag 和 Lead 函数，这两个函数结合开窗函数有效的提高了同比、环比等复杂运算的实现效率。在 5.x 系列版本中，MySQL 需要依赖多次嵌套子查询和自关联才能实现此类计算。

我们以一个简单的例子，来分别看下，MySql 5.x 和 8.0 是具体实现同比、环比计算的。

示例数据见表：

CREATE TABLE sales  (  `产品ID` varchar(20),    `销售数量` int(20) ,  `销售时间` timestamp(6) NULL DEFAULT NULL)INSERT INTO sales VALUES ('C1001', 15, '2020-06-01 10:10:12');INSERT INTO sales VALUES ('C1002',26, '2020-05-02 0:10:12');INSERT INTO sales VALUES ('C1003', 21, '2020-04-03 0:10:12');INSERT INTO sales VALUES ('C1003', 23, '2020-04-04 0:10:12');INSERT INTO sales VALUES ('C1003', 0, '2020-03-05 0:10:12');INSERT INTO sales VALUES ('C1001', 16, '2020-02-06 3:0:12');INSERT INTO sales VALUES ('C1002', 32, '2020-01-07 0:10:12');INSERT INTO sales VALUES ('C1001', 16, '2019-12-08 0:12:24');INSERT INTO sales VALUES ('C1001', 32, '2019-06-09  0:12:24');INSERT INTO sales VALUES ('C1002', 17, '2019-05-09 0:12:24');

复制代码

1、MySQL 5.x ：通过子查询和关联实现同比和占比计算

以按年月统计不同年份的销售总值，并计算环比（销售总额同比上期）、同比（销售总额同比去年同期）为例。

示例表结构和数据

通过 SQL 计算环比和同比：

select  year(c.销售时间) yy,month(c.销售时间) mm,     concat(ifnull(abs(round((sum(c.销售数量)-ss1)/ss1*100,2)),0),'%') 同比,concat(ifnull(abs(round((sum(c.销售数量)-ss2)/ss2*100,2)),0),'%')  环比from sales cleft join (select month(a.销售时间) mm1,                    year(a.销售时间) yy1,                    sum(a.销售数量) ss1          from sales a          GROUP BY mm1,yy1) a          on month(c.销售时间) = a.mm1           and a.yy1 = year(c.销售时间)-1    left join  (select month(a.销售时间) mm2,                    year(a.销售时间) yy2,                    sum(a.销售数量) ss2             from sales a                     GROUP BY mm2,yy2) bon (b.yy2 = year(c.销售时间) and b.mm2+1 = month(c.销售时间) OR (yy2=year(c.销售时间)-1 AND b.mm2 = 12 AND month(c.销售时间) = 1)) group by yy, mm order by yy,mm asc

复制代码

计算结果：

2、 MySQL 8.0 ：通过分析函数实现同比和占比计算**

MySql8.0 支持了 Lead 和 Lag 分析函数，虽然可以大幅提高同、环比计算的效率，但仍然需要编写 SQL 语句处理。

2、1 计算同比

select t2.年份,t2.月份,concat(round((t2.数量-t1.数量)/t1.数量,2)*100,'%') as 同比 from (SELECT year(销售时间) as 年份,month(销售时间) as 月份,sum(销售数量) as 数量 from sales group by year(销售时间),month(销售时间) order by year(销售时间) desc, month(销售时间) desc) t1,(SELECT year(销售时间) as 年份,month(销售时间) as 月份,sum(销售数量) as 数量 from sales group by year(销售时间),month(销售时间) order by year(销售时间) desc, month(销售时间) desc) t2 where t1.年份=t2.年份-1 and t1.月份=t2.月份

复制代码

2、2 计算环比

SELECT  mm,  CONCAT(    ROUND(      IFNULL(        (xl - first_xl) / first_xl * 100,        2      ),      0    ),    '%'  ) AS 环比FROM  (    SELECT      mm,      xl,      lead (xl, 1) over (ORDER BY mm DESC) AS first_xl    FROM      (        SELECT          DATE_FORMAT(销售时间, '%Y-%m') AS mm,          sum(销售数量) AS xl        FROM          sales        GROUP BY          DATE_FORMAT(销售时间, '%Y-%m')      ) t  ) a

复制代码

在 SqlServer2008R2 和 Oracle10g 之后，都提供了 Lag 和 Lead 分析函数。具体的计算逻辑和用法与上述 MySQL8.0 类似。

3、使用 BI 工具的计算引擎

针对此类复杂的计算场景，商业智能 BI 数据分析工具提供了更加高效的解决方案。以Wyn Enterpris为例，其内置的 wax 分析表达式和快速计算引擎，提供直接实现同比、环比等复杂计算的能力，而不再需要写复杂冗长的 SQL。

3、1 使用内置的同比、环比快速计算功能**

同比、环比等计算一般是 BI 工具的标准功能，我们可以直接通过设置实现。

3、2 使用数据分析表达式

如果内置的快速计算无法满足要求，还可以通过分析表达式实现更复杂的计算。分析表达式是一种更加灵活、强大的数据计算方式，通过丰富的函数，用户可以像 Excel 公式一样自由组合，实现更加强大的分析能力。分析表达式基于数据模型进行业务计算，以一些定义好的函数运用正确的语法来完成某个复杂的业务逻辑计算。这样可以使用户更灵活的地使用数据，最大限度的利用数据。