Model Oracle

kasiaKasia

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 -

0 komentarzy