Cel: uzyskanie rekordu z np. największą wartością w skali grupy

Jak wyświetlić rekord (wszystkie atrybuty tj. zawartość wszystkich kolumn) z zestawu rekordów (czyli po prostu tabeli), który charakteryzuje się/zawiera informację dotyczącą osobnika o najwyższym wzroście.

To takie zadanko da się rozwiązać podzapytaniem, ale najprawdopodobniej wynikiem będzie jeden rekord i tyle (może być więcej, ale tylko wtedy gdy więcej niż jeden osobnik ma ten sam najwyższy wzrost).

Jak wyświetlić wszystkie rekordy (wszystkie atrybuty tj. zawartość wszystkich kolumn)  z zestawu rekordów (czyli po prostu z tabeli), które jest zestawieniem np. umów leasingowych a warunki klasyfikacji/wyboru do zestawienia to: wartość przedmiotu leasingu jest maksymalna i  od razu uwzględniamy rok zawarcia umowy (grupujemy po roku zawarcia umowy). 

Podkreślam: od razu uwzględnić rok zawarcia umowy i by wynik od razu jedną kwerenda dostać, a nie zapuszczać kilka kwerend zmieniając  rok.

Patent najbardziej uniwersalny przedstawiam na przykładzie, patent/pomysł zadziała i na MS SQL Serverze i na Oracle (na Oracle osobiście lubię parę wartości i operator IN, ale to nie działa na serwerze firmy Microsoftu).

Po kolei:
 

Najpierw wskazuję bazę, to poniższy zapis ustawi odpowiednią bazę:

use zielony;

Podglądam tabelę, co w niej jest, jakie kolumny:

select top 10 * from kontrakt_start;

Klauzulę: top 10 należy używać, oczywiście może być top 20 etc. To otrzymamy próbkę w tym przypadku 10 rekordów tabeli. Nie należy do podglądania pobierać całych tabel i dobry zwyczaj nakazuje jawnie pobierać próbki, a nie zdawać się na „mądrość” używanego oprogramowania. Top nie oznacza, maksymalnych wartości, to może zmylić, bo niby jakie kryterium?

Jak już widzimy to wiemy jakie mamy kolumny, to przechodzimy do rzeczy, rozwiązanie jest następujące.

select 
  k.*

  from

      kontrakt_start k  -- ta tabela ma alias k

     ,  (select
          max(wartosc_przedmiotu) max_wartosc 
         , year(data_umowy) rok_umowy 
         from kontrakt_start

        group by year(data_umowy) ) m  -- zapytanie generujące tabelkę pośrednią o aliasie m
   where
   k.wartosc_przedmiotu = m.max_wartosc 
   and year(k.data_umowy) = m.rok_umowy;

 

Czyli wyciągam dane z tabeli o aliasie „k” (czyli po prostu z wykazu umów), komplet/wszystkie atrybuty (wartości w kolumnach) ale łączę z drugą tabelą o aliasie „m”.

Tabela „m” to dwa pola zawierające maksymalną wartość przedmiotu jako maks_wartosc pogrupowaną na rok , drugie pole to właśnie rok umowy pozyskany funkcją YEAR z daty umowy.

I jak złączymy „k” i „m”, po klauzuli WHERE odpowiednimi warunkami to tym zapytaniem od razu dostaniemy wykaz umów, wszystkie atrybuty, z każdego roku, z maksymalną wartością w danym roku.

I przećwiczyć można, a wszystko jest do przećwiczenia na bazie testowej ZIELONYCH :-)
 
I jest zadanie, zmodyfikuj tak kwerendę, by uzyskać umowy z maksymalną wartością przedmiotu leasingu na tle grupy: marki (producenta przedmiotu leasingu).
 

Tu szukaj bazy testowej.

A tu obrazki i kwerenda, w tym rozwiązanie zadanka.

PS.  Patent, że tak powiem wypracowany samodzielnie, ale też opisany w Roz. 15 książki  „Antywzorce języka SQL” jako właściwe rozwiązanie. Maczkiem o posiadaniu tej książki i jej czytaniu, bo powiem otwarcie, ze 300  stron ma książka, a jak jedną trzecią zrozumiem co autor napisał, to będzie dobrze. No nie ma co się chwalić...

Zawartość

Co prawda nie ma tu tylu nowych ludzi co bym chciał, ale kilka jest :-)

To bardzo się cieszę.

To dla Was pewna forma spisu dotychczasowej zawartości mego mini bloga, a też mam nadzieję, że dla starszych przyjaciół się przyda.

Namiary na mnie i moja silnie podretuszowana fota. Namiary i fota.

Dlaczego powstają hurtownie danych. Hurtownia danych

Posty na temat grupowania:

Group by

Klauzula CUBE

Klauzula roll – up, dwa posty.  Roll – up i analiza kolejności pól w nawiasie, w roll up

OVER i PARTTION BY

Parę trików w EXCEL:

Insert, jak szybko zrobić w excel formuły insert

Tabela przestawna

Wyszukaj pionowo

Wykres automatycznie dostosowujący się do zakresu danych...

Lag i lead, przykłady zastosowania

ZNOWU LAG, nie mamy danych z dni wolnych w miesiącu, to jak uzupełnić dane?

Jak nie lagą (taki kij) to go leadem...

Wysyłka oferty, tu pięć postów z rzędu. To przykład analizy z użyciem danych wydobywanych przy pomocy SQL, jest wyłka oferty w czasie, jest podsumowanie efektów, analiza efektów w czasie.

Analiza post nr 1.

Analiza post nr 2.

Analiza post nr 3

Analiza post nr 4.

Analiza post nr 5.

Baza testowa. Zielony SQL, dorobił się bazy testowej :-)

Klauzula WITH, opis, zastosowanie w trzech postach.

With post nr 1.

With post nr 2.

With post nr 3.

Funkcja: CASE

Perypetie z NULL w dwóch postach, null po raz pierwszy, null po raz drugi.

Funkcja agregująca COUNT(*)

Kiedy przydaje się klauzula UNION

Posty na temat złączeń.

Złączenie post nr 1.

Złączenie post nr 2.

Złączenia post nr 3.

Złączenia post nr 4.

Operator IN.

Jak zapisać wynik kwerendy w nowej tabeli na MS SQL Server.

Sortowanie, czyli ORDER BY

Operator LIKE, czyli przykład na podstawie kodów pocztowych namierzyć województwo

Jak instalować bazy danych.

 

Jest jeszcze kilka innych, ale to już głównie polecane książki i „mądrości ludowe” w moim wydaniu.

Jeżeli masz pytanie, to proszę daj zań.

 

Pozdrawiam,

W.

Zielony SQL, dorobił się bazy testowej :-)

Zielony SQL, dorobił się bazy testowej :-)

Dostępna jako plik do podczepienia w MS SQL SERVER EXPRESS, plik z rozszerzeniem mdf jest pod linką poniżej. 

W tej lokalizacji też kilka slajdów jak ten plik podczepić do Servera przy użyciu STUDIA.

Tabelek jest tam całkiem dużo, bo to nieco śmietnik się zrobił, to też tam są tabelki wykorzystywane do wcześniejszych postów. Główne i najpełniejsze to te niżej wymienione.

/*
NA TYM PRACUJEMY
      select * from kostka_umowy;
      select * from klienci;
      select * from kontrakt_start;
      select * from portfel;
*/ 




https://drive.google.com/drive/folders/0B0DNBH1DOPAfc3dwU1EwZ0FoR0k?usp=sharing

ZNOWU LAG, nie mamy danych z dni wolnych w miesiącu, to jak uzupełnić dane?

ZNOWU LAG, nie mamy danych z dni wolnych w miesiącu, to jak uzupełnić dane?

Lepiej późno niż wcale, powiedziała pewna pani, gdy na pociąg się spóźniła… Pewną zagadkę swego czasu rozwiązywałem, rozwiązywałem, a później temat stał się nieaktualny :-)

Opis sytuacji:

W dni robocze są ładowane dane do hurtowni. Po prostu dla każdego dnia roboczego jest dostępna informacja o saldzie środków na koncie danego Klienta.

Odsetki płacimy za każdy dzień trzymania środków u nas przez Klienta, ale także za dni wolne.

Robimy plan kosztów na kolejny rok.  Mamy dane z jednego stycznia 2012 roku.

No to cóż, trzeba policzyć średnie saldo w miesiącu środków, to saldo pomnożyć przez stopę procentową, według której płacimy odsetki i  już wiemy ile zapłacimy Klientowi.

No właśnie, ale jak ma się średnia obliczona na danych w hurtowni z dni roboczych do średniej policzonej na wszystkich dniach w miesiącu. Jak uzupełnić informację o salda z niezaładowanych dni?

Oto jest pytanie?

Prezentacja problemu, dane, kody i tak dalej. Wszystko w lokalizacji poniżej. A rozwiązanie to połączenie wykorzystania COALESCE i LAG (w przypadku Oracle to wykorzystać należy Marcinie NVL2 i LAG). 

https://drive.google.com/drive/folders/0B0DNBH1DOPAfUk1JQktFVHRsTnc?usp=sharing

Jak nie lagą (taki kij) to go leadem...

Była promocja i tym podobne, posty, posty, a brak nowych treści odnośnie SQL.

Zatem poprawiam się, polecam książkę po raz wtóry: 

http://helion.pl/ksiazki/ms-sql-server-zaawansowane-metody-programowania-adam-pelikant,sqlszm.htm

A w tej książce na stronie 71 jest:

Nie po raz pierwszy staje się bardzo widoczny nacisk producentów baz danych na przetwarzanie analityczne. Praktycznie z każdą nową realizacją produktu wprowadzane są nowe funkcjonalności.

Już nie tak nowe, ale dwie wymienię LAG (w moich stronach, laga to taki kij) lub LEAD. To fajne funkcje jeżeli chcemy szybko policzyć zmiany, ale hurtem,  wskaźniki procentowe, ale chcemy na przykład wziąć wartość do licznika z danego wiersza, a do mianownika wartość z innego wiersza, np. 3 wiersze wstecz, jeżeli je posortować według daty6 (innego pola, ale sortowanie musi być, bo w bazie dane nie są posortowane tak jak mam intuicyjnie się wydaje, to błąd). 
Tu są dwie funkcje do stosowania, LAG i LEAD, różnica pomiędzy nimi jest taka, że LAG bierze wartość wstecz, przesunięcie wstecz, a LEAD wprzód. Nie da się wpisać w LAG lub w LEAD wartości ujemnej na drugim miejscu w nawiasie: sprawdziłem.   
Obie funkcje mają w nawiasie do wypełnienia trzy parametry:
Pierwszy jest obowiązkowy, trzeba podać z jakiego pola ma zapytanie pobrać wartość.
Drugi już nie jest obligatoryjny, jak pominiesz, to baza przyjmie domyślnie 1.
Jak trzeci pominiesz, to domyślnie baza przyjmie NULL

Ten NULL to ma zastosowanie jeżeli np. jest to pierwszy wiersz z sortowania, to 3 wstecz to co podać? Domyślnie jest NULL, ale możesz wskazać inną wartość. 

LAG (nazwa pola, przesunięcie1, wartość domyślna)

LEAD (nazwa pola, przesunięcie2, wartość domyślna) 

A więc ww. LAG  i LEAD działają jedynie na posortowanych wynikach, zatem po:

LAG lub LEAD musi być słowo kluczowe OVER a następnie w nawiasie minimum (ORDER BY nazwa pola).
W tym nawiasie może być jeszcze „partition by” ale to na teraz proszę Marcinie daj spokój, nie za dużo.
Zobacz  na przykładzie, tu zwróć uwagę, że pokazuję działanie LAG i LEAD na danych w tabeli portfel, która zawiera już włożone agregowane dane (bo to przykład), ale zamiast odwołania do tabeli portfel może i często w nawiasie bywa długi SELECT z funkcjami agregującymi, to wówczas widać finezję stosowania LAG i LEAD.

Potestuj działanie LAG i LEAD, a może się przekonasz do stosowania :-) 
W tej lokalizacji znajdziesz dane wsadowe, kwerendy, prezentację.

https://drive.google.com/drive/folders/0B0DNBH1DOPAfME5wUXlyb3V6U28?usp=sharing

Warto mieć książkę w PDF, do SQL

Warto mieć książkę w PDF.

W przypadku uczenia się SQL nie trzeba przepisywać kwerend z książki.

Tylko kopiuj i wklej oraz EXECUTE i widać efekt.

Nie przepadam za elektroniczną lekturą ale tu warto zrobić wyjątek.

Polecam książkę  do nauki SQL na MS Server:

MS SQL Server. Zaawansowane metody programowania.
Autor: Adam Pelikant
Do kupienia w HELION, pierwsza linka.

Pod drugą linką przygotowałem  slajdy jak podczepić pod serwer przykładową do książki bazę i jak z niej korzystać na zasadzie kopiuj i wklej.

http://helion.pl/ksiazki/ms-sql-server-zaawansowane-metody-programowania-adam-pelikant,sqlszm.htm

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