SQL中几个常用的排序函数
|
最近使用窗口函数的频率越来越高，这里打算简单介绍一下几个排序的函数，做一个引子希望以后这方面的问题能够更深入的理解，这里先简单介绍一下几个简单的排序函数及其相关子句，这里先从什么是排序开始吧。 排序函数是做什么的？ 排序函数的作用是基于一个结果集返回一个排序值。排序值就是一个数字，这个数字是典型的以1开始且自增长为1的行值。由ranking函数决定排序值可以使唯一的对于当前结果集，或者某些行数据有相同的排序值。在接下来我将研究不同的排序函数以及如何使用这些函数。 使用RANK函数的例子RANK函数每个分区的排序都是从1开始。“partition”是一组有相同指定分区列值的数据行的集合。如果一个分区中有相同排序列的值（这个列指定在ORDER BY后面），然后相同排序列值的行将会分配给相同的排序值。有点绕口，为了更好的理解，如何使用，让我们看下下面的语法: RANK ( ) OVER ( [ PARTITION BY 这里有几个参数:
注意:PARTITION BY子句是一个可选项。如是不使用，数据将按照一个分区对所有数据进行排序。如果指定了PARTITION BY子句，则每个分区的数据集都各自进行从1开始的排序。 现在对RANK函数的语法和如何工作有了一定的理解，下面运行一对该函数的例子。需要说明一下我的例子的运行环境都是AdventureWorks2012 数据库，可以从网络上下载这里给出一个下载地址。 下面是第一个使用RANK函数的例子: 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 进行分区，然后进行排序我可以做按照如下的例子来执行： 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 语法中唯一的不同就是函数名称的改变。让我们运行下面的代码来研究下函数： 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 Where:
为了更好地理解，让我们回顾几个不同的例子。运行下面代码： 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。不出所料整个结果集被平均分成了两组。 如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内，知道所有的剩余行都被分配完毕。如下所示： 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排序。下面展示一下运行代码： Code 6: 使用分区子句后，使用NTile 查询不平均分组 （编辑：商洛站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
