SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Wyszukiwanie ciągłych zakresów, dziur, problemy wysp w SQL

Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.


Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.


Wykorzystując funkcję ROW_NUMBER() i powyższą zasadę, sprawdźmy ciągłość dla poniższego zbioru:

Use tempdb
GO
 
set nocount on 
go
 
IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;
 
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1
 
WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;
 
-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Zakresy_ciagle_01

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Zakresy_ciagle_02


Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1, @los int =0, @cnt int =1
 
WHILE (@cnt<=10)
BEGIN
 
	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end
 
	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;
 
select * from dbo.zakres

Zakresy_ciagle_03

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Zakresy_ciagle_04

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Zakresy_ciagle_05
Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).


Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
 
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL
 
	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)

Zakresy_ciagle_06

Funkcja NTILE(n)

Funkcja szeregująca NTILE(n), podobnie jak pozostałe rankingowe, operuje w oparciu o funkcję okna OVER(). Jej działanie polega na podziale zbioru elementów na przedziały lub jak to jest często określane w literaturze – na kafelki lub płytki (NTILE = n tiles czyli n płytek).


Sposób działania

Możemy dzięki niej, podzielić zbiór na jednakowo liczne grupy – czyli służy do podziału elementów na n-tyle (np. NTILE(4) = kwartyle ). Jako jedyna z funkcji rankingowych, przyjmuje parametr określający liczbę przedziałów na jakie będziemy szeregować zbiór, który zdefiniowany jest za pomocą funkcję okna.

Przypomnę tylko, że dzięki funkcji okna możemy działać na pełnym zbiorze rekordów lub jego podzbiorach (element określany w PARTITION BY). Jest to opisane w artykule na temat składni funkcji szeregujących. Kolejność, czyli przyporządkowanie do konkretnego podzbioru (płytki), jest ustalone zgodnie z metodą sortowania w klauzuli OVER().

Jeśli liczba wierszy nie jest podzielna bez reszty, to n pierwszych płytek, będzie posiadać +1 element. Zobaczmy to na przykładzie :

USE AdventureWorks2008
GO
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
   -- funkcja NTILE(3), dzieli i przypisuje elementy do 3 "równych" zbiorów
   NTILE(3) OVER(ORDER BY P.ListPrice DESC) AS Tile
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
    ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
where PSC.Name = 'Handlebars'

NTILE_01
Liczba płytek oraz ich elementów, wyznaczona została w następujący sposób :

  • dla 8 wierszy i 3 przedziałów, liczba płytek będzie równa CEILING(8./3.) = 3 płytki
  • podstawowy rozmiar płytki : FLOOR(8./3.) = 2
  • reszta z dzielenia 8%3 = 2, czyli dwie pierwsze płytki dostaną +1 elementów

Opisany algorytm obowiązuje również gdy liczba rekordów zbioru na którym operujemy jest mniejsza niż liczba przedziałów na które chcemy dzielić.

SELECT P.Name Product, P.ListPrice, PSC.Name SubCategory,
   -- funkcja NTILE(4), dzieli na kwartyle
   NTILE(4) OVER(PARTITION BY PSC.NAME ORDER BY P.ListPrice asc) AS Quartile
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
    ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE ListPrice between 255 and 500

NTILE_03
Tym razem, utworzyliśmy dla każdej partycji elementów – 4 przedziały – NTILE(4). Pierwsza partycja to grupa elementów podkategorii Cranksets. Liczy tylko dwa elementy, tak więc przedziały 3 i 4 dla niej będą puste. Myślę, że idea i sposób jej użycia jest intuicyjny. Możemy ją użyć np. do oznaczenia przedziałów, w zależności od sprzedaży produktów, na trzy równe kategorie : low, medium, high.

Określenie zakresu przedziałów

Możemy dość łatwo wyznaczyć zakres przedziałów, które zostały wyznaczone przeez NTILE :

WITH CTE_Tile AS
(
	SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
		 NTILE(3) OVER( ORDER BY P.ListPrice DESC) AS Tile
 
	FROM Production.Product P JOIN Production.ProductSubCategory PSC
		 ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
	WHERE PSC.Name = 'Handlebars'
)
SELECT Tile, MIN(ListPrice) AS Przedzial_od, MAX(ListPrice) AS Przedzial_do, COUNT(*) as ile
FROM CTE_Tile
GROUP BY Tile
ORDER BY Tile

NTILE_02
Różnica liczebności poszczególnych zbiorów (n-tyli), wynosi maksymalnie 1 w stosunku do ilości podstawowej.

Funkcja NTILE, ma charakter niedeterministyczny. Dla równych wartości według których dokonuje zaszeregowania pomiędzy dwa przedziały, może zwracać różne wyniki (jeśli kolejność nie jest ściśle wymuszona), zgodnie z zasadą losowości w zbiorach nieuporządkowanych.

Funkcje RANK() oraz DENSE_RANK()

Szereg – czyli numer miejsca na liście

Funkcje RANK() oraz DENSE_RANK(), służą do określania pozycji w szeregu.
Działają one zawsze na zbiorach, określonych za pomocą funkcji okna OVER(), według określonego w niej porządku.

Uwzględniają powtórzenia w wyznaczaniu numerów rekordów, posiadających te same wartości klucza, według którego sortujemy. Dla przypomnienia, ROW_NUMBER() nadawała zawsze kolejny numer, bez względu na to czy wartość sortowana się różniła czy nie.

Podobnie jak dla pozostałych funkcji rankingowych, możemy wykorzystać tutaj pełen zakres możliwości składni funkcji okna OVER() (np. dzielenie zbioru na partycje), a także zastosowanie mają tu ogólne reguły działania dla funkcji szeregujących.


Funkcja RANK()

Nadaje kolejne wartości wierszom w ramach zbioru, pod warunkiem, że są one różne ze względu na sposób sortowania w klauzuli OVER().

Jej działanie najlepiej pokazać na przykładzie :

USE AdventureWorks2008
GO
 
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
     RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
     ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'

RANK_01
Wyznaczany jest kolejny numer pozycji elementu w zbiorze, pod warunkiem, że wartość kolumny po której sortujemy, jest unikalna (lub wystąpiła po raz pierwszy). Jeśli wartość atrybutu sortującego, pojawiła się już wcześniej w rekordzie poprzedzającym, zwróci ona ten sam numer. W naszym przykładzie, pierwsze dwie pozycje mają ten sam numer określony przez RANK(), bo wartość w kolumnie ListPrice jest dla nich równa.

Warto zauważyć, że dla wartości inkrementowanych – będzie to zawsze numer bezwzględnej pozycji rekordu w tabeli wynikowej. W naszym przykładzie będą to wiersze numer 1, 3, 4, 6 i 7. Reszta to miejsca ex aequo (ze względu na wartość sortowania) stąd wyznaczone przeez RANK() wartości są takie same, jak dla wierszy poprzedzających je (rekordy 5 i 8).

Funkcja ta, z założenia może być nieciągła – czyli może zostawiać „dziury” w numeracji (w naszym przykładzie nie zwróciła np. wartości 2).

Żeby dobrze zapamiętać jej sposób działania – można odnieść ją do analogii określania listy wyników w zawodach sportowych. Nadaje bowiem te same miejsca dla wyników ex aequo. Jednak funkcja RANK(), bywa niesprawiedliwa i w wyniku działania możliwe jest określenie zwycięzców, ale czasem bez srebrnych lub brązowych medali (przy jednoczesnej możliwości nadania x medali złotych) :

SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
   RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
  ON P.ProductSubCategoryID = PSC.ProductSubCategoryID

RANK_02


Funkcja DENSE_RANK() – szereg zwarty

Działa analogicznie do RANK() z jedną drobną różnicą – jest funkcją ciągłą, więc jeśli inkrementuje wartości, robi to bez względu na faktyczną pozycję w rankingu, nadaje dokładnie kolejny numer pozycji.

Nawiązując znów do analogii wyników zawodów sportowych, DENSE_RANK() jest „sprawiedliwa” i zawsze wyznaczy drugie (srebrne) i trzecie (brązowe) miejsce w szeregu :

SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
     DENSE_RANK() OVER( ORDER BY P.ListPrice DESC) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
     ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'

DENSE_RANK_01
Poza tą drobną, choć bardzo istotną różnicą, działa identycznie do RANK() czyli dla tego samego argumentu (wartość atrybutu sortującego) zwraca zawsze tą samą wartość.


RANK() i DENSE_RANK() w praktyce

W odróżnieniu do ROW_NUMBER, funkcje RANK() oraz DENSE_RANK() są deterministyczne. Oznacza to, że dla danych argumentów zwracaję zawsze te same wartości.

Podobnie jak dla pozostałych funkcji szeregujących, aby wykorzystać wartości przez nie wyznaczane, np. w warunkach filtracji, musimy użyć podzapytań (lub CTE). Możemy stosować je tylko w SELECT lub ORDER BY zapytania głównego. Związane jest to z logiczną kolejnością przetwarzania zapytań.

-- pobieranie tylko najdroższych (również ex aequo) produktów z podkategorii
with RANKED_CTE(Product, ListPrice , Category, PriceRank )
as (
 
	SELECT P.Name Product, P.ListPrice, PSC.Name Category,
	     RANK() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS PriceRank
	FROM Production.Product P JOIN Production.ProductSubCategory PSC
             ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
)
select * from RANKED_CTE
where PriceRank = 1

RANK_03

Porównanie wyników funkcji RANK i DENSE_RANK()

W poniższym przykładzie, pokazana jest różnica pomiędzy wyznaczanymi wartościami funkcji RANK i DENSE_RANK.

SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
     RANK() OVER( ORDER BY P.ListPrice DESC) AS RANK_result,
     DENSE_RANK() OVER( ORDER BY P.ListPrice DESC) AS DENSE_RANK_result
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
     ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
WHERE PSC.Name = 'Handlebars'

RANK_I_DENSE_01

Funkcja ROW_NUMBER()

Jest to jedna z najbardziej praktycznych i najczęściej stosowanych funkcji szeregujących. Zarys jej możliwości, pokazałem na przykładach, opisujących ogólną składnię funkcji rankingowych. Działają one w oparciu o funkcję okna OVER() i bez niej nie mogą się obyć.

Określa ona kolejny, unikalny numer wiersza w ramach partycji (lub całego zbioru wynikowego) zgodnie z zastosowanym sposobem sortowania.

Use AdventureWorks2008
Go
-- działanie ROW_NUMBER() na całym zbiorze 
SELECT P.ProductID, P.Name Product, P.ListPrice, 
 
ROW_NUMBER() OVER( ORDER BY P.ProductID ) AS Pozycja
 
FROM Production.Product P

ROW_NUMBER_01


Funkcja ROW_NUMBER() – naturalna (ciągła) numeracja rekordów

ROW_NUMBER() – to funkcja niedeterministyczna. Oznacza to, że jeśli np. dwa rekordy w ramach partycji (podzbioru lub całego zbioru, w zależności czy określony jest element funkcji okna PARTITION BY), posiadają te same wartości w kolumnach po których sortujemy, wartość zwracana przez ROW_NUMBER() będzie dla nich niedeterministyczna czyli w zasadzie losowa. Funkcja deterministyczna, na podstawie tych samych argumentów zwraca zawsze ten sam wynik. W przypadku ROW_NUMBER tak nie jest.

Kolejność elementów w zbiorze np. dla tej samej ceny (przykład poniżej), może być różna – bo przecież nic jej nie wymusza, więc na jakiej podstawie, miała by być określona (zdeterminowana) …

SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
  ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
    ON P.ProductSubCategoryID = PSC.ProductSubCategoryID

ROW_NUMBER_02
Dla pierwszej partycji rekordów, czyli produktów z podkategorii Bib-Shorts, wartość ListPrice jest równa i wynosi 89,99. To że akurat element „Men’s Bib-Shorts, S” otrzymał wartość funkcji ROW_NUMBER() = 1 a nie np. 2 – zgodnie z teorią zbiorów to czysty przypadek – bo nigdzie jawnie nie zostało określone jak traktować równe wartości elementów (tutaj patrzymy tylko na cenę) w kontekście zbioru. Kolejność przy kolejnych wywołaniach kwerendy, będzie prawdopodobnie taka sama. Warunkowana jest tylko fizycznym składowaniem danych w tabeli. Może się to jednak zmienić wskutek modyfikacji danych.

Co innego jeśli jawnie określimy sposób sortowania w ramach podzbioru, dodatkowo po nazwie :

SELECT P.Name Product, P.ListPrice, PSC.Name Category,
 
  ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC , P.Name ) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
    ON P.ProductSubCategoryID = PSC.ProductSubCategoryID

ROW_NUMBER_02a
Teraz, kolejność jest wymuszona i zakładając, że wartości w kolumnie P.Name są unikalne w ramach podkategorii – funkcja będzie zachowywała się w sposób deterministyczny.

Trzeba być tego świadomym, bo brak znajomości mechanizmów działania funkcji – może prowadzić do błędów logicznych w aplikacji. Czasami takie błędy wychodzą dopiero wiele miesięcy po wdrożeniu (na testowych, lub fragmentarycznych danych wszystko działało ok.).

Stronnicowanie przy użyciu ROW_NUMBER()

Innym przykładem zastosowania tej funkcji jest realizacja stronnicowania rekordów. Sposobów na realizację tego zadania jest wiele. Poniżej sposób, bazujący na prostej kwerendzie z wykorzystaniem ROW_NUMBER(), która zwraca nam określoną liczbę rekordów (stronę). Przypominam, aby pamiętać o determinizmie, który może być wymagany w danym rozwiązaniu :

with paging as (
 
SELECT P.Name Product, P.ListPrice, 
ROW_NUMBER() OVER( ORDER BY P.ListPrice DESC) AS Pozycja
FROM Production.Product P 
 
)
-- np. 10 strona, wiersze od 91-100 
 select * from paging where Pozycja between 91 and 100
 go

ROW_NUMBER_04

Porównanie wydajności do zapytań skorelowanych

Alternatywą do numerowanie wierszy za pomocą ROW_NUMBER(), jest stosowanie np. podzapytań skorelowanych (w SQL Server 2000 nie było dostępnych funkcji rankingowych). Jest to jednak sposób znacznie mniej wygodny i wydajny niż za pomocą wbudowanej funkcji rankingowej.

Aby się o tym przekonać możemy porównać statystyki wykonania dwóch kwerend (najlepiej uruchomić je razem, w jednym batchu, dodatkowo warto włączyć opcję „Include Actual Execution Plan” oraz statystyki IO.

SET STATISTICS IO ON
 
-- Query1 = old one SQL Server 2K
select ProductId, Name,
	( 
		select count(*) 
		FROM Production.Product P2 
		where P2.ProductId <= P.ProductID
 
	) as RowNum
FROM Production.Product P
 
-- Query2 = new one since SQL 2K5
select ProductId, Name, ROW_NUMBER() OVER( ORDER BY P.ProductID ) AS Pozycja
 
FROM Production.Product P

Obydwie kwerendy zwróciły to samo, ale zerknijmy na statystykę wykonania :

(504 row(s) affected)
Table 'Product'. Scan count 505, logical reads 4235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(504 row(s) affected)
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

ROW_NUMBER_05
Wynik mówi sam za siebie – w trakcie wykonania części pierwszej (zapytanie skorelowane), wykonanych zostało 4235 logicznych odczytów i 505 skanów indeksu. Zapytanie z ROW_NUMBER() kosztowało 15 odczytów i 1 index scan. Ponadto przypadek drugi pochłonął tylko 4% całkowitego kosztu wykonania skryptu.

Funkcje szeregujące w SQL Server

W SQL Server w wersji 9-tej (czyli 2005) zostały wprowadzone cztery analityczne funkcje szeregujące. Ich składnia i funkcjonalności zostały zdefiniowane w standardzie ANSI SQL:2003 – spotkasz je również w innych dialektach np. PL/SQL (Oracle).

Stanowią rozszerzenie języka i w większości zastosowań poprawiają również wydajność zapytań w stosunku do „tradycyjnych” metod np. numerowania rekordów.

Zanim przejdziesz do poznawania poszczególnych funkcji – powinieneś poznać ogólne zasady według których działają. Wspólnym mianownikiem i ich nieodłącznym elementem, jest funkcja okna – OVER(). Omówienie poszczególnych funkcji, umieściłem w osobnych, dedykowanych artykułach :

Artykuł ten przedstawia ideę działania i składnię – wspólną dla wszystkich funkcji rankingowych. Opisywane tu funkcjonalności są spójne dla wszystkich wersji SQL Server w których możesz je spotkać (czyli od 2005+).


Składnia funkcji szeregujących – funkcja okna OVER()

Praktyczne zastosowanie funkcji rankingowych, jest wbrew pozorom bardzo łatwe i intuicyjne.

Każda funkcja szeregująca działa w oparciu o funkcję okna – OVER(). Zastanówmy się jakich elementów składni, możemy potrzebować, aby wykonać szeregowanie np. numerowanie rekordów.

Nadawanie jakiejkolwiek numeracji elementów w ramach zbioru, wykonujemy zazwyczaj według określonego porządku (np. wartości zamówień, liczby, daty pojawienia się, czasu etc.). Pierwsza rzecz, która będzie potrzebna, to określenie sposobu sortowania zbioru – klucza, według którego, będziemy numerowali rekordy. Jest to pierwsza (z dwóch możliwch do określenia) właściwość, funkcji okna. Właściwości funkcji okna, zademonstruję na przykładzie funkcji ROW_NUMBER() czyli prostego, kolejnego numerowania wierszy.

Szeregowanie elementów według określonego porządku (sortowanie)

W funkcji okna, określamy przede wszystkim sposób uporządkowania elementów według którego będziemy dokonywać numeracji rekordów. Ten element składni, trzeba zawsze określić – jest on wymagany. Dla przykładu załóżmy, że chcemy ponumerować zamówienia, według ich wartości :

Use AdventureWorks2008
Go
 
select SalesOrderId,TotalDue , 
                -- funkcja szeregująca ROW_NUMBER() wraz z nierozłączną funkcją okna OVER()
		ROW_NUMBER() OVER( ORDER BY TotalDue desc) as RowNum
 
from Sales.SalesOrderHeader

OVER_01
Jak widać, sposób sortowania elementów, określamy za pomocą dobrze znanej klauzuli ORDER BY, wewnątrz funkcji OVER().

Jeśli nie zależy nam na jakimś konkretnym sortowaniu, lub chcemy aby to było wykonane w sposób pseudolosowy możemy wykorzystać inne możliwości jakie daje ORDER BY, ale zrezygnować z niej nie możemy – jest obowiązkowa.

select SalesOrderId,TotalDue , 
                -- sortowanie w ramach funkcji okna w sposób pseudolosowy
		ROW_NUMBER() OVER( ORDER BY NEWID()) as RowNum
 
from Sales.SalesOrderHeader

OVER_02
W tym przykłądzie nie ma określonych żadnych dodatkowych warunków filtracji w kwerendzie (ani we FROM, ani w WHERE – którego z resztą w ogóle tu nie ma) – operujemy więc funkcją ROW_NUMBER(), na wszystkich elementach ze zbioru Sales.SalesOrderHeader. Nadajemy im kolejne, unikalne numery w kolumnie RowNum.

Ważne jest żeby dostrzec, że ten przykład szereguje elementy, w ramach całego zbioru na którym operuje SELECT. Wynik zwracany przez funkcję jest ściśle określony, według przyjętego porządku – zastosowanego klucza sortowania w funkcji okna.

Szeregowanie elementów z uwzględnieniem podzbiorów (partycje)

Funkcja okna, umożliwia szeregowanie elementów zbioru, również w węższym zakresie, czyli w kontekście jego podzbiorów. Jest to druga właściwość jaką możemy tu określić, czyli partycjonowanie elementów (PARTITION BY) ze względu na wartość atrybutu (lub atrybutów).
Np. ponumerujmy produkty w ramach podkategorii :

SELECT P.Name as Product, P.ListPrice, PSC.Name as Category,
 
  -- funkcja szeregująca, działająca w ramach partycji wartości atrybutu PSC.Name 
        ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS PriceRank
 
FROM Production.Product P JOIN Production.ProductSubCategory PSC
    ON P.ProductSubCategoryID = PSC.ProductSubCategoryID

OVER_03
Jak widać na powyższym przykładzie, możemy szeregować zlecenia, ze względu na wartość kolumny (lub kolumn), według której wyznaczane są podzbiory (partycje) .

Numeracja wykonywana jest w ich zakresie, dla każdego z podzbiorów osobno.

Podzbiory definiowane są przez wartość kolumny (atrybutu) PSC.Name czyli podkategorii produktu. Nadawane numery są unikalne, ale tylko w wąskim zakresie (domenie czy też dziedzinie) – danej kategorii, określonej w atrybucie partycji.

Implementacja funkcji okna OVER(), została znacząco rozszerzona w SQL Server 2012. Jej możliwości opisałem w poprzednim artykule tego kursu


Ogólne zasady działania funkcji szeregujących

Funkcje rankingowe działają zawsze na tabeli wirtualnej, na której operuje (w odpowiednim czasie przetwarzania zapytania) klauzula SELECT. Omawiam to szczegółowo w rozdziale dotyczącym logicznych faz wykonywania zapytań .

Skutkiem tego, funkcje rankingowe możemy używać, tylko w krokach SELECT oraz ORDER BY, danej kwerendy. Jeśli chcesz użyć ich wyniku w warunkach filtracji – nie obejdzie się bez korzystania z podzapytania np. z wykorzystaniem CTE.

Najlepiej zobrazować to za pomocą przykładu. Pobierzmy informacje o trzech najdroższych produktach w ramach kategorii (sortujemy według klucza wartość ListPrice dla każdej kategorii produktów (partycjonujemy według wartości atrybutu ProductCategory.Name)

With CTE as (
SELECT 
 
	ROW_NUMBER() OVER(PARTITION BY PC.Name ORDER BY ListPrice desc) AS RowNum,
 
	PC.Name Category, P.Name Product, P.ListPrice
 
FROM 
   Production.Product P JOIN Production.ProductSubCategory PSC
      ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
   JOIN Production.ProductCategory PC
      ON PSC.ProductCategoryID = PC.ProductCategoryID
)
select * from CTE where RowNum<4

OVER_04
Przykład ten pokazuje również pewną specyficzną właściwość funkcji ROW_NUMBER(). Zauważ, że istnieją produkty w ramach kategorii, które mają tą samą cenę. Na jakiej podstawie nadawany jest zatem numer pozycji, jeśli wartości kolumny według której działamy (sortujemy) są równe? Zgodnie z teorią, jeśli zbiór nie jest posortowany (tu mam na myśli X elementów o tej samej wartości), to elementy teoretycznie zwracane są w losowej kolejności.
Funkcja ROW_NUMBER() jest funkcją niedeterministyczną. Wyjaśniam to szczegółowo w artykule na jej temat.