Klauzula MODEL - własne formuły część 1

 Poprzedni post zakończyłem następującym zapytaniem:
 

 SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES( 
       NUM_ROWS AS LICZBA_WIERSZY,
      'NIC' AS NUM_ROWS,
       TRUNC(SYSDATE) AS DATA,
       CAST(NULL AS VARCHAR2(30 CHAR) ) AS TEKST,
      1 LICZBA)
 RULES(); 

Co w nim mamy:

• definicję  wymiaru (tutaj nazwa tabeli)
• definicje kolumn, dla których określmy, typ, wielkość i ew wartość domyślną
• pustą klauzulę  RULES()

O ile  mamy zainstalowany schemat HR, zapytanie zwraca następujące wyniki:
REGIONS                4    NIC    2018-11-10 00:00:00        1
COUNTRIES          25    NIC    2018-11-10 00:00:00        1
LOCATIONS          23    NIC    2018-11-10 00:00:00        1
DEPARTMENTS    27    NIC    2018-11-10 00:00:00        1
JOBS                       19    NIC    2018-11-10 00:00:00        1
EMPLOYEES       107    NIC    2018-11-10 00:00:00        1
JOB_HISTORY      10    NIC    2018-11-10 00:00:00        1
Zakładamy, że na razie używamy domyślnych ustawień klauzuli model. Klauzula RULES() zawiera  sposób wyliczenia kolumn zdefiniowanych w klauzuli  MEASURES() .Klauzula ta oczywiście może być pusta,Odwołanie do poszczególnych wartości w kolumnach następuje za pomocą nawiasu kwadratowego.
Korzystamy z dwóch predefiniowanych operacji dostępu CV() i ANY, gdzie CV() to bieżaca wartość wymiaru,  a ANYto dowolna wartość wymiaru.  CV() wykorzystuje się w wyrażeniach dla  bieżącego wiersza, a ANYdla operacji agregujących lub przy ustawianiu wartości dla całej kolumny

Zacznijmy od najprostszych  
 RULES( NUM_ROWS['JOBS'] = -1 );
Powyższa klauzula wstawiona do zapytania zmieni nam jedną wartość,  co  widać poniżej na czerwono: 
REGIONS                4    NIC    2018-11-10 00:00:00        1
COUNTRIES          25    NIC    2018-11-10 00:00:00        1
LOCATIONS          23    NIC    2018-11-10 00:00:00        1
DEPARTMENTS    27    NIC    2018-11-10 00:00:00        1
JOBS                        -1    NIC    2018-11-10 00:00:00        1
EMPLOYEES       107    NIC    2018-11-10 00:00:00        1 


 RULES( NUM_ROWS['JOBS'] = 44 , NUM_ROWS['JOBS'] = -1 );
 Powyższa klauzula  ustawia wartość wiersza w kolumnie NUM_|ROWS dla tabeli JOBS najpierw
na 44, a później na- 1, wynik taki sam jak dla przypadku
 RULES( NUM_ROWS['JOBS'] = -1 );

 RULES(
    TEKST[ NAZWA_TABELI<>'JOBS' ] = 'To nie jest robota' 
 );  

Klauzula powyżej dla wszystkich rekordów, gdzie nazwa tabeli jest inna ni z JOBS ustawia w kolumnie TEKST   wartość:  To nie jest robota. Otrzymujemy poniższy wynik
REGIONS               4    NIC    2018-11-12 00:00:00    To nie jest robota    1
COUNTRIES        25    NIC    2018-11-12 00:00:00    To nie jest robota    1
LOCATIONS         23    NIC    2018-11-12 00:00:00    To nie jest robota    1
DEPARTMENTS   27    NIC    2018-11-12 00:00:00    To nie jest robota    1
JOBS                      19    NIC    2018-11-12 00:00:00                                    1
EMPLOYEES      107    NIC    2018-11-12 00:00:00    To nie jest robota    1
JOB_HISTORY     10    NIC    2018-11-12 00:00:00    To nie jest robota    1



 

Klauzula MODEL - najprostsze przykłady

Klauzulę MODEL umieszczamy w zapytaniu  przed klauzulą porządkującą ORDER BY. Służy ona do wykonywania dość złożonych obliczeń  na jedno i wielowymiarowych arkuszach, jest to pewna proteza MS Excel w SQL. Poniżej na przykładzie powszechnie znanej tabeli słownikowej zobaczymy jej działanie
SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
 DIMENSION BY (TABLE_NAME)
 MEASURES( NUM_ROWS)
 RULES();

Zapytanie zwraca nam tylko kolumny zdefiniowane w klauzulach wymiarów( DIMENSION BY) i miar ( MEASURES ). Wymiar to zbiór kolumn o niepustych wartościach, który pełni funkcję  klucza, przez co zawsze dla zapytania musi być unikalny. Akurat nazwa tabeli w danym schemacie musi być unikalna. Miara to dowolna kolumna z zapytania lub zdefiniowana przez użytkownika.
Zatem jako wynik otrzymamy  kolumny TABLE_NAME i NUM_ROWS z widoku ALL_TABLES.

Poniższe zapytanie zwróci błąd ORA-32638: Non unique addressing in MODEL dimensions, bo dla każdej nazwy tabeli, która ma być kluczem, występują w wyniku operacji UNION ALL na dwóch identycznych zbiorach, Jako wymiaru można użyć funkcji ROWNUM, ale czy takie rozwiązanie ma sens ??
SELECT* FROM
( 
       SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
           UNION ALL
      SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
)
MODEL
 DIMENSION BY (TABLE_NAME )
 MEASURES( NUM_ROWS )
 RULES();


Dla wymiarów i miar można stosować  aliasy, co pokazuje poniższy przykład - zapytanie zwróci nam wiersze NAZWA_TABELI i LICZBA_WIERSZY .

SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES( NUM_ROWS AS LICZBA_WIERSZY)
 RULES(); 

Jak należy definiować własne kolumny?? Poniższy przykład pokazuje kilka możliwości:

 SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES( 
       NUM_ROWS AS LICZBA_WIERSZY,
      'NIC' AS NUM_ROWS,
       TRUNC(SYSDATE) AS DATA,
       CAST(NULL AS VARCHAR2(30 CHAR) ) AS TEKST,
      1 LICZBA)
 RULES(); 

 Następujące kolumny miar zostały utworzone:

1. LICZBA_WIERSZY  -  to kolumna NUM_ROWS z widoku
2. NUM_ROWS -  to kolumna tekstowa zainicjowana słowem NIC, czyli może mieć długość maksymalnie trzy znaki
3. DATA - kolumna typu DATE zainicjowana bieżącą datą
4. TEKST - trzydziestoznakowy, niezainicjowana kolumna typu VARCHAR2
5. LICZBA - kolumna typu NUMBER