@HiDiv
fullstack разработчик (php, js, html, css, vuejs)

Как лучше сформулировать sql-запрос для поиска по историческим данным?

Есть две таблицы. Основная (id-GUID, date_create - datetime, value - varchar) и с данными истории/аудита (id - guid, date_created - datetime, parent_id - GUID, before_value - varchar, after_value - varchar). Допустим индексы есть по id и parent_id, но если нужно, то можно добавить любые.

Изначально создается запись в основной таблице. Генерируется случайный GUID, фиксируется текущая дата+время и собственно некоторое значение, включая null. Когда запись в основной таблице изменяется (и только тогда!), в основной таблице просто меняется value плюс создается запись в таблице истории со случайным GUID, текущем датой+временем, parent_id ссылка на id в основной, before_value равно value, которое было, а after_value равно value, которое стало.

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

Пример упрощенный, но суть изменить нельзя!

Задача, получить срез значений value из основной таблицы на произвольную дату. Если на отчетную дату запись в основной таблице еще не была создана (date_create больше отчетной даты), то такая запись в результат не включается. Если запись в основной таблице ни разу не изменялась, то вывести текущее значение value. Если были изменения, то из таблицы истории вывести значение, которое действовало на отчетную дату.

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

Я сам уже написал "лобовое решение" для данной задачи, но для одного value оно выполняется от 2 до 10 сек, а для полной выгрузки около 3 минут, что слишком долго...

Можно предлагать создать отдельные view для упрощения. Хранимых процедур желательно избежать, но в крайнем случае можно использовать и их. Сервер MySQL 5.7, БД InnoDB.
  • Вопрос задан
  • 159 просмотров
Пригласить эксперта
Ответы на вопрос 1
@rPman
Так как тебе варианты с готовыми решениями подсказали, предлагаю еще вариант - считай промежуточный итог сам, в большинстве случаев всю историю даже хранить не придется, только за последний период, за который накапливаются текущие значения

Большинство функций группировки такие как sum, min, max,.. равны точно такой же функции от этой функции за все промежуточные периоды, грубо говоря считаешь ежемесячный min, сохраняешь их в табличке ежемесячных итогов, а затем чтобы получить за весь период min достаточно брать min от этих сохраненных min значений.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы