Mianowicie, dla każdego wynikowego wiersza zapytania określamy zbiór powiązanych z nim wierszy - nazywany oknem tego wiersza. Definiuje się go za pomocą tzw. klauzuli analitycznej. Rozmiary okien określa się, albo za pomocą liczby wierszy stosując klauzulę ROWS, albo za pomocą przedziałów wartości, takich jak czas, stosując klauzulę RANGE. Zbiór wszystkich wynikowych wierszy można najpierw podzielić na zbiór rozłącznych grup (np. pracownicy pracujący w tym samym departamencie, pracownicy pracujący na tym samym stanowisku), a następnie w ramach jednej grupy określać okno danego wiersza.
Oto składnia funkcji analitycznej:
| nazwa_funkcji_grupowej(argument,...) OVER (klauzula_analityczna) |
gdzie klauzula analityczna może zawierać następujące cztery podklauzule:
-
PARTITION BY wyrażenie, ... określa podział całego zbioru wynikowego wierszy na grupy; jeśli zostanie opuszczona, cały zbiór wynikowych wierszy stanowi jedną grupę. Wiersze wchodzące w skład okna są zawsze ograniczone (zawarte) do jednej grupy.
-
ORDER BY wyrażenie, ... określa porządek wierszy w ramach podziału określonego w grupie przez podklauzulę PARTITION BY.
-
ROWS specyfikacja_okna specyfikuje okno poprzez określenie liczby wierszy;
-
RANGE specyfikacja_okna specyfikuje okno poprzez określenie zakresu wierszy.
Podamy teraz kilka przykładów zastosowań.
Przykład (podklauzula PARTITION)
PARTITION jest podobne do GROUP BY z tym, że wartości podsumowujące są załączane do każdego wiersza w grupie, a nie tylko raz dla całej grupy.
SELECT e.Ename, e.Sal,
SUM(e.Sal) OVER
(PARTITION BY e.Deptno) AS Suma_zarob,
Round(100*e.Sal/(SUM(e.Sal) OVER
(PARTITION BY e.Deptno))) AS
Proc_zarob
FROM Emp e
ORDER BY e.Deptno, e.Ename;
SUM(e.Sal) OVER (PARTITION BY e.Deptno)
jest funkcją analityczną, która sumuje zarobki wszystkich pracowników pracujących
w tym samym departamencie.W tym przypadku okno obliczeniowe dla danego pracownika pokrywa się z grupą określoną przez PARTITION BY - czyli ze zbiorem pracowników pracujących w tym samym departamencie co dany pracownik. Oto wynik:
ENAME SAL SUMA_ZAROB PROC_ZAROB ---------- ---------- ---------- ---------- CLARK 2450 8750 28 KING 5000 8750 57 MILLER 1300 8750 15 ADAMS 1100 10875 10 FORD 3000 10875 28 JONES 2975 10875 27 SCOTT 3000 10875 28 SMITH 800 10875 7 ALLEN 1600 9400 17 BLAKE 2850 9400 30 JAMES 950 9400 10 MARTIN 1250 9400 13 TURNER 1500 9400 16 WARD 1250 9400 13Tab. 7.7 Ilustracja działania podklauzuli PARTITION Przykład (podklauzula RANGE)
Rozważmy problem analityczny wyznaczenia dla każdego pracownika, liczby pracowników, których zarobki są co najwyżej 50zł mniejsze lub 150zł większe od zarobków tego pracownika. W tym przypadku okno obliczeniowe dla danego pracownika o zarobkach
e.Sal nie zależy od
departamentu i obejmuje wszystkich pracowników, których zarobki są w
przedziale [e.Sal-50, e.Sal+150]. (Jest określona tylko jedna grupa
- zbiór wszystkich wierszy.)Zauważmy, że co najmniej jedna osoba spełnia ten warunek – pracownik względem którego określamy okno.
SELECT e.Ename, e.Sal,
COUNT(*) OVER (ORDER BY e.Sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS Licznik
FROM Emp e;
Oto wynik:ENAME SAL LICZNIK --------- ------- ------- SMITH 800 2 JAMES 950 2 ADAMS 1100 3 WARD 1250 3 MARTIN 1250 3 MILLER 1300 3 TURNER 1500 2 ALLEN 1600 1 CLARK 2450 1 BLAKE 2850 4 JONES 2975 3 SCOTT 3000 3 FORD 3000 3 KING 5000 1Tab. 7.8 Ilustracja działania podklauzuli RANGE Przykład (podklauzula ROWS)
Dla każdego pracownika podaj numer jego kierownika, datę zatrudnienia, zarobki oraz średnią wartość zarobków pracowników zatrudnionych bezpośrednio przed i po zatrudnieniu tego pracownika (włącznie z tym pracownikiem) wśród pracowników tego kierownika.
SELECT mgr, ename, hiredate, sal,
AVG(sal) OVER (PARTITION BY mgr ORDER BY hiredate
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ave
FROM Emp;
Oto wynik: MGR ENAME HIREDATE SAL AVE
---------- ---------- -------- ---------- ----------
7566 FORD 81/12/03 3000 3000
7566 SCOTT 82/12/09 3000 3000
7698 ALLEN 81/02/20 1600 1425
7698 WARD 81/02/22 1250 1450
7698 TURNER 81/09/08 1500 1333,3
7698 MARTIN 81/09/28 1250 1233,3
7698 JAMES 81/12/03 950 1100
7782 MILLER 82/01/23 1300 1300
7788 ADAMS 83/01/12 1100 1100
7839 JONES 81/04/02 2975 2912,5
7839 BLAKE 81/05/01 2850 2758,3
7839 CLARK 81/06/09 2450 2650
7902 SMITH 80/12/17 800 800
KING 81/11/17 5000 5000
Tab. 7.9 Ilustracja działania podklauzuli ROWSOracle dostarcza dodatkowych funkcji statystycznych do zastosowania w funkcjach analitycznych. Dwie z nich są pokazane poniżej.
Przykład
Rozważmy problem analityczny wyznaczenia dla każdego pracownika procentowego zaszeregowania jego zarobków w jego departamencie (procentowe zaszeregowanie zarobków danego pracownika określamy jako
pr=(i-1)/(n-1), gdzie n>1 jest liczbą pracowników w dziale, a
i jest numerem zaszeregowania zarobków danego pracownika wśród zarobków pracowników w jego
dziale według wzrastających zarobków).
SELECT e.Deptno, e.Ename, e.Sal,
PERCENT_RANK() OVER (PARTITION BY e.Deptno ORDER BY e.Sal) AS Pr
FROM Emp e;
Oto wynik: DEPTNO ENAME SAL PR
------- ------- ------- ------
10 MILLER 1300 0
10 CLARK 2450 .5
10 KING 5000 1
20 SMITH 800 0
20 ADAMS 1100 .25
20 JONES 2975 .5
20 SCOTT 3000 .75
20 FORD 3000 .75
30 JAMES 950 0
30 WARD 1250 .2
30 MARTIN 1250 .2
30 TURNER 1500 .6
30 ALLEN 1600 .8
30 BLAKE 2850 1
Tab. 7.10 Ilustracja działania funkcji PERCENT_RANK z podklauzulą PARTITIONPrzedstawione
dane można użyć do sporządzenia wykresu słupkowego obrazującego
zaszeregowania zarobków pracowników w ramach ich działów.Przykład
Rozważmy problem analityczny wyznaczenia dla każdego sprzedawcy stosunku jego zarobków do sumy zarobków wszystkich sprzedawców.
SELECT e.Ename, e.Sal, RATIO_TO_REPORT(e.Sal) OVER () AS Rr
FROM Emp e
WHERE e.Job = 'SALESMAN';
Oto wynik:ENAME SAL RR -------- ---- ------------ ALLEN 1600 .285714286 WARD 1250 .223214286 MARTIN 1250 .223214286 TURNER 1500 .267857143Tab. 7.11 Ilustracja działania funkcji RATIO_TO_REPORT
Jako dodatkową informację podajemy możliwość stosowania algorytmów eksplorujących dane na serwerze bazy danych Oracle.
ODM - Oracle Data Mining
Oracle rozpoczął w wersji 10g wprowadzanie pojęć i algorytmów data-miningowych na razie głównie przy pomocy pakietu DBMS_DATA_MINING (jeszcze nie na poziomie SQL i specjalnych obiektów zapisywanych w bazie danych). Centralne pojęcie to model określający parametry, algorytm data-miningowy i wprowadzone dane uczące.
Dane do analizy – zbiór punktów w przestrzeni wielowymiarowej - są dostarczane w jednej tabeli. Wiersze są nazywane przypadkami (ang. cases). Kolumna ID przypadku dostarcza jednoznacznego identyfikatora np. CUSTOMER ID w tabeli klientów. Kolumny są nazywane atrybutami. Np. model regresji może przewidzieć poziom dochodów klienta (atrybut typu target) w oparciu o datę urodzenia i płeć (atrybuty typu predictors).
Parametry procedury CREATE_MODEL
- model_name -- nazwa modelu
- mining_function -- stała reprezentująca rodzaj problemu eksploracji danych np. klasyfikacja, clustering, regresja
- data_table_name -- nazwa tabeli z danymi „uczącymi” model
- case_id_column_name
- target_column_name -- NULL dla modeli deskryptywnych, nie NULL dla modeli predyktywnych
- settings_table_name -- ustawienia dla funkcji i algorytmu (np. nazwa algorytmu eksploracyjnego)
Stosuje podany model eksploracyjny do podanych danych i generuje wyniki w tabeli, której nazwa jest specyfikowana w APPLY. Operacja APPLY jest też nazywana scoring. Dla modeli predyktywnych, operacja APPLY generuje wynik w kolumnie docelowej. Dla modeli deskryptywnych jak clustering, operacja APPLY przyporządkowuje każdemu przypadkowi prawdopodobieństwo należenia do klastra.
DBMS_DATA_MINING.APPLY
(model_name IN VARCHAR2,
data_table_name IN VARCHAR2,
case_id_column_name IN VARCHAR2,
result_table_name IN VARCHAR2 -- schemat tworzonej przez APPLY tabeli zależy od
algorytmu );
Źródło: http://edu.pjwstk.edu.pl/wyklady/szb/scb/wyklad7/w7.htm
Brak komentarzy:
Prześlij komentarz