SQL
Funkcje (analityczne)

Wprowadzenie

Składnia

1. FIRST_VALUE (wyrażenie_skalarowe) OVER ([podział_klasy]] porządek_klasy [rzędy zakresy]
2. LAST_VALUE (wyrażenie_skalarowe) PONAD ([podział_klucza] klauzula_klasyfikacji [klauzula_wierszy])
3. LAG (wyrażenie_skalarne [, przesunięcie] [, domyślnie]) PONAD ([podział_klucza] porządek_klucza)
4. OŁÓW (wyrażenie_skalarne [, przesunięcie], [domyślny]) OVER ([podział_klucza] kolejność_klucza)
5. PERCENT_RANK () OVER ([podział_klucza] porządek_klucza)
6. CUME_DIST () OVER ([podział_klucza] porządek_klucza)
7. PERCENTILE_DISC (literatura_ liczbowa) W GRUPIE (ZAMÓWIENIE WEDŁUG_wyrażenia zamówienia [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (literatura_ liczbowa) W GRUPIE (ZAMÓWIENIE WEDŁUG_wyrażenia zamówienia [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

Za pomocą funkcji FIRST_VALUE określa się pierwszą wartość w uporządkowanym zestawie wyników, którą identyfikuje się za pomocą wyrażenia skalarnego.

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

W tym przykładzie funkcja FIRST_VALUE jest używana do zwracania ID stanu lub prowincji o najniższej stawce podatkowej. Klauzula OVER służy do ustalenia stawek podatkowych w celu uzyskania najniższej stawki.

LAST_VALUE

Funkcja LAST_VALUE zapewnia ostatnią wartość w uporządkowanym zestawie wyników, którą określasz za pomocą wyrażenia skalarnego.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

W tym przykładzie użyto funkcji LAST_VALUE aby zwrócić ostatnią wartość dla każdego zestawu wierszy w uporządkowanych wartościach.

LGD i OŁOWIU

Funkcja LAG dostarcza dane o wierszach przed bieżącym wierszem w tym samym zestawie wyników. Na przykład w instrukcji SELECT można porównać wartości w bieżącym wierszu z wartościami w poprzednim wierszu.

Do określenia wartości, które należy porównać, używasz wyrażenia skalarnego. Parametrem przesunięcia jest liczba wierszy przed bieżącym wierszem, które zostaną użyte w porównaniu. Jeśli nie określisz liczby wierszy, zostanie użyta wartość domyślna jednego wiersza.

Domyślny parametr określa wartość, która powinna zostać zwrócona, gdy wyrażenie z przesunięciem ma wartość NULL . Jeśli nie określisz wartości, zwracana jest wartość NULL .

Funkcja LEAD zapewnia dane dotyczące wierszy po bieżącym wierszu w zestawie wierszy. Na przykład w instrukcji SELECT można porównać wartości w bieżącym wierszu z wartościami w następnym wierszu.

Wartości należy porównać z wyrażeniem skalarnym. Parametrem przesunięcia jest liczba wierszy po bieżącym wierszu, które zostaną użyte w porównaniu.

Określ wartość, która powinna zostać zwrócona, gdy wyrażenie z przesunięciem ma wartość NULL używając parametru domyślnego. Jeśli nie określisz tych parametrów, zostanie użyta wartość domyślna jednego wiersza i zostanie zwrócona wartość NULL .

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

W tym przykładzie użyto funkcji LEAD i LAG do porównania wartości sprzedaży dla każdego dotychczasowego pracownika z wartościami pracowników wymienionych powyżej i poniżej, z rekordami uporządkowanymi na podstawie kolumny BusinessEntityID.

PERCENT_RANK i CUME_DIST

Funkcja PERCENT_RANK oblicza ranking wiersza względem zestawu wierszy. Procent jest oparty na liczbie wierszy w grupie, które mają niższą wartość niż bieżący wiersz.

Pierwsza wartość w zestawie wyników ma zawsze rangę procentową równą zero. Wartość najwyższej lub ostatniej wartości w zestawie jest zawsze równa jeden.

Funkcja CUME_DIST oblicza względne położenie określonej wartości w grupie wartości, określając procent wartości mniejszych lub równych tej wartości. Nazywa się to rozkładem skumulowanym.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

W tym przykładzie użyto klauzuli ORDER do podziału lub grupowania wierszy pobranych przez instrukcję SELECT na podstawie tytułów stanowisk pracowników, a wyniki w każdej grupie posortowano na podstawie liczby godzin zwolnienia lekarskiego, z których skorzystali pracownicy.

Funkcja PERCENT_RANK szereguje wpisy w każdej grupie. Dla każdego wpisu zwraca procent wpisów w tej samej grupie, które mają niższe wartości.

Funkcja CUME_DIST jest podobna, z tym wyjątkiem, że zwraca procent wartości mniejszych lub równych bieżącej wartości.

PERCENTILE_DISC i PERCENTILE_CONT

Funkcja PERCENTILE_DISC wyświetla wartość pierwszego wpisu, w którym skumulowany rozkład jest wyższy niż percentyl PERCENTILE_DISC za pomocą parametru numeric_literal .

Wartości są pogrupowane według zestawu wierszy lub partycji, zgodnie z klauzulą WITHIN GROUP .

Funkcja PERCENTILE_CONT jest podobna do funkcji PERCENTILE_DISC , ale zwraca średnią sumy pierwszego pasującego wpisu i następnego wpisu.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

Aby znaleźć dokładną wartość z wiersza, która odpowiada lub przekracza 0,5 percentyla, przekazujesz percentyl jako literał liczbowy w funkcji PERCENTILE_DISC . Kolumna Dyskretna wartość procentowa w zestawie wyników zawiera wartość wiersza, w którym skumulowany rozkład jest wyższy niż określony percentyl.

Aby oprzeć obliczenia na zbiorze wartości, użyj funkcji PERCENTILE_CONT . Kolumna „Percentile Continuous” w wynikach zawiera średnią wartość sumy wartości wyniku i następną najwyższą pasującą wartość.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;