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.