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

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

1. Создайте таблицу, в которой хранятся только увеличивающиеся числа - это легко сделать с помощью 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;
   

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

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

   ... для любого количества значений.

3. Используйте DATE_ADD, чтобы создать список дат, увеличивая дни на основе значения NUMBERS.id. Замените «2010-06-06» и «2010-06-14» соответствующими датами начала и окончания (но используйте тот же формат, ГГГГ-ММ-ДД) -

   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
   

4. 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`

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

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

If обнаружит дни, в которых не было оценок, и установит для них значение 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;

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

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

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

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