Złączenie tabel, iloczyn kartezjański… dodanie stanów zerowych do tabeli

Złączenie polegające na iloczynie kartezjańskim znam Marcinie od samego początku, gdy tylko usłyszałem o złączeniach tabel. De facto iloczyn kartezjański oznacza brak wskazania warunku złączenia, zatem łączenie dwóch tabel w ten sposób powoduje wygenerowanie znacznej liczby rekordów, dla każdego możliwego złączenia wartości z tabeli A z wartością z tabeli B.

Na przykład wyobraź sobie: tabela IMIONA to wykaz 5 imion (tylko jedno pole), tabela NAZWISKA to wykaz 10 nazwisk (tylko jedno) pole to: 

Select * from IMONA, NAZWISKA; 

Wygeneruje zestaw par: imię plus nazwisko, 50 takich par będzie.  Jak potrzebujemy dużej liczby danych, to jest to podstawowe  zastosowanie tego typu złączenia wskazane w literaturze. 

Tu pod linką masz kwerendę tworzącą ww. tabele, przykładowe dane i  możesz zobaczyć przykładowe 50 par imion z nazwiskami.

  

Marcinie, nie ma co ukrywać, iż złączenia -> iloczynu kartezjańskiego dotychczas nie używałem, aż… odkryłem, iż w ten sposób, przy jego pomocy mogę uzupełnić brakujące stany (najczęściej zerowe) w tabelach, które mają daty i salda, ale nie mają dat i sald jeszcze nieistniejących oraz już nieistniejących. 

Tu powrócę to problemu, zadania już przeze mnie raz opisanego w poście:

„Samozłączenie… Tymrazem popracujemy na jednej tabeli, to nie pomyłka :-)” 

W sumie po to go wyprodukowałem ten post, by też móc do niego sięgać. Tym razem w celu rozwiązania zadania generuję kompletny wykaz par danych: data stanu i kontrakt.  Do tak utworzonego wykazu dociągam łącząc po dacie stanu (z plusikiem) i po numerze kontraktu (z plusikiem) rzeczywiste salda, ale w przypadku braku salda w danej dacie, dla danego kontraktu wstawiam zero korzystając z funkcji NVL.

Tu też zastosuję nową technikę, utworzyłem na wykorzystując tabelę ROZLICZENIA i zgodnie z powyższym WIDOK, do którego sięgam by obliczyć salda. I mam zmianę sald pomiędzy stanem na koniec 2012 a stanem na koniec 2010, spłaty, bez kombinowania, bez uważania na feler „brak rozliczonych kontraktów w danej dacie”. Bo WIDOK nie ma feleru braku sald zerowych, WIDOK ma nie tylko salda zerowe już rozliczonych kontraktów, ale ma też salda zerowe dla kontraktów jeszcze niezawartych :-)

Tutaj kwerendy, rozwiązanie, przy czym jeżeli jesteś stałym czytelnikiem (mam nadzieję) to tabelkę ROZLICZENIA wraz z danymi możesz już mieć. Zacznij wówczas analizę od KROKU 3.

Dodatkowo zamieszczam obraz dokładnie ilustrujący etapy rozwiązania, naprawdę się starałem i warto go zobaczyć, jeden z lepszych jakie tu zamieszczam na blogu :-)

 

W. 

DUAL, co to jest … i przy okazji MONTHS_BETWEEN

Select * from DUAL;

I już widzisz co to jest. To ma baza produkcji ORACLE. Jest to tabelka, która ma jedno pole DUMMY, jeden rekord, a w nim X. Po co to?

Bo jak wiesz konstrukcja ogólna, bardzo uproszczona zapytania do bazy jest następująca: SELECT ……. FROM NAZWA_TABELI;

Jeżeli chciałbyś na przykład dostać tą drogą wynik takiego działania: 2 + 4 * 12 to jak? No to piszemy:

SELECT 2 + 4 * 12 FROM ….. Jaką podasz nazwę tabeli, jak podasz jakąś dowolną to wynik działania wyświetli się tyle razy ile jest rekordów w tabeli, a Ty chcesz tylko wynik działania i to raz. No to ktoś wpadł na pomysł utworzenia tabelki DUAL i to zawsze do niej można się w takich sytuacjach odwołać, wynik będzie tylko raz.

Na obrazku zaprezentowałem Ci Marcinie jak wygląda DUAL -> zapytania i wynik z numerem  1. Wykorzystanie tabeli DUAL do uzyskania wyniku działania to zapytanie i wynik z numerem 2.  I jeszcze jeden bajer, który jest dostępny na bazie ORACLE.

Czy wiesz ile jest miesięcy pomiędzy 12 grudnia 2001 a 3 lutym 2015. I nie o to chodzi by obliczyć liczbę dni i później dzielić przez … no właśnie, przez 30 czy przez średnią liczbę dni w miesiącu. Wiemy przecież, iż pomiędzy 30 listopadem 2014 a 31 marca 2015 roku są cztery miesiące i tyle. To na bazie ORACLE działa funkcja MONTHS_BETWEEN, która policzy miesiące pomiędzy datami właśnie tak jak my.

I ile jest tych miesięcy pomiędzy ww. datami …. Prawie 166, zapytanie w i wynik z numerem 3 na obrazku :-)

A po co to, po co takie „szczególarstwo”. Ano po to, iż wyobraź sobie, iż w ustawie masz napisane: termin na ….. 3 miesiące od daty stempla pocztowego. To by sprawdzić czy jesteś w terminie to nie możesz sobie dzielić liczby dni pomiędzy datami przez 30, bo to będzie szacunkowa liczba miesięcy, niejako „na oko” :-)

W. 

SQL Developer, enter nie działa, strzałki nie działają... co się stało z klawiaturą

Marcin, w szoku jestem. Skąd Ty żeś wygrzebał SQL Developera w takiej wersji? Zdecydowanie polecam aktualizację i używanie wersji  3….
W starszych wersjach to fakt, spotyka się taki feler, iż nie wiem skąd, dlaczego przestaje działać ENTER, strzałki…. No klawiatura nie zachowuje się tak jakbyśmy chcieli. To spotkało mnie na pewno ze dwa, trzy razy :-)
Szukałem pomocy wśród informatyków, a Ci rozwiązali problem, przy pomocy GOOGLE. Starsze wersje SQL Developera tak mają. By ten feler polegający na niedziałaniu klawiatury we właściwy sposób usunąć należy po kolei, zgodnie z obrazkiem:
1 Przejść / kliknąć zakładkę TOOLS 
2 Wybrać Preferences 
3 Wybrać Accelerators (w nowszej wersji SQL Developera tego nie znalazłem)
4 Wybrać LOAD PRESET 
5 Z dostępnych opcji wybrać DEFAULT 
6 OK., zaakceptować załadowanie DEFAULT i pozamykać.
Klawiatura powinna zadziałać poprawnie.
I zaktualizuj SQL Developera.  
W.

Insert, jak szybko skonstruować formułę i wstawić dane do bazy

Marcinie, rozumiem o co Ci chodzi. Masz dane w tabelce sporządzonej w excel  i chcesz je wstawić do bazy. Można próbować poprzez sterowniki podczepić tabelę, ćwiczyłem to swego czasu, może jednak później, zostawię to na inny wpis. Jeżeli masz do kilku tysięcy rekordów to polecam zwykły INSERT. Ten zwykły INSERT jest całkiem niezwykły :-) a przede wszystkim skuteczny.

Musisz wiedzieć do jakiej tabeli wstawić dane, jeżeli nie masz tabeli to musisz ją założyć. Formułę „INSERT” konstruujesz w excel wykorzystując jego funkcjonalność polegającą na możliwości wstawiania do tekstu wartości z komórek.
Jak to się robi dokładnie narysowałem na obrazku, przykład z tabelką PACJENCI i datami ich wizyt. W przypadku gdy zasilamy tabelę kompletnie, to jest wykaz wstawianych wartości jest zgodny z wykazem pól tabeli i w tej samej kolejności to wystarczy: 
insert into nazw_tabeli values ( wartości oddzielone przecinkami) 
Jeżeli jednak nie mamy wszystkich wartości, kolejność jest inna niż tabela na bazie, to wówczas trzeba podać nazwy pól po nazwie tabeli, zgodnie z tym co będziemy wstawiać, czyli 
insert into nazwa_tabeli (lista pól oddzielona przecinkami) values (wartości oddzielone przecinkami). 
Po skonstruowaniu formuły w excel kopiujemy zawartość komórki do SQL Developera (bo to nasz ulubiony klient do komunikacji z bazą) i uruchamiamy.

Na koniec COMMIT, zatwierdź transakcję, bo bez tego po wyłączeniu klienta, zamknięciu połączenia z bazą możesz się zdziwić :-)
I oczywiście pomoce: 

• Plik excelowy z danymi i formułami
• Skrypt sql – utworzenie tabelki i inserty skopiowane z excel 

Kiedy przydaje się klauzula UNION

Analityk danych korzysta z hurtowni, a w hurtowni… Nawet główne bazy  w firmach zmienia się. Może na przykład się zdarzyć, iż historia rozliczeń danego kontrahenta, ceny tego samego produktu etc. są na różnych schematach,  siłą rzeczy w różnych tabelach. 
Dane z systemów są zrzucane do hurtowni i tyle. Mało prawdopodobne jest by ktoś przygotował Ci dane w jednej tabeli, zresztą nie ma to sensu, bo trudno odgadnąć wszystkie przyszłe potrzeby.

UNION jest bardzo wygodny by poradzić sobie z połączeniem danych z różnych tabel, a dotyczących tego samego zagadnienia. 

UNION lub UNION ALL łączy wyniki dwóch i więcej zapytań w jedno, są pewne ale:

• finalne nazwy pól lub aliasów pochodzą z pierwszego zapytania, kolejne nie mają znaczenia
• liczba pól każdego zapytania łączonego musi być identyczna,
• format danych analogicznych pól w każdym zapytaniu łączonym (pierwsze pole z pierwszego zapytania i pierwsze pole z drugiego zapytania i tak dalej) musi być identyczny.

UNION – dublujące się rekordy występują raz, UNION ALL rekordy dublujące się są tyle razy ile wynika to z każdego zapytania. Przykład zawsze Marcinie działa najlepiej, zatem mamy następującą sytuację jak na obrazku. Dane o rozliczeniach na koniec 2008 i 2009 są w tabeli ROZLICZENIA_STARE, kolejne lata już są w nowej tabeli, w ROZLICZENIA_NOWE. I Twoim zadaniem jest podanie sumy należności i liczby kontraktów na koniec poszczególnych lat począwszy od końca 2009. Nie wolno Ci to zrobić kalkulatorkiem :-) 

Zadanie rozwiązuję dokładnie jak na drugim obrazku, a źródła do utworzenia tabel, ich zasilenia i kwerendę do wykonania zadania znajdziesz tutaj.