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

## 語法Syntax

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

## 引數Arguments

integer_expressioninteger_expression

<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 語法，請參閱 OVER 子句 (Transact-SQL)For the PARTITION BY syntax, see OVER Clause (Transact-SQL).

<order_by_clause><order_by_clause>

bigintbigint

## RemarksRemarks

NTILE 不具決定性。NTILE is nondeterministic. 如需詳細資訊，請參閱 決定性與非決定性函數For more information, see Deterministic and Nondeterministic Functions.

## 範例Examples

### A.A.將資料列分割成數個群組Dividing rows into groups

``````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
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.B.使用 PARTITION BY 分割結果集Dividing the result set by using PARTITION BY

``````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
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)
``````

## 範例： Azure SQL 資料倉儲Azure SQL Data Warehouse 和 平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

### C.C.將資料列分割成數個群組Dividing rows into groups

``````-- 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.D.使用 PARTITION BY 分割結果集Dividing the result set by using PARTITION BY

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