Funkcje rankingu w MsSQL

1. Funkcje rankingu w SQL Server

W tym rozdziale dowiesz się jak korzystać z funkcji rankingu. Funkcje rankingu zwracają pozycję danego rekordu na tle innych rekordów. Samo obliczenie odbywa się na podstawie wartości w kolumnach. Ich składnia jest bardzo podobna do składni funkcji analitycznych, które poznasz w kolejnym rozdziale kursu SQL Server. Spośród funkcji rankingu w SQL Server wyróżniamy:

• RANK() – zwraca liczbę(pozycję) porządkową w zbiorze wartości. Uwzględnia miejsca ex-equo. W przypadku miejsc ex-equo kolejna pozycja jest pomijana. Np 1,2,2,4,5,6,6,8…n
• DENSE_RANK() –  zwraca liczbę(pozycję) porządkową w zbiorze wartości. Uwzględnia miejsca ex-equo. W przypadku miejsc ex-equo kolejna pozycja nie jest pomijana. Np 1,2,2,3,4,4,5,6…n
• ROW_NUMBER() –  zwraca liczbę(pozycję) porządkową w zbiorze wartości. Nie uwzględnia miejsc ex-equo. Np 1,2,3,4,5,6…n
• NTILE() – dzieli zbiór na n określonych części. Np NTILE(4) podzieli zbiór na 4 części wg. wybranej kolumny. W statystyce możemy na tej podstawie wskazać takie wartości jak mediany, kwartyle, centyle itp.

Funkcje rankingu mogą porządkować wartości wg. liczby, daty, alfabetycznie itp. Poniżej najprostszy przykład wykorzystania funkcji rankingu. Na bazie AdventureWorks2016 stwórzmy rankingi z użyciem wszystkich poznanych funkcji analitycznych dla kolumny SalesAmountQuota z tabeli dbo.FactSalesQuota.

--tomaszkenig.pl
SELECT SalesQuotaKey
      ,EmployeeKey
      ,CalendarYear
      ,CalendarQuarter
      ,SalesAmountQuota
  ,RANK() OVER (ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,DENSE_RANK() OVER (ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,ROW_NUMBER() OVER (ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,NTILE(4) OVER (ORDER BY SalesAmountQuota) AS NTILE_EXAMPLE
  FROM AdventureworksDW2016CTP3.dbo.FactSalesQuota;

Jak widzisz po wprowadzeniu nazwy funkcji wprowadzamy wyrażenie OVER. Następnie w nawiasie wprowadzamy sortowanie za pomocą klauzuli ORDER BY. To na podstawie sortowania tworzymy ranking Tylko funkcja NTILE() wymaga argumentu. Wpisując 4 dzielisz tabelę na 4 części zgodnie z określonym sortowaniem. W sortowaniu możemy użyć wyrażeń ASC I DESC. Na poniższym przykładzie możesz zobaczyć jak wygląda porządkowanie wartości z miejscami ex-equo w przypadku funkcji rankingu.

2. Funkcje rankingu w MsSQL – Klauzula PARTITION BY

Funkcje rankingu mogą także wskazywać pozycję danego rekordu w ramach określonej grupy. Służy do tego klauzula PARTITON BY. Klauzulę PARTITION BY wstawiamy przed klauzulą ORDER BY. Stwórzmy prosty skrypt, by to pokazać na przykładzie wszystkich funkcji rankingu. Warto zaznaczyć, że wartości możemy partycjonować według więcej niż jednej kolumny.

--tomaszkenig.pl
SELECT SalesQuotaKey
      ,EmployeeKey
      ,CalendarYear
      ,CalendarQuarter
      ,SalesAmountQuota
  ,RANK() OVER (PARTITION BY CalendarQuarter ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,DENSE_RANK() OVER (PARTITION BY CalendarQuarter ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,ROW_NUMBER() OVER (PARTITION BY CalendarQuarter ORDER BY SalesAmountQuota) AS RANK_EXAMPLE
  ,NTILE(4) OVER (PARTITION BY CalendarQuarter ORDER BY SalesAmountQuota) AS NTILE_EXAMPLE
  FROM AdventureworksDW2016CTP3.dbo.FactSalesQuota
  WHERE CalendarYear = 2013;

Artykuł jest częścią kursu SQL Server. Jeśli masz jakieś pytania, możesz je zamieścić na forum bez logowania lub zostawić komentarz.