MODEL ORACLE
Klauzula MODEL umożliwia tworzenie tablicy wielowymiarowej do której można zastosować formuły (reguły), wyznaczające nowe wartości, poprzez przetwarzanie komórek lub zestawów komórek. Tworzenie odbywa się poprzez mapowanie kolumn w zapytaniu na 3 grupy- kolumn :
- Partitions (partycji), partycje dzielą zestaw wyników na bloki.
- Dimensions (wymiarów) – określa, jakie kolumny w instrukcji SELECT są kolumnami wymiaru. Czyli do identyfikacji komórek w partycji. Domyślną kolumną w klauzuli MODEL jest unikatowy klucz. W razie zapotrzebowania dozwolone jest zdefiniowanie więcej niż jedną kolumnę wymiaru. W klauzuli Dimensions nie można używać aliansów SELECT, jedynie całość zdefiniowanego aliansu.
- Measures (miar). Kolumny zdefiniowane w Measures mogą być przypisane nowe wartości w Rules w klauzuli modelu.
Składnia:
model [main]
[ reference models ]
[ partition by (<cols>)]
dimension by (<cols>)
measures (<cols>)
[ ignore nav ] | [ keep nav ]
[ rules
[ upsert | update]
[ automatic order | sequential order ]
[ iterate (n) [ until <condition>]
]
(<cell_assignment> = <expression> ...)
Zobrazowanie mechanizmu modelu zostanie przedstawione w kilku przykładach, opartych o dialekt PL/SQL wykorzystując poniższą tabelę:
| id | name | year | capital | country |
|---|---|---|---|---|
| 1 | TP S.A. | 1991 | 240000 | Warszawa |
| 2 | Kolporter S.A. | 1992 | 20000 | Kielce |
| 3 | Orange | 1994 | 564000 | Warszawa |
| 4 | Siemens | 1986 | 190000 | Berlin |
| 5 | Samsung | 1938 | 20000 | Suwon |
| 6 | Nokia | 1965 | 18000 | Espoo |
| 7 | Allianz | 1890 | 18000 | Berlin |
| 8 | RWE | 1898 | 18000 | Berlin |
Przykład 1:
select
*
from
company
model
RETURN ALL ROWS
dimension by (id)
measures (name, country, capital)
rules (
);
select
*
from
company
model
dimension by (id)
measures (name, country, capital)
rules (
);
| id | name | country | capital |
|---|---|---|---|
| 1 | TP S.A. | Warszawa | 240000 |
| 2 | Kolporter S.A. | Kielce | 20000 |
| 3 | Orange | Warszawa | 564000 |
| 4 | Siemens | Berlin | 190000 |
| 6 | Nokia | Espoo | 18000 |
| 7 | Allianz | Berlin | 18000 |
| 8 | RWE | Berlin | 18000 |
| 5 | Samsung | Suwon | 20000 |
| Powyższe polecenia zwracają te same wyniki. Dla pierwszego zapytania zawarto klauzurę RETURN ALL ROWS oznaczająca zwracanie wszystkich wierszy. |
Przykład 2:
Wyświetlenie nowo powstałych rekordów można przedstawić następującymi poleceniami:
SELECT *
FROM company
MODEL
RETURN UPDATED ROWS
DIMENSION BY (id)
MEASURES ( capital)
RULES (
capital[9] = 99,
capital[10] = 9999
);
| id | capital |
|---|---|
| 10 | 9999 |
| 9 | 99 |
SELECT *
FROM company
MODEL
DIMENSION BY (id)
MEASURES ( capital)
RULES (
capital[9] = 99,
capital[10] = 9999
);
SELECT *
FROM company
MODEL
RETURN ALL ROWS
DIMENSION BY (id)
MEASURES ( capital)
RULES (
capital[9] = 99,
capital[10] = 9999
);
| id | capital |
|---|---|
| 10 | 240000 |
| 2 | 20000 |
| 3 | 564000 |
| 4 | 190000 |
| 6 | 18000 |
| 7 | 18000 |
| 8 | 18000 |
| 5 | 20000 |
| 10 | 9999 |
| 9 | 99 |
| W pierwszym poleceniu klauzula RETURN UPDATED ROWS spowoduje wyświetlenie jedynie nowo utworzonych rekordów. Termin "capital[9] " zawarty w RULES nazywana się "symbolicznym odwołaniem do komórki" i odnosi się do wartości kolumny capital, gdzie „ID” ma wartość kolumny równą "9". Czyli określenie komórek, które są częścią formuły. Możesz używać warunków, takich jak <,>, IN, BETWEEN. Podczas dodania nowych kolumn w MEASURES spowoduje to wyświetlenie krotek (wierszy) tych atrybutów (kolumn). |
Przykład 3:
SELECT country, name, year, capital
FROM company
MODEL
RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (name, year)
MEASURES (capital)
RULES (
capital['TP S.A.', 1991] = 10,
capital['Siemens', 1986] = 20)
ORDER BY country, name, year;
| country | name | year | capital |
|---|---|---|---|
| Berlin | Siemens | 1986 | 20 |
| Berlin | TP S.A. | 1991 | 10 |
| Espoo | Siemens | 1986 | 20 |
| Espoo | TP S.A. | 1991 | 10 |
| Kielce | Siemens | 1986 | 20 |
| Kilece | TP S.A. | 1991 | 10 |
| Suwon | Siemens | 1986 | 20 |
| Suwon | TP S.A. | 1991 | 10 |
| Warszawa | Siemens | 1986 | 20 |
| Warszawa | TP S.A. | 1991 | 10 |
| Zapytanie zwraca wynik według reguł RULES podzielonych na partycje PARTITION BY (country) . |
Przykład 4:
SELECT country, name, year, capital
FROM company
WHERE country = 'Warszawa'
MODEL
RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (name, year)
MEASURES (capital)
RULES (
capital[name='TP S.A.', year > 1900] = 10)
ORDER BY country, name, year;
| country | name | year | capital |
|---|---|---|---|
| Warszawa | TP S.A. | 1991 | 10 |
| W tym przykładzie formuła ma zastosowanie do każdej komórki, która ma nazwę równą „TP S.A.” i dla roku o wartości większej niż „1900”. |
Przykład 5:
SELECT country, name, year, capital
FROM company
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (name, year)
MEASURES (capital)
RULES (
capital['TP S.A.', 2011] = capital['Samsung', year = 1938] ,
capital[name='TP S.A.', year > 1900] = capital['Orange', 1994],
capital['nowa firma', 2005] = capital['RWE', 1898] + capital['Allianz', 1890]
)
ORDER BY country, name, year;
| country | name | year | capital |
|---|---|---|---|
| Berlin | nowa firma | 2005 | - |
| Berlin | TP S.A. | 2011 | - |
| Espoo | nowa firma | 2005 | - |
| Espoo | TP S.A. | 2011 | - |
| Kielce | nowa firma | 2005 | - |
| Kielce | TP S.A. | 2011 | - |
| Suwon | nowa firma | 2005 | - |
| Suwon | TP S.A. | 2011 | - |
| Warszawa | nowa firma | 2005 | - |
| Warszawa | TP S.A. | 1991 | 564000 |
| Warszawa | TP S.A. | 2011 | 564000 |
Powyższy przykład zwraca dodatkowo nowe wartości. Wykonany przez regułę zawierającą większą wartość dla roku niż 1994, a także dla nazwy firmy. Pierwsza reguła aktualizuje istniejące dane. Druga wyświetla już zaktualizowane wartości nowych komórek. Ostania reguła ustawia nowe komórki.
Przykład 6:
SELECT
*
FROM
company
MODEL
DIMENSION BY ( id)
MEASURES (capital, name, year)
RULES(
capital[any] = CV(id)
)
order by id;
| id | capital | name | year |
|---|---|---|---|
| 1 | 1 | TP S.A. | 1991 |
| 2 | 2 | Kolporter S.A. | 1992 |
| 3 | 3 | Orange | 1994 |
| 4 | 4 | Siemens | 1986 |
| 5 | 5 | Samsung | 1938 |
| 6 | 6 | Nokia | 1965 |
| 7 | 7 | Allianz | 1890 |
| 8 | 8 | RWE | 1898 |
| Funkcja CV jest używana wyłącznie podczas tworzenia klauzury model. Upraszcza zapis reguł. W powyższym przykładzie „CV(id)” zwraca bieżącą wartość „id” kolumny DIMENSION BY. | |||
| Możliwe jest również użycie CV() bez żadnych argumentów. |
Przykład 7:
SELECT
*
FROM
company
MODEL
DIMENSION BY (country , name )
MEASURES (capital , year)
RULES(
capital[any, any] = capital[ CV(), CV() ]
)
order by country, name;
| country | name | capital | year |
|---|---|---|---|
| Berlin | RWE | 18000 | 1898 |
| Berlin | Allianz | 18000 | 1890 |
| Berlin | Siemens | 190000 | 1986 |
| Espoo | Nokia | 18000 | 1965 |
| Kielce | Kolporter S.A. | 20000 | 1992 |
| Suwon | Samsung | 20000 | 1938 |
| Warszawa | Orange | 564000 | 1994 |
| Warszawa | TP S.A. | 240000 | 1991 |
| Funkcja CV() przekazuje wartości z DIMENSION BY (country, name ) od lewej do prawej dla klauzury RULES. Parametr „any” oznacza dla wszystkich wartości w DIMENSION BY . |
Domyślnie Oracle wykonuje zasady w kolejności, w jakiej pojawiają się w klauzuli RULES. Są one określane jako SEQUENTIAL ORDER. Alternatywną zasadą jest AUTOMATIC ORDER. Różnica polega na tym, że jeśli komórki nie są zależne od siebie, zostanie podjęta akcja określona w RULES. Dla AUTOMATIC ORDER należy pamiętać, że do tej samej komórki można przypisać wartość tylko raz. Z kolei dla SEQUENTIAL ORDER przypisanie do tych samych komórek wartości nie spowoduje błędu.
Przykład 8:
SELECT id, name, year, sales
FROM company
MODEL
RETURN UPDATED ROWS
PARTITION BY (id)
DIMENSION BY (name, year)
MEASURES (capital sales)
RULES SEQUENTIAL ORDER (
sales['RWE', 1898 ] = sales['Orange', 1994] * 1.1,
sales['Orange', 1994] = sales['Nokia', 1965]
)
ORDER BY id, name, year;
| id | name | year | sales |
|---|---|---|---|
| 1 | Orange | 1994 | - |
| 1 | RWE | 1898 | - |
| 2 | Orange | 1994 | - |
| 2 | RWE | 1898 | - |
| 3 | Orange | 1994 | - |
| 3 | RWE | 1898 | 620400 |
| 4 | Orange | 1994 | - |
| 4 | RWE | 1898 | - |
| 5 | Orange | 1994 | - |
| 5 | RWE | 1898 | - |
| 6 | Orange | 1994 | 18000 |
| 6 | RWE | 1898 | - |
| 7 | Orange | 1994 | - |
| 7 | RWE | 1898 | - |
| 8 | Orange | 1994 | - |
| 8 | RWE | 1898 | - |
W momencie gdy zostanie zamieniona kolejność w RULES zostaną wyświetlone rekordy w innej kolejności.
SELECT id, name, year, sales
FROM company
MODEL
RETURN UPDATED ROWS
PARTITION BY (id)
DIMENSION BY (name, year)
MEASURES (capital sales)
RULES SEQUENTIAL ORDER (
sales['Orange', 1994] = sales['Nokia', 1965] ,
sales['RWE', 1898 ] = sales['Orange', 1994] * 1.1
)
ORDER BY id, name, year;
| id | name | year | sales |
|---|---|---|---|
| 1 | Orange | 1994 | - |
| 1 | RWE | 1898 | - |
| 2 | Orange | 1994 | - |
| 2 | RWE | 1898 | - |
| 3 | Orange | 1994 | - |
| 3 | RWE | 1898 | - |
| 4 | Orange | 1994 | - |
| 4 | RWE | 1898 | - |
| 5 | Orange | 1994 | - |
| 5 | RWE | 1898 | - |
| 6 | Orange | 1994 | 18000 |
| 6 | RWE | 1898 | 19800 |
| 7 | Orange | 1994 | - |
| 7 | RWE | 1898 | - |
| 8 | Orange | 1994 | - |
| 8 | RWE | 1898 | - |
| Podczas użycia AUTOMATIC ORDER i zamiany kolejności wystąpienia reguł, zostanie wyświetlony identyczne rekordy. |
SELECT id, name, year, sales
FROM company
MODEL
RETURN UPDATED ROWS
PARTITION BY (id)
DIMENSION BY (name, year)
MEASURES (capital sales)
RULES AUTOMATIC ORDER (
sales['Orange', 1994] = sales['Nokia', 1965] ,
sales['RWE', 1898 ] = sales['Orange', 1994] * 1.1
)
ORDER BY id, name, year;
| id | name | year | sales |
|---|---|---|---|
| 1 | Orange | 1994 | - |
| 1 | RWE | 1898 | - |
| 2 | Orange | 1994 | - |
| 2 | RWE | 1898 | - |
| 3 | Orange | 1994 | - |
| 3 | RWE | 1898 | - |
| 4 | Orange | 1994 | - |
| 4 | RWE | 1898 | - |
| 5 | Orange | 1994 | - |
| 5 | RWE | 1898 | - |
| 6 | Orange | 1994 | 18000 |
| 6 | RWE | 1898 | 19800 |
| 7 | Orange | 1994 | - |
| 7 | RWE | 1898 | - |
| 8 | Orange | 1994 | - |
| 8 | RWE | 1898 | - |