OUTER JOIN, uważnie dołączaj tabele!

Marcinie, na luzie dzisiaj porada. Jeżeli chcesz łączyć więcej niż dwie tabele w sposób „inner  join”, to znaczy w wyniku mają być tylko te rekordy, dla których złączenie występuje, to dużych niespodzianek nie powinieneś spotkać. Gdy jednak chcesz łączyć więcej niż dwie tabele w sposób „outer join” lub stosując „plusik” to takie łączenie wymaga trzymania się jednej podstawowej zasady i nie będzie problemów. 

Zasada: Wybierz tabelę największą i dołączaj kolejne tabele w sposób „z plusikiem” lub „outer join” pisząc warunek złączenie danej/bieżącej dołączanej tabeli zawsze do tej największej tabeli.

Przykład: kadry prowadzą tabelę PRACOWNICY, w polach jest: ID, IMIĘ, NAZWISKO. Pracownicy zostali wysłani na różne szkolenia, w różnych składach. Listy obecności są w trzech osobnych tabelach, SZKOLENIE_SLQ, SZKOLENIE_PLSQL i SZKOLENIE_EXCEL, każda z tabel ma dwa pola OPIS i ID_UCZESTNIKA.

Jeżeli chcemy zrobić wykaz dla każdego pracownika szkoleń, w których był to najprościej będzie gdy do tabeli PRACOWNICY w sposób „outer join” dodasz każdą z ww. tabel z listą obecności, ale w każdym razie łącz w następujący sposób:

PRAWCOWNIK.ID=NAZWA_TABELI.ID_UCZESTNIKA (+), gdzie nazwa_tabeli to poszczególne tabele z listami uczestników.

W przykładzie to wyraźnie widać, iż nie należy łączyć list, ale w praktyce na bardziej złożonych i obszerniejszych tabelach łatwo ten błąd popełnić.  I tak na marginesie, w przykładzie po lewej mam największą tabelę i stosowałem „left outer join”, no bo tak piszemy, od lewej :-).

W.

Linka do skryptu tutaj, a plik w EXCEL tu.

Last_day … nie, wcale nie ostatni dzień bloga :-)

Marcinie, po co takie funkcje? To tylko na pozór zabawne rozwiązanie.

Zgodnie z dokumentacją cyt:

"The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a date value"

Funkcja zwraca dla danej daty ostatni dzień miesiąca, czyli last_day( 14 maja 2015) da wynik 31 maja 2015 roku, i po co to ?

Oczywiście można w ten sposób uzupełnić formatowanie, to jest jeżeli masz tylko miesiąc i rok to możesz dopisać ostatni dzień miesiąca, czasami tak trzeba, taki jest wymóg.

Jednak w mojej praktyce wykorzystanie tej funkcji jest zgoła inne. Bardzo często mam do czynienia z tak zwanymi kostkami danych, które mają zebrane stany dzienne, w hurtowni codziennie gromadzone są dane i są datowane. Przetwarzanie ogromnych zbiorów danych z jednej strony wymaga zasobów (siły maszyny), Twego oczekiwania (czasu)  i na wykresach też tylko punkty się prezentuje. Innymi słowy wystarczą stany na koniec miesiąca, by dane ograniczyć, tylko skoncentrować się na końcach miesięcy wystarczy prosty trik sprowadzający się do tego by dane z tabeli, datowane na dzień DATA_DANYCH sprawdzać, czy to jest właśnie koniec miesiąca, czyli 

czy last_day ( DATA_DANYCH ) = DATA_DANYCH, 

jeżeli tak, to analizuję, biorę dane z rekordu do obliczeń, jak nie to pomijam. I bez takiego wsparcia analityk danych by padł*

To samo na obrazkach i przykład do poćwiczenia pod linką.

W.

*Analityk danych oczywiście by nie padł, bo nie taka przecież jego natura, nie poddaje się tak łatwo... Jest jak japoński poborca podatkowy. Analityk danych na przykład  utworzyłby sobie tabelę, do której by zapisywał daty końca miesięcy i porównywałby sobie datę danych z datą pobieraną z utworzonej przez siebie dodatkowej tabeli, z ręki wpisał daty, może jeszcze inaczej.

KONKURS - SQL. Ćwiczenia praktyczne. Wydanie II

„SQL. Ćwiczenia praktyczne. Wydanie II” – co to za książka i co w niej można znaleźć zobaczysz tutaj. Po pierwsze polecam, Helion sprzedaje wersję papierową ale też elektroniczną. Po drugie  chcę „kupić” Wasze większe zainteresowanie, zaangażowanie…

Potrzebuję kilku „like,ów” na profilu facebookowym by portal udostępniał mi statystyki. Oczywiście mógłbym parę złotych zapłacić Facebookowi za promocję strony, ale wybrałem inną metodę. Na skutek zawarcia umowy kupna-sprzedaży stałem się dwa dni temu właścicielem wersji papierowej ww. książki i jestem gotów zainwestować kolejne parę złotych na przesłanie pocztą jej do wybranej osoby.

Subiektywnego wyboru, komu wyślę książkę dokonam wśród osób, które:

• Ujawnią się w komentarzu do posta na Facebooku (komentarze na blogu nie będą sięliczyć), na przykład wpiszą tekst „zadanie”
• Prześlą poprzez Facebook w wiadomości prywatnej kod, zapytanie w sql rozwiązujące zadanie, którego treść jest dalej.  Podkreślam: musi to być kod w sql, który uruchomiony na bazie wyświetli prawidłowy, zgodnie z treścią zadania wynik zadania.  
• Są w grupie „lajkujących” profil na Facebooku, to nie dotyczy tylko nowych, wszyscy biorą udział :-)
• Czekam na wiadomości  do dnia 27 maja 2015, do godziny 23:59
• Ujawnią po wyborze gdzie wysłać książkę w prywatnej wiadomości.

Zadanie:

Sięgnij do tabeli salda, skrypt do jej utworzenie jest w poście tutaj. Firma udziela kredytu kupieckiego, finansuje go środkami własnymi, których ma dokładnie 45 tys. złotych oraz jeżeli jest konieczność to posiłkuje się kredytem bankowym. Innymi słowy jeżeli suma udzielonych kredytów kupieckich przekracza 45 tys. pln (chodzi o faktyczne wykorzystanie kredytów kupieckich przez kontrahentów) to nadwyżka jest finansowana przez zaciągnięcie kredytu w banku na brakującą kwotę. Podaj proszę korzystając z danych w tabelce salda kiedy, w których datach wystąpiło wykorzystanie kredytu bankowego i w jakiej kwocie?

Powodzenia życzę Wam oraz sobie – > tu przyznam się szczerze, iż bardzo liczę na to, iż odpowiedzi się pojawią :-)

W.

Podzapytania proste, odsłona pierwsza :-)

Marcinie,

Fakt, już od pewnego czasu zastanawiam się nad jednym zestawem tabel i przykładowych danych by następnie na tychże prezentować ciekawostki. Tak mają kursy… Ja na blogu nie realizuję co prawda kursu, ale nieco marzy mi się taka sytuacja, takie środowisko, ale go nie mam… To jeszcze przede mną…

Dlatego nadal po staremu, a więc nowy przykład, nowe tabele. Sytuacja jest następująca:

1. firma handlowa sprzedaje czterem kontrahentom towary, numery kontrahentów od 1 do 4, dane o nazwie i województwie w tabeli KLIENCI,
2. firma sprzedaje z odroczonym terminem płatności, to jest udziela kredytu kupieckiego,
3. każdemu kontrahentowi firma określa limit kredytu kupieckiego, jest on zmienny w czasie,
4. firma co miesiąc rejestruje w tabelce saldo, zadłużenie danego kontrahenta oraz jego limit kredytu kupieckiego,
5. dane, o których mowa w punktach 2, 3 i 4 są zebrane w tabelce SALDA, mamy zdane za 4 lata, identyfikator klienta umożliwiający powiązanie z tabelą KLIENCI, limit i saldo... tu to jest.

Problem: czy, a jeżeli odpowiedź brzmi tak, to ile razy, kiedy, o ile kwotowo i procentowo zostały przekroczone w badanych czterech latach przyznane limity kredytu kupieckiego.  

By to zrobić szybko i sprawnie, to trzeba przejrzeć tabelkę SALDA i by nie robić tego osobno dla każdego kontrahenta od razu warto znaleźć pary danych: id kontrahenta oraz czas, gdy dla danego kontrahenta wystąpiło przekroczenie limitu, czyli:

(ID, DATA_STANU) IN (SELECT ID,  DATA_STANU FROM SALDA WHERE SALDO/LIMIT>1)

Wynikiem zapytania po lewej będzie zbiór par danych, gdy było przekroczenie limitu - o tym decyduje fakt, iż relacja salda do limitu jest powyżej 1.

To co mamy po prawej, to konieczny element, by napisać kompletny warunek – interesują nas pary danych co są w zbiorze par danych wygenerowanych przez zapytanie po lewej, tak to czytać trzeba J

No to mamy warunek, który użyjemy po WHERE. Właśnie po WHERE podamy, to co wyżej napisałem i to będzie wykonane w pierwszej kolejności, to jest właśnie podzapytanie proste.

Kiedy już wiemy kiedy, w zależności od kontrahenta, nastąpiło przekroczenie limitu możemy się „pobawić” i ustalić co chcemy zobaczyć finalnie „na ekranie”.

Nazwa kontrahenta, województwo jest w osobnej tabeli, dlatego będziemy sięgać do dwóch tabel, obu nadamy aliasy i je powiążemy. To pierwsza rzecz, która przychodzi do głowy przed skonstruowaniem co wyświetlimy, czyli:

FROM Salda s, Klienci k WHERE s.ID=k.KLIENT_ID

Od końca piszemy kwerendę, ale tak do niej po kolei dochodzimy. Jeżeli wiemy skąd, jaki warunek mają spełniać dane  to już wszystko. Wyświetlamy pożądane informacje i koniec.

I przy okazji uczymy się co to jest konkatenacja, to dodanie dwóch tekstów czymś takim ||

Tradycyjnie:

-skrypt do utworzenie danych, załadowania danych i zobaczenie wyniku, rozwiązania,

-dane w excel z formułą do ładowania.

Reszta na obrazku.

W.

SQL Developer, guziki co powodują...

Marcin, dzisiaj krótko, prosto, co powodują guziczki w SQL Developerze, dlaczego raz Execute Statement a dlaczego warto RUN Script. Wszystko w załączonym pliku na blogu jest dostępny, a za tydzień o zapytaniach prostych, prosta a bardzo silna technika.

Pliczek tu.

W.