NTILE (Transact-SQL)

順序付けられたパーティションの行を、指定した数のグループに分散します。 グループには、1 から始まる番号が付けられます。 行ごとに、NTILE はその行が属しているグループの番号を返します。

トピック リンク アイコン Transact-SQL 構文表記規則

構文

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

引数

  • integer_expression
    各パーティションを分割するグループの数を表す正の整数定数式を指定します。 integer_expression には、int 型または bigint 型を指定できます。

  • <partition_by_clause>
    FROM 句で生成された結果セットをパーティションに分割します。このパーティションに関数が適用されます。 PARTITION BY の構文については、「OVER 句 (Transact-SQL)」を参照してください。

  • <order_by_clause>
    NTILE 値がパーティション内の行に割り当てられる順序を決定します。 <order_by_clause> が順位付け関数で使用される場合、整数値で列を表すことはできません。

戻り値の型

bigint

説明

パーティション内の行の数が integer_expression で割り切れない場合、1 つのメンバーによって異なる 2 つのサイズのグループが生成されます。 OVER 句で指定される順序では、大きいグループが小さいグループよりも前になります。 たとえば、行の総数が 53 でグループの数が 5 の場合、最初の 3 つのグループに 11 行が割り当てられ、残りの 2 つのグループにはそれぞれ 10 行が割り当てられます。 一方、行の総数がグループの数で割り切れる場合、行はそれらのグループに均等に割り当てられます。 たとえば、行の総数が 50 で、5 つのグループがある場合、各グループに 10 行ずつ割り当てられます。

使用例

A. 行をグループに分割する

次の例では、年度累計の売上高に基づいて、行を 4 つの従業員グループに分割します。 行の総数がグループの数で割り切れないため、最初の 2 つのグループに 4 つの行が割り当てられ、残りのグループにはそれぞれ 3 つの行が割り当てられます。

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

以下に結果セットを示します。

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. PARTITION BY を使用して結果セットを分割する

次の例では、PARTITION BY 引数を例 A のコードに追加します。 まず、行を PostalCode でパーティション分割した後、それぞれの PostalCode 内で 4 つのグループに分割します。 また、変数 @NTILE\_Var を宣言し、その変数を使用して integer_expression パラメーターの値を指定します。

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

以下に結果セットを示します。

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)

関連項目

参照

RANK (Transact-SQL)

DENSE_RANK (Transact-SQL)

ROW_NUMBER (Transact-SQL)

順位付け関数 (Transact-SQL)

組み込み関数 (Transact-SQL)