対象者:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticsアナリティクスプラットフォームシステム(PDW)Microsoft FabricにおけるSQLデータベース
順序付けられたパーティションの行を、指定した数のグループに分散します。 グループには、1 から始まる番号が付けられます。 各行について、 NTILE は行が属するグループの番号を返します。
構文
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] <order_by_clause> )
引数
integer_expression
各パーティションを分割する必要があるグループの数を指定する正の整数式。 であれば、 任意 の型にint, 、または bigintです。
<partition_by_clause>
FROM 句と JOIN、APPLY、PIVOT 句で生成された結果セットを、関数が適用されるパーティションに分割します。
PARTITION BY構文については、「SELECT - OVER 句」を参照してください。
<order_by_clause>
パーティション内の行に NTILE 値を割り当てる順序を決定します。 ランク付け関数で <order_by_clause> が使用されている場合、整数は列を表すことはできません。
戻り値の型
bigint
解説
パーティション内の行数が integer_expressionで割り切れない場合、2 つのサイズのグループが 1 つのメンバーによって異なります。 大きなグループは、 OVER 句で指定された順序で小さいグループの前に来ます。 たとえば、行の合計数が 53 で、グループの数が 5 の場合、最初の 3 つのグループは 11 行、残りの 2 つのグループはそれぞれ 10 行になります。 一方、行の合計数がグループの数で割り切れる場合、行はグループ間で均等に分散されます。 たとえば、行の合計数が 50 で、グループが 5 つある場合、各バケットには 10 行が含まれます。
NTILE は非決定的です。 詳細については、「 決定論的関数と非決定的関数」を参照してください。
例
この記事のコード サンプルでは、microsoft AdventureWorks2025のホーム ページからダウンロードできるAdventureWorksDW2025またはサンプル データベースを使用します。
注
次の例では、 CONVERT 関数を使用して、 SalesYTD データを通貨として書式設定します。
A. 行をグループに分割する
次の例では、年度累計の売上高に基づいて、行を 4 つの従業員グループに分割します。 行の合計数はグループの数では割り切れないので、最初の 2 つのグループには 4 つの行があり、残りのグループにはそれぞれ 3 つの行があります。
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
結果セットは次のとおりです。
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
B. PARTITION BY を使用して結果セットを除算する
次の例では、PARTITION BY 引数を例 A のコードに追加します。まず、行を PostalCode でパーティション分割した後、それぞれの PostalCode 内で 4 つのグループに分割します。 この例では、@NTILE_Var 変数も宣言し、その変数を使用して integer_expression パラメーターの値を指定します。
USE AdventureWorks2022;
GO
DECLARE @NTILE_Var AS 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
結果セットは次のとおりです。
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
例: Azure Synapse Analytics、Analytics Platform System (PDW)
C. 行をグループに分割する
次の例では、 NTILE 関数を使用して、2003 年に割り当てられた販売クォータに基づいて販売員のセットを 4 つのグループに分割します。 行の合計数はグループの数で割り切れないので、最初のグループには 5 つの行があり、残りのグループにはそれぞれ 4 つの行があります。
-- 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;
結果セットは次のとおりです。
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. PARTITION BY を使用して結果セットを除算する
次の例では、例 A のコードに PARTITION BY 引数を追加します。行は最初に SalesTerritoryCountry でパーティション分割され、次に各 SalesTerritoryCountry内の 2 つのグループに分割されます。
ORDER BY句のOVERはNTILEを並べ替え、ORDER BYステートメントのSELECTは結果セットを並べ替える。
-- 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;
結果セットは次のとおりです。
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