Laboratorium oracle - funkcje agregujące

1.

INFORMATYKA
LABORATORIUM ORACLE - FUNKCJE AGREGUJĄCE
Dr inż. Marcin Kowalski

2.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące
W Oracle istnieje grupa funkcji, których argumentem
jest grupa wierszy a wynikiem działania jest jeden
wiersz. Funkcje takie nazywane są funkcjami
agregującymi. Wszystkie funkcje agregujące (oprócz
COUNT) ignorują wartość NULL. Ponadto można je
wywołać z użyciem słowa kluczowego DISTINCT,
oznaczającego, że funkcja ma pominąć powtarzające
się wartości argumentów. Poniżej ważniejsze funkcje
agregujące:
AVG() - oblicza wartość średnią, argumentami funkcji
mogą być wartości kolumn lub dowolne prawidłowe
wyrażenia, na przykład sumy czy iloczyny.

3.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące
COUNT() - oblicza liczbę wierszy zwróconych przez
zapytanie. W nawiasie należy podać nazwę kolumny.
Funkcja zwróci liczbę wierszy tej kolumny, które nie
mają wartości NULL.
MAX(), MIN() - funkcje zwracają maksymalną i
minimalną wartość spośród wszystkich argumentów
funkcji. Argumentami tych funkcji oprócz wartości
numerycznych, mogą być także napisy i daty.
STDDEV() - funkcja
odchylenie standardowe.
statystyczna
obliczająca

4.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące
VARIANCE() - funkcja
wariancję (jest równa
standardowego).
SUM() - funkcja
argumentów.
statystyczna
kwadratowi
zwracająca
sumę
obliczająca
odchylenia
wszystkich
Na przykład średnią ocen wszystkich studentów można
uzyskać zapytaniem:
SELECT ROUND(AVG(o.ocena),2)
FROM oceny_full o;

5.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Jeśli zachodzi konieczność wywołania funkcji
agregującej, której argumentem ma być grupa wierszy
zapytania, na przykład średnia ocen studentów
należących do poszczególnych grup studenckich lub
suma punktów studentów poszczególnych wydziałów,
to należy zastosować klauzulę GROUP BY. Grupuje
ona wiersze w bloki na podstawie wartości wybranej
kolumny lub kolumn. Na przykład można grupować
wiersze w perspektywie OCENY_FULL według takiego
samego id_grupy czy takiego samego id_grupy i
id_wydz. Poniżej przykład użycia funkcji agregującej
AVG, obliczającej średnią ocen studentów, należących
do jednej grupy studenckiej.

6.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Średnią ocen zaokrąglono do 2 miejsc po przecinku za
pomocą funkcji ROUND:
SELECT o.id_grupy, ROUND(AVG(o.ocena),2)
FROM oceny_full o
GROUP BY o.id_grupy
ORDER BY o.id_grupy;
Aby móc pokazać działanie kolejnej funkcji agregującej
SUM utwórzmy najpierw tabelę zawierającą liczbę
punktów uzyskanych przez studentów.
CREATE
TABLE
punkty( id_stud
CONSTRAINT pk_punkty PRIMARY KEY,
punkty NUMBER(5));
number(6)

7.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Należy w nowej tabeli PUNKTY dodać więzy
integralności klucza obcego, po to aby uniemożliwić
wprowadzenie
wiersza
z
nieistniejącym
identyfikatorem studenta.
ALTER TABLE punkty ADD CONSTRAINT
fk_pkt_id_stud
FOREIGN KEY(id_stud) REFERENCES
studenci(id_stud);

8.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Do tabeli PUNKTY kopiujemy identyfikatory
studentów za pomocą następującej komendy:
INSERT INTO punkty (id_stud) SELECT id_stud
FROM studenci;
Następnie wstawiamy do kolumny punkty wartości
uzyskane z generatora liczb pseudolosowych:
UPDATE punkty SET punkty =
ROUND(100*DBMS_RANDOM.VALUE(),0);

9.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Następnie tworzymy perspektywę zawierającą dane
studenta i liczbę uzyskanych punktów:
CREATE OR REPLACE VIEW v_student_punkty
AS SELECT s.id_stud, s.nr_ind, s.imie1, s.nazwisko,
s.id_grupy, s.id_wydz, p.punkty, g.rok_nr
FROM studenci s, grupy g, punkty p
WHERE s.id_stud = p.id_stud AND s.id_grupy =
g.id_grupy;

10.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
Aby obliczyć sumy punktów zdobyte przez studentów
poszczególnych wydziałów zostanie użyta funkcja
SUM z klauzulą GROUP BY:
SELECT v.id_wydz, SUM(v.punkty)
FROM v_student_punkty v
GROUP BY v.id_wydz
ORDER BY v.id_wydz;

11.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
W zapytaniach z klauzulą GROUP BY możliwe jest
filtrowanie wierszy, które mają podlegać grupowaniu, a
także filtrowanie grup wierszy ze względu na wynik
funkcji grupującej. Wiersze filtruje się za pomocą
klauzuli WHERE natomiast wyniki grupowania filtruje
się za pomocą klauzuli HAVING.
Poniższy przykład pokazuje, jak zgrupować oceny
studentów wydziału o id_wydz = 4 oraz jak wyniki
grupowania (średnią) ograniczyć do grup studenckich,
w których średnia ocen jest w przedziale 3,5 – 4,0:

12.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY
SELECT o.id_grupy, o.id_wydz,
ROUND(AVG(o.ocena),2)
FROM oceny_full o
WHERE o.id_wydz = 4
GROUP BY o.id_grupy, o.id_wydz
HAVING AVG(o.ocena) BETWEEN 3.51 AND 4.0
ORDER BY o.id_wydz, o.id_grupy;

13.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
Klauzula ROLLUP modyfikuje działanie grupowania
wierszy za pomocą GROUP BY: oprócz wyników
funkcji agregującej (sum, średnich, itp.) dla grup
wierszy zwracane jest także działanie funkcji
agregującej dla wyników poszczególnych grup.
Na przykład zastosowanie klauzuli ROLLUP w jednym
z poprzednich przykładów, gdzie obliczano ilości
punktów zdobytych przez studentów poszczególnych
wydziałów, spowoduje, że oprócz sum punktów w
poszczególnych wydziałach zostanie zwrócony też
wiersz zawierający sumę punktów wszystkich
wydziałów.

14.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
SELECT v.id_wydz, SUM(v.punkty)
FROM v_student_punkty v
GROUP BY ROLLUP (v.id_wydz)
ORDER BY v.id_wydz;

15.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
Klauzulę ROLLUP można używać do grupowania
wierszy według wartości więcej niż jednej kolumny. Na
przykład zapytanie, które ma obliczyć sumy punktów w
poszczególnych grupach a następnie sumy punktów w
poszczególnych wydziałach i na końcu podać sumę
punktów wszystkich studentów będzie następujące:
SELECT v.id_wydz, v.id_grupy, SUM(v.punkty)
FROM v_student_punkty v
GROUP BY ROLLUP(v.id_wydz, v.id_grupy)
ORDER BY v.id_wydz, v.id_grupy;

16.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
Poprzednie zapytanie w kolumnie id_grupy w wierszu,
który jest sumą punktów wszystkich grup należących
do jednego wydziału zwróci NULL, podobnie w
wierszu, który jest sumą punktów studentów
wszystkich wydziałów w kolumnach id_wydz oraz
id_grupy
zostanie
zwrócone
NULL.
Aby
przekonwertować NULL na bardziej czytelną
informację można zastosować funkcję NVL.

17.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
Funkcja ta ma następującą składnię:
NVL(x, 'wyświetlony napis')
W przypadku gdy x ma wartość NULL funkcja zwraca
wyświetlony napis a gdy x nie ma wartości NULL
zwracane jest x. Ponieważ NVL jest funkcją znakową
najpierw zamieniono wartość liczbową w kolumnach
id_grupy i id_wydz na napis za pomocą funkcji
TO_CHAR.

18.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
SELECT NVL(TO_CHAR(s.id_wydz), 'SUMA CAŁKOWITA'),
NVL(TO_CHAR(s.id_grupy), 'WYDZIAŁ'),
SUM(s.punkty)
FROM studenci s
GROUP BY ROLLUP(s.id_wydz, s.id_grupy)
ORDER BY s.id_wydz, s.id_grupy;

19.

SQL i wprowadzenie do PL/SQL
Klauzula GROUP BY ROLLUP
W celu lepszej czytelności wyników zapytania
zastosowano aliasy nagłówków kolumn zwracanych
przez zapytanie.
SELECT
NVL(TO_CHAR(s.id_wydz), 'SUMA CAŁKOWITA') WYDZIAŁ,
NVL(TO_CHAR(s.id_grupy), 'WYDZIAŁ') GRUPA,
SUM(s.punkty) SUMA_PUNKTÓW
FROM studenci s
GROUP BY ROLLUP(s.id_wydz, s.id_grupy)
ORDER BY s.id_wydz, s.id_grupy;

20.

SQL i wprowadzenie do PL/SQL
Funkcje agregujące z klauzulą GROUP BY CUBE
Klauzula CUBE rozszerza działanie GROUP BY o
podsumowania częściowe dla wszystkich kolumn
występujących w GROUP BY, zwraca również sumę dla
wszystkich wierszy.
Poniższe zapytanie zwróci najpierw średnią ocen z
przedmiotów w danej grupie, później to samo dla
wszystkich grup. Następnie średnie ocen dla wszystkich
przedmiotów. Na końcu znajdzie się średnia wszystkich
ocen.
SELECT o.ID_GRUPY, o.ID_PRZED, AVG(o.OCENA)
FROM OCENY_FULL o
GROUP BY CUBE (o.ID_GRUPY, o.id_przed)
ORDER BY o.ID_GRUPY, o.ID_PRZED;