Dzielimy kwotę na banknoty i monety

W jaki sposób  w SQL można rozbić dana kwotę na minimalną liczbę banknotów i monet??. To jest proste przy wykorzystaniu niezapomnianej klauzuli MODEL, kod przypomina prosty arkusz w MS Excel. Kwota, która ns interesuje wstawiana jest zamiast  przykładowej zielonej kwoty w kolorze zielonym Polecam analizę tego zapytania, ma kilka smaczków takich jak:

• podzapytanie ze złączeniem kartezjańskim
• ciekawy warunek stopu w iteracji
• indeks do iteracji liczony  w funkcji analitycznej

SELECT *
  FROM ( SELECT KWOTA,
                NOMINAL,
                LICZBA_NOMINALOW,
                RESZTA
           FROM (   SELECT LICZBA * MNOZNIK                                       NOMINAL,
                           ROW_NUMBER( ) OVER (ORDER BY LICZBA * MNOZNIK DESC)    LP
                      FROM( SELECT 1 AS LICZBA FROM DUAL
                            UNION
                            SELECT 2 FROM DUAL
                            UNION
                            SELECT 5 FROM DUAL),
                           (SELECT 100 AS MNOZNIK FROM DUAL
                            UNION
                            SELECT 10 FROM DUAL
                            UNION
                            SELECT 1 FROM DUAL
                            UNION
                            SELECT 0.1 FROM DUAL
                            UNION
                            SELECT 0.01 FROM DUAL)
                  ORDER BY 1 DESC )
         MODEL
             DIMENSION BY( LP - 1 LP )
             MEASURES( NOMINAL,
                       15329.98 KWOTA,
                       0 LICZBA_NOMINALOW,
                       1 RESZTA,
                       0 Z )
             RULES
             ITERATE( 100 ) UNTIL (RESZTA[ITERATION_NUMBER] = 0)
             (
                 UPSERT
                 KWOTA [ITERATION_NUMBER] =
                     CASE ITERATION_NUMBER
                         WHEN 0 THEN TRUNC( KWOTA[0], 2 )
                         ELSE RESZTA[ITERATION_NUMBER - 1]
                     END,
                 LICZBA_NOMINALOW [ITERATION_NUMBER] =
                     TRUNC(
                         KWOTA[ITERATION_NUMBER] / NOMINAL[ITERATION_NUMBER] ),
                 RESZTA [ITERATION_NUMBER] =
                       KWOTA[ITERATION_NUMBER]
                     -   NOMINAL[ITERATION_NUMBER]
                       * LICZBA_NOMINALOW[ITERATION_NUMBER] ) )
 WHERE LICZBA_NOMINALOW <> 0

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