H r
u tow
o n
w i
n e
e da
d n
a y
n ch- imp
m l
p em
e e
m n
e t
n ac
a ja
a pr
p zet
e wa
w r
a zan
a i
n a
a an
a a
n l
a ityc
y zn
z e
n g
e o
g
o pr
p zy
z
y
wy
w kor
o z
r ys
y tan
a i
n u
u Orac
a l
c e
e 9i
9 OLA
L P
Jedną z tendencji rozwojowych, która dominuje w ostatnich latach w systemach relacyjnych baz danych np. Oracle jest integracja w jednym produkcie mechanizmów analitycznych (OLAP) z funkcjonalnością systemu zarządzania bazą danych.
Taka integracja ma dwa cele:
• Możliwość przetwarzania analitycznego (OLAP) w ramach operacyjnej bazy danych-pozwala uniknąć kosztownego procesu replikacji danych z niej do hurtowni danych;
• SZBD staje się poważną platformą implementacji hurtowni danych; Podstawowym składnikiem analitycznego rozszerzenia funkcjonalności SZBD
jest rozbudowa języka SQL.
Do podstawowych rozszerzeń języka SQL w zakresie OLAP należą:
• Operatory agregujące:
o ROLLUP,
o CUBE,
o GROUPING SETS,
• Funkcje analityczne SQL:
Partycje obliczeniowe,
Ruchome okno obliczeniowe,
Funkcje rankingowe,
Funkcje statystyczne.
Wykłąd HD 3
1
p r
e at
a or
o rRO
R LL
L U
L P
U
Operator ROLLUP stanowi rozszerzenie klauzuli GROUP BY zapytania SELECT. Pozwala on na wyznaczanie funkcji agregujących na rosnących poziomach agregacji. Skutkiem tego jest fakt, że agregaty szczegółowe i ogólne mogą być wyznaczane za pomocą tego samego zapytania.
SELECT region, produkt, Sum(kwota) FROM sprzedaz group BY rollup (region,produkt);
SELECT region,produkt, okres ,Sum(kwota) FROM sprzedaz group BY
rollup (region,produkt,okres);
REGION
PRODUKT SUM(KWOTA)
REGION
PRODUKT
OKRES SUM(KWOTA)
KIELCE
KLEJE
84040 KIELCE
KLEJE
1
42000
KIELCE
OLEJE
2240 KIELCE
KLEJE
2
42040
KIELCE
OPNY
46200 KIELCE
KLEJE
84040
KIELCE
REFLEKTORY
164000 KIELCE
OLEJE
1
20
KIELCE
TLUMIKI
94000 KIELCE
OLEJE
2
2220
KIELCE
390480 KIELCE
OLEJE
2240
MAZOWSZE KLEJE
400 KIELCE
OPNY
1
4000
MAZOWSZE OLEJE
4000 KIELCE
OPNY
2
42200
MAZOWSZE OPNY
400 KIELCE
OPNY
46200
MAZOWSZE REFLEKTORY
400 KIELCE
REFLEKTORY
1
82000
MAZOWSZE TLUMIKI
400 KIELCE
REFLEKTORY
2
82000
MAZOWSZE
5600 KIELCE
REFLEKTORY
164000
396080 KIELCE
TLUMIKI
1
32000
KIELCE
TLUMIKI
2
62000
KIELCE
TLUMIKI
94000
KIELCE
390480
MAZOWSZE
KLEJE
1
200
MAZOWSZE
KLEJE
2
200
MAZOWSZE
KLEJE
400
MAZOWSZE
OLEJE
1
2000
MAZOWSZE
OLEJE
2
2000
MAZOWSZE
OLEJE
4000
MAZOWSZE
OPNY
1
200
MAZOWSZE
OPNY
2
200
MAZOWSZE
OPNY
400
MAZOWSZE
REFLEKTORY
1
200
MAZOWSZE
REFLEKTORY
2
200
MAZOWSZE
REFLEKTORY
400
MAZOWSZE
TLUMIKI
1
200
MAZOWSZE
TLUMIKI
2
200
MAZOWSZE
TLUMIKI
400
MAZOWSZE
5600
Wykłąd HD 3
2
396080
Ope
p r
e at
a or
o rCU
C B
U E
Operator CUBE stanowi rozszerzenie klauzuli GROP BY zapytania SELECT.
Zapytanie z operatorem CUBE jest równoważne złożeniu wielu zapytań, w których stosowane jest grupowanie według wszystkich możliwych kombinacji podanych kolumn.
SELECT
region,
produkt,
Sum(kwota)
FROM
sprzedaz
GROUP
BY
cube(region,produkt);
REGION
PRODUKT
SUM(KWOTA)
KIELCE
KLEJE
84040
KIELCE
OLEJE
2240
KIELCE
OPNY
46200
KIELCE
REFLEKTORY
164000
KIELCE
TLUMIKI
94000
KIELCE
390480
MAZOWSZE KLEJE
400
MAZOWSZE OLEJE
4000
MAZOWSZE OPNY
400
MAZOWSZE REFLEKTORY
400
MAZOWSZE TLUMIKI
400
MAZOWSZE
5600
KLEJE
84440
OLEJE
6240
OPNY
46600
REFLEKTORY
164400
TLUMIKI
94400
396080
Ope
p r
e at
a or
o rGRO
R UP
U ING
N SETS
T
Operator GROUPING SETS umożliwia realizację wielu schematów grupowania w jednym wywołaniu instrukcji SELECT. Argumentem operatora jest lista wielu zestawów kolumn grupujących.
SELECT region,produkt,Sum(kwota) FROM sprzedaz GROUP BY grouping sets ((region,produkt), (produkt));-- ORACLE 9i
Wykłąd HD 3
3
REGION
PRODUKT
SUM(KWOTA)
KIELCE
KLEJE
84040
MAZOWSZE KLEJE
400
KLEJE
84440
KIELCE
OLEJE
2240
MAZOWSZE OLEJE
4000
OLEJE
6240
KIELCE
OPNY
46200
MAZOWSZE OPNY
400
OPNY
46600
KIELCE
REFLEKTORY
164000
MAZOWSZE REFLEKTORY
400
REFLEKTORY
164400
KIELCE
TLUMIKI
94000
MAZOWSZE TLUMIKI
400
TLUMIKI
94400
Fu
F n
u k
n cje
e an
a a
n l
a ityc
y zne
n
e SQL
Par
a tyc
y je
e ob
o l
b icz
c en
e i
n ow
o e
w
Tradycyjne funkcje grupowe (agregujące): SUM(), AVG(), COUNT(), obliczają swoje wartości dla grup wierszy, które są definiowane przez klauzulę GROUP BY instrukcji SELECT. Dla każdej grupy, funkcja grupowa zwraca pojedynczą wartość wynikową. Lecz od niektórych wersji ORACLE 8i oraz w wersji ORACLE 9i możliwe jest wyznaczanie wartości funkcji grupowej oddzielnie dla każdego wiersza grupy (a nie tylko raz dla całej grupy).
W tym celu skorzystania z takiego rozwiązania należy zastąpić klauzulę GROUP BY wyrażeniem PARTITION.
funkcja_grupowa() over (partition by kolumna)
SELECT produkt, region, okres, kwota, Sum(kwota) over (PARTITION
BY
produkt)
Sum_kowta,
Round(100*kwota/(Sum(kwota)
over
(PARTITION BY produkt))) "UDZIAL %" FROM sprzedaz ORDER BY produkt,region,okres;
Wykłąd HD 3
4
PRODUKT
REGION OKRES KWOTA SUM_KOWTA UDZIAL %
KLEJE
KIELCE
1
42000
84440
50
KLEJE
KIELCE
2
42040
84440
50
KLEJE
MAZOWSZE
1
200
84440
0
KLEJE
MAZOWSZE
2
200
84440
0
OLEJE
KIELCE
1
20
6240
0
OLEJE
KIELCE
2
2220
6240
36
OLEJE
MAZOWSZE
1
2000
6240
32
OLEJE
MAZOWSZE
2
2000
6240
32
OPNY
KIELCE
1
4000
46600
9
OPNY
KIELCE
2
42200
46600
91
OPNY
MAZOWSZE
1
200
46600
0
OPNY
MAZOWSZE
2
200
46600
0
REFLEKTORY KIELCE
1
82000
164400
50
REFLEKTORY KIELCE
2
82000
164400
50
REFLEKTORY MAZOWSZE
1
200
164400
0
REFLEKTORY MAZOWSZE
2
200
164400
0
TLUMIKI
KIELCE
1
32000
94400
34
TLUMIKI
KIELCE
2
62000
94400
66
TLUMIKI
MAZOWSZE
1
200
94400
0
TLUMIKI
MAZOWSZE
2
200
94400
0
SELECT produkt,region,okres,kwota,Sum(kwota) over (PARTITION BY
region) Sum_kowta, Round(100*kwota/(Sum(kwota) over (PARTITION BY
region))) "UDZIAL %" FROM sprzedaz
ORDER BY region, produkt, okres;
REGION
PRODUKT OKRES KWOTA SUM_KOWTA UDZIAL %
KIELCE
KLEJE
1
42000
390480
11
KIELCE
KLEJE
2
42040
390480
11
KIELCE
OLEJE
1
20
390480
0
KIELCE
OLEJE
2
2220
390480
1
KIELCE
OPNY
1
4000
390480
1
KIELCE
OPNY
2
42200
390480
11
KIELCE
REFLEKTORY
1
82000
390480
21
KIELCE
REFLEKTORY
2
82000
390480
21
KIELCE
TLUMIKI
1
32000
390480
8
KIELCE
TLUMIKI
2
62000
390480
16
MAZOWSZE KLEJE
1
200
5600
4
MAZOWSZE KLEJE
2
200
5600
4
MAZOWSZE OLEJE
1
2000
5600
36
MAZOWSZE OLEJE
2
2000
5600
36
MAZOWSZE OPNY
1
200
5600
4
MAZOWSZE OPNY
2
200
5600
4
MAZOWSZE REFLEKTORY
1
200
5600
4
MAZOWSZE REFLEKTORY
2
200
5600
4
MAZOWSZE TLUMIKI
1
200
5600
4
MAZOWSZE TLUMIKI
2
200
5600
4
Wykłąd HD 3
5
Ru
R c
u ho
h m
o e
m
e ok
o no
n
o ob
o l
b iczen
e i
n ow
o e
w
Wartości funkcji grupowych mogą być również wyznaczane w oparciu o grupy wierszy przesuwające się wraz z wierszem bieżącym. Grupy takie są nazywane oknami i mogą być definiowane przy pomocy wyrazeń ROWS lub RANGE. Wyrażenia ROWS definiuje tzw. okno fizyczne –rozmiar określony liczbą wierszy. Wyrażenie RANGE definiuje tzw. okno logiczne – rozmiar określony warunkiem selekcji wierszy.
funkcja() over ([partition by kol1] order by kol2 [desc] range between wyr1 and wyr2)
gdzie:
funkcja()-funkcja grupowa(agregująca)
kol1-kolumna grupująca wiersze w grupy, wewnątrz których przesuwa się okno; jeśli nie wystąpi fraza partition, oznacza to że okno przesuwa się przez całą tabelę.
kol2- kolumna porządkująca wiersze wewnątrz grupy.
Desc-powoduje porządek przesuwania okna od wartości największej do najmniejszej.
Wyr1-określa położenie początku okna;
Wyr2-określa położenia końca okna.
<<omówić zakresy>>
SELECT rok,okres,kwota, Sum(kwota) over (PARTITION BY rok ORDER BY
okres RANGE BETWEEN unbounded preceding AND CURRENT ROW) AS
kwota_sum from sprzedaz1 ORDER BY rok,okres;
ROK
OKRES KWOTA KWOTA_SUM
2001
1
2000
2000
2001
2
200
2200
2001
3
20
2220
2002
1
200
200
2002
2
200
400
2002
3
200
600
2002
4
4000
4600
2002
5
82000
86600
2002
6
42000
128600
SELECT rok,okres,kwota, avg(kwota) over (PARTITION BY rok ORDER BY
okres RANGE BETWEEN 3 preceding AND CURRENT ROW) AS avg_kw Wykłąd HD 3
6
from sprzedaz1 ORDER BY rok,okres;
ROK OKRES KWOTA AVG_KW
2001
1
2000
2000
2001
2
200
1100
2001
3
20
740
2002
1
200
200
2002
2
200
200
2002
3
200
200
2002
4
4000
1150
2002
5
82000
21600
2002
6
42000
32050
SELECT rok,okres,kwota, Lag(kwota,1) over (ORDER BY rok,okres ) AS
lag, kwota-(Lag(kwota,1) over (ORDER BY rok,okres )) AS wzrost from sprzedaz1 ORDER BY rok,okres;
ROK OKRES KWOTA LAG WZROST
2001
1
2000
2001
2
200 2000
-1800
2001
3
20
200
-180
2002
1
200
20
180
2002
2
200
200
0
2002
3
200
200
0
2002
4
4000
200
3800
2002
5
82000 4000
78000
2002
6
42000 82000
-40000
Fu
F n
u k
n cje
e ran
a k
n ing
n o
g w
o e
w
Funkcje rankingowe umożliwiają wyznaczenie położenia wiersza w odniesieniu do pozostałych wierszy grupy względem wybranej funkcji porządkującej. Implementacja SQL w Oracle 9i zawiera następujące funkcje tego typu <<opis>>:
• RANK()
• DENSE_RANK()
• CUME_DIST()
• PERCENT_RANK()
• NTILE()
Wykłąd HD 3
7
SELECT region, produkt, kwota, Rank() over (PARTITION BY region ORDER BY kwota DESC) AS ranking, Dense_Rank () over (PARTITION BY
region ORDER BY kwota DESC) AS ranking1 FROM sprzedaz ORDER BY
region, ranking1;
REGION
PRODUKT KWOTA RANKING RANKING1
KIELCE
REFLEKTORY
82000
1
1
KIELCE
REFLEKTORY
82000
1
1
KIELCE
TLUMIKI
62000
3
2
KIELCE
OPNY
42200
4
3
KIELCE
KLEJE
42040
5
4
KIELCE
KLEJE
42000
6
5
KIELCE
TLUMIKI
32000
7
6
KIELCE
OPNY
4000
8
7
KIELCE
OLEJE
2220
9
8
KIELCE
OLEJE
20
10
9
MAZOWSZE OLEJE
2000
1
1
MAZOWSZE OLEJE
2000
1
1
MAZOWSZE OPNY
200
3
2
MAZOWSZE TLUMIKI
200
3
2
MAZOWSZE KLEJE
200
3
2
MAZOWSZE OPNY
200
3
2
MAZOWSZE KLEJE
200
3
2
MAZOWSZE REFLEKTORY
200
3
2
MAZOWSZE REFLEKTORY
200
3
2
MAZOWSZE TLUMIKI
200
3
2
Wykłąd HD 3
8