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

у меня есть таблица с 2 столбцами, дата и результат. Он имеет более 30 записей, для каждого из последних 30 суток. 

date      score
-----------------
1.8.2010  19
2.8.2010  21
4.8.2010  14
7.8.2010  10
10.8.2010 14

моя проблема в том, что некоторые даты отсутствуют - я хочу видеть:

date      score
-----------------
1.8.2010  19
2.8.2010  21
3.8.2010  0
4.8.2010  14
5.8.2010  0
6.8.2010  0
7.8.2010  10
...

что мне нужно от одного запроса, чтобы получить: 19,21,9,14,0,0,10,0,0,14... Это означает, что недостающие даты заполняются 0.

Я знаю, как получить все значения и на серверном языке перебирать даты и пропускать пробелы. Но это можно сделать в mysql, так что я сортирую результат по дате и получаю недостающие части.

EDIT: в этой таблице есть еще один столбец с именем UserID, поэтому у меня есть 30.000 пользователей, и некоторые из них имеют счет в этой таблице. Я удаляю даты каждый день, если дата

3 56
sql mysql gaps-and-islands

3 ответа:

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

  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. левый присоединиться к вашей таблице данных на основе части времени:

       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`
53

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

14

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

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;

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

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

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

3