Trzecia i ostatnia część cyklu poświęconego podstawom języka SQL. W pierwszej i drugiej części stworzyliśmy polecenie SQL, które na podstawie zdefiniowanych kryteriów wyszukuje w bazie danych stanowiska i powiązane z nimi fakty kulturowe (określenie okresu dziejów, jednostki kulturowej, funkcji a także znalezionych śladów działalności człowieka). To polecenie ma następującą postać:
SELECT S.ID, S.OBSZAR_AZP,
S.NR_AZP, F.FUNKCJA FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO WHERE S.OBSZAR_AZP = '55-12' AND F.KULTURA IS NOT NULL AND S.DATA_BADAN >= '2012-01-01' AND S.DATA_BADAN <= '2012-12-31'
Wskazujemy w nim, że w zestawieniu ma znaleźć się identyfikator stanowiska, numer obszaru AZP, numer stanowiska w ramach obszaru AZP - te atrybuty są pobierane z tabeli Stanowiska oznaczonej literą 'S' oraz fukcję stanowiska - informacja zapisana w tabeli FaktyKulturowe. Pod uwagę są brane stanowiska z obszaru '55-12', które mają określoną funkcję i zostały przebadane między 1 stycznia 2012 i 31 grudnia 2012 roku.
Istotą wielu zestawień są obliczenia, które można także wykonywać przy pomocy języka SQL. Służą do tego tak zwane funkcje agregujące. Nazywamy je w ten sposób, ponieważ zasadą ich działania jest to, że zbierają wyniki z wierszy, które spełniają podane warunki i przetwarzają je w określony sposób.
Przykładem funkcji agregującej jest funkcja COUNT(nazwa_atrybutu), która oblicza ile razy pojawiła się wartość w kolumnie, której nazwę wpisaliśmy między nawiasami okrągłymi (w miejscu oznaczonym jako nazwa_atrybutu). Na przykład, jeżeli w poleceniu SQL użyjemy funkcji COUNT, a w jej parametrze podamy kolumnę id z tabeli stanowiska, to policzy wszystkie wiersze, które mają w tej kolumnie jakąś wartość.
SELECT COUNT(S.ID) FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO WHERE S.OBSZAR_AZP = '55-12' AND F.KULTURA IS NOT NULL AND S.DATA_BADAN >= '2012-01-01' AND S.DATA_BADAN <= '2012-12-31'
Wynik powyższego polecenia można zinterpretować jako liczbę stanowisk zapisanych w bazie danych z obszaru '55-12', które mają określoną funkcję i zostały przebadane między 1 stycznia 2012 i 31 grudnia 2012 roku. Warto zauważyć, że wynik takiego polecenia będzie zawsze zawierał dokładnie jeden wiersz z jedną kolumną, w które zostanie wyświetlona liczba stanowisk spełniających kryteria.
Innym przykładem funkcji agregującej jest SUM(nazwa_atrybutu), która dodaje do siebie wartości zapisane w kolumnie nazwa_atrybutu. Wartościami tymi muszą być liczby. Możemy jej użyć do obliczenia łącznej liczby fragmentów ceramiki znalezionych na stanowiskach.
SELECT SUM(F.CERAMIKA) FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO WHERE S.OBSZAR_AZP = '55-12' AND F.KULTURA IS NOT NULL AND S.DATA_BADAN >= '2012-01-01' AND S.DATA_BADAN <= '2012-12-31'
Ponownie wynikiem będzie dokładnie jeden wiersz zawierający sumę wartości z kolumny CERAMIKA w tabeli FaktyKulturowe, w wierszach, które spełniają podane kryteria.
W jednym poleceniu SQL można użyć więcej niż jednej funkcji agregującej. Na przykład:
SELECT COUNT(S.ID), SUM(F.CERAMIKA) FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO WHERE S.OBSZAR_AZP = '55-12' AND F.KULTURA IS NOT NULL AND S.DATA_BADAN >= '2012-01-01' AND S.DATA_BADAN <= '2012-12-31'
Wynikiem polecenia będzie dokładnie jeden wiersz z dwoma kolumnami. W pierwszej zostanie wyświetlona liczba stanowisk, w drugiej liczba fragmentów ceramiki, które zostały na nich znalezione. Inne przydatne funkcje agregujące to MIN - określa minimalną wartość w kolumnie, MAX - maksymalną wartość w kolumnie, AVG - oblicza średnią wartości w kolumnie.
Ważnym aspektem używania funkcji agregujących jest grupowanie wyników. Wyobraźmy sobie, że chcemy uzyskać liczbę stanowisk zapisanych w bazie danych z podziałem na jednostki kulturowe. W takim wypadku polecenie SQL powinno mieć taką postać:
SELECT F.KULTURA, COUNT(S.ID) FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO GROUP BY F.KULTURA
Na pierwszy rzut oka widać, że polecenie jest bardzo podobne do tych, które opisałem wcześniej. Mamy funkcję agregującą COUNT(S.ID), która policzy wszystkie stanowiska. Oprócz tego mamy atrybut KULTURA, według którego zostaną pogrupowane i policzone stanowiska. Wynik polecenia będzie zawierał dwie kolumny i tyle wierszy, ile różnych kultur zostało znalezionych w tabeli FaktyKulturowe. Do nazwy każdej kultury, w drugiej kolumnie wiersza będzie przypisana liczba oznaczająca liczbę stanowisk reprezentujących jednostkę z kolumny pierwszej.
Oprócz tego w poleceniu pojawiło się wyrażenie GROUP BY F.KULTURA. Używając grupowania w poleceniach sql trzeba jawnie podać, według której kolumny (albo wielu kolumn) mają być agregowane wyniki. Należy pamiętać, że w poleceniach grupujących, między słowami SELECT i FROM mogą znaleźć się tylko funkcje agregujące i nazwy atrybutów, według których są łączone wartości. Te same nazwy atrybutów muszą znaleźć się w wyrażeniu GROUP BY.
W ramach ćwiczenia spróbuj napisać polecenie obliczające średnią ilość fragmentów ceramiki na wszystkich stanowiskach w bazie danych z podziałem na numer obszaru AZP i porównaj to z prawidłową odpowiedzią poniżej.
SELECT S.OBSZAR_AZP, AVG(F.CERAMIKA) FROM Stanowiska S LEFT OUTER JOIN FaktyKulturowe F
ON S.ID = F.STANOWISKO GROUP BY S.OBSZAR_AZP
Brak komentarzy:
Prześlij komentarz