„Słowo się rzekło, kobyła u płotu” Rozwiazanie wyzwania z 11.11.

„Słowo się rzekło, kobyła u płotu” 

Tutaj:

https://pl.wikiquote.org/wiki/S%C5%82owo_si%C4%99_rzek%C5%82o,_koby%C5%82a_u_p%C5%82otu

polecam zajrzeć do informacji o pochodzeniu tego powiedzenia. Obiecałem to słowa dotrzymuję, aczkolwiek nieco rozczarowany Marcinie jestem, iż sam nie spróbowałeś podjąć wyzwania z dnia 11.11

Po kolei zatem:

Wyzwanie tutaj: http://zielonysql.blogspot.com/2015/11/wyzwanie-with-over.html

Opis klauzuli WITH tutaj:  http://zielonysql.blogspot.com/2015/11/wybrac-cos-z-czegos-czy-z-czegos-cos.html

Opis OVER oraz PARTITION BY tutaj: http://zielonysql.blogspot.com/2015/11/over-i-partition-by.html

Zwykłe grupowanie z użyciem GROUP BY to już na pewno znasz

Jak rozwiązałem wyzwanie, po kolei.

Tabela zawierające surowe/źródłowe dane to ROZLICZENIA_DO_OVER_PLUS (nazwa nie jest tu kluczowa, tak wyszło). Tabela posiada cztery pola (DATA_STANU, TYP_TOWARU, KONTRAKT, SALDO) i są w tej tabeli 52 wypełnione wiersze z danymi.

Używając klauzuli WITH tworzę tabelę na bazie ww. mającej identyczną liczbę wierszy co tabela źródłowa, do każdego wiersza danych dodaje dodatkowe informacje w trzech dodatkowych polach tj.: ZLICZ, SUMA_DATA_STANU, LICZBA_DATA_STANU.

• ZLICZ – tu wartość jest stała i jest to 1,
• SUMA_DATA_STANU – używam funkcji analitycznej SUM, ale z opcją OVER i wskazuję, iż partycjonowanie ma być po DATA_STANU, to powoduje , iż dla każdego wiersza, mającego tę samą wartość DATA_STANU będzie dodana suma SALDO w tej dacie.
• LICZBA_DATA_STANU – używam funkcji analitycznej COUNT, ale z opcją OVER i wskazuję, iż partycjonowanie ma być po DATA_STANU, to powoduje , iż dla każdego wiersza, mającego tę samą wartość DATA_STANU będzie dodana liczba wierszy w danej dacie, czyli można to zinterpretować jako liczbę kontraktów w danej dacie.

Polecam podejrzeć zawartość tabeli TABELA_BUFOROWANA zaznaczając kwerendę w nawiasie po klauzuli WITH i ją uruchamiając/wykonując.

No jak już mamy TABELE_BUFOROWANĄ to z niej wybieramy ponownie dane, ale już grupując tradycyjnie po DATA_STANU i TYP_TOWARU.

Tu zwracam uwagę na użycie funkcji analitycznej MAX. Każdy wiersz w TABELA_BUFOROWANA, mający tę samą wartość w polu DATA_STANU ma tez tę samą wartość w polach SUMA_DATA_STANU i LICZBA_DATA_STANU. A potrzebujemy wartości jednej, to by nie przekombinować użycie MIN lub MAX ze zbioru tych samych wartości daje szybka jedną unikalną wartość. Ten patent polecam zapamiętaj Marcinie. 

I wynik Marcinie widzisz, puściłem kwerendy na bazie ORACLE oraz na bazie MS SQL SERVER. Baza ORACLE bardzo dobrze odczytała moje intencje jeżeli chodzi o typy danych ale MS SQL SERVER już nie. Stąd konieczność użycia CAST, ROUND a i tak wynik jeszcze odbiega od oczekiwane w zakresie formatowania, ale to już Ci Marcinie zostawię do samodzielnego wykończenia.

A tutaj na koniec kod do uruchomienia, poćwiczenia na MS SQL SERVER.

https://drive.google.com/file/d/0B0DNBH1DOPAfcFZxemw0dm1FdkE/view?usp=sharing

Pozdrawiam,

W.