Używanie funkcji RANK, AGGREGATE i OFFSET

  • 6 min

W operacjach okien można użyć funkcji agregujących, takich jak SUM, MIN i MAX, aby wykonywać operacje na zestawie wierszy zdefiniowanych przez klauzulę OVER i jej argumenty.

Funkcje okien można podzielić na kategorie:

  • Funkcje agregujące. Na przykład SUM, AVG i COUNT, które działają w oknie i zwracają wartość skalarną.
  • Funkcje klasyfikacji. Na przykład RANK, ROW_NUMBER i NTILE. Funkcje klasyfikacji wymagają kolejności sortowania i zwracają wartość klasyfikacji dla każdego wiersza w partycji.
  • Funkcje analityczne. Na przykład CUME_DIST, PERCENTILE_CONT lub PERCENTILE_DISC. Funkcje analityczne obliczają rozkład wartości w partycji.
  • Funkcje przesunięcia. Takie jak LAG, LEAD i LAST_VALUE. Funkcje przesunięcia zwracają wartości z innych wierszy względem położenia bieżącego wiersza.

Funkcje agregujące

Funkcje agregujące zwracają sumy, średnie lub liczby elementów. Funkcje agregujące wykonują obliczenia i zwracają pojedynczą wartość. Z wyjątkiem COUNT(*), funkcje agregujące nie zliczają wartości NULL.

Rozważ następujący kod, który stosuje niektóre typowe funkcje agregujące do cen produktów w tabeli products:

SELECT Name, ProductNumber, Color, SUM(Weight) 
OVER(PARTITION BY Color) AS WeightByColor
FROM SalesLT.Product
ORDER BY ProductNumber;

Spowoduje to zwrócenie kolumny o nazwie WeightByColor, która zawiera całkowitą wagę dla wszystkich produktów o tym samym kolorze, co pokazano w poniższym zestawie wyników częściowych.

A screenshot showing results from the OVER and PARTITION BY Color clause.

Funkcje klasyfikowania

Funkcje klasyfikacji przypisują liczbę do każdego wiersza, w zależności od jego pozycji w określonej kolejności. Kolejność jest określana przy użyciu klauzuli ORDER BY.

Rozważmy następujący kod, który stosuje wszystkie cztery funkcje klasyfikacji do produktów w tabeli products.

SELECT productid, name, listprice 
    ,ROW_NUMBER() OVER (ORDER BY productid) AS "Row Number"  
    ,RANK() OVER (ORDER BY listprice) AS PriceRank  
    ,DENSE_RANK() OVER (ORDER BY listprice) AS "Dense Rank"  
    ,NTILE(4) OVER (ORDER BY listprice) AS Quartile  
FROM SalesLT.Product

Spowoduje to zwrócenie kolumny dla każdej funkcji z odpowiednim numerem klasyfikacji.

A screenshot showing results from ranking functions.

Funkcje analityczne

Funkcje analityczne obliczają wartość na podstawie grupy wierszy. Funkcje analityczne służą do obliczania średnich ruchomych, sum uruchomionych i wyników pierwszych N. Te funkcje obejmują:

  • CUME_DIST
  • FIRST_VALUE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTIL_DISC

Funkcje OFFSET

Funkcje przesunięcia umożliwiają zwrócenie wartości kolejnych lub poprzednich wierszy w zestawie wyników.

Funkcje przesunięcia działają na pozycji, która jest względna względem bieżącego wiersza lub względem granicy początkowej lub końcowej ramy okna. Funkcje przesunięcia to:

  • LAG i LEAD — działają na przesunięcie do bieżącego wiersza i wymagają klauzuli ORDER BY.
  • FIRST_VALUE i LAST_VALUE - działają na przesunięcie od ramy okna. Poniżej przedstawiono składnię funkcji LAG. Funkcja LEAD działa w taki sam sposób.
LAG (scalar_expression [,offset] [,default])  
    OVER ( [ partition_by_clause ] order_by_clause )

W poniższym przykładzie kodu funkcja przesunięcia LEAD zwraca wartość budżetu następnego roku:

SELECT [Year], Budget, LEAD(Budget, 1, 0) OVER (ORDER BY [Year]) AS 'Next'
    FROM dbo.Budget
    ORDER BY [Year];

Poniżej przedstawiono składnię LAST_VALUE. FIRST_VALUE działa w ten sam sposób.

LAST_VALUE ( [ scalar_expression ] )  
OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

Składnia jest podobna do LAG i LEAD z dodawaniem klauzuli wierszy/zakresu.