С таблицей:
id date_from date_to
---------------------------
1 2010-01-01 2010-03-01
2 2010-02-07 2010-05-01
3 2010-07-05 2010-07-10
Я пытаюсь возвратить результат, который ссорится в течение каждого месяца года, который сообщает мне, сколько строк было активно в тот период.
Таким образом для вышеупомянутого я хотел бы результат
2010-01-01 1
2010-02-01 2
2010-03-01 2
2010-04-01 1
2010-05-01 1
2010-06-01 0
2010-07-01 1
2010-08-01 0
2010-09-01 0
etc...
Я попытался группировать свой МЕСЯЦ (date_from), но это не возвращает строки без результатов
MySQL не имеет рекурсивной функциональности, поэтому вам остается использовать уловку с таблицей NUMBERS -
Создайте таблицу, которая содержит только увеличивающиеся числа - легко сделать с помощью auto_increment:
УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ `example`.`numbers`;
СОЗДАТЬ ТАБЛИЦУ `example`.`numbers` (
`id` int (10) unsigned NOT NULL auto_increment,
ПЕРВИЧНЫЙ КЛЮЧ (`id`)
) ДВИГАТЕЛЬ = ИННОДБ СИСТЕМА ПО УМОЛЧАНИЮ = latin1;
Заполните таблицу, используя:
ВСТАВИТЬ В ЧИСЛА
(я бы)
ЦЕННОСТИ
(НУЛЕВОЙ)
... для любого количества значений.
Используйте DATE_ADD , чтобы создать список времен, увеличивая месяцы на основе значения NUMBERS.id:
SELECT x. *
ОТ (ВЫБЕРИТЕ DATE_FORMAT (DATE_ADD ('2010-01-01', ИНТЕРВАЛ n.id - 1 МЕСЯЦ), '% Y-% m-% d')
ИЗ чисел n) x
LEFT JOIN к вашей таблице данных на основе временной части:
SELECT x.ts AS timestamp,
СУММА (СЛУЧАЙ, КОГДА x.ts МЕЖДУ y.date_from И y.date_to THEN 1 ELSE 0 END) AS cnt
FROM (SELECT DATE_FORMAT (DATE_ADD ('2010-01-01', INTERVAL n.id - 1 МЕСЯЦ), '% Y-% m-% d') КАК ts
ИЗ чисел n) x
ЛЕВАЯ ТАБЛИЦА СОЕДИНЕНИЙ y ON x.ts МЕЖДУ y.date_from И y.date_to
ГРУППА ПО x.ts