SQL中几个常用的排序函数

2022-07-12 10:42:11

     最近使用窗口函数的频率越来越高，这里打算简单介绍一下几个排序的函数，做一个引子希望以后这方面的问题能够更深入的理解，这里先简单介绍一下几个简单的排序函数及其相关子句，这里先从什么是排序开始吧。

排序函数是做什么的？

排序函数的作用是基于一个结果集返回一个排序值。排序值就是一个数字，这个数字是典型的以1开始且自增长为1的行值。由ranking函数决定排序值可以使唯一的对于当前结果集，或者某些行数据有相同的排序值。在接下来我将研究不同的排序函数以及如何使用这些函数。

使用RANK函数的例子

RANK函数每个分区的排序都是从1开始。“partition”是一组有相同指定分区列值的数据行的集合。如果一个分区中有相同排序列的值（这个列指定在ORDER BY后面），然后相同排序列值的行将会分配给相同的排序值。有点绕口，为了更好的理解，如何使用，让我们看下下面的语法:

RANK ( ) OVER ( [ PARTITION BY <partition_column> ] ORDER BY <order_by_column> )

这里有几个参数:

  • <partition_column>: 指定一个或者多个列名作为分区数据
  • <order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序
注意:

PARTITION BY子句是一个可选项。如是不使用，数据将按照一个分区对所有数据进行排序。如果指定了PARTITION BY子句，则每个分区的数据集都各自进行从1开始的排序。

现在对RANK函数的语法和如何工作有了一定的理解，下面运行一对该函数的例子。需要说明一下我的例子的运行环境都是AdventureWorks2012 数据库，可以从网络上下载这里给出一个下载地址http://msftdbprodsamples.codeplex.com/releases/view/93587。

下面是第一个使用RANK函数的例子:

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       RANK() OVER

         (ORDER BY PostalCode ASC) AS RankingValue

FROM Person.Address

WHERE StateProvinceID IN (23,46);

Code1: 只有RANK函数不分区

运行代码后，结果集如下：

PostalCode      StateProvinceID RankingValue

--------------- --------------- --------------------

03064           46              1

03064           46              1

03106           46              3

03276           46              4

03865           46              5

83301           23              6

83402           23              7

83501           23              8

83702           23              9

83864           23              10
 

如上所示，按照RANK函数使结果集按照列RankingValue进行了排序。在例子中排序是基于列PostalCode。每一个唯一的PostalCode 得到一个不同的排序值。这里PostalCode 为03054 有两行数据，它们的排序值都是1,因为有两个1，所以排序2就被跳过。其余的排序继续往下依次进行。

由于RANK函数的分区子句没有使用，那么整个结果集被当做一个单一的分区。如果我打算按照独立的StateProvinceID 进行分区，然后进行排序我可以做按照如下的例子来执行：

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       RANK() OVER

         (PARTITION BY StateProvinceID

		  ORDER BY PostalCode ASC) AS RankingValue

FROM Person.Address

WHERE StateProvinceID IN (23,46);

Code 2: 使用分区子句

运行代码后的结果集：

PostalCode      StateProvinceID RankingValue

--------------- --------------- --------------------

83301           23              1

83402           23              2

83501           23              3

83702           23              4

83864           23              5

03064           46              1

03064           46              1

03106           46              3

03276           46              4

03865           46              5

在输出结果中分为了两个分区，一个分区是StateProvinceID 是23的，而另一个是包含StateProvinceID 值为46的、注意每个分区都是从1开始进行排序的。

使用DENSE_RANK函数

当运行RANK函数时，由于有一个相同的PostalCode ，输出结果会跳过一个排序值2，通过使用DENSE_RANK函数我能生成一个不省略改相同排序值的一个排序。该函数语法如下:

DENSE_RANK ( ) OVER ( [ PARTIION BY <partition_column> ] ORDER BY <order_by_column> )

语法中唯一的不同就是函数名称的改变。让我们运行下面的代码来研究下函数：

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       DENSE_RANK() OVER

         (PARTITION BY StateProvinceID

		  ORDER BY PostalCode ASC) AS RankingValue

FROM Person.Address

WHERE StateProvinceID IN (23,46);

Code3: 使用 DENSE_RANK

结果集如下：

PostalCode      StateProvinceID RankingValue

--------------- --------------- --------------------

83301           23              1

83402           23              2

83501           23              3

83702           23              4

83864           23              5

03064           46              1

03064           46              1

03106           46              2

03276           46              3

03865           46              4

根据结果集，可以看到PostalCode 03064 有相同的排序值，但是下一个PostalCode 的排序值为2而不是3了。与RANK函数的不同就是当有重复排序值时它能保证了排序序列中没有省略排序。

使用NTILE 函数

该函数将数据集合划分为不同的组。得到组的数量是根据指定的一个整数来确定的。下面就是NTILE 函数的语法:

NTILE (integer_expression) OVER ( [ PARTIION BY <partition_column> ] ORDER BY <order_by_column> )

Where:

  • <integer_expression>: 确定创建不同组的数量
  • <partition_column>:确定一个或者多个列用来进行分区数据
  • <order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序

为了更好地理解，让我们回顾几个不同的例子。运行下面代码：

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       NTILE(2) OVER

         (ORDER BY PostalCode ASC) AS NTileValue

FROM Person.Address

WHERE StateProvinceID IN (23,46);

Code4: 使用NTILE 函数查询

运行结果如下：

PostalCode      StateProvinceID NTileValue

--------------- --------------- --------------------

03064           46              1

03064           46              1

03106           46              1

03276           46              1

03865           46              1

83301           23              2

83402           23              2

83501           23              2

83702           23              2

83864           23              2

通过观察结果集，能很容易发现有两个不同的NTileValue 的列值，1和2。两个不同的NTileValue 值被创建是因为这里我查询语句中指定了“NTILE(2)” 。这个括号内的值就是整数表达式，作用就是指定创建的组的数量。当看到结果集中有10行数据，前五行NTileValue 为1,后五行为2。不出所料整个结果集被平均分成了两组。

如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内，知道所有的剩余行都被分配完毕。如下所示：

USE AdventureWorks2012;

GO

DECLARE @Integer_Expression int = 4;

SELECT PostalCode, StateProvinceID,

       NTILE(@Integer_Expression) OVER

         (ORDER BY PostalCode ASC) AS NTileValue

FROM Person.Address

WHERE StateProvinceID IN (46,23);

Code 5: NTile 查询不能平均分配结果集

运行代码如下：

PostalCode      StateProvinceID NTileValue

--------------- --------------- --------------------

03064           46              1

03064           46              1

03106           46              1

03276           46              2

03865           46              2

83301           23              2

83402           23              3

83501           23              3

83702           23              4

83864           23              4

这里直奔主题，10个结果行，参数为4需要分成4组，那么10除以4 余数为2。这意味着前两组会多一行比后两组。如上所示，在这个输出结果中1和2组都有3行，然后NTileValue 为3和4的组只有两行。

跟RANK函数一样，我们也能使用partition 分区子句来创建分区下的NTILE 函数。当引入PARTITION BY 子句时，每个分区内部都从1开始进行NTILE排序。下面展示一下运行代码：

USE AdventureWorks2012;

GO

DECLARE @Integer_Expression int = 3;

SELECT PostalCode, StateProvinceID,

       NTILE(@Integer_Expression) OVER

		 (PARTITION BY StateProvinceID

		  ORDER BY PostalCode ASC) AS NTileValue

FROM Person.Address

WHERE StateProvinceID IN (46,23);

Code 6: 使用分区子句后，使用NTile 查询不平均分组

运行代码如下：

PostalCode      StateProvinceID NTileValue

--------------- --------------- --------------------

83301           23              1

83402           23              1

83501           23              2

83702           23              2

83864           23              3

03064           46              1

03064           46              1

03106           46              2

03276           46              2

03865           46              3

通过结果集可以看到加入分区子句后对NTILE函数的影响。如果观察输出的NTileValue列值，可以发现排序从StateProvinceID  为46开始重新从1开始。这就是加入“PARTITION BY StateProvinceID”子句的作用，先分区在分组排序。

使用 ROW_NUMBER 函数

当打算为输出的行生成一个行号时，行号顺序地自增长，步长为1.为了完成目标我们需要使用ROW_NUMBER 函数。

下面是使用ROW_NUMBER 的例子:

ROW_NUMBER () OVER ( [ PARTIION BY <partition_expressions> ] ORDER BY <order_by_column> )

代码如下：

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       ROW_NUMBER() OVER

		 (ORDER BY PostalCode ASC) AS RowNumber

FROM Person.Address

WHERE StateProvinceID IN (46,23);

Code  7: 使用ROW_NUMBER 函数

运行结果如下：

PostalCode      StateProvinceID RowNumber

--------------- --------------- --------------------

03064           46              1

03064           46              2

03106           46              3

03276           46              4

03865           46              5

83301           23              6

83402           23              7

83501           23              8

83702           23              9

83864           23              10

如果想对输出的PostalCode进行排序，但是你打算先按照StateProvinceID进行分组，再排序。为了实现上述要求，我加入PARTITION BY子句，代码如下：

USE AdventureWorks2012;

GO

SELECT PostalCode, StateProvinceID,

       ROW_NUMBER() OVER

		 (PARTITION BY StateProvinceID

		  ORDER BY PostalCode ASC) AS RowNumber

FROM Person.Address

WHERE StateProvinceID IN (46,23);

Code 8: 使用PARTITION BY 子句和ROW_NUMBER 函数查询

运行结果如下：

PostalCode      StateProvinceID RowNumber

--------------- --------------- --------------------

83301           23              1

83402           23              2

83501           23              3

83702           23              4

83864           23              5

03064           46              1

03064           46              2

03106           46              3

03276           46              4

正如你看到的结果，通过添加分区子句，行数列RowNumber 每个不同的StateProvinceID 值都会从1重新开始排序。

总结

本篇讲了多种不同的排序数据的方式，并且有一些方式要求分配一个序列化的数字。我先后展示了如何使用ROW_NUMBER, NTILE, RANK 和 DENSE_RANK函数，如何为每一行数据生成序列化的列值。希望能够让大家在使用时更方便，这里也只是展示了一部分窗口函数的使用。还有很多新的窗口函数希望跟大家一起讨论学习。这里只是做一个简单介绍了。

上一篇：discuz论坛几种安全策略（二）


下一篇：解决：error: .repo/manifests/: contains uncommitted changes