Gdzie jest Anna Kowalska??

Tworzymy sobie tabelę jak poniżej
CREATE TABLE KLIENCI
 (
            ID_KLIENTA NUMBER(10) NOT NULL,   
            IMIE  VARCHAR2 (50 CHAR)  NOT NULL,
            NAZWISKO VARCHAR2 (100 CHAR) NOT NULL
...................
) 
Czy   to już wystarczy do poprawnego wyszukiwania klientów np Anny Kowalskiej?? Jeszcze niestety nie.
W jaki sposób mogą zostać zapisane te dane:
Anna Kowalska
ANNA KOWALSKA
AnnA  KoWalskA
anna  Kowalska            
   Anna KOwalska

Możliwości jest wiele.  Problem opisany przez mnie dotyczy standaryzacji danych tekstowych...Ten aspekt należy ustalić już sobie na etapie projektowania systemu. - w jaki sposób standaryzujemy istotne dane tekstowe, a w tym przypadku, czy imiona i nazwiska klientów zapisujemy:

• dużymi  literami
• bez niewidocznych znaków wiodących i kończących  w polach imienia i nazwiska oraz znaków interpunkcyjnych
• czy dodajemy dodatkowe sprawdzenia na poziomie bazy danych np.
• CHECK (IMIE = UPPER(TRIM(IMIE) ) )
• lub CHECK (IMIE = INITCAP(TRIM(IMIE) ) ) 
• znaki interpunkcyjne  można  wyłowić za pomocą wyrażeń regularnych

Do czego to prowadzi??

• Otrzymujemy złożone i wolne zapytania jak np. poniżej

SELECT * FROM KLIENCI WHERE  REGEXP_LIKE(TRIM(UPPER( REGEXP_REPLACE(IMIE, '[^[:alpha:]]', ''))),  '^AN(N|K|I)A$') AND TRIM(UPPER(REGEXP_REPLACE(NAZWISKO, '[^[:alpha:]]', ''))) = 'KOWALSKA'

• Instrukcja REGEXP_REPLACE(IMIE, '[^[:alpha:]]', '')  służy do wycinania znaków, które nie są  literami (dla złożonych wieloczłonowych nazwisk może być problem)
• Uważny Czytelnik dostrzeże, że funkcja TRIM jest niepotrzebna i spełnia tylko rolę przejaskrawiającą
• Nawet takie poplątane zapytania mogą pokrywać jedynie większość danych, ale może istnieć wiele błędnych przypadków, których nie jesteśmy w stanie przewidzieć

Co nam da standaryzacja??

• Wyszukiwanie będzie działać szybciej, jeśli będą założone zwykłe indeksy po imieniu i nazwisku, stosowanie funkcji konwertujących będzie przeczesywać cała tabelę i generować niepotrzebne obciążenie
• Zapytania staną się prostsze i szybsze. Zapytanie poniższe będzie wystarczające:

SELECT * FROM KLIENCI WHERE IMIE = 'ANNA' AND NAZWISKO = 'KOWALSKA'

•  Indeks po nazwisku  zapewni range scan zamiast full scan
• Wyniki raportów będą wymagały mniejszej standaryzacji
• Standaryzacja  zmniejsza koszty utrzymania systemu

A co można jeszcze zrobić w tym przypadku??

• zesłownikować imiona, żeby była tylko Anna, a nie Anka lub Ańka  lub Ania.  Najczęściej występujące  imiona da się zebrać w słowniku , a na  formatce przy polu imię można wprowadzić checbox ,., którego odznaczenie wyłączy walidację imion ze słownikiem. Ciągle pojawiają się nowe serialowe inspiracje oraz imiona zagraniczne, których  nie da się  uwzględnić. Ale mimo wszystko są to sytuacje wyjątkowe

Klauzula MODEL c.d. - funkcje grupujące

Rozważmy poniższe zapytanie
SELECT* FROM ALL_TABLES WHERE OWNER = 'HR'
MODEL
DIMENSION BY (TABLE_NAME AS NAZWA_TABELI)
 MEASURES(
       NUM_ROWS AS LICZBA_WIERSZY,
      1 AS SUMA,
      1 AS SREDNIA
      )
 RULES(
 SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ANY],
 SREDNIA[ANY] = ROUND(AVG ( LICZBA_WIERSZY)[ANY])
 )
 ORDER BY NAZWA_TABELI;

Zwraca ono następujące kolumny:

• NAZWA_TABELI - pole pobierane z widoku ALL_TABLES, tutaj występuje jako indeks, daltego wartość tego pola powinna być unikalna i niezmienna
• LICZBA _WIERSZY - pole pobierane z kolumny NUM_ROWS widoku ALL_TABLES, zawiera  liczbę wierszy w tabeli na ostatnie odświeżenie statystyk
• SUMA - pole  wyliczeniowe typu numerycznego, wartość początkowa  w deklaracji pola w sekcji MEASURES w postaci 1 AS SUMA determinuje typ zmiennej
• SREDNIA - pole wyliczeniowe typu numerycznego

W sekcji  RULES mamy  następujące formuły
 SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ANY],
 SREDNIA[ANY] = ROUND(AVG ( LICZBA_WIERSZY)[ANY])

Słowo ANY oznacza, że dane  wyrażenie wykonuje się dla każdej wartości indeksu.
Dla każdej wartości indeksu dokonywane jest sumowanie wartości z kolumny LICZBA_WIERSZY dla wszystkich wierszy. Zwróćmy uwagę, że  po słowie SUM w nawiasie jest  nazwa kolumny, zakres sumowania znajduje się w nawiasie kwadratowym. Analogicznie wyliczana jest  wartość średniej.

U mnie zapytanie zwraca następujące wartości.Dla kolumny LICZBA_WIERSZY skróciłem nagłówek
NAZWA_TABELI  LW   SUMA     SREDNIA
COUNTRIES          25          215     30,71
DEPARTMENTS    27          215     30,71
EMPLOYEES       107          215     30,71
JOB_HISTORY      10          215     30,71
JOBS                      19           215     30,71
LOCATIONS         23           215     30,71
REGIONS               4            215     30,71



Spróbujmy te formuły nieco zmodyfikować. Zwróćmy uwagę, na sortowanie po nazwie tabeli.

 SUMA[ANY] = SUM ( LICZBA_WIERSZY) [NAZWA_TABELI LIKE 'J%']
Powyższe wyrażenie sumuje liczbę kolumn, ale do sumy bierze wiersze, dla których NAZWA tabeli zaczyna się  od litery J.

 SUMA[ANY] = SUM ( LICZBA_WIERSZY) [NAZWA_TABELI  IN ('COUNTRIES', 'JOBS', 'REGIONS') ]
Do sumy  brane są wiersze dla tabel występujących na liście zdefiniowanej  po  operatorze wyliczeniowym IN. Wartości z trzech wierszy wchodzą do sumy/

  SUMA[NAZWA_TABELI   IN ('COUNTRIES', 'JOBS', 'REGIONS')] = SUM ( LICZBA_WIERSZY) [ANY ],
W tym przypadku suma wyliczana jest dla trzech wierszy i uwzględnia dane ze wszystkich wierszy,  które znajdują się na liście, dla pozostałych wierszy wyświetla się 1,  ponieważ pole suma w klauzuli MEASURES inicjowane jest taką wartością

 SUMA[ANY] = SUM ( LICZBA_WIERSZY) [ NAZWA_TABELI <= CV() ]
Funkcja CV()  (należy pamiętać o nawiasach !!!!!)  - zwraca nam  bieżącą wartość indeksu, czyli w naszym przypadku wartość pola NAZWA_TABELI. W ten sposób otrzymamy sumę narastająco z uwzględnieniem aktualnego sortowania.

 SUMA[ANY] = SUM ( LICZBA_WIERSZY)  OVER ()
Jest to sumowanie wierszy z wykorzystaniem składni z funkcji analitycznych.

SUMA[ANY] = SUM ( LICZBA_WIERSZY)  OVER ( ORDER BY NAZWA_TABELI)
Jest to suma narastająco  z wykorzystaniem składni z funkcji analitycznych.

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', '')