Заполнение пропущенных дат в MySQL: сортировка и оценки

Пройдите тест, узнайте какой профессии подходите

Я предпочитаю

Работать самостоятельно и не зависеть от других

Работать в команде и рассчитывать на помощь коллег

Организовывать и контролировать процесс работы

Быстрый ответ

Для того чтобы заполнить отсутствующие даты, воспользуйтесь рекурсивным CTE, если у вас установлена версия MySQL 8.0 или более поздняя. Проделайте так:

SQL

Скопировать код

WITH RECURSIVE DateSeries AS (
  SELECT '2023-01-01' AS missing_date 
  UNION ALL
  SELECT DATE_ADD(missing_date, INTERVAL 1 DAY) FROM DateSeries WHERE missing_date < '2023-01-31'
)
SELECT * FROM DateSeries;

Замените '2023-01-01' на нужную начальную дату и '2023-01-31' на выбранную конечную дату, в результате чего вы полчите полный список дат без необходимости использования таблицы календаря. Это удобно и эффективно.

Кинга Идем в IT: пошаговый план для смены профессии

Как можно использовать сгенерированные даты?

Соедините полученный вами набор дат с вашими данными через LEFT JOIN следующим образом:

SQL

Скопировать код

SELECT DateSeries.missing_date, COALESCE(your_table.score, 0) AS score
FROM DateSeries
LEFT JOIN your_table ON DateSeries.missing_date = your_table.date;

Каждый дате будет соответствовать её балл, а если для каких-то дат данных не было, они получат нулевой балл.

А если у меня версии MySQL ниже 8.0?

Если вы используете версии MySQL ниже 8.0 без поддержки CTE, можно применить таблицу чисел:

SQL

Скопировать код

SELECT DATE_ADD('2023-01-01', INTERVAL seq_num DAY) AS date_series
FROM (
  SELECT a.i + b.i * 10 + c.i * 100 AS seq_num
  FROM 
    (SELECT 0 i 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) a,
    (SELECT 0 i 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) b,
    (SELECT 0 i 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) c
) numbers
WHERE seq_num <= DATEDIFF('2023-01-31', '2023-01-01')
ORDER BY seq_num;

После генерации используется результат в LEFT JOIN и COALESCE, где это потребуется.

Как поддерживать данные за последние 30 дней?

Если требуется поддерживать данные за последние 30 дней, следует использовать событие MySQL:

SQL

Скопировать код

CREATE EVENT purge_old_entries
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  DELETE FROM your_table WHERE date < CURRENT_DATE – INTERVAL 30 DAY;

Включите планировщик событий, чтобы избежать его простоя:

SQL

Скопировать код

SET GLOBAL event_scheduler = ON;

Это аналог регулярной весной уборки.

Визуализация

Можно представить заполнение пропущенных дат как дополнение к пазлу:

Markdown

Скопировать код

Исходный диапазон (📅): [1 января, 2 января, 4 января, 5 января]
Пропущенные даты (🔍): [3 января]

Этот SQL-запрос найдет и вставит пропущенные даты:

SQL

Скопировать код

SELECT a_date
FROM (
    SELECT '2023-01-01' + INTERVAL sequence.seq DAY AS a_date
    FROM (
        SELECT sequence FROM seq_0_to_999
        LIMIT DATEDIFF('2023-01-05', '2023-01-01') + 1
    ) sequence
) dates
WHERE a_date NOT IN ('2023-01-01', '2023-01-02', '2023-01-04', '2023-01-05');

Пазл завершен:

Markdown

Скопировать код

Полный диапазон (📅🧩): [1 января, 2 января, 3 января, 4 января, 5 января]

Даты успешно восстановлены.

А что если мне нужны другие диапазоны?

Для генерации других интервалов, например 15-минутных, попробуйте следующее:

SQL

Скопировать код

WITH RECURSIVE timeslot_series AS (
  SELECT CAST('2023-01-01 00:00:00' AS DATETIME) AS timeslot
  UNION ALL
  SELECT timeslot + INTERVAL 15 MINUTE FROM timeslot_series WHERE timeslot < '2023-01-01 23:45:00'
)
SELECT * FROM timeslot_series;

Таким образом, будут сгенерированы 15-минутные интервалы за день. Замените 15 MINUTE на любой другой интервал, который вам понадобится.

Преобразуем ваши данные в презентабельный вид

Отсортируйте данные по датам для более наглядного представления:

SQL

Скопировать код

SELECT missing_date, COALESCE(score, 0) AS score
FROM DateSeries
LEFT JOIN your_table ON DateSeries.missing_date = your_table.`date`
ORDER BY DateSeries.missing_date ASC;

Полезные материалы

MySQL :: Руководство по MySQL 8.0 :: 5.6 Примеры распространенных запросов — Официальная документация.
sql – generate days from date range – Stack Overflow — Обсуждение задачи и её решений на форуме разработчиков.
Not Acceptable! — Если вы столкнулись с ошибкой при отправке запроса, попробуйте еще раз.
Page not found | Brian Showalter — Ссылка на несуществующий ресурс.
sql – How to declare a variable in MySQL? – Stack Overflow — Информация о том, как объявить переменную в MySQL.

Свежие материалы

Оптимизация скорости загрузки сайта

6 сентября 2024

Методы HTTP: GET, POST и другие

6 сентября 2024

Вставка и редактирование изображений в Google Презентациях

6 сентября 2024