Bazy danych

Model Oracle

  • 2012-09-04 19:34
  • 0 komentarzy
  • 1536 odsłon
  • Oceń ten tekst jako pierwszy
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ę:

idnameyearcapitalcountry
1TP S.A.1991240000Warszawa
2Kolporter S.A.199220000Kielce
3Orange1994564000Warszawa
4Siemens1986190000Berlin
5Samsung193820000Suwon
6Nokia196518000Espoo
7Allianz189018000Berlin
8RWE189818000Berlin


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 (
  );


idnamecountrycapital
1TP S.A.Warszawa240000
2Kolporter S.A.Kielce20000
3OrangeWarszawa564000
4SiemensBerlin190000
6NokiaEspoo18000
7AllianzBerlin18000
8RWEBerlin18000
5SamsungSuwon20000

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
       );


idcapital
109999
999

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
       );

idcapital
10240000
220000
3564000
4190000
618000
718000
818000
520000
109999
999

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;


countrynameyearcapital
BerlinSiemens198620
BerlinTP S.A.199110
EspooSiemens198620
EspooTP S.A.199110
KielceSiemens198620
KileceTP S.A.199110
SuwonSiemens198620
SuwonTP S.A.199110
WarszawaSiemens198620
WarszawaTP S.A.199110

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;

countrynameyearcapital
WarszawaTP S.A.199110

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;

countrynameyearcapital
Berlinnowa firma2005-
BerlinTP S.A.2011-
Espoonowa firma2005-
EspooTP S.A.2011-
Kielcenowa firma2005-
KielceTP S.A.2011-
Suwonnowa firma2005-
SuwonTP S.A.2011-
Warszawanowa firma2005-
WarszawaTP S.A.1991564000
WarszawaTP S.A.2011564000


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;


idcapitalnameyear
11TP S.A.1991
22Kolporter S.A.1992
33Orange1994
44Siemens1986
55Samsung1938
66Nokia1965
77Allianz1890
88RWE1898

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;


countrynamecapitalyear
BerlinRWE180001898
BerlinAllianz180001890
BerlinSiemens1900001986
EspooNokia180001965
KielceKolporter S.A.200001992
SuwonSamsung200001938
WarszawaOrange5640001994
WarszawaTP S.A.2400001991

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;


idnameyearsales
1Orange1994-
1RWE1898-
2Orange1994-
2RWE1898-
3Orange1994-
3RWE1898620400
4Orange1994-
4RWE1898-
5Orange1994-
5RWE1898-
6Orange199418000
6RWE1898-
7Orange1994-
7RWE1898-
8Orange1994-
8RWE1898-


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;


idnameyearsales
1Orange1994-
1RWE1898-
2Orange1994-
2RWE1898-
3Orange1994-
3RWE1898-
4Orange1994-
4RWE1898-
5Orange1994-
5RWE1898-
6Orange199418000
6RWE189819800
7Orange1994-
7RWE1898-
8Orange1994-
8RWE1898-

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;


idnameyearsales
1Orange1994-
1RWE1898-
2Orange1994-
2RWE1898-
3Orange1994-
3RWE1898-
4Orange1994-
4RWE1898-
5Orange1994-
5RWE1898-
6Orange199418000
6RWE189819800
7Orange1994-
7RWE1898-
8Orange1994-
8RWE1898-