Nowości w SQL Server 2012 – funkcje analityczne

2012-06-23

W najnowszej wersji SQL Server język T-SQL wzbogacony został o kilka nowych funkcji analitycznych. Są to tzw. funkcje okienkowe operujące na podzbiorach (oknach danych tworzonych klauzulą OVER) głównego zbioru zwróconego przez zapytanie. Zanim przejdę do omówienia wspomnianych funkcji pokażę co zmieniło się w samym poleceniu OVER. Polecenie umożliwiało tworzenie podzbiorów danych poprzez umieszczane w nim instrukcje PARTITION BY i ORDER BY. Obecnie na potrzeby danej funkcji każdy taki podzbiór możemy podzielić na jeszcze mniejsze okna danych. Służy do tego instrukcja ROWS zawężająca dany podzbiór do określonych rekordów. Po słowie ROWS definiujemy zakres rekordów jaki wejdzie w skład danego okna, odbywa się to poprzez polecenia:

• CURRENT ROW – bieżący rekord;
• PRECEDING – liczba rekordów poprzedzających bieżący (UNBOUNDED PRECEDING oznacza początek podzbioru);
• FOLLOWING – liczba kolejnych rekordów po bieżącym (UNBOUNDED FOLLOWING oznacza koniec podzbioru);

Polecenia 0 PRECEDING oraz 0 FOLLOWING są jednoznaczne z CURRENT ROW.
Zamiast ROWS operującego na fizycznych pozycjach względem bieżącego rekordu, możemy użyć polecenia RANGE, które odnosi się do rekordów poprzez ich wartości. I tak np. CURRENT ROW w przypadku RANGE to wszystkie rekordy posiadające w polach z klauzuli ORDER BY taką samą wartość jak rekord bieżący.

Aby pokazać działanie omawianych mechanizmów przygotowałem tabelę tymczasową zawierającą średnie miesięczne ceny paliw w okresie czerwiec – wrzesień 2011 roku (a wówczas narzekaliśmy, że jest drogo ;)):

create table #CenyPaliw
(
    Rok int,
    Miesiac int,
    Paliwo varchar(10),
    Cena money
)

insert into #CenyPaliw(Rok, Miesiac, Paliwo, Cena)
values
    (2011, 6, 'PB95', 5.11), (2011, 6, 'ON', 4.95), (2011, 6, 'LPG', 2.49),
    (2011, 7, 'PB95', 5.13), (2011, 7, 'ON', 4.94), (2011, 7, 'LPG', 2.48),
    (2011, 8, 'PB95', 5.21), (2011, 8, 'ON', 5.05), (2011, 8, 'LPG', 2.47),
    (2011, 9, 'PB95', 5.10), (2011, 9, 'ON', 5.08), (2011, 9, 'LPG', 2.54)

select * from #CenyPaliw

Poniższe zapytanie pokazuje zastosowanie nowych możliwości polecenia OVER:

select Paliwo, Rok, Miesiac, Cena,
    max(cena) over(partition by paliwo) as [Max],
    max(cena) over(partition by paliwo order by rok, miesiac
        rows between unbounded preceding and current row) as [MaxDoTeraz],
    max(cena) over(partition by paliwo order by rok, miesiac
        rows between 2 preceding and 0 following) as [MaxOd3Mcy],
    min(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and 2 following) as [MinDo3Mcy],
    min(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and unbounded following) as [MinOdTeraz]
from #CenyPaliw
order by Paliwo, Rok, Miesiac

W poszczególnych kolumnach znajdują się następujące informacje w odniesieniu do każdego rodzaju paliwa:

• Max – maksymalna cena paliwa w całym badanym okresie;
• MaxDoTeraz – maksymalna cena paliwa do danego miesiąca (rekordu);
• MaxOd3Mcy – maksymalna cena paliwa z ostatnich trzech miesięcy (względem danego rekordu);
• MinDo3Mcy – minimalna cena paliwa w trzech kolejnych miesiącach (względem danego rekordu);
• MinOdTeraz – minimalna cena paliwa od danego miesiąca do końca okresu;

Po zapoznaniu się z rozszerzonymi możliwościami klauzuli OVER czas przejść do prezentacji nowych funkcji analitycznych. Są to funkcje: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENT_RANK, CUME_DIST, PERCENTILE_CONT oraz PERCENTILE_DISC. Ich działanie w odniesieniu do ceny każdego rodzaju paliwa pokazuje poniższe zapytanie:

select Paliwo, Rok, Miesiac, Cena,
    lag(cena, 1, 0) over(partition by paliwo order by rok, miesiac) as [Poprzednia],
    lead(cena, 1, 0) over(partition by paliwo order by rok, miesiac) as [Nastepna],
    first_value(cena) over(partition by paliwo order by rok, miesiac
        rows unbounded preceding) as [Pierwsza],
    last_value(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and unbounded following) as [Ostatnia],
    percent_rank() over(partition by paliwo order by rok, miesiac) as [Rank1],
    cume_dist() over(partition by paliwo order by rok, miesiac) as [Rank2],
    percentile_cont(0.5) within group (order by cena)
        over (partition by paliwo) as [Med1],
    percentile_disc(0.5) within group (order by cena)
        over (partition by paliwo) as [Med2]
from #CenyPaliw
order by Paliwo, Rok, Miesiac

Poszczególne funkcje oraz kolumny, w których zostały użyte:

• LAG [Poprzednia] – domyślnie funkcja zwraca wartość określonego pola z poprzedniego rekordu podzbioru, jeżeli jest to pierwszy rekord zwraca NULL, opcjonalnie można podać większy offset i wartość domyślną (w tym przypadku odpowiednio 1 i 0);
• LEAD [Nastepna] – domyślnie funkcja zwraca wartość określonego pola z następnego rekordu podzbioru, jeżeli jest to ostatni rekord zwraca NULL, opcjonalnie można podać większy offset i wartość domyślną (w tym przypadku odpowiednio 1 i 0);
• FIRST_VALUE [Pierwsza] – funkcja zwraca wartość pola z pierwszego rekordu w określonym oknie danych;
• LAST_VALUE [Ostatnia] – funkcja zwraca wartość pola z ostatniego rekordu w określonym oknie danych;
• PERCENT_RANK [Rank1] – ranking rekordu w danym podzbiorze obliczany wg wzoru: (RN – 1) / (RC – 1), gdzie RN to kolejny numer rekordu (funkcja Rank), RC to liczba rekordów w danym podzbiorze;
• CUME_DIST [Rank2] – ranking rekordu w danym podzbiorze obliczany wg wzoru: RS / RC, gdzie RS to liczba rekordów o wartości mniejszej bądź równej od bieżącego (dotyczy klauzuli ORDER BY), RC to liczba rekordów w danym podzbiorze;
• PERCENTILE_CONT [Med1] – funkcja zwraca percentyl dla przekazanego procentu (0.0 – 1.0). Jest to wielkość, poniżej której padają wartości zadanego procentu rekordów z danego podzbioru, np. percentyl 50% to mediana. Do określenia kolumny, której wartości będą analizowane służy dodatkowa klauzula WITHIN GROUP. Zwrócony wynik nie musi pokrywać się z jakąkolwiek wartością w danym podzbiorze;
• PERCENTILE_DISC [Med2] – funkcja działa analogicznie do powyższej, z tą różnicą, iż zwrócony wynik zawsze pokrywa się z wartością występującą w danym podzbiorze;

Jak widać nowe funkcje znacznie upraszczają proces analizy, eliminując konieczność tworzenia szeregu skomplikowanych podzapytań w celu uzyskania interesujących nas danych.

Więcej informacji o nowościach w SQL Server 2012: MSDN