MySQL, как заполнить отсутствующие даты в диапазоне?

Ответ 1

MySQL не имеет рекурсивной функциональности, поэтому вам остается использовать трюк таблицы NUMBERS -

• Создайте таблицу, содержащую только инкрементные числа - легко сделать с помощью auto_increment:

  DROP TABLE IF EXISTS `example`.`numbers`;
  CREATE TABLE  `example`.`numbers` (
    `id` int(10) unsigned NOT NULL auto_increment,
     PRIMARY KEY  (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

• Заполните таблицу, используя:

  INSERT INTO `example`.`numbers`
    ( `id` )
  VALUES
    ( NULL )
  

  ... для количества значений, которые вам нужны.

• Используйте DATE_ADD для создания списка дат, увеличивая дни на основе значения NUMBERS.id. Замените "2010-06-06" и "2010-06-14" на соответствующие даты начала и окончания (но используйте тот же формат, YYYY-MM-DD) -

  SELECT `x`.*
    FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
            FROM `numbers` `n`
           WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
  

• LEFT JOIN на таблицу данных на основе временной части:

     SELECT `x`.`ts` AS `timestamp`,
            COALESCE(`y`.`score`, 0) AS `cnt`
       FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
               FROM `numbers` `n`
              WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
  LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
  

Если вы хотите сохранить формат даты, используйте функцию DATE_FORMAT:

DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`

Ответ 2

Вы можете выполнить это, используя Таблица календаря. Таблицу, которую вы создаете один раз и заполняете диапазоном дат (например, один набор данных для каждого дня 2000-2050, который зависит от ваших данных). Затем вы можете сделать внешнее соединение таблицы против таблицы календаря. Если в вашей таблице отсутствует дата, вы получите 0 для оценки.

Ответ 3

Я не поклонник других ответов, требующих создания таблиц и т.д. Этот запрос делает это эффективно без вспомогательных таблиц.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date
FROM 
    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

Так что давайте проанализируем это.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date

Если будут обнаружены дни, у которых не было баллов, и установите их на 0. b.Days - это настроенное количество дней, которое вы выбрали для получения с текущей даты, до 1000.

    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

Этот подзапрос - это то, что я видел в stackoverflow. Он эффективно генерирует список последних 1000 дней с текущей даты. Интервал (в настоящее время 30) в предложении WHERE в конце определяет, какие дни возвращаются; максимум 1000. Этот запрос может быть легко изменен, чтобы возвращать 100-летние даты, но 1000 - это хорошо для большинства вещей.

LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

Это та часть, которая приносит вашу таблицу, которая содержит оценку. Вы сравниваете с выбранным диапазоном дат от запроса генератора дат, чтобы иметь возможность заполнить 0s, где это необходимо (сначала будет установлен NULL, потому что это LEFT JOIN, это зафиксировано в инструкции select). Я также заказываю его по датам, просто потому, что. Это предпочтение, вы также можете заказать по счету.

Перед ORDER BY вы можете легко присоединиться к своей таблице о пользовательской информации, которую вы упомянули в своем правлении, чтобы добавить это последнее требование.

Надеюсь, эта версия запроса поможет кому-то. Спасибо за прочтение.

Ответ 4

Ответ Майкла Конарда великолепен, но мне нужны были 15-минутные интервалы, когда время должно начинаться с начала каждой 15-й минуты:

SELECT a.Days 
FROM (
    SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60)) - INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE AS Days
    FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY

Это установит текущее время к предыдущему раунду 15-й минуты:

FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60))

И это уберет время с 15-минутным шагом:

- INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE

Если есть более простой способ сделать это, пожалуйста, дайте мне знать.