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

Question

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, чтобы я отсортировал результат по дате и получил недостающие фрагменты.

РЕДАКТИРОВАТЬ: В этой таблице есть еще один столбец с именем UserID, поэтому у меня есть 30 000 пользователей, и некоторые из них имеют счет в этой таблице. Я удаляю даты каждый день, если дата < 30 дней назад, потому что мне нужны последние 30 дней для каждого пользователя. Причина в том, что я делаю график активности пользователей за последние 30 дней, и для построения графика мне нужно 30 значений, разделенных запятой. Таким образом, я могу сказать, что в запросе вы получите действие USERID = 10203, и запрос даст мне 30 баллов, по одному на каждый из последних 30 дней. Я надеюсь, что теперь я более ясен.

58

gaps-and-islands mysql sql

Решение

Другие решения

Вы можете сделать это с помощью Календарь стол. Это таблица, которую вы создаете один раз и заполняете диапазоном дат (например, один набор данных на каждый день 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;

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

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

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

7

Источник

Accepted Answer

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» на соответствующие даты начала и окончания (но используйте один и тот же формат, ГГГГ-ММ-ДД) —
```
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
```

ВСТАВЬТЕ НА ВАШУ таблицу данных на основе временной доли

   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`

55