NTILE (Transact-SQL)NTILE (Transact-SQL)

このトピックに適用されますはいSQL Server (2008 以降)はいAzure SQL DatabaseはいAzure SQL Data Warehouse [はい]。並列データ ウェアハウス THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

順序付けられたパーティションの行を、指定した数のグループに分散します。Distributes the rows in an ordered partition into a specified number of groups. グループには、1 から始まる番号が付けられます。The groups are numbered, starting at one. 行ごとに、NTILE はその行が属しているグループの番号を返します。For each row, NTILE returns the number of the group to which the row belongs.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

引数Arguments

integer_expressioninteger_expression
各パーティションを分割するグループの数を表す正の整数定数式を指定します。Is a positive integer constant expression that specifies the number of groups into which each partition must be divided. であれば、 任意 の型にint, 、または bigintです。integer_expression can be of type int, or bigint.

<partition_by_clause><partition_by_clause>
は、FROM 句で生成された結果セットをパーティションに分割します。このパーティションに関数が適用されます。Divides the result set produced by the FROM clause into partitions to which the function is applied. PARTITION BY の構文で、を参照してください。 経由句 (&) #40 です。TRANSACT-SQL と #41;.For the PARTITION BY syntax, see OVER Clause (Transact-SQL).

<order_by_clause><order_by_clause>
NTILE 値がパーティション内の行に割り当てられる順序を決定します。Determines the order in which the NTILE values are assigned to the rows in a partition. <<order_by_clause> が順位付け関数で使用される場合、整数値で列を表すことはできません。An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

戻り値の型Return Types

bigintbigint

RemarksRemarks

パーティション内の行の数がで割り切れるかどうか であれば、任意, 、2 つのサイズが異なる 1 つのメンバーによってグループが生成されます。If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. OVER 句で指定される順序では、大きいグループが小さいグループよりも前になります。Larger groups come before smaller groups in the order specified by the OVER clause. たとえば、行の総数が 53 でグループの数が 5 の場合、最初の 3 つのグループに 11 行が割り当てられ、残りの 2 つのグループにはそれぞれ 10 行が割り当てられます。For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. 一方、行の総数がグループの数で割り切れる場合、行はそれらのグループに均等に割り当てられます。If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. たとえば、行の総数が 50 で、5 つのグループがある場合、各グループに 10 行ずつ割り当てられます。For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

NTILE は非決定的です。NTILE is nondeterministic. 詳細については、「 決定的関数と非決定的関数」を参照してください。For more information, see Deterministic and Nondeterministic Functions.

使用例Examples

A.A. 行をグループに分割するDividing rows into groups

次の例では、年度累計の売上高に基づいて、行を 4 つの従業員グループに分割します。The following example divides rows into four groups of employees based on their year-to-date sales. 行の総数がグループの数で割り切れないため、最初の 2 つのグループに 4 つの行が割り当てられ、残りのグループにはそれぞれ 3 つの行が割り当てられます。Because the total number of rows is not divisible by the number of groups, the first two groups have four rows and the remaining groups have three rows each.

USE AdventureWorks2012;   
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  

以下に結果セットを示します。Here is the result set.


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.B. PARTITION BY を使用して結果セットを分割するDividing the result set by using PARTITION BY

次の例では、PARTITION BY 引数を例 A のコードに追加します。まず、行を PostalCode でパーティション分割した後、それぞれの PostalCode 内で 4 つのグループに分割します。The following example adds the PARTITION BY argument to the code in example A. The rows are first partitioned by PostalCode and then divided into four groups within each PostalCode. この例では、@NTILE_Var 変数も宣言し、その変数を使用して integer_expression パラメーターの値を指定します。The example also declares a variable @NTILE_Var and uses that variable to specify the value for the integer_expression parameter.

USE AdventureWorks2012;  
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  

以下に結果セットを示します。Here is the result set.

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)  

例: Azure SQL データ ウェアハウスAzure SQL Data Warehouse および Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL データ ウェアハウスAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

C.C. 行をグループに分割するDividing rows into groups

次の例では、各自に割り当てられた 2003 年度の販売ノルマに基づいて、一連の販売員を 4 つのグループに分割します。The following example uses the NTILE function to divide a set of salespersons into four groups based on their assigned sales quota for the year 2003. 行の総数がグループの数で割り切れないため、最初のグループに 5 つの行が割り当てられ、残りのグループにはそれぞれ 4 つの行が割り当てられます。Because the total number of rows is not divisible by the number of groups, the first group has five rows and the remaining groups have four rows each.

-- 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;  

以下に結果セットを示します。Here is the result set.

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.D. PARTITION BY を使用して結果セットを分割するDividing the result set by using PARTITION BY

次の例では、PARTITION BY 引数を例 A のコードに追加します。まず、行を SalesTerritoryCountry でパーティション分割した後、それぞれの SalesTerritoryCountry 内で 2 つのグループに分割します。The following example adds the PARTITION BY argument to the code in example A. The rows are first partitioned by SalesTerritoryCountry and then divided into two groups within each SalesTerritoryCountry. OVER 句の ORDER BY は STDEV を並べ替え、SELECT ステートメントの NTILE と ORDER BY は結果セットを並べ替えることに注意してください。Notice that the ORDER BY in the OVER clause orders the NTILE and the ORDER BY of the SELECT statement orders the result set.

-- 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;  

以下に結果セットを示します。Here is the result set.

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

参照See Also

RANK (Transact-SQL) RANK (Transact-SQL)
DENSE_RANK (&) #40 です。TRANSACT-SQL と #41 です。 DENSE_RANK (Transact-SQL)
ROW_NUMBER (&) #40 です。TRANSACT-SQL と #41 です。 ROW_NUMBER (Transact-SQL)
順位付け関数 (&) #40 です。TRANSACT-SQL と #41 です。 Ranking Functions (Transact-SQL)
組み込み関数 (Transact-SQL)Built-in Functions (Transact-SQL)