Trzecia kawa Szkolenie Scrum

Gdzie są moje daty?

2010Dziś odbiegając od poprzednich wpisów dotyczących sfery zarządzania w obszarze IT, zajmiemy się bardzo konkretnym problemem, na który można trafić podczas pracy z relacyjnymi bazami danych.

Podczas implementacji raportów jednym z często spotykanych typów raportu, z którymi przyjdzie nam praocować jest szereg czasowy. Upraszczając chodzi o takie zestawienia, w których obserwujemy zdarzenia w dobrze określonych odstępach czasu. Niestety zazwyczaj dane jakimi dysponujemy rejestrują tylko czas wystąpienia zdarzenia, w którym cokolwiek zaobserwowano.

Problem

Dla przykładu rozpatrzmy poniższe zestawienie sprzedaży sztuk towaru określonej kategorii (tabela faktów):

SPRZEDAŻ
Tabela 1. SPRZEDAZ

Wyobraźmy sobie, że naszego klienta interesuje raport, który będzie pokazywał podsumowanie sprzedaży w PLN za okres od  2013-08-01 do 2013-08-15. Poniżej przedstawiono tabelę z cenami (tabela słownikowa):

PRODUKT_CENA
Tabela 2. PRODUKT_CENA

Odpowiednie złączenie wraz z agregacją prowadzi do tabeli pośredniej (tabela agregacyjna):

SPRZEDAŻ_WARTOŚĆ
Tabela 3. SPRZEDAZ_WARTOSC

W PL/SQL zapytanie dla tabeli pośredniej będzie wyglądało następująco:

 

Wszystko byłoby dobrze, gdyby nie fakt, że nie posiadamy pełnego zakresu dat.  Możliwym podejsciem jest dodatkowe złączenie zewnętrzne tabeli pośredniej z tabelą zawierającą interesujący zakres dat. Skąd w takim razie wziąć brakujące daty?

Rozwiązanie

Daty trzeba sobie wygenerować.  W Oracle możemy posłużyć się zapytaniem hierarchicznymi (CONNECT BY PRIOR). Składnia takiego zapytania wygląda następująco:

 

, gdzie :1 i :2 to zmienne wiązane będące parametrem

Ostatecznie zapytanie w PL/SQL zwracające interesujące nas wyniki będzie miało postać:

 

Generowanie listy dat w T-SQL (MS-SQL) wygląda również dosyć elegancko:

 

Poniżej linki z propozycjami rozwiązania problemu generowania zakresu dat w MySQL oraz PosgreSQL.

PosgreSQL:

W PostgreSQL by uzyskać podobny efekt używa się funkcji generate_series, która pozwala ustalić interesujący nas interwał.

 

http://stackoverflow.com/questions/4002029/how-to-generate-a-virtual-table-to-generate-a-sequence-of-dates-in-postgresql

MySQL:

http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates

Podsumowanie

Powyżej udało się przedstawić proste rozwiązanie bardzo często spotykanego problemu braku istnienia obserwacji, który ujawnia się przy budowaniu raportów opartych o szereg czasowy dla chyba najbardziej popularnych silników relacyjnych baz danych. Zaskoczeniem jest bardzo prosty sposób generowania wszelkich zakresów w PostgreSQL. Rozwiązania dla wielkich komercyjnych baz danych wyglądają dosyć elegancko i są proste, niemniej jednak nadal pozostawiają pewne braki.  Czy można łatwo  zamknąć zapytanie w widoku, tak by nie istniała konieczność jego modyfikacji?  Czy istnieje  homogeniczne rozwiązanie spójne dla dowolnej relacyjnej bazy danych wspierającej standardy, w końcu język SQL jest standardem… podobno ;-)

2 komentarze

  1. Maciej Wakuła napisał(a):

    Warto zauważyć, że takie rozwiązanie bardzo obciąża bazę i prowadzi do tworzenia dużych kolekcji w pamięci. Sprzedajemy przecież nie tylko gwoździe i szpilki ale także śruby, nakrętki, … To „normalny” problem z iloczynem kartezjańskim zbiorów.

    Z n wierszy wejściowych generujemy do n*<okres analizy> wierszy w pamięci.

    Zamiast obciążać serwer generując z małej ilości danych wejściowych dużą kolekcję wyjściową… można obejść logikę naokoło, wygenerować minimum danych na serwerze, ściągnąć je do maszyny klienta (rozmiar danych jest mały więc zadziała to szybko i nie obciąży sieci) i już w aplikacji klienckiej wygenerować pożądane wyniki.

  2. Łukasz Walkiewicz napisał(a):

    Wydaje mi sie, że to co warto użyć zależy od konkretnego problemu i sposobu użycia bazy danych.
    Jeżeli mamy do czynienia z systemem raportowym typu OLAP generującym raporty w określonych cyklach, a nie mamy do czynienia z aplikacją instalowaną u klienta typu OLTP, to może nie warto przenosić logiki z silnika bazy danych do części klienckiej, a obciążenie bazy też nie będzie problemem, bo zapytań do silnika raportowego i klientów jest mniej niż przy systemie transakcyjnym.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *