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

Источник

7 ответов

Решение

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. СЛЕДУЮЩИЙ ПРИСОЕДИНЯЙТЕСЬ к своей таблице данных, основанной на временной части:

       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`
Источник

Прошло время с тех пор, как был задан этот вопрос. MySQL 8.0 был выпущен в 2018 году и добавил поддержку рекурсивных общих табличных выражений, которые обеспечивают элегантный и современный способ решения этого вопроса.

Следующий запрос можно использовать для создания списка дат, скажем, первых 15 дней августа 2010 года:

with recursive all_dates(dt) as (
    -- anchor
    select '2010-08-01' dt
        union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt + interval 1 day <= '2010-08-15'
)
select * from all_dates

Тогда ты можешь left join этот набор результатов с вашей таблицей, чтобы сгенерировать ожидаемый результат:

with recursive all_dates(dt) as (
    -- anchor
    select '2010-08-01' dt
        union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt + interval 1 day <= '2010-08-15'
)
select d.dt date, coalesce(t.score, 0) score
from all_dates d
left join mytable t on t.date = d.dt
order by d.dt

Демо на DB Fiddle:

дата | Гол:--------- | ----:
2010-08-01 | 19
2010-08-02 | 21 год
2010-08-03 | 0
2010-08-04 | 14
2010-08-05 | 0
2010-08-06 | 0
2010-08-07 | 10
2010-08-08 | 0
2010-08-09 | 0
2010-08-10 | 14
2010-08-11 | 0
2010-08-12 | 0
2010-08-13 | 0
2010-08-14 | 0
2010-08-15 | 0
Источник

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

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

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

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

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

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

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

Источник

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

Источник

Ответ Майкла Конарда великолепен, но мне нужны были 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

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

Источник

вы можете использовать прямо с даты начала до сегодняшнего дня со вставкой

              with recursive all_dates(dt) as (
        -- anchor
        select '2021-01-01' dt
            union all 
        -- recursion with stop condition
        INSERT IGNORE  INTO mytable (date,score) VALUES (dt + interval 1 day ,0 )  where dt + interval 1 day <= curdate()
    )
    select * from all_dates
Источник

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

      select c.calendar_date,fact.column3,fact.column4,fact.column5 
from calendar c 
join (SELECT min(your_date) as mindt, max(your_date) as maxdt,column3,column4,column5
from your_table         
group by 3,4,5
) fact
on c.calendar_date between fact.mindt and fact.maxdt
Источник
Другие вопросы по тегам