Wiele przykładów pokazuje największą wartość albo najwcześniejszą datę, albo najniższy wskaźnik itp. A jak odnaleźć drugą wartość!? Jak ustalić który produkt był w ramach jednego dużego zakupu kupowany jako drugi? Jak znaleźć drugą co do wartości wielkość zakupu?
Przypomina mi się dowcip:
Żona mówi do męża:
– Ale Ty jesteś pierdoła. Jesteś taki pierdoła, że większego na świecie
nie ma. Wszystko za co byś się nie wziął, zaraz chrzanisz. Gdybyś wystartował w konkursie na największego pierdołę, zająłbyś drugie miejsce.
– Dlaczego drugie?
– Bo taka jesteś pierdoła.
Drugi ma pecha! Okazuje się, że wcale nie. Można też odnaleźć drugą wartość.
Załóżmy, że mamy tabele o podanej niżej strukturze. Tak, struktura jest nieco dziwna, ale tak to właśnie wyglądało u klienta. Tabela sprzedaż opisuje sprzedaż a tabela Produkty opisuje kiedy sprzedaż miała miejsce i co za ile sprzedano:
CREATE TABLE Sprzedaz ( IdSprzedazy INT, Opis VARCHAR(30)); GO CREATE TABLE Produkty ( IdProduktu INT, IdSprzedazy INT, Data DATE, Nazwa VARCHAR(20),Wartosc INT); GO INSERT Sprzedaz VALUES (1, 'Licencja nr 1′), (2, 'Licencja nr 2′); GO INSERT Produkty VALUES (101,1,’2011-01-01′, 'Windows’,300), (102,1,’2011-01-02′, 'office’,300), (103,1,’2011-01-03′, 'Server’,500), (104,2,’2011-01-01′, 'Windows’,280), (105,2,’2011-01-10′, 'SQL’,900), (106,2,’2011-02-01′, 'office 2010′,200) GO
Teraz spróbujemy wyłuskać informację o tym który produkt był w ramach jednej sprzedaży sprzedawany jako drugi. Po pierwsze użyję funkcji RANK, która potrafi taki ranking zbudować:
SELECTRANK() OVER (PARTITION BY IDSprzedazy ORDER BY Data DESC) AS 'NrKolejny’, IDSprzedazy, Data, Wartosc FROM Produkty
Co robi ta dziwna funkcja RANK? Po pierwsze dzieli sobie za sprawą PARTITION BY IDSprzedazy wszystkie dostępne rekordy na paczki, dla każdego IdSprzedaży oddzielnie. W naszym przypadku więc, ranking odbędzie się w dwóch rozdzielnych grupach – dla sprzedaży nr 1 i dla sprzedaży nr 2. W ramach każdej z tych paczek (partycji, czy jak je tam zwać) sortuje rekordy według Daty malejąco. Dzieje sie to za sprawą ORDER BY Data DESC.
Urok funkcji RANK polega na tym, że numeruje ona rekordy ułożone w określonej kolejności oddzielnie w każdej partycji. Tak więc rekordy dotyczące sprzedaży nr 1 otrzymały numery kolejne 1, 2, 3 , zaś rekordy dotyczące sprzedaży numer 2 otrzymały też numerki 1, 2, 3.Teraz można się posłużyć podzapytaniem, aby dołączyć się z zewnętrzengo zapytania do uzyskanego przed chwilą wyniku i wybrać tylko drugie rekordy:
Zobacz! Join dołączył do danych dotyczących sprzedaży informacje o sprzedanym produkcie, ale tylko o drugim sprzedanym produkcie! Ważne jest, że ten drugi sprzedany produkt jest drugi ze względu na coś. W tym przypadku chodziło o datę, czyli był kupowany w drugiej kolejności. Teraz można zmienić nieco nasze zapytanie aby zwrócić rekordy drugie pod względem wysokości transakcji:
SELECT * FROM Sprzedaz AS s JOIN (SELECT RANK() OVER (PARTITION BY IDSprzedazy ORDER BY Wartosc DESC) AS 'NrKolejny’, IDSprzedazy, Data, Wartosc FROM Produkty) AS p ON s.IdSprzedazy = p.IdSprzedazy AND p.NrKolejny=2;
Co ciekawe w tym przypadku sprzedaż numer 1 ma na drugim miejscu pod względem sprzedaży 2 rekordy! Stąd też powyższe zapytanie wyświetli dwa rekordy znajdujące się na drugim miejscu pod względem wartości produktu.
Jeżeli chcesz, aby pokazać zawsze TYLKO jeden rekord użyj zamiast RANK() ROW_NUMBER(). RANK dla takich samych wartości zwraca te same numery, zaś ROW_NUMBER zawsze przydziela numer wiersza bez powtórzeń.
