Gruby czy chudy?

Święta tuż tuż… Wszystkiego najlepszego!

Tu pozwolę sobie nieco odjechać… na luzie, że tak powiem post, ale czy na pewno? Będzie odzyskiwanie wagi po poście (bo, że nie pościliście, to takiej wersji nie przyjmuję do wiadomości), zatem problem : czy jestem gruby czy chudy…  A tu spojrzenia mogą być różne, bo to zależy od kontekstu,  dla kogo ten wywiad :-)

Swego czasu z kolegą z pracy, który był mego wzrostu i w zasadzie tej samej wagi spierałem się, czy jesteśmy grubi, a może ja chudy, kolega gruby, albo na odwrót.  Na pierwszy rzut oka, to bez sensu, ale, ale… Metodą prób i błędów ustaliliśmy, że można moją i kolegi wagę opisać funkcją w zależności od wieku:

Waga A = 25,5 LN(WIEK) +1

Waga B = 22,0 LN(WIEK) +1

I już wszystko jasne :-) Dla kogo ten wywiad? To różne odpowiedzi.

Dla Taniej Linii Lotniczej jesteśmy jednakowo grubi, koszt paliwa w celu przewiezienia 1 kg jest dla nas obu jednakowy, koszt transportu nie zależy od wieku kilograma.

Jednak dla Funduszu Zdrowia to sprawa przedstawia się inaczej, nadwaga, to ryzyko zwiększone chorób, stawy puszczają… inne dolegliwości przy dużej wadze się szybciej przyklejają… Wyraźnie widać, że kolega najprawdopodobniej będzie większym obciążeniem dna Funduszu Zdrowia, niż ja. Co prawda dzisiaj ważymy tyle samo, ale kolega idzie na 100 kg w moim wieku. Bo pewno się domyślacie, że B, to ja. 

Tania Linia widzi nas niejako poziomo, a Fundusz Zdrowia w pionie. 

CTE i ROW_NUMBER() czyli jak wybrać co siódmą umowę?

Bywa, że trzeba wyprodukować „obiektywizm”. Na przykład mamy pulę trudnych umów, chcemy części z nich zaproponować niższą ratę przez pewien okres, w ten sposób chcemy zorientować się czy taka forma pomocy ma sens, czy umowy dzięki temu naprawiają się czy jest to po prostu zbędny ruch. 

Innymi słowy robimy test, ale które umowy będą nim objęte. Tu warto zadbać by algorytm był „ślepy”. 

Zatem pomysł jest następujący: układamy umowy według wartości przedmiotu oraz klient_id. Po takim ułożeniu oferta trafia do co siódmej umowy. 

To znaczy trafi do: 1, 8, 15 i tak dalej.

Jak to wyprodukować kwerendą prezentuje obrazek i kod do poćwiczenia.

Używam CTE i UNION ALL, to dzięki temu wyprodukuję pożądany ciąg numerów, a dzięki użyciu ROW_NUMBER() nadam wierszom numery.

Złączenie dwóch tabel daje mi finalny wykaz i jedziemy.

Do poćwiczenia tutaj.

 

https://drive.google.com/drive/folders/0B0DNBH1DOPAfYUhtLTFMaWxGanc?usp=sharing

RANK i DENSE_RANK, co trafi na podium...

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.