Группировать по месяцам с суммой и текущим годом

Я пытаюсь сгруппировать данные о продажах по месяцам в 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)

Результаты такие ожидаемые, но это очень неэффективно, поскольку для запуска требуется 30 секунд, если я использую интервал в 3 дня (поэтому я даже не могу запустить его с интервалом в 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;

В нем более 100 тыс. Строк, и я не могу перейти на MySQL> = 8

Если вы можете обновить MySQL до версии> = 8.0.2, вы можете использовать оконные функции с фреймами.

Madhur Bhaiya 31.10.2018 11:30

Кроме того, поделитесь своими операторами Create Table, указав подробную информацию об определенной индексации (если таковая имеется).

Madhur Bhaiya 31.10.2018 11:31

Думаю, у вас опечатка в исходном запросе, вместо AND должен быть Where

Madhur Bhaiya 31.10.2018 11:55

@MadhurBhaiya обновлено

caRameL 31.10.2018 11:58
0
5
132
1

Ответы 1

Вместо использования подзапросов более эффективным может быть «Само-внутреннее соединение». Вместо этого попробуйте следующее:

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)

Другие вопросы по теме