MySQL中按月統(tǒng)計并逐月累加統(tǒng)計值的幾種實現(xiàn)寫法

更新時間：2023年10月31日 15:33:52 作者：高級盤絲洞

有時候，我們可能有這樣的場景，需要將銷量按月統(tǒng)計，并且按月逐月累加，本文就來介紹一下MySQL中按月統(tǒng)計并逐月累加統(tǒng)計值的幾種實現(xiàn)寫法，感興趣的可以了解一下

本月第一天

-- 本月第一天
SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY);
 
-- 本月第一天
SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01');

建表及模擬數(shù)據(jù)

-- 創(chuàng)建表
CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sales_date` date NOT NULL,
  `sales_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入模擬數(shù)據(jù)
INSERT INTO `sales` (`sales_date`, `sales_amount`) VALUES
('2023-01-01', 1500.00),
('2023-01-02', 1800.00),
('2023-01-05', 2200.00),
('2023-02-01', 1200.00),
('2023-02-03', 1800.00),
('2023-03-01', 2500.00),
('2023-03-05', 2800.00),
('2023-03-08', 3200.00),
('2023-04-01', 2100.00),
('2023-04-03', 1900.00),
('2023-04-05', 2600.00),
('2023-05-01', 3100.00),
('2023-05-02', 3400.00),
('2023-06-01', 3800.00),
('2023-06-06', 4200.00);

該表包含三個字段：id、sales_date、sales_amount。id為自增長主鍵，sales_date為銷售日期，sales_amount為銷售額。插入了15條模擬數(shù)據(jù)，涵蓋了2023年1月至6月的銷售數(shù)據(jù)

一、自連接和子查詢

首先在內(nèi)部查詢中計算出每個月份的銷售總額和月份；接著在外部查詢中使用自連接和子查詢計算每個月份的累計銷售額

SELECT t1.month, t1.monthly_sales,
       SUM(t2.monthly_sales) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1
JOIN (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t2 ON t1.month >= t2.month
GROUP BY t1.month;

二、子查詢

SELECT month, monthly_sales,
       (SELECT SUM(monthly_sales) 
        FROM (
          SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
          FROM sales
          GROUP BY month
        ) t2
        WHERE t2.month <= t1.month) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1;

此處使用了兩個子查詢，第一個子查詢用于獲取每個月份的總銷售額和月份，第二個子查詢用于計算累加值。在內(nèi)部子查詢中，通過<=操作符將當前月份以及之前所有月份的銷售額相加，從而得到累加值

三、子查詢+變量

SELECT month, monthly_sales, @cumulative := @cumulative + monthly_sales AS cumulative
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1
CROSS JOIN (SELECT @cumulative := 0) t2;

此處使用了兩個子查詢，第一個子查詢用于獲取每個月份的總銷售額和月份，第二個子查詢用于初始化變量@cumulative。在外部查詢中，通過CROSS JOIN將兩個子查詢連接起來，并且使用變量@cumulative來計算累加值。

四、用戶變量和子查詢

在內(nèi)部查詢中先對銷售日期進行排序，然后使用用戶變量@cumulative來記錄每個月份的累加值。在最終的查詢結果中，輸出月份、當月銷售額以及累加值

SELECT month, monthly_sales,
       (@cumulative := @cumulative + monthly_sales) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, 
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
  ORDER BY sales_date ASC
) t1, (SELECT @cumulative := 0) t2;

此處使用了兩個子查詢，第一個子查詢用于獲取每個月份的總銷售額和月份，并按銷售日期升序排序；第二個子查詢用于初始化用戶變量@cumulative。在外部查詢中，通過,連接兩個子查詢，并使用用戶變量@cumulative來計算每個月份的累加值。

五、表達式（CTE）和窗口函數(shù)

使用MySQL 8.0引入的通用表表達式（CTE）和窗口函數(shù)，可以將累加值計算放在CTE中完成

WITH monthly_sales AS (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, 
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
)
SELECT month, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM monthly_sales;

此處使用了CTE來計算每個月份的總銷售額和月份，并在外部查詢中使用窗口函數(shù)SUM() OVER()對月份進行累加。

SUM() OVER()

使用MySQL 8.0引入的LATERAL關鍵字，以及OVER ORDER BY子句，按月份求和，再用SUM() OVER()進行累加，并分別輸出月份、當月銷售金額和累計銷售金額

##月統(tǒng)計
SELECT  month, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY month
) t1;

##日統(tǒng)計
SELECT  day, monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY day) AS cumulative_sales
FROM (
  SELECT DATE_FORMAT(sales_date, '%Y-%m-%d') AS day,
         SUM(sales_amount) AS monthly_sales
  FROM sales
  GROUP BY day
) t1;