Question

SQL中几个常用的排序函数

Answer 1

排序函数是做什么的？
排序函数的作用是基于一个结果集返回一个排序值。排序值就是一个数字，这个数字是典型的以1开始且自增长为1的行值。由ranking函数决定排序值可以使唯一的对于当前结果集，或者某些行数据有相同的排序值。在接下来我将研究不同的排序函数以及如何使用这些函数。
使用RANK函数的例子 RANK函数每个分区的排序都是从1开始。“partition”是一组有相同指定分区列值的数据行的集合。如果一个分区中有相同排序列的值（这个列指定在ORDER BY后面），然后相同排序列值的行将会分配给相同的排序值。有点绕口，为了更好的理解，如何使用，让我们看下下面的语法:
RANK()OVER([PARTITIONBY<partition_column>]ORDERBY<order_by_column>)这里有几个参数:
<partition_column>: 指定一个或者多个列名作为分区数据
<order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序
注意:PARTITION BY子句是一个可选项。如是不使用，数据将按照一个分区对所有数据进行排序。如果指定了PARTITION BY子句，则每个分区的数据集都各自进行从1开始的排序。
现在对RANK函数的语法和如何工作有了一定的理解，下面运行一对该函数的例子。需要说明一下我的例子的运行环境都是AdventureWorks2012数据库，可以从网络上下载这里给出一个下载地址http://msftdbprodsamples.codeplex.com/releases/view/93587。
下面是第一个使用RANK函数的例子:
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,RANK() OVER(ORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code1: 只有RANK函数不分区
运行代码后，结果集如下：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
03064461
03064461
03106463
03276464
03865465
83301236
83402237
83501238
83702239
838642310如上所示，按照RANK函数使结果集按照列RankingValue进行了排序。在例子中排序是基于列PostalCode。每一个唯一的PostalCode得到一个不同的排序值。这里PostalCode为03054 有两行数据，它们的排序值都是1,因为有两个1，所以排序2就被跳过。其余的排序继续往下依次进行。
由于RANK函数的分区子句没有使用，那么整个结果集被当做一个单一的分区。如果我打算按照独立的StateProvinceID进行分区，然后进行排序我可以做按照如下的例子来执行：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,RANK() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code 2: 使用分区子句
运行代码后的结果集：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064461
03106463
03276464
03865465
在输出结果中分为了两个分区，一个分区是StateProvinceID是23的，而另一个是包含StateProvinceID值为46的、注意每个分区都是从1开始进行排序的。
使用DENSE_RANK函数
当运行RANK函数时，由于有一个相同的PostalCode，输出结果会跳过一个排序值2，通过使用DENSE_RANK函数我能生成一个不省略改相同排序值的一个排序。该函数语法如下:
DENSE_RANK()OVER([PARTIIONBY<partition_column>]ORDERBY<order_by_column>)
语法中唯一的不同就是函数名称的改变。让我们运行下面的代码来研究下函数：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,DENSE_RANK() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code3: 使用 DENSE_RANK
结果集如下：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064461
03106462
03276463
03865464
根据结果集，可以看到PostalCode03064 有相同的排序值，但是下一个PostalCode的排序值为2而不是3了。与RANK函数的不同就是当有重复排序值时它能保证了排序序列中没有省略排序。
使用NTILE 函数该函数将数据集合划分为不同的组。得到组的数量是根据指定的一个整数来确定的。下面就是NTILE 函数的语法:
NTILE(integer_expression)OVER([PARTIIONBY<partition_column>]ORDERBY<order_by_column>)Where:
<integer_expression>: 确定创建不同组的数量
<partition_column>:确定一个或者多个列用来进行分区数据
<order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序
为了更好地理解，让我们回顾几个不同的例子。运行下面代码：
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,NTILE(2) OVER(ORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code4: 使用NTILE 函数查询
运行结果如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
03064461
03064461
03106461
03276461
03865461
83301232
83402232
83501232
83702232
83864232
通过观察结果集，能很容易发现有两个不同的NTileValue的列值，1和2。两个不同的NTileValue值被创建是因为这里我查询语句中指定了“NTILE(2)” 。这个括号内的值就是整数表达式，作用就是指定创建的组的数量。当看到结果集中有10行数据，前五行NTileValue为1,后五行为2。不出所料整个结果集被平均分成了两组。
如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内，知道所有的剩余行都被分配完毕。如下所示：
12345678USE AdventureWorks2012;GODECLARE@Integer_Expressionint= 4;SELECTPostalCode, StateProvinceID,NTILE(@Integer_Expression) OVER(ORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 5: NTile 查询不能平均分配结果集
运行代码如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
03064461
03064461
03106461
03276462
03865462
83301232
83402233
83501233
83702234
83864234
这里直奔主题，10个结果行，参数为4需要分成4组，那么10除以4 余数为2。这意味着前两组会多一行比后两组。如上所示，在这个输出结果中1和2组都有3行，然后NTileValue为3和4的组只有两行。
跟RANK函数一样，我们也能使用partition 分区子句来创建分区下的NTILE 函数。当引入PARTITION BY 子句时，每个分区内部都从1开始进行NTILE排序。下面展示一下运行代码：
123456789USE AdventureWorks2012;GODECLARE@Integer_Expressionint= 3;SELECTPostalCode, StateProvinceID,NTILE(@Integer_Expression) OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 6: 使用分区子句后，使用NTile 查询不平均分组
运行代码如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
83301231
83402231
83501232
83702232
83864233
03064461
03064461
03106462
03276462
03865463
通过结果集可以看到加入分区子句后对NTILE函数的影响。如果观察输出的NTileValue列值，可以发现排序从StateProvinceID 为46开始重新从1开始。这就是加入“PARTITION BY StateProvinceID”子句的作用，先分区在分组排序。
使用 ROW_NUMBER 函数 当打算为输出的行生成一个行号时，行号顺序地自增长，步长为1.为了完成目标我们需要使用ROW_NUMBER 函数。
下面是使用ROW_NUMBER 的例子:
ROW_NUMBER()OVER([PARTIIONBY<partition_expressions>]ORDERBY<order_by_column>)
代码如下：
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,ROW_NUMBER() OVER(ORDERBYPostalCodeASC)ASRowNumberFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 7: 使用ROW_NUMBER 函数
运行结果如下：
PostalCodeStateProvinceIDRowNumber
--------------------------------------------------
03064461
03064462
03106463
03276464
03865465
83301236
83402237
83501238
83702239
838642310
如果想对输出的PostalCode进行排序，但是你打算先按照StateProvinceID进行分组，再排序。为了实现上述要求，我加入PARTITION BY子句，代码如下：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,ROW_NUMBER() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRowNumberFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 8: 使用PARTITION BY 子句和ROW_NUMBER 函数查询
运行结果如下：
PostalCodeStateProvinceIDRowNumber
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064462
03106463
03276464正如你看到的结果，通过添加分区子句，行数列RowNumber每个不同的StateProvinceID值都会从1重新开始排序。
SQL中几个常用的排序函数
标签：sql