Как получить данные за последние N месяцев?

Question

[alekseyHunter]

Hi!
Имеется таблица, содержащая данные за каждый месяц. Хочу получить данные за предыдущие N месяцев, даже если в этот месяц данные не были добавлены в таблицу.

CREATE TABLE `date` (
  `year` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `value1` int(11) NOT NULL,
  `value2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `month` (
  `id` int(11) NOT NULL,
  `name` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `date` (`year`,`month_id`, `value1`, `value2`) VALUES
(2019,1, 1, 1),
(2019,2, 2, 2),
(2019,3, 3, 3),
(2019,4, 4, 4),
(2019,5, 5, 5),
(2019,6, 6, 6),
(2019,8, 8, 8),
(2019,9, 9, 9),
(2019,10, 10, 10),
(2019,11, 11, 11),
(2019,12, 12, 12),
(2020,1, 1, 1),
(2020,2, 2, 2),
(2020,3, 3, 3),
(2020,4, 4, 4),
(2020,5, 5, 5),
(2020,6, 6, 6),
(2020,8, 8, 8),
(2020,9, 9, 9),
(2020,10, 10, 10),
(2020,11, 11, 11),
(2020,12, 12, 12);

INSERT INTO `month` (`id`, `name`) VALUES
(1, "Январь"),
(2, "Февраль"),
(3, "Март"),
(4, "Апрель"),
(5, "Май"),
(6, "Июнь"),
(7, "Июль"),
(8, "Август"),
(9, "Сентябрь"),
(10, "Октябрь"),
(11, "Ноябрь"),
(12, "Декабрь");

Написал следующий запрос, но он возвращает данные, не ограничиваясь текущим месяцем:

Select Concat(m.name, " ", d.year) as name, ifnull(d.value1,0) 
From month m left join date d On m.id = d.month_id 
order by d.year,d.month_id limit 6 OFFSET 18;

Протестировать можно тут: www.sqlfiddle.com/#!9/2c1b67/23/0

Answer 1

[Константин Цветков]

SELECT Concat(calendar.name, " ", calendar.year) as name, ifnull(date.value1,0) AS value
 FROM (SELECT DISTINCT date.year, month.id, month.name FROM date, month) AS calendar
   LEFT JOIN date
     ON date.year = calendar.year AND date.month_id = calendar.id
 ORDER BY calendar.year, calendar.id LIMIT 6 OFFSET 18

ограничиваясь текущим месяцем … последние 6

SELECT Concat(calendar.name, " ", calendar.year) as name, ifnull(date.value1,0) AS value
 FROM (SELECT DISTINCT date.year, month.id, month.name FROM date, month) AS calendar
   LEFT JOIN date
     ON date.year = calendar.year AND date.month_id = calendar.id
  WHERE CONCAT(calendar.year, '-', LPAD( calendar.id, 2, '0')) 
                 > LEFT(DATE_SUB(NOW(), INTERVAL 6 MONTH), 7)
 ORDER BY calendar.year, calendar.id LIMIT 6

Comments

[alekseyHunter]

Благодарю!
А как получить последние 6 записей, ограничиваясь текущим месяцем? Т.е. с Октября 2019 до Марта 2020
В случае с Join мы получаем все возможные соединения и возвращаем последние 6 из них.

[alekseyHunter]

Решение отличное! Жаль, что не могу применить, т.к.на SQLite нет этих функций...

[Константин Цветков]

alekseyHunter, Есть аналоги. А если нет, то можно заменить на другие придерживаясь самой схемы.

[alekseyHunter]

Константин Цветков, для LEFT нашел следующую замену
substr(datetime('now','-6 month'),1, 7)
для Concat следущую
calendar.name || " " || calendar.year
для LPAN следующий код
substr('00' || calendar.id, -2, 2)

[alekseyHunter]

Константин Цветков, работает: www.sqlfiddle.com/#!7/b2dcd/1/0
правда он требуется, чтобы была хотя бы одна дата за последние 6 месяцев, иначе он начинает отчет с текущего года.

[Константин Цветков]

alekseyHunter, Я рассматривал, что в calendar должен быть сегодняшний год, но оставил вариант получения годов из DataDB, как более простой (хотя ещё там могут быть отсутствующие года).
А самым простым для получения calendar будет использовать две таблицы Years и Months.

[alekseyHunter]

Константин Цветков, Благодарю, добавил таблицу Years и все заработало!