Pobieranie danych z bazy

Aby wejść w interakcję z bazą należy posiąść umiejętność zadawania precyzyjnych pytań. Pytania zadane w sposób nie precyzyjny nie dostarczą oczekiwanej odpowiedzi. W pewnym sensie pracując z bazą danych znamy w przybliżeniu odpowiedzi na nurtujące nas pytania. Taka wiedza pozwala nam weryfikować czy zadane przez nas pytanie było precyzyjne.

Zadawanie pytań w celu pobrania informacji z bazy odbywa się za pomocą dedykowanych programów
dla bazy Oracle mamy narzędzie SQL Plus lub SQL developer dla MSSQL jest SQL menegament studio, dla bazy mysql istnieje PHPMyadmin dla Postgressa istnieje podobny skrypt w języku PHP. Dla każdej bazy istnieje wiele narzędzi do komunikacji i ogólnie z odrobiną wyobraźni można stworzyć własne narzędzia.

Jeśli dysponujemy odpowiednimi narzędziami możemy zacząć zadawać pytania.

Podstawowe pytanie ma następujący format:

Pobierz + co + skąd czyli mówiąc zrozumiale dla bazy Select … from ,,,,
gdzie w miejsce kropek wstawiamy kolumny po przecinku a w miejsce przecinków nazwę tabeli bądź tabel.

Gdy nie znamy listy kolumn, zamiast kropek możemy wstawić gwiazdkę, w ten sposób pobierzemy wszystkie kolumny zadeklarowane w tabeli:

select * from tabela

Jeśli nie mamy rozpoznanej bazy, i mamy świadomość iż pracujemy na dużej bazie, warto nauczyć się od samego początku oszczędności. Wyniki zapytań można filtrować tak by dostać tylko to co nas interesuje. Używamy do tego klauzuli where.

select * from tabela where kolumna1=1

cofając się do rozpoznawania kolumn w tabeli zalecam używanie konstrukcji:

select * from tabela where 0=1

Powyższe zapytanie zwróci błyskawicznie listę kolumn. Jeśli chcemy zobaczyć tylko jakiś wycinek danych nie mając zbytnio idei po czym filtrować mamy pewien problem. Norma ISO i ANSII dla języka SQL nie definiuje takiego konstruktu i jest to zależne od producenta RDBMS.

W bazie MySQL można zrobić tak: select * from tabela limit 10
W MSSQL: select top 10 * from tabela
w Oracle: select * from tabela where rownum < 10 W innych systemach bazodanowych może się to odbywać na inne sposoby. We wstępie napisałem iż jedno zapytanie zwraca jeden widok danych w formie tabelarycznej. Tabele mają kilka właściwości których należy być świadomym. W każdej kolumnie znajdują się dane tego samego rodzaju. Wiersze w tabeli powinny być unikatowe. Mówiąc o rodzajach danych by nie wchodzić w informatyczne szczegóły trzeba umieć rozróżnić kilka podstawowych rodzajów: liczby,daty,napisy Dla każdego z tych typów są przewidziane pewne dozwolone operacje. Schodząc do poziomu pierwszej klasy szkoły podstawowej i działań na zbiorach: Do gruszek nie dodamy jabłek. I tak należy rozumieć rozróżnienie rodzajów danych. Każdy z wspomnianych typów ma wiele podtypów z różnymi właściwościami dla różnych RDBMS. Wszystkie typy natomiast kilka cech wspólnych. Każdy z typów może przyjąć wartość specjalną null - co oznacza wartość nie określoną. Czyli mówiąc kolokwialnie - PUSTO. Wstawienie wartości do kolumny odpowiedniego typu musi być zgodne z jej typem. Tak więc jeśli mamy zdefiniowaną kolumnę liczbową do zapewne znajdziemy tam tylko liczby. Chcąc sprawdzić jakiego typu są kolumny można sobie to wyklikać w większości programów do obsługi RDBMS, klikając w drzewku prawym przyciskiem na tabele (należy szukać pod Design albo Desc, Describe). Mówiąc o unikalności wierszy należało by wspomnieć iż większość tabel posiada jakiś identyfikator który określa konkretny wiersz. Takie pola najczęściej nazywają się : id, gid,gidnumer Wielu producentów oprogramowania stosuje sensowne nazewnictwo kolumn. Spotkałem się w sumie z dwoma systemami sensownego nazewnictwa: każda tabela posiada w nazwach kolumn stały przedrostek np: tabela klienci to pola nazywają się kli_id,kli_nazwa etc. Lub pola nazywane są po prostu id,nazwa itd. Ale w tabelach w których istnieją odwołania do takich wartości umieszcza się nazwy typu klienci_id. do dalszych przykładów załóżmy że mamy tabele przyjaciele która posiada następujące pola: id int, -- unikalny id - pole numeryczne imie varchar2(255), -- pole tekstowe plec int, -- 0 dziewczyna 1 facet -- wartosc numeryczna wiek int, -- wiek w latach -- wartosc liczbowa zarobki int, -- zarobki w tysiacach -- wartosc liczbowa kolor int); -- kolor skory 0 jasny 10 ciemny - skala -- wartosc liczbowa jeśli chcemy pobrać informacje o naszych znajomych
select * from przyjaciele

jeśli interesuje nas tylko info płci i imieniu to:

select plec,imie from przyjaciele

jesli chcemy zobaczyć tylko facetów to
select plec,imie from przyjaciele where plec=1

możemy tez filtrować po nie wybranych kolumnach

select imie from przyjaciele where plec=1

zapytanie musi pobierać co najmniej jedną kolumnę i nie koniecznie z tabeli

możemy też np dostawiać własne kolumny w ‘locie’

select 'jakis tekst',imie from przyjaciele

pojawią nam się imiona w 2 kolumnie a w pierwszej napis jakis tekst

Do kolumn możemy przypisywać aliasy czyli nadawać im nagłówki. Teraz warto zwrócić uwagę na problem rozróżniania wielkości liter (case sensitive). Jeśli chodzi o nazwy tabel i kolumn to bazą nie robi różnicy wielkość liter.

select IMIE,Imie,iMIE from PrZyjAciele

zwróci nam 3 razy imie kazdego z przyjaciol. Jednak ma to pewną nie miłą konsekwencję
w bazach MSSQL nazwy kolumn są zwracane albo tak jak je zapisano w selekcie, albo tak jak są w bazie. Z rozróżnieniem liter(co jest dość śmieszne dla bazy która ogólnie nie rozróżnia wielkości liter, ale o tym później).
Ma to znaczenie jeśli programujemy. Zwracając wyniki do tablic asocjacyjnych możemy się nie miło zdziwić.

W przypadku Oracle sprawa jest jasna i fajna. Nie ważne jak wpiszemy zwrotka przyjdzie zapisana dużymi literami.
Chyba że użyjemy aliasów

select imie nazwa from przyjaciele

to to samo co:
select imie as nazwa from przyjaciele

nazwa kolumny imie zostanie podmieniona na nazwa

na oraclu możemy zrobić jeszcze tak:

select imie "nazwa" from przyjaciele

wtedy nazwa zostanie zwrócona małymi literami.
Zabawa z aliasami ma pewien sens o którym później się rozwinę.

bardzo ważna uwaga:

select imie nazwa from przyjaciele where nazwa='janek'

nie zadziała!!! JEST TO BŁĄD. Aliasy nie są widzialne na poziomie danego zapytania.

Zapytania można agregować czyli robić zapytanie z zapytania, nie jest to zbyt optymalne ale czasem inaczej się nie da.

select * from (
select imie nazwa from przyjaciele
) c

w nawiasie zamiast tabeli podajemy zapytanie, które jakby w locie stanie się tabelą do dalszego zapytania.
Interesujące jest iż w takim wypadku aliasy z podzapytania staną się normalna nazwą kolumny dla zapytania zewnętrznego. Należy wspomnieć też o literce c która dodałem za nawiasem. Aliasy możemy przypisywać także tabelą( i powinniśmy je przypisywać podczas pisania dużych zapytań). Więc c jest aliasem na podzapytania. Niektóre RDBMS wymagają takiego zapisu w innych jest on nie wymagany jednakże poprawny.

select * from (
select imie nazwa from przyjaciele
) c where nazwa = 'janek'

jest poprawne.
Tylko jeśli korzystamy z MSSQL i w bazie mamy Janek to baza i tak zwróci nam wynik. W przypadku Oracle janka nam nie znajdzie.

Rozszerzeniem możliwości pobierania danych jest możliwość manipulacji na nich w locie.
Takim przykładem są funkcje. Jest ich dużo i w każdym systemie bazodanowym mogą być realizowane inaczej i inaczej się nazywać. Funkcje możemy podzielić. Najpopularniejsze to skalarne i agregujące. Te drugie zostały opisane w podstawach grupowania. Te pierwsze dla każdej podanej wartości zwracają jakąś konkretną wartość.
Każda funkcja przyjmuje określony rodzaj argumentów ( rodzaj danych).

select funkcja(kolumna) from tabela

dla obslugi ciągów tekstowych najpopularniejsze funkcje to substring,lower,upper,concat
substring -> wycina kawałek napisu
substring(nazwa_kolumny,start,długość) -> gdzie start to numer znaku od którego ma się zacząc wycinek licząc od lewej (od 1 znaku )
pod substring można podstawić też napis
substring(‘moj napis’,1,3) zwróci nam ‘moj’

dla kolumn liczbowych mamy: round,ceil,floor
czyli zaokrąglanie

na kolumnach liczbowych możemy wykonywać operacje arytmetyczne w zapytaniach

select plec+zarobki from przyjaciele

zapytanie jest bez sensu ale pokazuje iż można dodać do siebie wartość kolumn tego samego typu

select (plec+zarobki)/3 from przyjaciele

do pierwiastkowania i potęgowania istnieją odpowiednie funkcje. Praktycznie wszystkie funkcje znane z excela mają swoje odpowiedniki w SQL.

Możemy także wybiórczo coś pobierać z bazy:

select
case when plec=1 then 'facet' when plec=0 then 'kobieta' else 'obojniaczek' end plec
from przyjaciele

jest to instrukcja warunkowa która zamiast wartości liczbowych zwróci nam napisy
jej ogólna konstrukcja to
case when warunek logiczny then gdy prawda else gdy powyższe fałsz end
sekcji when then moze byc wiele jak w przykładzie. W przypadku skomplikowanych case należy pamiętać iż zostanie zwrócony pierwszy prawdziwy wynik i case jest wykonywane w kolejności definicji.

Wstęp do SQL

SQL – jest strukturalnym językiem zapytań, tak przynajmniej wygląda jego skrócona definicja. Patrząc globalnie język ten w pewnych środowiskach (W bazach danych) pozwala nam na operowanie na dużych ilościach danych. W tej części kursu omówione zostaną możliwości tego języka przy pobieraniu i analizie danych od bazy.

Jak wspomniałem SQL to język zapytań. Interakcja z bazą odbywa się na zasadzie zadawania pytań i dostawania odpowiedzi w formie tabelarycznej.

Zapraszam do przeczytania rozdziału o pobieraniu danych.

o modelu relacyjnym i złączeniach w odzieżowej pigułce

Moim zdaniem kwintesencją baz danych są właśnie złączenia. Możliwość łączenia ze sobą różnych danych w logiczną całość. By zrozumieć złączenia trzeba zrozumieć logikę relacyjnych baz danych. W tej tematyce powstało wiele książek opartych o wizję w sumie jednego człowieka (Mr. Codd). Jeśli by spojrzeć na temat relacyjności w sposób dość uproszczony tak by przedstawić to w maksymalnej pigułce należy przekazać min. następujące informacje:

zbiór danych – przysłowiowy worek – nazywany dumnie encją lub po prostu tabelą – jest układem n x m.
Mamy n kolumn i m wierszy. Kolumny są poddane pewniej normalizacji. Ze względów praktycznych w danej kolumnie przechowuje się dane jednego rodzaju (typu) i wykorzystywane do tych samych rzeczy. W relacyjnym modelu wartość wiersza w kolumnie powinna być informacją atomową (czyli nierozbijalną). Patrząc od strony wierszy na tabelę musimy uzyskać ich (wierszy) niepowtarzalność.

Zostając chwilę przy niepowtarzalności danych należy spojrzeć na temat trochę bardziej globalnie. Jeśli weźmiemy pod uwagę wszystkie nasze zbiory w obrębie jednej bazy powinniśmy dążyć do minimalizowania powtórzeń. Ta minimalizacja powtórzeń ma swoją fachową nazwę pt. normalizacja. Do tej pory wymyślono kilka postaci normalizowanych z czego 3 są powszechnie używane. Możemy na normalizacje spojrzeć jak na pewnego rodzaju skalę gdzie 1 w obecnych standardach systemów transakcyjnych oznacza porażkę, a 3 jest oceną przesadną (niby od przybytku głowa nie boli, ale co za dużo to nie zdrowo). Większość systemów transakcyjnych, czyli użytkowych ocenia się na przysłowiowe 2,5. O normalizacji kiedyś napiszę osobną notkę, bo jest to dość głęboka sprawa.

Z powyższego akapitu należy zapamiętać iż w relacyjnych bazach danych staramy się dążyć do niepowtarzalności danych.

Takie myślenie wymusza na nas pewną logikę działania.

Jeśli posiadamy logiczne zbiory np. przyjaciół, klientów, skarpetek to rzeczy wrzucone do takiego worka mają jakieś wspólne cechy, po których przez nadanie im konkretnych wartości możemy zdefiniować iż jest to jakiś konkretny przedmiot (np. skarpetka).
Skarpetki są dość fajnym przykładem, z tego względu iż trzymamy je razem w jednej szufladzie, kartonie, skrzynce.
Patrząc na szufladę ze skarpetkami możemy im przypisać pewne właściwości: kolor, rozmiar, materiał, współczynnik zużycia ;-), lewa, prawa.

Definicja wspomnianych atrybutów definiuje nam konkretną parę skarpetek lub konkretną skarpetkę. No chyba, że kupiliśmy w makro zgrzewkę takich samych. A załóżmy, że tak się stało. Podpiszmy skarpetki markerem.
Napis stanie się naszym kluczem unikalnym. Kluczem głównym, czyli to co jednoznacznie określa nam że ta skarpetka w mojej ręce to jest właśnie ta. Jeśli mamy same białe skarpetki to prosząc kogoś o podanie białej skarpetki mamy wysoce dużą szanse iż nie otrzymamy tej, którą mieliśmy na myśli.

Tak samo mamy w bazie danych, gdzie każdy przedmiot w worku ma swoje atrybuty i numer, który go jednoznacznie identyfikuje. Oczywiście da się odejść od tej reguły (tylko po co?). Możemy się przy okazji dopuścić pewnej dawki filozofii, czy każda skarpetka powinna mieć swój nr? czy tylko każda para? W bazie danych powinniśmy każdą skarpetkę oznaczyć osobno i nadać jej atrybut z oznaczeniem pary. W ten sposób możemy sięgnąć konkretną skarpetkę z szufladki jak i od razu całą parę.

Trochę daleko nam od tematu złączeń, ale na przykładzie skarpetek w naszej wyobraźni możemy utworzyć worek z majtkami, stanikami, spódnicami i koszulkami.

Kompletując ubiór, zwłaszcza Panie zwrócą uwagę na kolorystykę. Zakładając dość częstą poranną sytuację pt w co mam się ubrać. Przeciętna kobieta chciała by uzyskać zbiory ubrań, które do siebie pasują. A że baza danych lubi odpowiadać na dobrze zadane pytania, z chęcią zwróci nam odpowiedź.

Pytając w co mam się ubrać
Otrzymamy dość żartobliwą odpowiedź, możesz założyć wszystko co masz w szafach.

select * from majtki,skarpetki,koszulki,spodnice,staniki;

To co otrzymaliśmy nazywa się w niektórych mniej humanistycznych kręgach iloczynem kartezjańskim. Czyli każdy element został powiązany z każdym z innego worka.
Po 2 rzeczy w każdym worku dostaniemy 2 do potęgi 5 (worków) wyników (dla czepialskich skarpetki są zwinięte razem i parę traktuję jako jedną rzecz). Taka forma pobierania danych nie jest zbyt szczęśliwa i nie jest raczej stosowana poza przypadkami gdy chcemy wygenerować wszystkie możliwe scenariusze.

Frustrując się nieco możemy zadać kolejne pytanie.

Co mogę ubrać białego ?

select * from majtki,skarpetki,koszulki,spodnice,staniki
where majtki_kolor = 'bialy' and skarpetki_kolor='bialy'
and koszulki_kolor = 'bialy' and spodnice_kolor= 'bialy'
and stanik_kolor='bialy'

Nie znam się aż tak na kobiecych garderobach, ale wydaje mi się że białe spódnice nie są zbyt popularne.
Jeśli więc nie posiadamy białej spódnicy to baza odpowie, że nie znalazła kompletu, który by spełniał pokładane oczekiwania. Możemy dopuścić się pewnej modyfikacji:

select * from majtki,skarpetki,koszulki,spodnice,staniki
where majtki_kolor = 'bialy' and skarpetki_kolor='bialy'
and koszulki_kolor = 'bialy' and spodnice_kolor in ('bialy','czarny','szary')
and stanik_kolor='bialy';

w ten sposób otrzymamy iloczyn kartezjański wszystkich białych rzeczy dorzucając czarne i szare spódnice. Jest w czym wybierać. Domyślam się iż kobiet to nadal nie zadowoli.

Zostawiając wybór całościowy, możemy na chwilę skupić się na mniejszym problemie. Można by pomyśleć o bieliźnie. Załóżmy, że omawiana kobieta kupuje markową bieliznę w kompletach. Nasze komplety leżą w osobnych workach, ale posiadają identyfikator kompletu.

select * from majtki,staniki
where majtki_komplet= statniki_komplet;

W wyniku otrzymamy bieliznę z tych samych kompletów. Na tym etapie warto by wprowadzić szerszy bardziej czytelny zapis:

select *from majtki
inner join staniki on
staniki_komplet = majtki_komplet

Osobiście najbardziej preferuję tą formę zapisu jednak dla większości RDBMS nie robi to żadnej różnicy.
Istnieje jeszcze zapis pośredni:

select * from majtki
join staniki on
staniki_komplet = majtki_komplet

Dla czytelności kodu lepiej jednak stosować mój ulubiony zapis.
Łączenia, które przedstawiłem nazywają się ogólnie złączeniami wewnętrznymi.
W powyższym przykładzie z majtkami i stanikami, jeśli założymy że numerujemy z jednej puli komplety i za komplet uważamy także rzeczy kupione pojedynczo, to w wyniku zapytania znikną nam wszystkie rzeczy bez kompletu. Można by też w ogóle nie oznaczać rzeczy niekompletowych, tylko wtedy albo byśmy uzyskali jeden wielki komplet np. z numerem 0 ;-), albo wartości null, które są przeze mnie bardzo nielubiane, bo w pewnych sytuacjach mogą niekorzystnie zmieniać nam wyniki zapytania. Na szczęście w większości RDBMS (nie znam wszystkich) wartości null nie idzie do siebie przypisać, w sensie da się to zapisać ale operacja logiczna nie powinna zwrócić prawdy.

Skoro istnieją złączenia wewnętrzne to istnieją pewnie złączenia zewnętrzne.
Zewnętrzne złączenia są bardzo użyteczne (i drogie) pozwalają one pokazać niedopasowane rzeczy.

I teraz o ile:
select *from majtki
inner join staniki on
staniki_komplet = majtki_komplet

i

select *from staniki
inner join majtki on
staniki_komplet = majtki_komplet

niczym w wyniku się nie różni, o tyle w złączeniach zewnętrznych kolejność łączenia ma znaczenie dla wyniku końcowego.

Więc zakładając iż w pewnych sytuacjach kobieta jest skłonna zrezygnować ze stanika możemy wytworzyć takie zapytanko:

select * from majtki
left outer join staniki on
staniki_komplet = majtki_komplet

zapytanie wyświetli nam listę wszystkich dostępnych majtek oraz dostawi próbując dopasować komplety staniki.
W miejscach gdzie nie powstało dopasowanie zostanie wyświetlona wartość null. Tak więc zobaczymy komplety plus zdekompletowane mówiąc kolokwialnie gacie.

Na obecnym etapie należy wspomnieć o możliwościach filtrowania zapytań łączonych.

Otóż

select * from majtki
inner join staniki on
majtki_komplet = staniki_komplet
where majtki_kolor = 'bialy'

jest dokładnie tym samym co:

select * from majtki
inner join staniki on
majtki_komplet = staniki_komplet
and majtki_kolor = 'bialy'

przynajmniej dla Oracle (przetestowane). Przy bardziej rozbudowanych zapytaniach i wielu złączeniach w MSSQL 2005 przerzucenie warunku joinowania pomiędzy inne tabele potrafi bardzo niekorzystnie wpłynąć na efektywność zapytania, Oracle wydaje się być na to odporne (ale muszę to potwierdzić).

Oczywistym powinno być iż złączenia są najefektywniejsze gdy są wykonywane na kolumnach objętych indeksami.
Ale sama indeksacja i optymalizacja jest osobnym tematem.

Przykład o którym mówię:

select * from tab1 t1
inner join tab2 t2 on
t1.id = t2.id
inner join tab3 t3 on
t2.id_f = t3.id
inner join tab4 t4 on
t3.id_f = t4.id
and t1.id_d = t2.id_d -- ta linia powinna być w pierwszym joinie.

takie zapytanie jest bardzo niekorzystne. Warunek oznaczony komentarzem powinien znaleźć się w pierwszym joinie. Napisałem że Oracle wydaje się być na to odporny. Wyznaczając plany dla różnych zapytań nie widać różnicy, jednak nie operowałem od dawna na przykładzie, który mógłby wykazać zauważalną różnicę.

W przypadku złączeń zewnętrznych warunki joinowania są rozróżniane z filtrami

select * from majtki
left outer join staniki on
majtki_komplet = staniki_komplet
where staniki_komplet is null

jest czymś innym niż

select * from majtki
left outer join staniki on
majtki_komplet = staniki_komplet
and staniki_komplet is null

pierwsze zapytanie zostanie wyfiltrowane do wierszy bez złączenia. A drugi przypadek zostanie potraktowany jako warunek łączenia i pokaże wiersze dla których staniki_komplet mają wartość null przed joinowaniem.

Napisałem też ze złączenia zewnętrzne są drogie. Ich koszt jest relatywnie większy (czasem nawet o setki procent) w stosunku do złączeń wewnętrznych. Dlatego warto przemyśleć strukturę zapytania by uniknąć złączeń zewnętrznych. O ile na etapie pobierania danych często dość trudno coś wykreować o tyle w trakcie definiowania struktury bazy warto zwrócić na to uwagę.

Istnieje także right outer join, który jest odwrotnym zestawieniem w stosunku do left outer join. Z moich obserwacji jest on mniej wydajny niż left outer join. Jeśli jestem zmuszony korzystać ze złączeń zewnętrznych staram się konstruować tak zapytanie by używać left outer join.
Right outer ma także dużą tendencję do generowania dużych wyników. Dla osób bez wprawy niezalecane jest pisanie takich zapytań na bazach transakcyjnych produkcyjnych (nie zalecane przeze mnie).

Współczesne bazy nie posiadają raczej rażących ograniczeń w ilości joinów.

Czego nie można powiedzieć o łączeniach typu union. Unie to łączenie wyników dwóch zapytań o takiej samej ilości kolumn. Dodatkowym ograniczeniem unii jest fakt iż kolumny muszą być tego samego typu.

select 'majtki',majtki_kolor from majtki
union all
select 'staniki',staniki_kolor from majtki

powyższy przykład jest mega trywialny, ale obrazuje zasadę działania. W jednym zapytaniu możemy mieć maksymalnie 255 unii (w MSSQL i ORACLE). Dodatkowo Unie uchodzą za bardzo kosztowne dla optymizerów.
I w wielu wypadkach da się obejść bez nich. Istnieje także rozwiązanie polegające na umieszczaniu wyników zapytań w tabelach tymczasowych.

Samych złączeń jest wiele więcej, opisałem najważniejsze i najczęściej wykorzystywane. Zrozumienie zagadnień związanych z opisanymi złączeniami oraz z wiedzą dotyczącą grupowania pozwala budować zapytania, które mogą być działającymi i efektywnymi raportami.

Na koniec należy się kilka słów o modelu relacyjnym. Skoro możemy efektywnie łączyć dane możemy je rozbijać by unikać ich powtarzalności. Idealnym przykładem jest zapis faktury w RDBMS.
Gdyby nie normalizować zapisu w każdym wierszu tabeli posiadalibyśmy wszystkie informacje dotyczące kontrahenta, konkretnej pozycji itd. Taki zapis jest opisany jako 1NF (1 stopień normalizacji). W takim wypadku nie ma potrzeby złączeń.
Bardziej efektywnym zapisem jest rozróżnienie danych na informacje o nagłówku i pozycjach. Mamy dwie osobne tabele powiązane identyfikatorem faktury. Dokonując złączenia uzyskamy wynik jak w 1NF. Taką sytuację nazywamy 2NF (2 stopień normalizacji). Tworząc kolejne relacje np. do tabeli z informacjami o towarach na fakturze oraz informacjami związanymi z kontrahentami otrzymamy 3NF (najbardziej pożądany przez teoretyków 3 stopień normalizacji).

Jednak teraz trzeba zadać sobie pytanie: Co się stanie jak klient zmieni swoje dane w 3NF ? Np. się przeprowadzi?
Wtedy aktualizując dane na karcie kontrahenta zmienimy zapis faktury, dlatego też napisałem że 3NF niekoniecznie jest najlepszym rozwiązaniem.

Filtrowanie w zapytaniach grupujących

W poprzednim poście poruszony został wątek grupowania. Warto też pomyśleć o możliwościach filtrowania takich zapytań w celu prezentacji wyników pod użytkownika. Tak aby zapytanie wróciło tylko te wiersze, o które chodzi użytkownikowi.

Mamy kilka opcji do wyboru w zależności od oczekiwanego efektu. Postaram się najpierw logicznie opisać jak to działa i przedstawię proste przykłady.

Pierwszą możliwością filtrowania jest klauzula where, która jak w przypadku normalnego zapytania pozwoli nam na wycięcie pewnych wierszy z wyniku. Jednak należy być ostrożnym. W zapytaniu grupującym where jest filtrem, który działa przed agregacją danych. Jeśli chcemy filtrować wyniki po zgrupowaniu to w języku SQL istnieje jeszcze klauzula HAVING, jest to filtr dla agregatów.

Operując na tabeli przyjaciele z postu o podstawach grupowania możemy chcieć sprawdzić czy mamy wśród naszych przyjaciół co najmniej 10 osobowe grupy osób o tym samym imieniu:

select imie,count(imie) form przyjaciele
group by imie
having count(imie)>=10

Jeśli chcemy sprawdzić jak wyglądają zarobki najbogatszych naszych kolegów i koleżanek możemy przeprowadzić coś takiego:

select avg(zarobki) from przyjaciele
where zarobki > 10000
group by plec

w wyniku dostaniemy średnią zarobków większych od 10k z uwzględnieniem płci.

Możemy też sprawdzić czy imiennicy mogą zarabiać razem więcej:

select imie,avg(zarobki) from przyjaciele
where zarobki > 1000
group by imie
having count(imie) > 10

zapytanie najpierw odsieje osoby, które zarabiają mniej niż 10k, a później zliczy agregacje i odsieje worki z imionami, które zawierają mniej niż 11 przyjaciół.

Teraz trochę bardziej skomplikowany temat, otóż jak wyciągnąć przyjaciół, którzy zarabiają więcej niż średnia?
Można by spróbować:

select sum(zarobki) from przyjaciele
having zarobki > avg(zarobki)

ale to nam niestety nic nie zwróci (przynajmniej w oracle), pokaże się błąd agregacji.
Gdy zmienimy na:

select sum(zarobki) from przyjaciele
group by zarobki
having zarobki > avg(zarobki)

nie pokaże błędów, ale też nic nie zwróci.

Musimy niestety (lub stety) skorzystać z podzapytania:

select sum(zarobki) from przyjaciele
where zarobki > (select avg(zarobki) from przyjaciele)

ciekawostką jest wynik zapytania:

select sum(zarobki) from przyjaciele
having zarobki = avg(zarobki)

który zwraca wszystkie wiersze

Czasem zachodzi konieczność przefiltrowania wyników już zagregowanych w sposób możliwy do zrealizowania przez where.

select imie,count(imie) imiona from przyjaciele
where imiona > 5

to zapytanie również wyświetli błąd i jest to błąd popełniany dość często przez niedoświadczone osoby. Powinienem napisać o tym w sumie na początku przed opisem HAVING, ale chciałbym uniknąć takiego rozumowania.

W takim zapytaniu możemy wykorzystać wspomniane having lub zrobić z tego podzapytanie i użyć filtru where

select * from (
select imie,count(imie) imiona from przyjaciele
group by imie
) jakis_alias
where imiona>5

Takie zapytanie jest już poprawne. Pozostaje tylko kwestia dyskusji nad optymalnością i kosztem takiego rozwiązania.

Warto zwrócić także uwagę na podzapytania agregujące w selekcie:

select zarobki-srednia from (
select imie, zarobki, (select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ) srednia from przyjaciele p2
) x

Nie jest to może zbyt szczęśliwy przykład, ale pokazuje możliwości.

W podzapytaniu grupującym, a właściwie na wynik takiego podzapytania w locie można nakładać funkcje arytmetyczne, np. zaokrąglenie

select zarobki-srednia from (
select imie, zarobki, round((select avg(zarobki) from przyjaciele p1 where p1.plec = p2.plec ),1) srednia from przyjaciele p2
) x

Podstawy grupowania

Ostatnio na grupie dyskusyjnej pl.com.bazy_danych przewinął się wątek grupowania, wielu początkującym osobom grupowanie sprawia wiele problemów. Z resztą zaawansowanym użytkownikom też zdarzają się wpadki.

Tak więc zaczynając od początku czym jest grupowanie?

Gdy w jednej tabeli mamy kolumny o różnej ziarnistości, np. unikalną wartość klucza głównego i np. typ dokumentu lub płeć lub kolor.

weźmy za przykład tabele przyjaciele

create table przyjaciele (
id int, -- unikalny id
imie varchar2(255),
plec int, -- 0 dziewczyna, 1 facet
wiek int, -- wiek w latach
zarobki int, -- zarobki w tysiącach
kolor int); -- kolor skory 0 jasny 10 ciemny - skala

Przyjmijmy że mamy ok. 1000 przyjaciół i chcielibyśmy przeprowadzić analizę naszych znajomych korzystając z grupowania.

Co da nam grupowanie? Pozwoli nam na wrzucanie do jednego worka określonej grupy.
Podchodząc dość rynkowo do naszych przyjaciół moglibyśmy sprawdzić jaki jest ich średni status finansowy

select avg (zarobki) from przyjaciele;

dostaniemy jeden wiersz zawierający ogólną informację ze średnią zarobków naszych kamratów.
Jeśli chcielibyśmy sprawdzić czy lepiej zarabiają nasze koleżanki czy koledzy należy wykorzystać
klauzulę group by i pogrupować dane według płci.

select avg(zarobki) from przyjaciele
group by plec;

w wyniku otrzymamy dwa wiersze ze średnimi wartościami, nie wiemy jeszcze kto jest lepszą grupą.

select avg(zarobki),plec from przyjaciele
group by plec;

teraz dostaniemy informacje o zarobkach per płeć.

Ciekawą sprawą jest dodawanie stringa do zapytania. Normalnie każda kolumna wymieniona w sekcji select nie objęta funkcją agregującą powinna znaleźć się w sekcji group by. Jednak jeśli wytwarzamy sami jakąś kolumnę nie powiązaną z danymi (string,data itp) nie podlega ona tej regule.

select 'moi znajomi srednio zarabiaja ' || count(avg) from przyjaciele

Warto też zwrócić uwagę na znak concatenacji(konkatenacji?), według ANSII (albo tego co udało mi się znaleźć) to znak ten nie jest objęty żadną normą. I tak w Oracle mamy || w MSSQL + a w MySQL można zapisać po prostu dwa stringi obok siebie
‘napis 1’ ‘ napis 2’. Jeśli komuś leży na sercu przenośność kodu (albo korzysta z wielu RDBMS) polecam funkcję concat.

Wracając do grupowania. W powyższych przykładach korzystaliśmy albo z worka zwanego przyjaciółmi, albo rozbijaliśmy nasz worek na dwa mniejsze worki naszych przyjaciół segregując ich po płci.

W jednym zapytaniu możemy korzystać z wielu funkcji agregujących i z wielu kolumn grupujących,
tak więc:

select avg(zarobki),avg(wiek),plec from przyjaciele
group by plec;

dodatkowo możemy podejrzeć średni wiek naszych przyjaciół:

select avg(zarobki),max(zarobki),min(zarobki),avg(plec) from przyjaciele;

teraz możemy przejrzeć zakres zarobków ich średnią i sprawdzić czy mamy więcej przyjaciół czy też przyjaciółek.

select max(zarobki) from przyjaciele
group by kolor,plec

teraz mamy pewna wariacje,
w wyniku dostaniemy ilość wierszy odpowiadającą iloczynowi ilości koloru i płci w tabeli
czyli maksymalnie w naszym przykładzie 20 (zakładając że mamy 2 płci i 10 kolorów).

Właściwość ta jest często dość przykrą konsekwencją i wymusza często stosowanie podzapytań i złączeń składających się z podzapytań w celu uniknięcia błędnego grupowania (zmiany ziarnistości wyniku).

Grupowanie możemy przeprowadzić także po pewnej operacji na kolumnie, np.

select count(*),imie from przyjaciele
group by imie

zwróci nam ilość przyjaciół o określonym imieniu.
Jeśli chcielibyśmy zobaczyć jak się rozkłada ilość przyjaciół, których imię zaczyna się na określoną literę
możemy przeprowadzić coś takiego:

select count(*),susbstr(imie,0,1) from przyjaciele
group by susbstr(imie,0,1)

jednak:

select count(*),susbstr(imie,0,2) from przyjaciele
group by susbstr(imie,0,1)

zwróci nam już błąd co jest nawet logiczne, ale:

select count(*),susbstr(imie,0,1) from przyjaciele
group by susbstr(imie,0,2)

też nam zwróci błąd co niekoniecznie musi być logiczne, tak więc trzeba uważać. Powyższe przykłady działają na Oracle w wersji 10G. Nie wydają mi się zgodne z ANSII (muszę sprawdzić), mile widziany feedback czy to działa na innych bazach.

W temacie grupowania istnieje także wątek unikalnych wartości. Grupując po jednej kolumnie dostaniemy jej unikalne wartości:

select imie from przyjaciele
group by imie

taki sam efekt tylko teoretycznie niższym kosztem uzyskamy

select distinct imie from przyjaciele

Na tabeli z kilkoma tysiącami rekordów na nie indeksowanych polach nie ma praktycznie żadnej różnicy w koszcie tych dwóch zapytań (na danych z różną ziarnistością).

Oczywiście grupowanie może być użyte przy wykonywaniu złączeń. Wszystkie zachowania pozostają zgodne z powyższymi założeniami.

zagadka – zapytania skorelowane ciąg dalszy

Na jednym z blogów znalazłem ciekawą zagadkę:

zagadka o bazach

SELECT
*
FROM
tab_a
WHERE
pole_a IN ( SELECT pole_a FROM tab_b );

Co wyświetli takie zapytanie?

Jak na ironie wyświetli wyniki i to niestety na pierwszy rzut oka sensowne.

Jednak jeśli się przyjrzeć widać w tym zapytaniu duży błąd.

Dzięki mechanizmowi zapytań skorelowanych mamy możliwość łączenia wyników zapytania z podzapytaniem. Jednak w tym wypadku mamy do czynienia z czymś na wzór:

select ‘cos’ from tabela

zapytanie zwróci n wierszy z ‘cos’ gdzie n to ilość wierszy w tabela.

Jednakże bardziej roztropni zrobią coś takiego:

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT a.pole_a FROM tab_b b );

i teraz błąd już łatwo dostrzec, ale nadal zapytanie wykona się prawidłowo.

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT b.pole_a FROM tab_b b );

Dopiero teraz dostaniemy błąd (o ile pole_a nie istnieje w tab_b)

Teraz mała kwestia optymalizacyjna

spotkałem się z tym iż w podzapytaniu funkcji in () niektórzy używają distincta.

select a from tab where tab_a in (select distinct b from tab_b )

Jest to kompletnie bez sensu, w przypadku baz Oracle hasło distinct zostanie pominięte i nie wpłynie na koszt zapytania.

Ale jeśli ktoś wpadnie na pomysł

select a from tab where tab_a in (select b from tab_b
group by b)

to koszt zapytania już wzrośnie.

Left outer join vs zapytanie skorelowane

Dość ciekawym polem do optymalizacji zapytań jest możliwość wykorzystania zapytań skorelowanych.

select col_a, col_b from tab_a a
left outer join tab_b b on
a.id = b.id

vs

select col_a, ( select col_b from tab_b b where a.id=b.id) from tab_a a

W przykładzie testowanym na bazie produkcyjnej okazało się że użycie zapytań skorelowanych dało 18% zysku w stosunku do left outer joinów. W testowanym przykładzie była joinowana ta sama tabela (słownikowa) 5 razy i za każdym razem były z niej pobrane 3 kolumny.

Widać iż ilość kolumn potrzebnych w zapytaniu finalnym będzie miało wpływ na koszt zapytania, jednakże w niektórych przypadkach może to być całkiem ciekawy sposób na optymalizacje.

Jak twierdzi Dan Tow w swojej książce SQL optymalizacja, opłaca się optymalizować od zysku 20% w górę, jednak osobiście uważam że w skali całej aplikacji z ziarenek może uzbierać się znaczna różnica w wydajności.