Model Oracle

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	-