SQL dla zielonych i żółtych

RANK i DENSE_RANK, co trafi na podium...

Przychodzi audyt wewnętrzny, zewnętrzny lub inna zaraza… i prosi o dokumentację dla top 10 umów leasingowych plus tabela z informacjami o tych umowach. Inny przykładowy problem do rozwiązania to np. wskaż 10 najlepszych sprzedawców, tu może się okazać, iż w pierwszym kroku powinniśmy w podzapytaniu uzyskać sprzedaż agregowaną na sprzedawcę, następnie na tym operować.

Są dwie funkcje:

RANK () OVER (order by …. )

DENSE_RANK () OVER (order by ….)

OVER (order by ….) są to obligatoryjne elementy użycia ww. funkcji, po tak jak w LAG I LEAD. Jak ma być ranking, to po prostu musimy wskazać według, której kolumny ułożyć dane, bo pamiętamy, iż w bazie dane nie są uporządkowane.

RANK nadaje rankingi/podium w ten sposób, iż liczba osobników/umów etc. jest zgodna z liczbą stopni podium. To znaczy, że jak np. są dwa jednakowe najlepsze wyniki, to wówczas są dwa pierwsze miejsca, drugiego już nie ma, następne to już trzecie miejsce.

DENSE_RANK, tu każde miejsce podium jest, to że na pierwszym czy innym miejscu jest więcej niż jeden osobnik/umowa nie ma znaczenia. Ta funkcja każdy stopień podium określa i najwyżej umieszcza na stopniu więcej niż jedną sztukę.

Innymi słowy na początku zadaj sobie pytanie, czy chcesz 10 umów największych i kropka, wówczas RANK. Jeżeli jednak podium ma mieć 10 stopni a umów może być więcej niż 10, wówczas DENSE_RANK, ale czy warto więcej dokumentacji dostarczać audytorowi? Jak dostanie mniej, to mniejsze prawdopodobieństwo, że w materiałach jest błąd.

Jak filtrować z wykorzystanie wartości wyprodukowanych przez użycie funkcji RANK lub DENSE_RANK?

Nie da się od razu, trzeba zastosować CTE (użyć klauzulę WITH na początku) i z takiej predefiniowanej tabeli wybrać kolekcję wpisując odpowiedni warunek po WHERE lub wybrać z podzapytania i też filtrujemy po WHERE. Obie metody dobre.

Jak wyglądają kwerendy to na obrazku i kod pod linką.

Sztuczka z podzapytaniem, jak policzyć LtV

W tabeli UMOWY_4 jest podstawowa informacja o umowach, przede wszystkim id_kontrakt oraz kwota_umowy. Wszelkie dane o zabezpieczeniach są w tabeli ZABEZPIECZENIA. To co łączy ww. tabele to id_kontrakt.

Należy policzyć LtV, czyli relację kwoty umowy do wartości zabezpieczeń. To na początku widać, że trzeba złączyć tabele, ponieważ są umowy, mogą być umowy bez zabezpieczeń, to musi to być LEFT OUTER JOIN.

Samo złączenie ww. tabel powoduje zwielokrotnienie liczby wierszy, ale LtV nie da się policzyć. Problemem jest to, że dla jednej umowy nie ma tylko jednego wiersza z tabeli ZABEZPIECZENIA. Musi nastąpić agregacja danych. Tu problem rozwiązuje zastosowanie podzapytania, przy okazji decydujemy, które zabezpieczenia bierzemy do liczenia LtV.

I to właśnie zrobiliśmy Marcinie :-)

Do poćwiczenia i obrazki są tutaj.

Trigger czyli wyzwalacz

Trigger czyli wyzwalacz. Tak, jest coś takiego, to już rozszerzenie SQL. Występuje na pewno na MS SQL Serwerze oraz na bazach ORACLE. Ponieważ opisuję triki, które sam stosowałem, to w zakresie rozszerzenia SQL szału nie ma, ale jakieś triggery stosowałem…

Zatem sytuacja wygląda następująco: co roku zbieramy dane o przychodzie i dochodzie klienta. Jest wypełniany formularz w EXCEL i przekazywanych do centrali. Wymyśliliśmy to tak, że każdy z formularzy miał wbudowaną formułę konstruującą odpowiednie polecenie INSERT…, którego wykonanie powodowało dopisanie danych do bazy. To, że tak powiem była pierwsza generacja, w kolejnej już mieliśmy postawioną osobna bazę i podpięty formularz internetowy do wprowadzania danych.

I powstało pytanie, czy nie zapiszemy danych, które już mamy, za rok który już mamy. Projektowanie komunikacji w dwie strony dla wpisującej osoby nie wchodziło w grę, to kosztowne i wymagające zachodu podejście, po prostu bez sensu, jak na realizowany cel: bieranie danych. No to zapisywaliśmy jak leci, co tylko do nas dotarło, do tabeli na bazie: KLIENT_WYNIKI.

Na tej tabelce został zbudowany, założony trigger, tu nazywam go: DODAJ_DATE_ZAPISU. Trigger uruchamia się po każdym insercie do tabelki KLIENT_WYNIKI.

Trigger powoduje, iż do drugiej tabelki o nazwie KLIENT_WYNIKI_TOTAL wykonuje się insert/załadowanie danych tych samych co zainsertowanych ostatnio do KLIENT_WYNIKI, cały wiersz w całości insertowany plus uzupełniana jest dodatkowa kolumna zawierające informację o dacie i czasie ładowania, tu wykorzystywana jest GETDATE().

Przyjmujemy teraz, że dane załadowane najmłodsze są najlepsze, dlatego widok, który używamy do celów raportowych wybiera dla każdego klienta ostatnio ładowane dane.

To tak z grubsza, oczywiście pominąłem tu kilka wątków, np. jak rodzaje źródeł danych etc. To nie jest istotne dla pokazania idei działania triggera jednak, za dużo by tego było.

Pewno i by obyło się bez dwóch tabel, jedna by wystarczyła, można nie insert, można update. To jak kto lubi, jak wymyśli, dostosuje do swoich potrzeb.

Tutaj kod, obrazki, do poćwiczenia.