Pchełki SQL, odcinek 6: pułapka na kursie

https://xpil.eu/Zca7d 📋

Trafiła mi się ostatnio w pracy krótka acz treściwa historia związana z kursami walut. Wprost idealny materiał na pchełkę.

Ponieważ - jak to zwykle bywa - w warunkach "bojowych" dane są dużo bardziej skomplikowane niż mówi teoria, zamiast wrzucać tutaj swój "brudny" kod z prawdziwego systemu, zbuduję od zera coś bardziej strawnego.

A ossso właśśśsiwie choźźzi? - zapyta lekko już ziewający Czytelnik.

Chodzi o kursy walut, a konkretnie o ich reprezentację w systemie bazodanowym.

Sprawa jest w zasadzie dość prosta; cztery kolumny: data kursu, waluta oryginalna, waluta po przeliczeniu, kurs. Do tego czasem dochodzi jeszcze kolumna z rodzajem kursu (bankowy, firmowy, kantorowy etc.) ale ten element sobie dziś odpuścimy dla uproszczenia. Czasem bywa też, że oprócz kursów dziennych w tabeli lądują również średnie kursy miesięczne (czy nawet roczne), ale ten temat też sobie darujemy.

No więc tak: mamy uproszczoną tabelę kursów walut:

CREATE TABLE FOREX_RATE (
  FROM_CUR CHAR(3),
  TO_CUR CHAR(3),
  DT_CONV TIMESTAMP(0),
  CONV_RATE NUMBER(38, 10)
);

FROM_CUR to waluta oryginalna, TO_CUR - docelowa, DT_CONV to data kursu a CONV_RATE to kurs wymiany. Proste jak konstrukcja gwoździa. Jeżeli ktoś się zastanawia po co nam 10 znaków po przecinku dziesiętnym w kursie wymiany, spieszę wyjaśnić, że niektóre waluty podlegają ogromnym fluktuacjom (galopująca inflacja itd), w związku z czym trzeba być przygotowanym na różne scenariusze.

W następnej kolejności trzeba nakarmić naszą tabelę danymi. Ten krok (niestety!) pominę, ponieważ danych jest mnóstwo - proszę sobie szybciutko policzyć: 150 walut na przestrzeni, dajmy na to, 10 lat, daje nam 150*149*2*10*365=163155000 rekordów (ponad 163 miliony rekordów). Skąd taki wzór na ilość rekordów? Ano, skoro walut jest 150, to trzeba uwzględnić każdą parę z tych 150 ze wszystkimi pozostałymi (149), w obie strony (a więc zarówno EUR -> PLN jak i PLN -> EUR), czyli mnożymy przez dwa, przez 10 lat, 365 dni w każdym roku (ignorujemy lata przestępne).

W warunkach rzeczywistych kursy wymiany są na ogół ograniczone do walut, w których dana firma handluje, a więc faktyczna ilość wpisów w tabeli kursów jest dużo mniejsza, ale prawie zawsze idzie w miliony. Nie zamierzam tutaj generować milionów rekordów, bo zamiast pchełki dostałbym jakiegoś mamuta. Jak ktoś chce, niech sobie we własnym zakresie pokombinuje, tymczasem zobaczmy co dalej.

Załóżmy, że mamy drugą tabelę, z transakcjami sprzedaży. A więc - w uproszczeniu - id transakcji, id produktu, id sprzedawcy, sklepu, kasy, data / godzina, kraj, waluta i kwota.

Ograniczymy się - dla jeszcze większego uproszczenia - do zaledwie pięciu kolumn: id transakcji, czas transakcji, id produktu, waluta, kwota:

CREATE TABLE SALES (
  TXN_ID NUMBER(38, 0),
  TXN_DT TIMESTAMP(9),
  PRD_ID NUMBER(38, 0),
  CUR_CD CHAR(3),
  TXN_AMT NUMBER(38, 2)
);

Teraz docieramy do samego gęstego: załóżmy, że mamy mnóstwo danych w tabeli SALES, z różnych krajów, i chcemy zbudować zapytanie pokazujące nam szczegóły sprzedaży przeliczone na dolary, według kursu z dnia transakcji. A więc wszystkie rekordy z tabeli SALES plus dodatkowa kolumna TXN_AMT_USD z wartością transakcji w dolarach.

SELECT
  TXN_ID,
  TXN_DT,
  PRD_ID,
  CUR_CD,
  TXN_AMT,
  TXN_AMT * CONV_RATE AS TXN_AMT_USD
FROM SALES S
LEFT OUTER JOIN FOREX_RATE FX
  ON FX.FROM_CUR = S.CUR_CD
  AND FX.TO_CUR = 'USD'
  AND TRUNC(S.TXN_DT) = TRUNC(DT_CONV)

Zadziała?

No właśnie, jak pokazuje moje niedawne doświadczenie, zadziała dla walut innych niż USD 🙂 Ponieważ tabela kursów jest tak skonstruowana, że nie zawiera kursu wymiany USD -> USD (ani żadnej innej waluty na samą siebie), wszystkie rekordy transakcji z tabeli SALES, dla których walutą jest USD, nie znajdą kursu wymiany, w związku z czym LEFT OUTER JOIN zwróci NULL i tym samym TXN_AMT_USD będzie również NULL.

Rozwiązanie?

Albo dla każdej pary walut, każdego dnia, dodać jeden rekord do przeliczania waluty na samą siebie (z kursem 1) - to skomplikuje nieco proces ładowania danych o kursach wymiany, za to znacznie uprości późniejsze przeliczanie walut - albo z kolei zadrutować problem po stronie raportu:

SELECT
  TXN_ID,
  TXN_DT,
  PRD_ID,
  CUR_CD,
  TXN_AMT,
  CASE CUR_CD
    WHEN 'USD' THEN TXN_AMT
    ELSE TXN_AMT * CONV_RATE END
  AS TXN_AMT_USD
FROM SALES S
LEFT OUTER JOIN FOREX_RATE FX
  ON FX.FROM_CUR = S.CUR_CD
  AND FX.TO_CUR = 'USD'
  AND TRUNC(S.TXN_DT) = TRUNC(DT_CONV)

Na ogół zaczyna się od zastosowania podejścia numer dwa (drutujemy!), aby po pewnym czasie dojść do wniosku, że podejście numer jeden (poprawiamy tabelę z kursami) daje więcej elastyczności i spokoju ducha. Zwłaszcza przy dużej ilości raportów opartych na przeliczaniu walut. Zamiast kodować USD "na twardo" w zapytaniu, dajemy sobie możliwość zapuszczenia raportu dla dowolnej waluty "docelowej" (nie tylko USD).

Czujny Czytelnik (znam co najmniej jednego takiego) zaraz zawrzaśnie, że brakuje mi indeksów, że dane mam nie popartycjonowane i że Wogle. Uprzedzam więc marudę: miała być pchełka, wyszło pchliszcze, nie zamierzam już dziś dodawać ani grama kodu 🙂

Kod powyższy pisałem w całości z palucha (nie testowałem), a ponieważ errare xpilum est, może on zawierać błędy. Jeżeli, Czytelniku, pracujesz w banku bądź na giełdzie, nie próbuj potem pozywać mnie o odpowiedzialność za krach.

https://xpil.eu/Zca7d 📋