SQL dla zielonych i żółtych

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.

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.

Microsoft SQL Server: nowa baza, ćwiczenia z tworzeniem tabel, over...

Cześć Marcinie,

Tym razem druga próba, drugie podejście do tej formy komunikacji.
Zamieszczam film jak utworzyć nową bazę na MICROSOFT SQL Server i jak później uruchamiać kwerendy.

Plik na którym ćwiczyłem jest dostępny tutaj.
Film też jest tutaj.

Ten plik i rozwiązanie pokazane w tym pliku: to jest wykorzystanie klauzuli WITH, wykorzystanie PARTITION OVER oraz GROUP BY w jednej kwerendzie (ale nie w jednym select) omówię w następnych postach.

Przy okazji też widać, jak to nie jest łatwo z typami danych :-)

Pozdrawiam,

W.

OVER i (partition by....)

Marcin,

Nieco o niecne intencje Ciebie podejrzewam. Jakiś taki czepliwy wydajesz mi się. Grupowanie i partycjonowanie to to samo, to synonimy, to wiesz przecież o co chodzi, a skąd żeś się dowiedział o OVER i PARTITION BY?

No skąd, w jakimś przykładzie to było, ja skrzętnie milczę, omijam te klauzule…

Eh…

No nie cierpię o nich opowiadać.

Wrócę do początków bloga, do tabelki rozliczenia. Ta tabelka ma de facto trzy pola: datę stanu, kontrakt, saldo.

Zadanie: do każdego wiersza danych dodaj pole zawierającego sumę sald w danej dacie oraz określ procentowo jak ważne jest saldo danego kontraktu dla sumy sald w danej dacie, jak ważny był kontrakt na koniec danego roku.

Innymi słowy muszą być co najmniej dwa dodatkowe pola dodane, które będą pokazywały:

- sumę sald w danej dacie

- oraz relację poszczególnych saldo do sumy sald w danej dacie.

No i właśnie do takich zastosowań nadaje się wykorzystanie funkcji agregujących (tu sumę wykorzystam) i grupowanie z użyciem OVER i wskazywanie według czego grupujemy przy pomocy PARTITION BY.

Zobacz przykład, może rozjaśni, bo ja naprawdę nie lubię tych klauzul.

A tu kod do poćwiczenia.