To bardzo zależy od okoliczności i dokładnych wymagań. Rozważ mój komentarz do pytania .
Proste rozwiązanie
Z DISTINCT ONw Postgres:
SELECT DISTINCT ON (i.good, i.the_date)
i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM inventory i
LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER BY i.good, i.the_date, p.the_date DESC;
Zamówiony wynik.
Lub ze NOT EXISTSstandardowym SQL (działa z każdym znanym RDBMS):
SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM inventory i
LEFT JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE NOT EXISTS (
SELECT 1 FROM price p1
WHERE p1.good = p.good
AND p1.the_date <= i.the_date
AND p1.the_date > p.the_date
);
Ten sam wynik, ale z dowolną kolejnością sortowania - chyba że dodasz ORDER BY.
W zależności od dystrybucji danych, dokładnych wymagań i wskaźników, jeden z nich może być szybszy.
Ogólnie rzecz biorąc, DISTINCT ONjest zwycięzcą, a na dodatek otrzymujesz posortowany wynik. Jednak w niektórych przypadkach inne techniki zapytań są (znacznie) szybsze. Patrz poniżej.
Rozwiązania z podzapytaniami do obliczania wartości maksymalnych / minimalnych są na ogół wolniejsze. Warianty z CTE są jednak ogólnie wolniejsze.
Zwykłe widoki (jak proponowana przez inną odpowiedź) wcale nie pomagają w wydajności w Postgres.
SQL Fiddle.
Właściwe rozwiązanie
Ciągi i zestawianie
Przede wszystkim cierpisz z powodu nieoptymalnego układu stołu. Może się to wydawać trywialne, ale normalizacja schematu może być bardzo trudna.
Sortowanie według typów znakowych ( text, varchar, ...) musi być wykonana zgodnie z lokalizacji - na zestawień w szczególności. Najprawdopodobniej twoja baza danych korzysta z lokalnego zestawu reguł (np. W moim przypadku de_AT.UTF-8:). Dowiedz się za pomocą:
SHOW lc_collate;
Powoduje to, że sortowanie i indeksowanie wyszukiwania jest wolniejsze . Im dłuższe są twoje łańcuchy (nazwy towarów), tym gorzej. Jeśli w rzeczywistości nie obchodzą Cię reguły sortowania (lub w ogóle porządek sortowania), może to być szybsze, jeśli dodasz COLLATE "C":
SELECT DISTINCT ON (i.good COLLATE "C", i.the_date)
i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM inventory i
LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER BY i.good COLLATE "C", i.the_date, p.the_date DESC;
Zwróć uwagę, jak dodałem zestawienie w dwóch miejscach.
Dwa razy szybciej w moim teście, każdy z 20 tys. Wierszy i bardzo podstawowymi nazwami („good123”).
Indeks
Jeśli zapytanie ma używać indeksu, kolumny z danymi znakowymi muszą używać zgodnego sortowania ( goodw przykładzie):
CREATE INDEX inventory_good_date_desc_collate_c_idx
ON price(good COLLATE "C", the_date DESC);
Przeczytaj dwa ostatnie rozdziały tej pokrewnej odpowiedzi na SO:
Możesz nawet mieć wiele indeksów z różnymi zestawieniami w tych samych kolumnach - jeśli potrzebujesz również towarów posortowanych według innego (lub domyślnego) zestawienia w innych zapytaniach.
Normalizować
Nadmiarowe ciągi (nazwa dobra) również nadmuchują tabele i indeksy, co czyni wszystko jeszcze wolniejszym. Przy prawidłowym układzie tabeli można na początku uniknąć większości problemów. Może wyglądać tak:
CREATE TABLE good (
good_id serial PRIMARY KEY
, good text NOT NULL
);
CREATE TABLE inventory (
good_id int REFERENCES good (good_id)
, the_date date NOT NULL
, quantity int NOT NULL
, PRIMARY KEY(good_id, the_date)
);
CREATE TABLE price (
good_id int REFERENCES good (good_id)
, the_date date NOT NULL
, price numeric NOT NULL
, PRIMARY KEY(good_id, the_date));
Klucze podstawowe automatycznie zapewniają (prawie) wszystkie potrzebne indeksy.
W zależności od brakujących szczegółów indeks wielokolumnowy włączony pricew kolejności malejącej w drugiej kolumnie może poprawić wydajność:
CREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);
Ponownie sortowanie musi pasować do zapytania (patrz wyżej).
W Postgresie 9.2 lub nowszym „indeksowanie indeksów” dla skanów tylko indeksowych może trochę pomóc - zwłaszcza jeśli tabele zawierają dodatkowe kolumny, dzięki czemu tabela jest znacznie większa niż indeks indeksujący.
Te wynikowe zapytania są znacznie szybsze:
NIE ISTNIEJE
SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM inventory i
JOIN good g USING (good_id)
LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
AND NOT EXISTS (
SELECT 1 FROM price p1
WHERE p1.good_id = p.good_id
AND p1.the_date <= i.the_date
AND p1.the_date > p.the_date
);
WYRÓŻNIJ WŁ
SELECT DISTINCT ON (i.the_date)
i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM inventory i
JOIN good g USING (good_id)
LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
ORDER BY i.the_date, p.the_date DESC;
SQL Fiddle.
Szybsze rozwiązania
Jeśli to nadal nie jest wystarczająco szybkie, mogą istnieć szybsze rozwiązania.
Rekurencyjne JOIN LATERALpodkwerendy CTE / /
Zwłaszcza w przypadku dystrybucji danych z wieloma cenami za towar :
Widok zmaterializowany
Jeśli musisz uruchamiać to często i szybko, sugeruję utworzenie zmaterializowanego widoku. Myślę, że można bezpiecznie założyć, że ceny i zapasy z poprzednich dat rzadko się zmieniają. Oblicz wynik raz i zapisz migawkę jako zmaterializowany widok.
Postgres 9.3+ ma zautomatyzowaną obsługę zmaterializowanych widoków. Możesz łatwo wdrożyć wersję podstawową w starszych wersjach.
