时间戳

首页 > 解决方案 > 按月分组，具有总和值和滚动年份

mysql - 按月分组，具有总和值和滚动年份

问题描述

我正在尝试在 MySQL 中按月对销售数据进行分组，但使用滚动年总和值。示例预期输出：

Month                           SUM
January 2018    Total from february 2017 to january 2018 (12months)
February 2018   Total from march 2017 to february 2018 (12months)
...

这是我到目前为止得到的：

SELECT YEAR(date), MONTH(date), 
(SELECT SUM(quantity) FROM tb t2 WHERE t2.date BETWEEN DATE_SUB(t1.date, INTERVAL 1 YEAR) AND t1.date)
FROM tb t1
WHERE date BETWEEN '2018-01-01' AND '2019-01-01'
GROUP BY YEAR(date), MONTH(date)

结果是预期的，但是效率很低，因为如果我使用 3 天的间隔运行它需要 30 秒（所以我什至不能以 1 年的间隔运行它）

任何想法如何改进此查询，以便我可以使用 1 年的时间间隔？特定字段是否需要索引？

编辑：创建语句：

CREATE TABLE IF NOT EXISTS `tb` (
  `inc` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` float NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`inc`),
  KEY `date` (`date`)
) ENGINE=InnoDB;

它有超过 100k 行，我无法升级到 MySQL >= 8

标签： mysqldategroup-by

解决方案

代替使用子查询，“自内连接”可以更有效。请尝试以下操作：

SELECT 
  YEAR(t1.date), 
  MONTH(t1.date), 
  SUM(t2.quantity) 
FROM tb AS t1 
JOIN tb AS t2 
  ON t2.date BETWEEN (t1.date - INTERVAL 1 YEAR) AND t1.date 
WHERE t1.date BETWEEN '2018-01-01' AND '2019-01-01'
GROUP BY YEAR(t1.date), MONTH(t1.date)

推荐阅读