Zielony SQL, dorobił się bazy testowej :-)
Zielony SQL, dorobił się bazy testowej :-)
Dostępna jako plik do podczepienia w MS SQL SERVER EXPRESS, plik z rozszerzeniem mdf jest pod linką poniżej.
W tej lokalizacji też kilka slajdów jak ten plik podczepić do Servera przy użyciu STUDIA.
Tabelek jest tam całkiem dużo, bo to nieco śmietnik się zrobił, to też tam są tabelki wykorzystywane do wcześniejszych postów. Główne i najpełniejsze to te niżej wymienione.
/*
NA TYM PRACUJEMY
select * from kostka_umowy;
select * from klienci;
select * from kontrakt_start;
select * from portfel;
*/
https://drive.google.com/drive/folders/0B0DNBH1DOPAfc3dwU1EwZ0FoR0k?usp=sharing
ZNOWU LAG, nie mamy danych z dni wolnych w miesiącu, to jak uzupełnić dane?
ZNOWU LAG, nie mamy danych z dni wolnych w miesiącu, to jak
uzupełnić dane?
Lepiej późno niż wcale, powiedziała pewna pani, gdy na
pociąg się spóźniła… Pewną zagadkę swego czasu rozwiązywałem, rozwiązywałem, a
później temat stał się nieaktualny :-)
Opis sytuacji:
W dni robocze są ładowane dane do hurtowni. Po prostu dla
każdego dnia roboczego jest dostępna informacja o saldzie środków na koncie
danego Klienta.
Odsetki płacimy za każdy dzień trzymania środków u nas przez
Klienta, ale także za dni wolne.
Robimy plan kosztów na kolejny rok. Mamy dane z jednego stycznia 2012 roku.
No to cóż, trzeba policzyć średnie saldo w miesiącu środków,
to saldo pomnożyć przez stopę procentową, według której płacimy odsetki i już wiemy ile zapłacimy Klientowi.
No właśnie, ale jak ma się średnia obliczona na danych w
hurtowni z dni roboczych do średniej policzonej na wszystkich dniach w
miesiącu. Jak uzupełnić informację o salda z niezaładowanych dni?
Oto jest pytanie?
Prezentacja problemu, dane, kody i tak dalej. Wszystko w
lokalizacji poniżej. A rozwiązanie to połączenie wykorzystania COALESCE i LAG
(w przypadku Oracle to wykorzystać należy Marcinie NVL2 i LAG).
Jak nie lagą (taki kij) to go leadem...
Była promocja i tym podobne, posty, posty, a brak nowych
treści odnośnie SQL.
Zatem poprawiam się, polecam książkę po raz wtóry:
A w tej książce na stronie 71 jest:
Nie po
raz pierwszy staje się bardzo widoczny nacisk producentów baz danych na przetwarzanie
analityczne. Praktycznie z każdą nową realizacją produktu wprowadzane są nowe
funkcjonalności.
Już nie tak nowe, ale dwie wymienię
LAG (w moich stronach, laga to taki kij) lub LEAD. To fajne funkcje jeżeli
chcemy szybko policzyć zmiany, ale hurtem,
wskaźniki procentowe, ale chcemy na przykład wziąć wartość do licznika z
danego wiersza, a do mianownika wartość z innego wiersza, np.
3 wiersze wstecz, jeżeli je posortować według daty6 (innego pola, ale sortowanie
musi być, bo w bazie dane nie są posortowane tak jak mam intuicyjnie się
wydaje, to błąd).
Tu są dwie funkcje do stosowania, LAG i LEAD, różnica pomiędzy nimi jest taka, że LAG bierze wartość wstecz, przesunięcie wstecz, a LEAD wprzód. Nie da się wpisać w LAG lub w LEAD wartości ujemnej na drugim miejscu w nawiasie: sprawdziłem.
Obie funkcje mają w nawiasie do wypełnienia trzy parametry:
Pierwszy jest obowiązkowy, trzeba podać z jakiego pola ma zapytanie pobrać wartość.
Drugi już nie jest obligatoryjny, jak pominiesz, to baza przyjmie domyślnie 1.
Jak trzeci pominiesz, to domyślnie baza przyjmie NULL
Tu są dwie funkcje do stosowania, LAG i LEAD, różnica pomiędzy nimi jest taka, że LAG bierze wartość wstecz, przesunięcie wstecz, a LEAD wprzód. Nie da się wpisać w LAG lub w LEAD wartości ujemnej na drugim miejscu w nawiasie: sprawdziłem.
Obie funkcje mają w nawiasie do wypełnienia trzy parametry:
Pierwszy jest obowiązkowy, trzeba podać z jakiego pola ma zapytanie pobrać wartość.
Drugi już nie jest obligatoryjny, jak pominiesz, to baza przyjmie domyślnie 1.
Jak trzeci pominiesz, to domyślnie baza przyjmie NULL
Ten NULL to ma zastosowanie jeżeli np. jest to pierwszy wiersz z sortowania, to 3 wstecz to co podać? Domyślnie jest NULL, ale możesz wskazać inną wartość.
LAG (nazwa pola, przesunięcie1, wartość domyślna)
LEAD
(nazwa pola, przesunięcie2, wartość domyślna)
A więc ww. LAG i LEAD działają jedynie na posortowanych wynikach, zatem po:
A więc ww. LAG i LEAD działają jedynie na posortowanych wynikach, zatem po:
LAG lub LEAD musi być słowo kluczowe OVER a
następnie w nawiasie minimum (ORDER BY nazwa pola).
W tym nawiasie może być jeszcze „partition by” ale to na teraz proszę Marcinie daj spokój, nie za dużo.
Zobacz na przykładzie, tu zwróć uwagę, że pokazuję działanie LAG i LEAD na danych w tabeli portfel, która zawiera już włożone agregowane dane (bo to przykład), ale zamiast odwołania do tabeli portfel może i często w nawiasie bywa długi SELECT z funkcjami agregującymi, to wówczas widać finezję stosowania LAG i LEAD.
W tym nawiasie może być jeszcze „partition by” ale to na teraz proszę Marcinie daj spokój, nie za dużo.
Zobacz na przykładzie, tu zwróć uwagę, że pokazuję działanie LAG i LEAD na danych w tabeli portfel, która zawiera już włożone agregowane dane (bo to przykład), ale zamiast odwołania do tabeli portfel może i często w nawiasie bywa długi SELECT z funkcjami agregującymi, to wówczas widać finezję stosowania LAG i LEAD.
Potestuj działanie LAG i LEAD, a może się przekonasz do
stosowania :-)
W tej lokalizacji znajdziesz dane wsadowe, kwerendy, prezentację.
W tej lokalizacji znajdziesz dane wsadowe, kwerendy, prezentację.
Warto mieć książkę w PDF, do SQL
Warto mieć książkę w PDF.
W przypadku uczenia się SQL nie trzeba przepisywać kwerend z książki.
Tylko kopiuj i wklej oraz EXECUTE i widać efekt.
Nie przepadam za elektroniczną lekturą ale tu warto zrobić wyjątek.
Polecam książkę do nauki SQL na MS Server:
MS SQL Server. Zaawansowane metody programowania.
Autor: Adam Pelikant
Do kupienia w HELION, pierwsza linka.
Pod drugą linką przygotowałem slajdy jak podczepić pod serwer przykładową do książki bazę i jak z niej korzystać na zasadzie kopiuj i wklej.
http://helion.pl/ksiazki/ms-sql-server-zaawansowane-metody-programowania-adam-pelikant,sqlszm.htm
https://drive.google.com/file/d/0B0DNBH1DOPAfT05BT0ZpTVRvRGc/view?usp=sharing
W przypadku uczenia się SQL nie trzeba przepisywać kwerend z książki.
Tylko kopiuj i wklej oraz EXECUTE i widać efekt.
Nie przepadam za elektroniczną lekturą ale tu warto zrobić wyjątek.
Polecam książkę do nauki SQL na MS Server:
MS SQL Server. Zaawansowane metody programowania.
Autor: Adam Pelikant
Do kupienia w HELION, pierwsza linka.
Pod drugą linką przygotowałem slajdy jak podczepić pod serwer przykładową do książki bazę i jak z niej korzystać na zasadzie kopiuj i wklej.
http://helion.pl/ksiazki/ms-sql-server-zaawansowane-metody-programowania-adam-pelikant,sqlszm.htm
https://drive.google.com/file/d/0B0DNBH1DOPAfT05BT0ZpTVRvRGc/view?usp=sharing
select count (*) from tabelka; -- 7, czy 5 a może 3?
Podstawowa, najczęściej używana funkcja agregująca to zliczanie rekordów w
wyniku zapytania, w tabeli.
A zliczyć można na co najmniej na trzy sposoby (te znam), wszystkie
rekordy, analizując zawartość określonego pola i policzyć tylko te co nie są
NULL, a jeszcze można nie są NULL I tylko unikalne wystąpienia.
Do przećwiczenia kod pod linką plus
obrazek.
Zobacz dlaczego wyniki są różne.
select count (*) from tabelka; -- wynik 7
select count (nazwisko) from tabelka; -- wynik 5
select count (distinct nazwisko) from tabelka; -- wynik 3
EXCEL: wyszukaj pionowo…
SQL i EXCEL to niejako rewers i awers.
Z różnych możliwości dzisiaj Marcinie patent na łączenie
danych.
Kto wymyślił coś takiego, nie mam pojęcia, ale pewno musiał
dostawać różne tabelki i szybko je łączyć, robić z danych wydruki, korespondencję seryjną, to wymyślił
WYSZUKAJ PIONOWO.
I obrazek oraz plik excel pokazują jak to działa.
Należy wskazać w nawiasie:
- w zestawieniu 1 pole, które zawiera wartość szukaną w zestawieniu 2 (musi być w pierwszym polu zestawienia 2),
- następnie definicja całego zestawienia 2 poprzez wskazanie narożników obszaru (lewy górny róg : prawy dolny róg)
- teraz z którego pola zestawienia 2 ma się pobrać wartość co przedłużonego zestawienia 1,
- fałsz, ta klauzula jest ważna, ponieważ tylko dokładne dopasowania są brane pod uwagę, jeżeli nie ma tej klauzuli, to różne podobne będą podkładane, nie polecam.
I jest, i od razu widać, że w zestawieniu 2 nie ma danych
dla umowy o ID A103. Nie wszystko musi być przy pomocy SQL. Nie próbuj Marcinie
otwierać drzwi kwerendą, prościej wstać i tradycyjnie to zrobić.
Jak używamy operator IN, jak to zadziała fajnie na Oracle, a jak nie na MS SQL...
Operator IN przydaje się, jak nie szukamy jednego
konkretnego rekordu, ale wielu, których pewna wartość jest w zbiorze. Na
przykład: znajdź wszystkie produkty w sklepie, które są marki, sony lub iphone.
Select * from produkty where
marka in ('sony' , 'iphone') ;
Właśnie dlatego, że więcej niż jednego producenta, to trzeba
użyć operatora IN i w nawiasach podać listę wartości. Na bazie ORACLE lista
może być, jak to powiedzieć: dwupolowa, a nawet więcej polowa. Gdybyśmy chcieli
znaleźć najdroższe produkty wyżej wymienionych marek, to można to uzyskać w
następujący sposób.
Select * from produkty
where (marka, cena)
in
(select marka, max(cena)
from produkty
where marka in ('sony' , 'iphone') group by marka ) ;
Ten drugi patent nie zadziała na MS SQL Serwerze. To zatem
jak? Pokazuję na przykładzie tabeli z cenami mieszkań, w latach, kwartałach,
miastach, dane z: https://www.nbp.pl/publikacje/rynek_nieruchomosci/ceny_mieszkan.xls
I chcę wydobyć dane o mieście, roku, kwartale dla każdego maksa
w danym roku.
W tej lokalizacji jest plik do załadowania danych oraz plik
z rozwiązaniem ww. problemu.
Subskrybuj:
Posty (Atom)