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'

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

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

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.