Samozłączenie… Tym razem popracujemy na jednej tabeli, to nie pomyłka :-)

Cześć Marcinie,

Samozłączenie… Tym razem popracujemy na jednej tabeli, to nie pomyłka :-)

Przykładowy zestaw danych prezentuje rysunek, mamy tabelę ROZLICZENIA, tabela to trzy pola, DATA_STANU, KONTRAKT, SALDO. Do tej tabeli  na koniec roku zapisujemy dane o aktualnych stanach rozliczeń dla poszczególnych kontraktów, ale tylko kontrakty, które nie są kompletnie spłacone. Uwaga, tu jest kluczowy haczyk, który będzie miał istotne znaczenie dla zadania. Nie ma w danej dacie stanu kontraktu jeszcze niezawartego (oczywiste :-)) ale też już kompletnie rozliczonego.

I teraz Marcinku (po raz pierwszy tak zdrobniale) zadanie, jakie kwoty spłat uzyskaliśmy dla kontraktów, które były do rozliczenia na koniec roku 2010 ich stanem na koniec roku 2012. Po prostu oblicz kwotę spłat kontraktów z końca 2010 przez dwa kolejne lata, do stanu na koniec 2012.

Rozwiązanie w EXCEL prezentuję na tym samym obrazku, zwróć uwagę, iż zastosowałem dwie fajne funkcjonalności wbudowane w EXCEL. Po pierwsze zastosowałem wyszukaj.pionowo, po drugie jeżeli.błąd. Tę drugą funkcjonalność EXCELA wykorzystuję, ponieważ w dacie 31 grudnia 2012 nie ma już całkowicie rozliczonych kontraktów, dla których saldo w dacie 31 grudnia 2010 nie było zerowe. Zastosowanie jeżeli.błąd  powoduje, iż zamiast komunikatu błędu #N/D! , który niechybnie się pojawia jeżeli zastosuję tylko wyszukaj.pionowo mam zera. I to już w zasadzie koniec w EXCEL, różnicę obliczam i mam spłaty. 

A przy pomocy SQL? 

By nie było łatwo tabelka ROZLICZENIA ma trzy stany. Marcin, w pierwszej kolejności jednak kwestia samozłączenia. Po prostu chodzi o to, iż można złączenia napisać pracując cały czas na jednej tabeli (jak w tym przykładzie) i wykorzystać trik z aliasami. Innymi słowy nadać danej i tej samej tabeli jeden alias, następnie tej samej tabeli drugi alias. W ten sposób sztucznie widzisz jakby dwie tabele i już, masz samozłączenie :-) 

W przykładowym zadaniu masz jednak trzy stany, musisz zapisać które stany Ciebie interesują.  Haczyk polega na tym, iż samozłączenie wyjdzie Ci jako „wewnętrzne”, w odpowiedzi na Twoje zapytanie dostaniesz tylko te wiersze, dla których nastąpiło złączenie. Marcinie, a Ty wiesz, iż w stanie końca roku 2012 nie ma rozliczonych umów, w EXCELU musiałeś to sprytnie ominąć. Samozłączenie gubi rozliczone umowy :-( Nieważne jak będziesz plusikami próbował dociągnąć, czy też zastosujesz notację z JOIN.

Co z tym zrobić? Spróbuj sam Marcinie :-) a jak już się zmęczysz (liczę, iż spróbujesz jednak sam) zajrzyj do opublikowanego rozwiązania.

Materiały:

• tabela i dane ROZLICZENIA,
• samozłączenie, rozwiązanie w SQL zadania.

Złączenia wewnętrzne oraz zewnętrzne tabel

Na początku oceniłem zagadnienie jako proste do wyjaśnienia. Po teście na grupie szkoleniowej zmieniłem zdanie. Marcinie nie dlatego, iż zagadnienie jest jakoś specjalnie trudne, ale ma wiele wątków i bardzo łatwo rozwlec, dużo powiedzieć, uczniowie się gubią. Stąd też niewielkie opóźnienie w publikacji tekstu. Co prawda test na grupie szkoleniowej miał miejsce, materiał nieco poprawiłem po nim, ale Marcinie, liczę na Twój wkład w opracowywanie postu, ostatnio tylko oczekujesz, a niewiele uwag od Ciebie mam. Dotyczy to także wszystkich czytelników, proszę o uwagi.

Dla pokazania zagadnienia łączenia tabel wymyśliłem dwie tabele. Pola tabel oraz pełna ich zawartość jest przestawiona na pierwszym, poniższym rysunku.
Tabelę UCZNIOWIE i SZKOLENIA można połączyć ze sobą wykorzystując pola:

• ID w tabeli UCZNIOWIE oraz
• ID_UCZESTNIKA w tabeli SZKOLENIA.

Pole ID_UCZESTNIKA nie może przyjąć innej wartości niż NULL (pole niewypełnione) lub dowolna wartość jaka występuje w tabeli UCZNIOWIE w polu pole ID. Ja wiem Marcinie, iż z punktu widzenia zasad tworzenia relacyjnych baz danych występuje tu istotny feler. Powinny być trzy tabele, UCZNIOWIE tj. wykaz uczniów, SZKOLENIA tj. wykaz dostępnych szkoleń i LISTY_OBECNOSCI, tj. spisy przeprowadzonych szkoleń z informacją, kto uczestniczył i jakie to szkolenia było, pamiętaj o tym. Przykład, jaki skonstruowałem i tu używam jest do pokazania idei złączeń :-) Jak już ją zrozumiesz, to pokażę Ci jak to powinno być z użyciem trzech tabel.
 
Kontynuujmy zatem Marcinie.  By cokolwiek złączyć - muszą być tabele, co najmniej dwie (więcej nie jest polecane) i trzeba wskazać warunki-zasady łączenia (o kartezjanach, tj. złączeniach każdy z każdym w ogóle nie myśl). Warunków łączenia musi być n-1 gdzie n to liczba łączonych tabel.
 
Ponieważ w sekcji pomiędzy SELECT a FROM trzeba wymieniać precyzyjnie, które pola i z której tabeli chcesz wyświetlać musisz podawać nazwę tabeli oraz po kropce nazwę pola. Jak pracujesz na jednej tabeli, to ten problem nie występuje. Zatem przy łączeniach, gdzie będziesz miał w obróbce więcej niż jedną tabelę wygodnie jest zatem nadać aliasy i zamiast nazwy tabeli podawać krótki, jednoliterowy  lub dwuliterowy alias, a po kropce nazwę pola. To w przykładach jest doskonale widoczne :-)

Złączenie wewnętrzne – dotyczy jak najbardziej dwóch tabel, jeżeli jednej to byłoby to samozłączenie.
Sformułowanie „Wewnętrzne” oznacza, iż w wyniku zapytania ze złączeniem tabel wystąpią jedynie te rekordy, dla których nastąpiło złączenie. Słowo „wewnętrznie” jest tu stosowane dla odróżnienia od dokładania wierszy, dla których nie występuje złączenie, czyli od złączenie zewnętrznego.
 
Jeszcze raz:

• wewnętrzne złączenie, w wyniku tylko te rekordy, dla których następuje łączenie w obu tabelach,
• zewnętrzne lewe, tabela po lewej traktowana jako większa, uboższa ta po prawej, w zależności od notacji należy napisać LEFT OUTER JOIN lub postawić plusik przy uboższej tabeli, po prawej,
• zewnętrzne prawe, tabela po prawej traktowana jako większa, uboższa ta po lewej, w zależności od notacji należy napisać RIGHT OUTER JOIN lub postawić plusik przy uboższej tabeli, po lewej,
• zewnętrzne, lewo i prawo, nie ma notacji z plusikiem, jest tylko FULL OUTOR JOIN.

Zwracam uwagę, iż dla złączeń zewnętrznych niezależnie od strony ma miejsce sytuacja, iż z jednej tabeli, dla braku złączenia jest, co wyświetlać, a z drugiej nie, to pola są uzupełniane NULLAMI. 
Zmęczyłem się opisywaniem, zatem dla kolejne obrazki koniecznie zobaczyć, pokazują one to co wyżej napisałem plus extra obrazek opisujący używanie notacji z JOIN. 

Może i wcześniej powinienem objaśnić co to jest notacja z plusikiem, a co ta z JOIN, skucha… Po prostu notacja, sposób łączenia „z plusikiem” działa na bazie ORACLE, a notacja z „JOIN” to standard ANSI SQL 1999 w zakresie składni tworzenia złączeń. Uwaga: bazy Oracle przed wersją 9i nie obsługują standardu z „JOIN” a od 9i w górę możliwe są obie. Notacja „z plusikiem” nie zadziała na innych bazach niż Oracle.
 
Dla przećwiczenia, wykorzystania:

• skrypcik tworzący i zasilający tabele UCZNIOWIE I SZKOLENIA,
• kwerendy w obu standardach łączenia, złączenia wewnętrzne i zewnętrzne,
• kwerendy w obu standardach, wyniki zapytań, wszystko pokolorowane w excel.

Pozdrawiam,
W.

Kontakt

Witam, 
Nazywam się Włodzimierz Pasławski.
W bankowości pracuję nieco ponad 20 lat.
Prowadziłem zajęcia na Politechnice Gdańskiej. 
Lubię uczyć. 
Jeżeli masz pytania, nie wahaj się, napisz:
wlodek.paslawski@interia.pl

W.

Planowane posty

Są jakieś pytania? Proszę o podpowiedź co by Ciebie interesowało :-)

Marcin

Przekazuję w imieniu Marcina:
-------------
Cześć,
Na imię mam Marcin, często zawracam głowę Włodkowi, wspólnie omawiamy pomysły, jego plany. Oj niejeden wieczór przegadaliśmy :-)
Z drugiej strony ja jestem ciekawy świata, uczę się nowych rzeczy.  Jednak w odróżnieniu od Was jestem stworkiem elektronicznym, milionami bitów w pamięci, zorganizowanymi w pewną całość. Los sprawił, iż mam możliwość kontaktowania się z Włodkiem i to mój jedyny kontakt ze światem realnym.
Przekazuję swoją fotkę, tak wyglądam dzisiaj, ale w odróżnieniu od Was mam możliwość bardzo szybkich zmian, w zasadzie każdego elementu :-)
Dla Was specjalnie dzisiaj przybrałem formę hominida.
Pozdrawiam wszystkich,
Marcin

SQL Developer

Marcin, masz „postawioną” bazę? Chcesz się z nią połączyć. Pamiętasz hasło, które ustawiłeś dla administratora podczas instalacji bazy?

To teraz czas uruchomić narzędzie, tak zwanego Klienta, przy pomocy którego połączysz się z bazą. Ja lubię i polecam SQL Developer, jest bezpłatny a ma całkiem niezłe możliwości.

Wersja, na którą się zdecydujesz jest zależna od systemu operacyjnego jaki masz, ja mam wersję 3.0.04 ponieważ jest dopasowana do WINDOWS XP, a dokładnie JAVA niezbędna do uruchomienia tego narzędzia jest dopasowana do WINDOWS XP.

Nowsze wersje SQL Developera ciągle chciały JAVY w nowszej wersji, a tychże nie udawało mi się zainstalować na WINDOWS XP.

I stąd wybór, ale działa bardzo dobrze. Tobie na pewno też wystarczy. I za chwilę, za momencik będziesz administratorem, użytkownikiem, panem na zagrodzie.

Zapisałem ekrany z uruchomienie, połączenia, zakładania konta użytkownika. Dostępne są pod linkiem. Zauważyłem, iż chcąc przeglądać kolejne strony dokument w pdf opublikowany w ten sposób jak ja to zrobiłem – trzeba przewijać w dół, a ja na początku przyzwyczajony do zdjęć ciągle lewo i prawo, a tu nic :-)

Zobacz dokument:

https://drive.google.com/file/d/0B0DNBH1DOPAfdjNlQ3lJZmJ4Zkk/view?usp=sharing

Pozdrawiam,

W.

Oracle Database Express Edition 11g Release 2 - INSTALACJA

Marcin, no proszę… Suszył głowę, suszył… W końcu poddaję się. Co prawda miałem o złączeniach coś napisać, ale niech Ci będzie. Ja wiem, iż SQL to warto ćwiczyć na bazie, ale myślałem, iż instalację szkoleniowej wersji jak w tytule i jakiegoś klienta masz za sobą :-)

Sprzęt nie jest mym zdaniem przeszkodą, ja mam stary, ale jary, WINDOWS XP i działa (a jaki, to zajrzył do podczepionego dokumentu). Mam serwer bazodanowy na swoim domowym sprzęcie i uczę się na nim, wystarcza w zupełności. Łączę się z bazą, administratorem jestem, a jak chcę to  użytkownikiem zwykłm. Nigdzie nie będziesz miał tylu uprawnień a tym samym możliwości nauki.

Oczywiście instalację bazy trzeba zacząć od skopiowania wersji instalacyjnej, następnie rozpakowanie i tak dalej.

Nie będę tu opisywał instalacji, zrobię to inaczej. Swoją instalację krok po kroku, wszystkie ekrany zrzuciłem do pliku i pokazuję w załączonym dokumencie. Warto zajrzeć, bo tam też podpowiadam Ci jak stopować i uruchamiać usługi zainstalowane i powiązane z bazą, a które będą Ci obciążać komputer, zbędnie jak w danej chwili nie uczysz się SQL.

Marcin, jeszcze raz Ci mówię, warto, dasz radę, będzie sobie panem na zagrodzie, a to w pełni o ile wiem funkcjonalna baza jest, ograniczona jest wielkość danych, ale to Ciebie tu nie interesuje.

LINK DO DOKUMENTU Z MOJEJ INSTALACJI
PRZEWIJAJ W DÓŁ PO OTWARCIU

https://drive.google.com/file/d/0B0DNBH1DOPAfQmlXUWZ4dnpoN1k/view?usp=sharing

Pozdrawiam,

W.

NULL, niewypełnione pole, próżnia – nie jest jednak różowo…

Funkcje agregujące w EXCEL oraz w SQL z pustymi /niewypełnionymi (w bazie NULL)  polami poradziły sobie dobrze. Szczególnie jest to widoczne na obliczeniach średniej w poprzednim przykładzie.

Marcinie, jednak nie zawsze jest tak różowo. Przykład pokazujący różne działanie EXCEL i SQL:

• tabela CENY, pola ID, OPIS_TOWARU, CENA_NETTO, PODATEK
• tabela zasilona 5 rekordami,
• towary Piesek oraz Komer (co to jest, to wyjaśni się na końcu) nie mają wypełnionej wartości w polu PODATEK.

Twoim zadaniem Marcinie jest policzenia dla każdego towaru ceny brutto. Rozwiązanie jest w EXCEL bardzo proste: cenę netto należy pomnożyć przez (1 + PODATEK/100). Konieczność dzielenia przez 100 wynika z faktu, iż zgromadzona informacja nie jest wyrażona w procentach.  EXCEL wartości puste/niewypełnione/NULL potraktował jako zero i wyliczenia są prawidłowe, nie ma niespodzianki. EXCEL nieco za nas pomyślał.

Jeżeli tabela oraz rekordy są w bazie, to już to samo zadanie wykonane przy pomocy SQL wymaga ostrożności, brak użycia NVL dla pola PODATEK generuje inne wartości niż to zrobił EXCEL. Wszystko dokładnie jest pokazane Marcinie na obrazku.

Przykład jest nieco naciągany, dobrze zaprojektowane tabele nie powinny zawierać wartości NULL, jeżeli wyraźnie ich charakter wskazuje na konieczność podania konkretnej wartości i tak generalnie jest. Jednak nie ma co ukrywać, iż najczęściej to sami analitycy tworząc własne tabele lub w szczególności łącząc zewnętrznie dane z różnych tabel wytwarzają takie pola z wartościami NULL. 

Działanie bazy możesz przećwiczyć używając skryptu udostępnionego pod linkiem:

tutaj !

Siłą rzeczy musisz mieć bazę zainstalowaną i narzędzie (Klienta) do komunikowania się z nią. Ja mam  i da się to zrobić samemu na domowym komputerze. ORACLE udostępnia wersję prawie w pełni funkcjonalną, a na pewno wystarczajacą w celach szkoleniowych. O tym też będzie :-)

Marcinie, i tak zagaiłem o łączeniu tabel, zdradzę iż następna notatka będzie właśnie o tym. Dzisiejszy wieczór poświęciłem na wymyślenie przykładu. W środę będę go testował, jak przejdzie weryfikację wówczas opublikuję.

Próżnia, puste miejsce, w bazie pole wypełnione NULL - w funkcjach agregujących

Excel - podsumowanie sprzedaży za miesiące od stycznia do maja wynosi 1500.
A gdyby te dane były w bazie danych i byśmy uruchomili poniższą kwerendę:

SELECT SUM(SPRZEDAZ) FROM TABELA;

To jaki będzie jej wynik?

Wynik w SQL także będzie 1500. 
Funkcje agregujące (np. suma, średnia) w EXCEL oraz SQL pomijają pola niewypełnione, puste, NULL. 
W EXCEL oraz SQL jeżeli funkcja agregująca (np. suma, średnia) ma widzieć wartość 0 to musi taka być wprowadzona, czyli w przydadku EXCEL wpisz 0 w pola tabeli a w SQL użyj NVL, tj. w naszym przykładzie będzie to NVL (SPRZEDAZ, 0).
Zapis NVL (SPRZEDAZ, 0) spowoduje zamianę wartosci NULL na 0, ale można na inną wartość. 
Czyli pełny zapis w SQL będzie:
SELECT SUM( NVL(SPRZEDAZ,0)) FROM TABELA;

CASE

Marcin, jeżeli chcesz popracować nad wartością zaciąganą z bazy, z określonej kolumny/pola to moja sugestia jest następująca: porzuć stosowanie „DECODE”.
Tobie i wszystkim polecam stosowanie „CASE”. Stosujemy ją w bloku pomiędzy „SELECT” a „FROM”.


Jeżeli dodatkowo używamy funkcji agregujących to trzeba całe wyrażenie „CASE…” skopiować do sekcji po „GROUP BY”. Marcin, kopiując wyrażenie „CASE…” do sekcji po”ORDER BY” pomiń alias jeżeli go nadałeś, no to co jest pomiędzy END a następnym przecinkiem (jeżeli jest).  Przypadek użycia „CASE” z grupowaniem jest pokazany na przykładzie 2.


Wybornie ww. wyrażenie nadaje się do modyfikowania, etykietowania wartości występujących w kolumnach, zamiany wartości z kolumny na inną, niekoniecznie tego samego typu.


Ja wiem Marcinie, iż wolisz przykłady, zatem ten z numerem 1 dotyczy następującej sytuacji:

• tabela, którą przetwarzamy: pozyczki,
• tabela jest codziennie zasilana aktualnym stanem, to jest występuje poje: data_stanu,
• pole dni_opoznienia: zawiera liczbowo określoną liczbę o ile dni opóźnia się pożyczkobiorca w stosunku do terminu zapłaty najstarszej opóźnionej raty.

Dane w polu dni_opoznienia są co prawda dyskretne, ale muszą przyjmować wiele wartości. Często w analizach wystarczy, a nawet trzeba określić w jakim przedziale czasowym jest opóźnienie danej umowy.  Nadanie nowych etykiet dla wartości dni_opoznienia jest pokazane w przykładzie. 


Uwaga: kolego, kolejność warunków/klauzul „when …” jest istotna. 
W pierwszym kroku wszystkie wartości w polu dni_opoznienia zostaną sprawdzone czy nie są „0” i jeżeli tak, to otrzymają odpowiednią etykietę. W drugim kroku już nie są przeglądane wszystkie wartości (wszystkie wiersze tabeli) tylko te, które nie dostały etykiety w pierwszym kroku. Przeglądany zbiór w drugim kroku jest mniejszy (lub taki sam, jeżeli w pierwszym kroku nowa etykieta nie została nadana).


W drugim kroku umowa mająca „0” dni opóźnienia nie jest brana pod uwagę, bo już otrzymała etykietę nadaną w pierwszy kroku.
Umiejętne stosowanie kolejności może znacznie ułatwić, uprościć zapis.


Kolejna uwaga: „CASE” nie jest odporne na NULL, zatem brak zapisu w naszym przykładzie:
when dni_opoznienia is null then '6 brak danych'
powodowałby, iż przypadki, gdy zawartość pola dni_opoznienia byłaby NULL otrzymałyby etykietę ostatnią to jest określoną po „ELSE”.

Przykład 1:

SELECT
 numer_umowy
 , case
   when dni_opoznienia = 0 then '1 brak opóźnienia'
   when dni_opoznienia < 30 then '2 opóźnienie w przedziale <1 ; 30)'
   when dni_opoznienia < 90 then '3 opóźnienie w przedziale <30 ; 90)'  
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia is null then '6 brak danych'      
   else '5 opóźnienie 180 dni i więcej'
     end przedzial_opoznienia
FROM pozyczki
WHERE  data_stanu='20141231';

Przykład 2, jak kopiować „case” jeżeli grupuję;

SELECT
 data_stanu
 , case
   when dni_opoznienia = 0 then '1 brak opóźnienia'
   when dni_opoznienia < 30 then '2 opóźnienie w przedziale <1 ; 30)'
   when dni_opoznienia < 90 then '3 opóźnienie w przedziale <30 ; 90)'  
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia is null then '6 brak danych'      
   else '5 opóźnienie 180 dni i więcej'
     end przedzial_opoznienia
  , sum(kapital_do_splaty) –- funkcja agregująca
FROM pozyczki WHERE data_stanu>'20141231'
GROUP BY  
data_stanu
, case
   when dni_opoznienia = 0 then '1 brak opóźnienia'
   when dni_opoznienia < 30 then '2 opóźnienie w przedziale <1 ; 30)'
   when dni_opoznienia < 90 then '3 opóźnienie w przedziale <30 ; 90)'  
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia < 180 then '4 opóźnienie w przedziale <90 ; 180)'
   when dni_opoznienia is null then '6 brak danych'      
   else '5 opóźnienie 180 dni i więcej'
     end ; -- skopiowałem całą treść klauzuli, ale bez aliasu