„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.

Wybrać „coś z czegoś” czy „z czegoś coś” wybrać, oto jest pytanie…

Marcinie zwróć uwagę, iż zapytania dotychczas z jakimi miałeś do czynienia miały postać: wybieram (select) wartości z pól z tabeli (from ….). Przy czym po „from” można podać nazwę/nazwy konkretnych tabel albo po prostu napisać w nawiasie kolejne zapytanie (zwane podzapytaniem).

Wybierałeś dotychczas „coś z czegoś”:

SELECT ….. FROM
(SELECT ….. FROM …. WHERE …. GROUP BY ….) alias_tabeli
WHERE …. GROUP BY …. ORDER BY…. ;

W SQL istnieje podobnie jak w życiu możliwość wybierania nie tylko „coś z czegoś” ale „z czegoś coś”. Co więcej okazuje się, iż ten drugi sposób („z czegoś coś”) jest dla serwera bazy wydajniejszy, Twoje zapytanie zostanie wykonane szybciej.

Robi się to następująco:

WITH alias_tabeli
AS (SELECT ….. FROM …. WHERE …. GROUP BY ….)
SELECT …. FROM alias_tabeli WHERE …. GROUP BY …. ORDER BY…. ;

Jak widać dzięki pokolorowaniu na obrazku poniżej obu wyżej wymienionych konstrukcji mają one dużo wspólnych elementów, pojawiło się jedynie dodatkowo WITH oraz AS i wyraźnie inna kolejność.

Użycie drugiej konstrukcji powoduje, iż wyniki są buforowane i dzięki temu jest to wydajniejszy sposób. Warto go znać, bo może istotnie przyspieszyć uzyskanie wyniku zapytania, oczywiście kosztem obciążenia bazy, ale wydajniejszy dla Ciebie, powodujący, iż wyraźnie szybciej otrzymasz wynik.

W.

Wyzwanie... with over :-)

Marcinie, naczytałeś się porad, to czas na wyzwanie.

Wyzwanie jest oczywiście dla wszystkich, pomysły, propozycje proszę w komentarzach. 

Opis danych źródłowych. Posiadamy tabelę: ROZLICZENIA_DO_OVER_PLUS. Tabela składa się z czterech pól:

•     data_stanu: trzy wartości przyjmuje to pole, trzy daty wskazujące na określony koniec roku,

•     kontrakt: czteroznakowy kod kontraktu,

•     saldo: wartość liczbowa, ile z danego kontraktu na dany koniec roku, na daną datę stanu jeszcze  otrzymamy pieniędzy,

•     typ_towaru: dwie wartości pole przyjmuje to jest: noteboki lub akcesoria, wskazuje a rodzaj towaru zakupiony przez kontrahenta.

Zadanie wykonania: napisać kwerendę, w wyniku której otrzymamy tabelę, a jakżeż by inaczej, zawierającej następujące pola i wartości w nich.

•     datę stanu tj. wartość z pola data_stanu,

•     typ towaru tj. wartość z pola typ_towaru (noteboki lub akcesoria),

•     łączne saldo w danej dacie, ile po prostu pieniędzy się spodziewamy łącznie w danej kwocie, łącznie, jedna wartość podkreślam,

•     saldo w danej dacie i na dany rodzaj typu towaru,

•     relację w procentach salda w danej dacie przypadającego na daty towar do łącznego salda w danej dacie np. saldo wynikające ze sprzedaży akcesoriów na dzień 20101231 wynosi 0,31 * 100 = 31%,

•     liczbę kontraktów aktywnych łącznie w dacie,

•     liczbę kontraktów w dacie przypadającą na noteboki lub akcesoria,

•     relację w procentach liczby kontraktów dotyczących danego typu towaru do łącznej liczby aktywnych kontraktów w danej dacie, np. relacja liczby kontraktów aktywnych związanych ze sprzedażą akcesoriów do łącznej liczby aktywnych kontraktów na dzień 20101231 wynosi 0,43*100 = 43%.

Wyraźnie zadanie wskazuje liczbę kolumn. Liczba kolumn w wyniku musi być: 8 (osiem).

Dane oraz zadanie w arkuszu excel są dostępne tutaj i na obrazku. W arkuszu excel są dane źródłowe oraz rozwiązanie w tym narzędziu, są formuły po prostu.

Linki:

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

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

Czy Marcinie podejmiesz się wyzwania?

Podpowiadam, iż będę chciał w ten sposób pokazać wykorzystanie dwóch typów grupowania, że tak powiem typowego GROUP BY oraz rzadziej stosowanego OVER (PARTITION BY).

WITH w tytule posta też nie jest przypadkowe…

Wyzwanie jest oczywiście dla wszystkich, pomysły, propozycje proszę w komentarzach. 

W.