W tabeli SQL person_rate zapisaliśmy wartość zmiennoprzecinkową, która zmienia się w czasie. Kolumny:

id (serial, PK)
person_id (int)
date_from (date)
rate (float)

(person_id, date_from) jest wyjątkowy, ponieważ dozwolona jest najwyżej jedna zmiana dziennie (może to być PK, ale to nie jest ważne)

Wartość kursu dla danego person_id jest ważna w domenie czasu zdate_from data następnego rekordu z sukcesemdate_fromlub do nieskończonej przyszłości, jeśli nie ma takiego zapisu. Przykład:

id   person_id  date_from  rate
101  1          2011-01-01 100.0
145  1          2012-01-01 180.0
193  1          2012-05-01 140.0

Teraz potrzebuję zapytania SELECT, które dla każdegoperson_id zwraca arate ważne dla danej daty $. Powiedzmy, że 2012-03-01 to 180; na 2012-05-02 to 140 i tak.

Rozwiązania, które testowałem:

1) warunekdate_from <= $date + użyj funkcji oknarank() OVER (PARTITION BY person_id ORDER BY date_from DESC + w superselekcjiWHERE rank = 1

2) podobny do 1), ale użyjSELECT DISTINCT ON (person_id) zamiast ograniczać rangę do 1

Zarówno 1, jak i 2) nie działają dobrze, EXPLAIN pokazuje, że db musi sortować wszystkie rekordy dla każdegoperson_id a następnie najpierw ogranicz do 1. Prawdopodobnie ten typ zapytania nie może w pełni wykorzystywać indeksudate_from?

POMYSŁ - dodaćdate_to kolumna, która będzie trochę zbędna, ponieważ wartością będzie „date_from kolejnego rekordu, minus 1 dzień” (lub + infty, jeśli nie ma kolejnego rekordu). Ale zapytanie może być wtedydate_from <= $date AND date_to >= $date - który prawdopodobnie miałby dobrą wydajność z indeksami na date_from i date_to.

Ale trochę boję się, jak zarządzać integralnością danych w tym przypadku - jak skonfigurować ograniczenie, które [date_from .. date_to] dla jednego person_id nie powinno się nakładać?

Jakie jest najlepsze rozwiązanie dla postgresql dla tego typu zapytania? Wczytanie jest najbardziej czytelne, nie ma zbyt wielu zapisów w tabeli person_rate. Typowe zapytanie wymagałoby wewnętrznej stawki za każdy dzień miesiąca ...

Może toZapytanie SQL dla indeksu / klucza podstawowego porządkowe z nowymi indeksami na pg 9.2 może jakoś pomóc?