實現同比、環比計算的N種姿勢

葡萄城技術團隊發表於2022-03-11

在做資料分析時，我們會經常聽到同比、環比同比的概念。各個企業和組織在釋出統計資料時，通常喜歡用同比、環比來和之前的歷史資料進行比較，用來說明資料的變化情況。例如，統計局公佈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 c
left 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) b
on (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 Enterprise嵌入式商業智慧軟體為例，其內建的wax分析表示式和快速計算引擎，提供直接實現同比、環比等複雜計算的能力，而不再需要寫複雜冗長的SQL。

3、1 使用內建的同比、環比快速計算功能**

同比、環比等計算一般是BI工具的標準功能，我們可以直接通過設定實現。

3、2 使用資料分析表示式

如果內建的快速計算無法滿足要求，還可以通過分析表示式實現更復雜的計算。分析表示式是一種更加靈活、強大的資料計算方式，通過豐富的函式，使用者可以像Excel公式一樣自由組合，實現更加強大的分析能力。分析表示式基於資料模型進行業務計算，以一些定義好的函式運用正確的語法來完成某個複雜的業務邏輯計算。這樣可以使使用者更靈活的地使用資料，最大限度的利用資料。

各位老闆們，通過對比SQL和BI資料分析工具在處理同比、環比等複雜計算中的差異，我們可以發現，還是專業的工具在資料計算和處理能力上要更加便捷。以後在工作中，如果有類似的分析計算需求，選擇BI分析工具來處理就是再合適不過的了。

相關文章