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 дней. Я надеюсь, что теперь я более ясен.

59

mysql sql gaps-and-islands

задан Cœur 19 August 2017 в 13:41

2 ответа

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

14

ответ дан 24 November 2019 в 18:29

Другие вопросы по тегам:

mysql sql gaps-and-islands

Похожие вопросы:

score 55 · Accepted Answer

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

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

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`