SQL NTILE 函数

SQL Server 中的 NTILE 函数是排名函数之一。此 NTILE 函数会将排名编号分配给分区中的每个记录，其语法如下：

SQL NTILE 函数语法

SELECT NTILE(Interger_Value) OVER (PARTITION_BY_Clause ORDER_BY_Clause)
FROM [Source]

Integer_Value：它将使用此整数值来决定为每个分区分配的排名数量。例如，如果指定 2，SQL Server NTILE 函数将为每个类别分配 2 个排名编号。

Partition_By_Clause：这将把 SELECT 语句选择的记录划分为分区。

  • 如果指定了 Partition By Clause，它将为每个部分分配排名编号。
  • 如果未指定，NTILE 函数会将所有记录视为一个分区。因此，它将从上到下分配排名编号。

对于此 NTILE 函数示例，我们将使用以下表数据。

SQL NTILE 函数示例

NTILE 函数允许您为分区中的每个记录分配排名编号。在此 排名函数 示例中，我们将展示如何在 SQL Server 表中对分区记录进行排名。以下 Select Statement 查询将按职业（Occupation）分隔数据，并使用年收入（yearly income）分配排名编号。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,NTILE(2) OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [NTILE NUMBER]
  FROM [Customers]
SQL Server NTILE Function Example 1

下面的 PARTITION BY [Occupation] 语句使用 Occupation 列将选定的数据分为一个分区。从上图可以看出，我们得到了四个分区。

此 Order By 按 [年收入] 降序对数据进行排序。

在下面的语句中，我们使用了带有 Partition by 子句的 NTILE(2) 函数。这里的整数值为 2。这意味着此函数将为每个分区（4 个部分）分配两个排名编号。

尽管第 6 和第 7 条记录的年收入不相同（分别为 90000 和 80000），但它们被赋予了相同的排名，因为 NTILE(2) 只能为每个分隔分配 2 个排名。

SQL NTILE 函数示例 2

在此 NTILE 函数示例中，我们将展示如果在 Integer_Value 位置分配不同的值会发生什么。以下示例使用了上面的示例查询，但我们将数字从 2 更改为 NTILE(3)。

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,NTILE(3) OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [NTILE NUMBER]
  FROM [Customers]

上述查询将为每个职业分配三个排名编号。

NTILE FUNCTION 2

第 3 条和第 4 条记录的年收入相同（均为 80000）。但是，它们被赋予了不同的排名，因为 NTILE(3) 会为每个职业分配三个排名。

SQL Server NTILE 函数，不带 Partition By Clause

如果在 NTILE 函数中省略 Partition By Clause 会发生什么？例如，以下查询将使用上面示例查询，但不带 Partition by 子句。

这里的整数值为 2。这意味着 NTILE 函数将为每个部分分配两个排名编号。

由于我们没有使用任何分区，它会将所有十条记录视为一个分区。接下来，它将为这十条记录分配两个排名编号。

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(2) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]
Example 3

NTILE，不带 Partition By Clause 2

在此 NTILE 函数示例中，我们将在 Integer_Value 位置分配一个不同的值。例如，这里我们将 2 改为了 3。

SELECT [LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Occupation]
      ,[FirstName]
      ,NTILE(3) OVER (
                       ORDER BY [YearlyIncome] DESC
                     ) AS [NTILE NUMBER]
  FROM [Customers]
NTILE Function without Partition By Clause 4

这里我们没有使用任何分区子句。这意味着此函数会将所有 10 条记录视为一个分区。接下来，它将为这 10 条记录分配 3 个排名编号，因为我们将 Integer_Value 设置为 3。

评论已关闭。