Как сделать поиск ближайшей строки по индексу?

Question

[pqgg7nwkd4]

Добрый день.

Предположим есть такая таблица, которая отражает разные версии одного и того же объекта с течением времени:

CREATE TABLE foo (
  id INTEGER,
  updated DATE,
  PRIMARY KEY (id, updated)
)

-- Данные:
INSERT INTO foo (id, updated) VALUES
  (1, '2000-01-01'),  -- актуальная с 01 по 09 число
  (1, '2000-01-10'),  -- актуальная с 10 по 19 число
  (1, '2000-01-20'),  -- актуальная с 20 по 24 число
  (1, '2000-01-25'),  -- актуальная с 25 по 29 число
  (1, '2000-01-30')  -- актуальная с 30 и по сей день

Задача найти актуальную на дату :date_param строку с :id_param.

Не очень желательной реализацией можно считать такой запрос:

SELECT * FROM foo WHERE id = :id_param AND date <= :date_param  ORDER BY updated DESC LIMIT 1

Теоретически этот запрос должен выполнится на основе индекса, без извлечения и сортировки предварительного результата. Но всё же выглядит несколько громоздко. А если понадобится извлечь актуальные записи для нескольких значений id, то запрос становится еще более сложным: я даже не представляю каким (думаю можно сделать с помощью unnest или union).

Вопрос, есть ли возможность сделать такой поиск более элегантно? С возможностью поиска сразу нескольких "актуальных" строк.

Спасибо.

Answer 1

[xmoonlight]

1. Вариант 1: expr BETWEEN min AND max => тут
2. Вариант 2: Прослойка: добавляете дополнительную таблицу временных интервалов (dateRanges), в эту - доп.поле с ID записи из dateRanges. dateRanges - поддерживаете при добавлении новой записи: дата вышла из последнего интервала - создаёте следующий.

Если добавить оба варианта - это лучший оптимальный выбор.
Т.к. при "водопадном" спуске:
[Вариант_2]->[Вариант_1]->RESULT
Вы в разы уменьшите и время на поиск/выборку/сортировку, и требуемые выч. ресурсы.
Если нужен будет специфический запрос 1 раз - используем только 1-й вариант.
Если нужно повторить специфический запрос многократно - мы можем уменьшить зону поиска на основе таблицы DateRanges через набор временных интервалов.

Answer 2

[lega]

Если актуальная дата - текущий день, то можно в таблице хранить только активные, и перекладывать данные по шедуллеру.