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

Przykłady błędnych zapytań odnoszących sie do wartości NULL

Wartość  NULL oznacza , ze dane pole w bazie danych lub zmienna w kodzie PL/SQL nie zostały zainicjowane... O ile  powszechnie  przy porównaniach stosowane są operatory IS NULL lub IS NOT NULL,  inne ciekawsze przypadki nie zawsze są  prawidłowo obsługiwane.
 Zwróćmy uwagę że wartości NULL nie są porównywalne między sobą ani tez  z innymi niepustymi wartościami...  Oracle wg mnie powinien  potraktować   użycie NULL  w warunkach po klauzuli WHERE przy operatorach porównujących jako błąd , wszelkie warunki typu a  = NULL, a < NULL.

Dla operatorów arytmetycznych jest przyjęta zasada, jeśli co najmniej jeden argument  IS NULL, to wartość też jest NULL.  Dlatego zapytanie
  SELECT  1 + NULL FROM dual  zwróci wynik NULL
a zapytanie
  SELECT NULL/0   FROM dual zwróci też NULL bez generowania wyjątku.

Zatem też poniższe zapytanie  nie zwróci wyjątku, jeżeli co najmniej jeden ze składników  sumy  w mianowniku będzie  niezainicjowany, mimo, że pozostałe będą sumować się do 0

SELECT (a+b+c+d) / e FROM  tabela
jeśli  którekolwiek  z pól  bazie miało wartość NULL, wynik będzie także NULL.

W wyrażeniach  tekstowych należy zwrócić uwagę, że pusty tekst traktowany jest jako niezainicjowana wartość, co więcej długość takiego pola wynosi NULL, a nie zero
 SELECT NVL(LENGTH(''), -1) FROM DUAL --zwraca -1
Zatem wszystkie poniższe trzy zapytania nie zwrócą żadnego rekordu, w każdym mamy porównanie względem wartości NULL
     
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = ''
SELECT * FROM HR.EMPLOYEES WHERE LENGTH(LAST_NAME) =0
SELECT * FROM HR.EMPLOYEES WHERE LENGTH(LAST_NAME)  < 1

Poniższe zapytanie nie pokaże rekordów,  dla których pole COMMISSION_PCT ma wartość NULL
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT IN (0.1, 0.2, NULL)
Prawidłowe wygląda następująco
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT IN (0.1, 0.2) OR COMMISSION_PCT IS NULL

Z operatorem NOT IN sprawa wygląda ciekawiej, jeśli jedna wartość na liście jest pusta, to zapytanie nic nie zwróci
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT NOT  IN (0.1, 0.2, NULL)
Prawidłowe wygląda następująco
SELECT * FROM HR.EMPLOYEES WHERE COMMISSION_PCT NOT IN (0.1, 0.2) AND COMMISSION_PCT IS NOT  NULL 

Zgodnie z powyższą zasadą to zapytanie też nie zwróci rekordów - NULL został przemycony w pustym tekście
SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME NOT IN ('Lorentz', '')

Ściągawka dla Kolegi - dyrektora handlowego w pewnej firmie IT - zastosowanie złączeń kartezjańskich

Zainspirowany bardzo bogatym korposłownictwem z zakresu prezentacji handlowych rozwiązań i usług postanowiłem  napisać ściągawkę do wykorzystania przez przesympatycznego prelegenta. Przykład też pokazuje w jaki sposób zamienić elementy listy na  kolejne wiersze w strukturze zbliżonej do tabeli. Świadome pominięcie warunków złączenia ma na celu wygenerowanie wszystkich możliwych  połączeń bardzo profesjonalnych sformułowań..

SELECT TABLE_1.COLUMN_VALUE || ' ' ||
     TABLE_2.COLUMN_VALUE  || ' ' ||
     TABLE_3.COLUMN_VALUE || ' ' ||
     TABLE_4.COLUMN_VALUE || ' ' || 
     TABLE_5.COLUMN_VALUE || ' ' ||
     TABLE_6.COLUMN_VALUE || ' ' ||
     TABLE_7.COLUMN_VALUE    
FROM
 TABLE(SYS.ODCIVARCHAR2LIST('Przestrzeganie najwyższych standardów podczas ',
    'Ciągłe doskonalenie procesu',
    'Wieloletnie doświadczenie w zakresie',
    'Unikalny zbiór kompetencji naszego zespołu w zakresie')) TABLE_1,
 TABLE(SYS.ODCIVARCHAR2LIST( 'analizy', 'tworzenia', 'projektowania', 'testowania', 'wdrażania' )) TABLE_2,
 TABLE(SYS.ODCIVARCHAR2LIST( 'skalowalnych ', 'wydajnych', 'niezawodnych' , 'optymalnie wspierających biznes') ) TABLE_3,
 TABLE(SYS.ODCIVARCHAR2LIST( 'aplikacji', 'systemów', 'kompleksowych rozwiązań') ) TABLE_4,
 TABLE(SYS.ODCIVARCHAR2LIST( 'w największych bankach', 'w kluczowych organach administracji państwowej', 'w największych zakładach produkcyjnych', 'w największych sieciach handlowych') ) TABLE_5,
 TABLE(SYS.ODCIVARCHAR2LIST( 'potwierdza', 'zapewnia', 'trafnie weryfikuje') ) TABLE_6,
 TABLE(SYS.ODCIVARCHAR2LIST( 'wybór naszej firmy jako gwarancję sukcesu ',
  'korzyści z długofalowej współpracy', 'terminowe wdrożenie systemu', 'uzyskanie przewagi technologiczną dzięki naszej firmie') ) TABLE_7

Pseudominizacja numeru PESEL - przykład rozwiązania

Problem pseudominizacjidanych  występuje często kiedy dane z systemów zawierających dane osobowe lub dane wrażliwe musimy  udostępnić osobom  trzecim,  ukrywając te dane i możliwość identyfikacji osób

Założenia algorytmu:

• nowy PESEL dla danej osoby zachowuje informacje o płci i o dacie urodzenia
• numer ewidencyjny w obrębie daty urodzenia  jest numerowany od stałej cn_DayIndex w dół
• zakłada się, że parametry są poprawne -  parametr pc_PESEL nie jest walidowany
• na wejściu algorytmu powinien być zbiór SELECT DISTINCT PESEL FROM....
• przed  wykonaniem anonimizacji należy wykonać blok:

              BEGIN
                    PCKG_ANONYMIZE_DATA.PDB_CLEARINTERNALDATA;
             END;   

Jak to działa??
Poniższe zapytanie generuje nowy PESEL dla Jarosława K.
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
Otrzymamy       49061899990
Wywołajmy teraz  funkcję  5 razy
SELECT PCKG_ANONYMIZE_DATA.FDB_GenerateNewPESEL('49061804592') FROM DUAL
CONNECT BY LEVEL <= 5
Jako wynik otrzymamy
49061899976
49061899952
49061899938
49061899914
49061899891
Czyli widzimy, że dla kolejnych nr  PESELw obrębie daty urodzenia generowane są kolejne nowe PESELE, ale nie jest sprawdzana unikalność nr PESEL na wejściu..

Czy nowe nr PESEL zapewniają pełną pseudonimizację ze względu na ten nr. Nie..,  Jeśli np wiemy , ze  w bazie było dwóch  klientów  w wieku po 80 lat, to możemy z dużym prawdopodobieństwem ich  zidentyfikować, jeśli maja różne daty urodzenia. Dla  lat, w których jest dużo klientów  w poszczególnych dniach będzie na podstawie  tylko nr  PESEL niemożliwe

CREATE OR REPLACE PACKAGE PCKG_ANONYMIZE_DATA IS
    PROCEDURE PDB_ClearInternalData;  
    FUNCTION FDB_GenerateNewPESEL(pc_PESEL VARCHAR2) RETURN VARCHAR2 ;
    PRAGMA RESTRICT_REFERENCES (FDB_GenerateNewPESEL, WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY PCKG_ANONYMIZE_DATA
IS
   TYPE T_NUMBERS IS TABLE OF VARCHAR2 (4)
      INDEX BY VARCHAR2 (6);

   Vt_Numbers             T_NUMBERS;
   cn_DayIndex   CONSTANT NUMBER (4) := 9999;
/************************************************************************/
   PROCEDURE PDB_ClearInternalData
   IS
   BEGIN
      Vt_Numbers.DELETE;
   END;
/************************************************************************/
   FUNCTION FDB_GenerateNewPESEL (pc_PESEL VARCHAR2 )
      RETURN VARCHAR2
   IS
      TYPE t_Weights IS VARRAY (10) OF SIMPLE_INTEGER;

      vt_Weights    t_weights
                       := t_weights (1,3,7,9,1,3,7,9,1,3);
      vn_CheckSum   SIMPLE_INTEGER := 0;
      vc_PESEL      VARCHAR2 (15);
      vc_DayIndex   NUMBER (4) := '0';
      vn_Plec       NUMBER (1);
   BEGIN
      vc_Pesel := SUBSTR (pc_PESEL, 1, 6);
      vn_Plec := SUBSTR (pc_PESEL, 10, 1);
--------------------
      IF  vt_Numbers.exists(vc_PESEL ) THEN              
        vc_DayIndex := NVL (Vt_Numbers (vc_Pesel) - 1, cn_DayIndex);        
      ELSE  
        vc_DayIndex := cn_DayIndex;           
      END IF;
--------------------
      IF MOD (vc_DayIndex, 2) <> MOD (vn_Plec, 2) THEN
         vc_DayIndex := vc_DayIndex - 1;
      END IF;
--------------------
      Vt_Numbers (vc_Pesel) := vc_DayIndex;
      vc_Pesel :=vc_Pesel || LPAD (TO_CHAR (vc_DayIndex), 4, '0');
--------------------
      FOR i IN 1 .. vt_Weights.COUNT
      LOOP
         vn_CheckSum :=
              vn_CheckSum
            + TO_NUMBER (SUBSTR (vc_PESEL, i, 1)) * vt_Weights (i);
      END LOOP;
--------------------
      IF MOD (vn_CheckSum, 10) = 0 THEN         vc_PESEL := vc_PESEL || '0'; 
    ELSE
         vc_PESEL := vc_PESEL || (10 - MOD (vn_CheckSum, 10));
      END IF;
--------------------
      RETURN vc_Pesel;
   /*EXCEPTION WHEN OTHERS THEN
      RETURN NULL;*/
   END;
/*******************************************************************/
BEGIN  
    Vt_Numbers.DELETE;
END;
/