Skuteczne porównywanie cen w różnych walutach

Chcę umożliwić użytkownikom wyszukiwanie produktów w przedziale cenowym. Użytkownik powinien mieć możliwość korzystania z dowolnej waluty (USD, EUR, GBP, JPY, ...), bez względu na walutę ustawioną przez produkt. Cena produktu wynosi 200 USD, a jeśli użytkownik wyszuka produkty kosztujące 100 EUR - 200 EUR, nadal może go znaleźć. Jak zrobić to szybko i skutecznie?

Oto, co zrobiłem do tej pory. Przechowuję price, currency codea calculated_priceto jest cena w euro (EUR), która jest domyślną walutą.

CREATE TABLE "products" (
  "id" serial,
  "price" numeric NOT NULL,
  "currency" char(3),
  "calculated_price" numeric NOT NULL,
  CONSTRAINT "products_id_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL,
  "modified" timestamp NOT NULL,
  "is_default" boolean NOT NULL DEFAULT 'f',
  "value" numeric NOT NULL,       -- ratio additional to the default currency
  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

INSERT INTO "currencies" (id, modified, is_default, value)
  VALUES
  ('EUR', '2012-05-17 11:38:45', 't', 1.0),
  ('USD', '2012-05-17 11:38:45', 'f', '1.2724'),
  ('GBP', '2012-05-17 11:38:45', 'f', '0.8005');

INSERT INTO "products" (price, currency, calculated_price)
  SELECT 200.0 AS price, 'USD' AS currency, (200.0 / value) AS calculated_price
    FROM "currencies" WHERE id = 'USD';

Jeśli użytkownik szuka innej waluty, powiedzmy USD, obliczamy cenę w EUR i przeszukujemy calculated_pricekolumnę.

SELECT * FROM "products" WHERE calculated_price > 100.0 AND calculated_price < 200.0;

W ten sposób możemy bardzo szybko porównywać ceny, ponieważ nie musimy obliczać rzeczywistej ceny dla każdego wiersza, ponieważ jest ona obliczana raz.

Złą rzeczą jest to, że przynajmniej codziennie musimy ponownie obliczyć default_pricewszystkie wiersze, ponieważ zmieniono kursy walut.

Czy istnieje lepszy sposób, aby sobie z tym poradzić?

Czy nie ma innego sprytnego rozwiązania? Może jakiś wzór matematyczny? Mam pojęcie, że calculated_pricejest to stosunek do jakiejś zmiennej, Xa kiedy zmienia się waluta, aktualizujemy tylko tę zmienną X, a nie tę calculated_price, więc nawet nie musimy niczego aktualizować (wiersze) ... Może jakiś matematyk może to rozwiązać lubię to?

Oto inne podejście, dla którego ponowne obliczenie calculated_pricejest tylko optymalizacją, a nie koniecznością.

Załóżmy, że w currenciestabelach dodajesz kolejną kolumnę, last_ratektóra zawiera kurs wymiany z calculated_priceostatniej aktualizacji, bez względu na to, kiedy to się stało.

Aby szybko pobrać zestaw produktów z ceną między, powiedzmy, 50 USD a 100 USD, które zawierają pożądane wyniki, możesz zrobić coś takiego:

  SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))

gdzie :last_ratezawiera kurs EUR / USD w czasie ostatniej aktualizacji. Chodzi o zwiększenie interwału, aby uwzględnić maksymalne zróżnicowanie każdej waluty. Współczynniki wzrostu dla obu końców przedziału są stałe między aktualizacjami stawek, więc można je wstępnie obliczyć.

Ponieważ stawki zmieniają się tylko nieznacznie w krótkich okresach czasu, powyższe zapytanie może dać dokładne przybliżenie ostatecznego wyniku. Aby uzyskać końcowy wynik, odfiltrujmy produkty, dla których ceny spadły poza granice z powodu zmian stawek od ostatniej aktualizacji calculated_price:

  WITH p AS (
   SELECT * FROM products
   WHERE calculated_price > 50.0/(:last_rate*
    (SELECT coalesce(max(value/last_rate),1) FROM currencies
      WHERE value>last_rate))
   AND calculated_price < 100.0/ (:last_rate*
    (SELECT coalesce(min(value/last_rate),1) FROM currencies
      WHERE value<last_rate))
  )
  SELECT price,c.value FROM p join currencies c on (p.currency=c.id)
     WHERE price/c.value>50/:current_rate
       AND price/c.value<100/:current_rate;

gdzie :current_ratejest bardziej aktualna stawka w EUR za pieniądze wybrane przez użytkownika.

Efektywność wynika z faktu, że zakres stawek ma być mały, a wartości są zbliżone do siebie.

To brzmi jak praca dla zmaterializowanego widoku. Chociaż PostgreSQL nie obsługuje ich wprost, możesz tworzyć i utrzymywać zmaterializowane widoki za pomocą funkcji i wyzwalaczy w normalnych tabelach.

Ja bym:

• Utwórz nowy stół, powiedzmy products_summary, ze schematem bieżącego productsstołu;
• ALTER TABLE products DROP COLUMN calculated_pricepozbyć się calculated_pricekolumnyproducts
• Napisz, że produkuje wyjście chcesz na products_summaryprzez SELECTing od productsi JOINing na currencies. Nazwałbym to, products_summary_dynamicale nazwa zależy od ciebie. Jeśli chcesz, możesz użyć funkcji zamiast widoku.
• Okresowo odświeżaj zmaterializowaną tabelę widoków za products_summarypomocą products_summary_dynamicprzy pomocy BEGIN; TRUNCATE products_summary; INSERT INTO products_summary SELECT * FROM products_summary_dynamic; COMMIT;.
• Utwórz AFTER INSERT OR UPDATE OR DELETE ON productswyzwalacz, który uruchamia procedurę wyzwalacza, aby zachować products_summarytabelę, usuwając wiersze po usunięciu z products, dodając je po dodaniu do products(poprzez SELECTwejście z products_summary_dynamicwidoku) i aktualizując je, gdy zmieniają się szczegóły produktu.

To podejście zastosuje wyłączną blokadę products_summarypodczas TRUNCATE ..; INSERT ...;transakcji, która aktualizuje tabelę podsumowań. Jeśli powoduje to przeciągnięcie w aplikacji, ponieważ trwa to tak długo, możesz zamiast tego zachować dwie wersje products_summarytabeli. Zaktualizuj ten, który nie jest używany, a następnie w transakcjiALTER TABLE products_summary RENAME TO products_summary_old; ALTER TABLE products_summary_new RENAME TO products_summary;

Alternatywnym, ale bardzo podejrzanym podejściem byłoby użycie indeksu wyrażeń. Ponieważ aktualizacja tabeli walut przy użyciu tego podejścia najprawdopodobniej będzie wymagała blokady podczas DROP INDEXa CREATE INDEXja nie robiłbym tego zbyt często - ale może być odpowiedni w niektórych sytuacjach.

Chodzi o to, by zawrzeć przelicznik walut w IMMUTABLEfunkcji. Ponieważ IMMUTABLEgwarantujesz silnikowi bazy danych, że wartość zwracana dla dowolnych argumentów zawsze będzie taka sama i że możesz robić różne szalone rzeczy, jeśli wartość zwracana jest inna. Wywołać funkcję, powiedzmy to_euros(amount numeric, currency char(3)) returns numeric. Wdróż to, jak chcesz; duże CASEzestawienie według waluty, tabela odnośników, cokolwiek. W przypadku korzystania z tabeli odnośników nigdy nie wolno jej zmieniać, chyba że opisano poniżej .

Utwórz indeks wyrażeń, na productsprzykład:

CREATE INDEX products_calculated_price_idx
ON products( to_euros(price,currency) );

Możesz teraz szybko wyszukiwać produkty według obliczonej ceny, np .:

SELECT *
FROM products
WHERE to_euros(price,currency) BETWEEN $1 and $2;

Problemem jest teraz sposób aktualizacji tabel walut. Sztuczka polega na tym, że możesz zmienić tabele walut, wystarczy upuścić i ponownie utworzyć indeks, aby to zrobić.

BEGIN;

-- An exclusive lock will be held from here until commit:
DROP INDEX products_calculated_price_idx;
DROP FUNCTION to_euros(amount numeric, currency char(3)) CASCADE;

-- It's probably better to use a big CASE statement here
-- rather than selecting from the `currencies` table as shown.
-- You could dynamically regenerate the function with PL/PgSQL
-- `EXECUTE` if you really wanted.
--
CREATE FUNCTION to_euros(amount numeric, currency char(3))
RETURNS numeric LANGUAGE sql AS $$
SELECT $1 / value FROM currencies WHERE id = $2;
$$ IMMUTABLE;

-- This may take some time and will run with the exclusive lock
-- held.
CREATE INDEX products_calculated_price_idx
ON products( to_euros(price,currency) );

COMMIT;

Upuszczam i ponownie definiuję funkcję powyżej tylko po to, aby podkreślić, że musisz porzucić wszystko , co korzysta z tej funkcji, jeśli ponownie zdefiniujesz funkcję niezmienną. CASCADENajlepszym sposobem na to jest użycie kropli.

Podejrzewam, że lepsze jest podejście zmaterializowane. Jest to z pewnością bezpieczniejsze. Włączam ten głównie do kopnięć.

Wpadłem na własny pomysł. Powiedz mi, czy to naprawdę zadziała, proszę!

Problem.

Po dodaniu produktu do productstabeli cena jest konwertowana na domyślną walutę (EUR) i zapisywana w calculated_pricekolumnie.

Chcemy, aby użytkownik mógł wyszukiwać (filtrować) ceny dowolnej waluty. Odbywa się to poprzez konwersję ceny wejściowej na domyślną walutę (EUR) i porównanie jej z calculated_pricekolumną.

Musimy zaktualizować kursy walut, aby użytkownicy mogli wyszukiwać według świeżego kursu walut. Problem polega jednak na tym - jak calculated_priceskutecznie aktualizować .

Rozwiązanie (mam nadzieję).

Jak calculated_priceskutecznie aktualizować .

Nie! :)

Chodzi o to, że bierzemy wczorajsze kursy walut ( wszystkie z tego samego dnia ), calculated_priceużywając tylko tych. Jak zawsze! Brak codziennych aktualizacji. Jedyne, czego potrzebujemy, zanim porównamy / przefiltrujemy / przeszukamy ceny, to przyjąć dzisiejsze kursy walut, tak jak były wczoraj.

Tak więc, calculated_priceużyjemy tylko kursu waluty z ustalonej daty (wybraliśmy, powiedzmy, wczoraj). Będziemy potrzebować przeliczyć dzisiejszą cenę na cenę wczorajszą. Innymi słowy, weź dzisiejszy kurs i przelicz go na wczorajszy kurs:

cash_in_euros * ( rate_newest / rate_fixed )

A to jest tabela walut:

CREATE TABLE "currencies" (
  "id" char(3) NOT NULL, -- currency code (EUR, USD, GBP, ...)
  "is_default" boolean NOT NULL DEFAULT 'f',

  -- Set once. If you update, update all database fields that depends on this.
  "rate_fixed" numeric NOT NULL, -- Currency rate against default currency
  "rate_fixed_updated" timestamp NOT NULL,

  -- Update as frequently as needed.
  "rate_newest" numeric NOT NULL, -- Currency rate against default currency
  "rate_newest_updated" timestamp NOT NULL,

  CONSTRAINT "currencies_id_pkey" PRIMARY KEY ("id")
);

W ten sposób można dodać produkt, który kosztuje 200 USD, oraz sposób calculated_priceobliczenia ceny: od USD do najnowszej stawki EUR i do stałej (starej) stawki

INSERT INTO "products" (price, currency, calculated_price)
  SELECT
  200.0 AS price,
  'USD' AS currency,

  ((200.0 / rate_newest) * (rate_newest / rate_fixed)) AS calculated_price

    FROM "currencies" WHERE id = 'USD';

Można to również wstępnie obliczyć po stronie klienta i to właśnie zamierzam zrobić - obliczyć cenę wejściową użytkownika na calculated_pricezgodną wartość przed wykonaniem zapytania, aby użyć starego dobregoSELECT * FROM products WHERE calculated_price > 100.0 AND calculated_price < 200.0;

Wniosek.

Ten pomysł przyszedł do mnie zaledwie kilka godzin temu i obecnie proszę o sprawdzenie, czy mam rację co do tego rozwiązania. Co myślisz? Czy to zadziała? Czy myliłem się?

Mam nadzieję, że to wszystko rozumiesz. Nie jestem rodzimym językiem angielskim, jest też późno i jestem zmęczony. :)

AKTUALIZACJA

Wygląda na to, że rozwiązuje jeden problem, ale wprowadza inny. Szkoda :)