SQL dla zielonych i żółtych

Na szkolenie w grudniu 2018

Na szkolenie w grudniu 2018:
Po linką poniżej są wersje instalacyjne MS SQL Server 2014 i z tego okresu MS STUDIO, trzeba zainstalować oba programy, osobno najczęściej. Jest prezentacja jak to robić oraz jest ilustracja z problemami najczęstszymi.

Problemy jakie odnotowano przy instalacjach to: pomimo funkcjonującej usługi MSSQLSERVER, to MS STUDIO nie widzi żadnego serwera. Spróbuj zmienić sposób uruchamiania usługi MSSQLSERVER na: z lokalnego konta systemowego.

Bywa, że trzeba poszukać w MS Studio serwera po uruchomieniu MS STUDIA.

Ilustracja ww. problemów jest na obrazkach poniżej.

Są osoby co wolą wziąć komputer pod pachę, uzgodnić czas i miejsce, jestem na Pałubickiego i wspólnie zainstalowaliśmy to co trzeba i działa. To taki wariant też jest możliwy, daj znać, polecam, ja to pomocny, miły, sympatyczny, do rany przyłóż .... jestem :-)

----------------------

Pod linką pliki:

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

WIDOK ZMATERIALIZOWANY na MS SQL Serwerze

Na bazie produkcji ORACLE można utworzyć tak zwany zmaterializowane widoki, jest to o niebo czytelniejsze mym zdaniem :-)

A na MS SQL Serwerze, to zacytuję za książką SQL Serwer 2005 autorstwa Igora Kruka i Artura Mościckiego.

Perspektywa (widok) to rodzaj tabeli wirtualnej zdefiniowanej przez zapytanie SQL. Jednak w przeciwieństwie do zwykłych tabel perspektywa nie ma fizycznej reprezentacji danych aż do momentu, gdy użytkownik zdefiniuje dla niej indeks.

No to mamy sytuacją taką, że widok to po prostu zapytanie zapisane na bazie, które można wywołać jak zwykła tabelę, ale de facto serwer korzysta z danych w tabelach, a utworzenie do widoku indeksu powoduje materializację widoku, to już zajmuje miejsce na dysku, ale też dużo szybciej działa wywoływanie danych z takiego widoku.

Używanie widoków w biznesie jest powszechne bo ułatwia rozwiązywanie złożonych zadań metodą pojedynczych kroków, ale jak utworzy się na MS SQL Serwerze indeks do takiego widoku, to są zapisywane dane i najczęściej podnosi się istotnie wydajność z punktu widzenia aplikacji biznesowych, które korzystają z danych.

A na obrazku przykład, a ten przykład można przećwiczyć na bazie testowej ZIELONY.

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

A baza testowa przypomnę, to jest tu:

http://zielonysql.blogspot.com/2016/12/zielony-sql-dorobi-sie-bazy-testowej.html

Formatuj tak jak wszyscy :-)

Podglądaj jak formatują swoje zapytania inni i rób tak samo.

Na przykład:

- zaczynaj wiersze od przecinków, separatory listy nie na końcu linii, ale na początku,

- rób wcięcia

etc.

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...

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ą.