Rozważmy poniższe zapytanie
SELECT*
FROM ALL_TABLES
WHERE OWNER =
'HR'
MODEL
DIMENSION BY (TABLE_NAME
AS NAZWA_TABELI)
MEASURES(
NUM_ROWS
AS LICZBA_WIERSZY,
1
AS SUMA,
1
AS SREDNIA
)
RULES(
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY) [
ANY],
SREDNIA[
ANY] =
ROUND(AVG ( LICZBA_WIERSZY)[
ANY])
)
ORDER BY NAZWA_TABELI;
Zwraca ono następujące kolumny:
- NAZWA_TABELI - pole pobierane z widoku ALL_TABLES, tutaj występuje jako indeks, daltego wartość tego pola powinna być unikalna i niezmienna
- LICZBA _WIERSZY - pole pobierane z kolumny NUM_ROWS widoku ALL_TABLES, zawiera liczbę wierszy w tabeli na ostatnie odświeżenie statystyk
- SUMA - pole wyliczeniowe typu numerycznego, wartość początkowa w deklaracji pola w sekcji MEASURES w postaci 1 AS SUMA determinuje typ zmiennej
- SREDNIA - pole wyliczeniowe typu numerycznego
W sekcji
RULES mamy następujące formuły
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY) [
ANY],
SREDNIA[
ANY] =
ROUND(AVG ( LICZBA_WIERSZY)[
ANY])
Słowo
ANY oznacza, że dane wyrażenie wykonuje się dla każdej wartości indeksu.
Dla każdej wartości indeksu dokonywane jest sumowanie wartości z kolumny LICZBA_WIERSZY dla wszystkich wierszy. Zwróćmy uwagę, że po słowie SUM w nawiasie jest nazwa kolumny, zakres sumowania znajduje się w nawiasie kwadratowym. Analogicznie wyliczana jest wartość średniej.
U mnie zapytanie zwraca następujące wartości.Dla kolumny LICZBA_WIERSZY skróciłem nagłówek
NAZWA_TABELI LW SUMA SREDNIA
COUNTRIES 25 215 30,71
DEPARTMENTS 27 215 30,71
EMPLOYEES 107 215 30,71
JOB_HISTORY 10 215 30,71
JOBS 19 215 30,71
LOCATIONS 23 215 30,71
REGIONS 4 215 30,71
Spróbujmy te formuły nieco zmodyfikować. Zwróćmy uwagę, na sortowanie po nazwie tabeli.
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY) [NAZWA_TABELI
LIKE 'J%']
Powyższe wyrażenie sumuje liczbę kolumn, ale do sumy bierze wiersze, dla których NAZWA tabeli zaczyna się od litery J.
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY) [NAZWA_TABELI
IN (
'COUNTRIES',
'JOBS',
'REGIONS') ]
Do sumy brane są wiersze dla tabel występujących na liście zdefiniowanej po operatorze wyliczeniowym
IN. Wartości z trzech wierszy wchodzą do sumy/
SUMA[NAZWA_TABELI
IN (
'COUNTRIES',
'JOBS',
'REGIONS')] = SUM ( LICZBA_WIERSZY) [
ANY ],
W tym przypadku suma wyliczana jest dla trzech wierszy i uwzględnia dane ze wszystkich wierszy, które znajdują się na liście, dla pozostałych wierszy wyświetla się 1, ponieważ pole suma w klauzuli
MEASURES inicjowane jest taką wartością
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY) [ NAZWA_TABELI <=
CV() ]
Funkcja
CV() (należy pamiętać o
nawiasach !!!!!) - zwraca nam bieżącą wartość indeksu, czyli w naszym przypadku wartość pola NAZWA_TABELI. W ten sposób otrzymamy sumę narastająco z uwzględnieniem aktualnego sortowania.
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY)
OVER ()
Jest to sumowanie wierszy z wykorzystaniem składni z funkcji analitycznych.
SUMA[
ANY] =
SUM ( LICZBA_WIERSZY)
OVER (
ORDER BY NAZWA_TABELI)
Jest to suma narastająco z wykorzystaniem składni z funkcji analitycznych.