NTILE (Transact-SQL)
Berlaku untuk: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Mendistribusikan baris dalam partisi yang diurutkan ke dalam jumlah grup tertentu. Grup diberi nomor, mulai dari satu. Untuk setiap baris, NTILE mengembalikan jumlah grup tempat baris berada.
Sintaksis
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
integer_expression
Adalah ekspresi bilangan bulat positif yang menentukan jumlah grup tempat setiap partisi harus dibagi. integer_expression bisa berjenis int, atau bigint.
<partition_by_clause>
Membagi tataan hasil yang dihasilkan oleh klausul FROM menjadi partisi tempat fungsi diterapkan. Untuk sintaks PARTITION BY, lihat Klausul OVER (Transact-SQL).
<order_by_clause>
Menentukan urutan penetapan nilai NTILE ke baris dalam partisi. Bilangan bulat tidak dapat mewakili kolom saat <order_by_clause> digunakan dalam fungsi peringkat.
Jenis Kembalian
bigint
Keterangan
Jika jumlah baris dalam partisi tidak dapat dibagi berdasarkan integer_expression, ini akan menyebabkan grup dengan dua ukuran yang berbeda dengan satu anggota. Grup yang lebih besar datang sebelum grup yang lebih kecil dalam urutan yang ditentukan oleh klausa OVER. Misalnya jika jumlah total baris adalah 53 dan jumlah grup adalah lima, tiga grup pertama akan memiliki 11 baris dan dua grup yang tersisa masing-masing akan memiliki 10 baris. Jika di sisi lain jumlah total baris dapat dibagi berdasarkan jumlah grup, baris akan didistribusikan secara merata di antara grup. Misalnya, jika jumlah total baris adalah 50, dan ada lima grup, setiap wadah akan berisi 10 baris.
NTILE bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.
Contoh
J. Membalikan baris menjadi grup
Contoh berikut membagi baris menjadi empat grup karyawan berdasarkan penjualan tahunan hingga saat ini. Karena jumlah total baris tidak dapat dibagi berdasarkan jumlah grup, dua grup pertama memiliki empat baris dan grup yang tersisa masing-masing memiliki tiga baris.
USE AdventureWorks2022;
GO
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
Berikut adalah hasil yang ditetapkan.
FirstName LastName Quartile SalesYTD PostalCode
------------- --------------------- --------- -------------- ----------
Linda Mitchell 1 4,251,368.55 98027
Jae Pak 1 4,116,871.23 98055
Michael Blythe 1 3,763,178.18 98027
Jillian Carson 1 3,189,418.37 98027
Ranjit Varkey Chudukatil 2 3,121,616.32 98055
José Saraiva 2 2,604,540.72 98055
Shu Ito 2 2,458,535.62 98055
Tsvi Reiter 2 2,315,185.61 98027
Rachel Valdez 3 1,827,066.71 98055
Tete Mensa-Annan 3 1,576,562.20 98055
David Campbell 3 1,573,012.94 98055
Garrett Vargas 4 1,453,719.47 98027
Lynn Tsoflias 4 1,421,810.92 98055
Pamela Ansman-Wolfe 4 1,352,577.13 98027
(14 row(s) affected)
B. Membalah hasil yang ditetapkan dengan menggunakan PARTITION BY
Contoh berikut menambahkan PARTITION BY argumen ke kode dalam contoh A. Baris pertama kali dipartisi oleh PostalCode dan kemudian dibagi menjadi empat grup dalam masing-masing PostalCode. Contoh ini juga mendeklarasikan variabel @NTILE_Var dan menggunakan variabel tersebut untuk menentukan nilai untuk parameter integer_expression .
USE AdventureWorks2022;
GO
DECLARE @NTILE_Var INT = 4;
SELECT p.FirstName, p.LastName
,NTILE(@NTILE_Var) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
Berikut adalah hasil yang ditetapkan.
FirstName LastName Quartile SalesYTD PostalCode
------------ -------------------- -------- ------------ ----------
Linda Mitchell 1 4,251,368.55 98027
Michael Blythe 1 3,763,178.18 98027
Jillian Carson 2 3,189,418.37 98027
Tsvi Reiter 2 2,315,185.61 98027
Garrett Vargas 3 1,453,719.47 98027
Pamela Ansman-Wolfe 4 1,352,577.13 98027
Jae Pak 1 4,116,871.23 98055
Ranjit Varkey Chudukatil 1 3,121,616.32 98055
José Saraiva 2 2,604,540.72 98055
Shu Ito 2 2,458,535.62 98055
Rachel Valdez 3 1,827,066.71 98055
Tete Mensa-Annan 3 1,576,562.20 98055
David Campbell 4 1,573,012.94 98055
Lynn Tsoflias 4 1,421,810.92 98055
(14 row(s) affected)
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
C. Membalikan baris menjadi grup
Contoh berikut menggunakan fungsi NTILE untuk membagi satu set tenaga penjual menjadi empat grup berdasarkan kuota penjualan yang ditetapkan untuk tahun 2003. Karena jumlah total baris tidak dapat dibagi berdasarkan jumlah grup, grup pertama memiliki lima baris dan grup yang tersisa masing-masing memiliki empat baris.
-- Uses AdventureWorks
SELECT e.LastName, NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE sq.CalendarYear = 2003
AND SalesTerritoryKey IS NOT NULL AND SalesAmountQuota <> 0
GROUP BY e.LastName
ORDER BY Quartile, e.LastName;
Berikut adalah hasil yang ditetapkan.
LastName Quartile SalesYTD
----------------- -------- ------------`
Blythe 1 4,716,000.00
Carson 1 4,350,000.00
Mitchell 1 4,682,000.00
Pak 1 5,142,000.00
Varkey Chudukatil 1 2,940,000.00
Ito 2 2,644,000.00
Saraiva 2 2,293,000.00
Vargas 2 1,617,000.00
Ansman-Wolfe 3 1,183,000.00
Campbell 3 1,438,000.00
Mensa-Annan 3 1,481,000.00
Valdez 3 1,294,000.00
Abbas 4 172,000.00
Albert 4 651,000.00
Jiang 4 544,000.00
Tsoflias 4 867,000.00
D. Membalah hasil yang ditetapkan dengan menggunakan PARTITION BY
Contoh berikut menambahkan argumen PARTITION BY ke kode dalam contoh A. Baris pertama kali dipartisi oleh SalesTerritoryCountry dan kemudian dibagi menjadi dua grup dalam masing-masing SalesTerritoryCountry. Perhatikan bahwa ORDER BY dalam klausul OVER mengurutkan NTILE dan ORDER BY dari pernyataan SELECT mengurutkan tataan hasil.
-- Uses AdventureWorks
SELECT e.LastName, NTILE(2) OVER(PARTITION BY e.SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
,st.SalesTerritoryCountry
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory AS st
ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE sq.CalendarYear = 2003
GROUP BY e.LastName,e.SalesTerritoryKey,st.SalesTerritoryCountry
ORDER BY st.SalesTerritoryCountry, Quartile;
Berikut adalah hasil yang ditetapkan.
LastName Quartile SalesYTD SalesTerritoryCountry
----------------- -------- -------------- ------------------
Tsoflias 1 867,000.00 Australia
Saraiva 1 2,293,000.00 Canada
Varkey Chudukatil 1 2,940,000.00 France
Valdez 1 1,294,000.00 Germany
Alberts 1 651,000.00 NA
Jiang 1 544,000.00 NA
Pak 1 5,142,000.00 United Kingdom
Mensa-Annan 1 1,481,000.00 United States
Campbell 1 1,438,000.00 United States
Reiter 1 2,768,000.00 United States
Blythe 1 4,716,000.00 United States
Carson 1 4,350,000.00 United States
Mitchell 1 4,682,000.00 United States
Vargas 2 1,617,000.00 Canada
Abbas 2 172,000.00 NA
Ito 2 2,644,000.00 United States
Ansman-Wolfe 2 1,183,000.00 United States
Lihat Juga
RANK (T-SQL)
DENSE_RANK (T-SQL)
ROW_NUMBER (T-SQL)
Fungsi Peringkat (Transact-SQL)
Fungsi Bawaan (Transact-SQL)
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk