sql rank

We perform calculations on data using various aggregated functions such as Max, Min, and AVG. We get a single output row using these functions. SQL Sever provides SQL RANK functions to specify rank for individual fields as per the categorizations. It returns an aggregated value for each participating row. SQL RANK functions also knows as Window Functions.

我们使用各种汇总函数（例如最大，最小和AVG）对数据进行计算。 使用这些函数，我们得到一个输出行。 SQL Sever提供了SQL RANK函数，以根据分类为各个字段指定等级。 它为每个参与的行返回一个汇总值。 SQL RANK函数也称为窗口函数。

• Note: Windows term in this does not relate to the Microsoft Windows operating system. These are SQL RANK functions.
• 注意： Windows术语与Microsoft Windows操作系统无关。 这些是SQL RANK函数。

We have the following rank functions.

我们具有以下等级函数。

• ROW_NUMBER()
  ROW_NUMBER（）
• RANK()
  秩（）
• DENSE_RANK()
  DENSE_RANK（）
• NTILE()
  NTILE（）

In the SQL RANK functions, we use the OVER() clause to define a set of rows in the result set. We can also use clause to define a subset of data in a partition. You can also use Order by clause to sort the results in a descending or ascending order.

在SQL RANK函数中，我们使用OVER（）子句在结果集中定义一组行。 我们还可以使用子句在分区中定义数据的子集。 您也可以使用Order by子句对结果进行降序或升序排序。

Before we explore these SQL RANK functions, let’s prepare sample data. In this sample data, we have exam results for three students in Maths, Science and English subjects.

在探索这些SQL RANK函数之前，让我们准备示例数据。 在此样本数据中，我们获得了三个数学，科学和英语学科学生的考试成绩。

CREATE TABLE ExamResult(StudentName VARCHAR(70),  Subject     VARCHAR(20),  Marks       INT);INSERT INTO ExamResultVALUES('Lily',  'Maths',  65);INSERT INTO ExamResultVALUES('Lily',  'Science',  80);INSERT INTO ExamResultVALUES('Lily',  'english',  70);INSERT INTO ExamResultVALUES('Isabella',  'Maths',  50);INSERT INTO ExamResultVALUES('Isabella',  'Science',  70);INSERT INTO ExamResultVALUES('Isabella',  'english',  90);INSERT INTO ExamResultVALUES('Olivia',  'Maths',  55);INSERT INTO ExamResultVALUES('Olivia',  'Science',  60);INSERT INTO ExamResultVALUES('Olivia',  'english',  89);

We have the following sample data in the ExamResult table.

ExamResult表中包含以下示例数据。

Let’s use each SQL Rank Functions in upcoming examples.

让我们在接下来的示例中使用每个SQL Rank函数。

ROW_Number（）SQL RANK函数 (ROW_Number() SQL RANK function)

We use ROW_Number() SQL RANK function to get a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.

我们使用ROW_Number（）SQL RANK函数为指定数据中的每一行获取唯一的顺序号。 它为第一行赋予等级1，然后为每一行将值递增1。 对于具有相似值的行，我们得到的排名也不同。

Execute the following query to get a rank for students as per their marks.

执行以下查询以根据学生的分数获得其排名。

SELECT Studentname,        Subject,        Marks,        ROW_NUMBER() OVER(ORDER BY Marks) RowNumberFROM ExamResult;

By default, it sorts the data in ascending order and starts assigning ranks for each row. In the above screenshot, we get ROW number 1 for marks 50.

默认情况下，它将按升序对数据进行排序，并开始为每一行分配等级。 在上面的屏幕截图中，我们获得了标记50的ROW号1。

We can specify descending order with Order By clause, and it changes the RANK accordingly.

我们可以使用Order By子句指定降序，然后相应地更改RANK。

SELECT Studentname,        Subject,        Marks,        ROW_NUMBER() OVER(ORDER BY Marks desc) RowNumberFROM ExamResult;

RANK（）SQL RANK函数 (RANK() SQL RANK Function)

We use RANK() SQL Rank function to specify rank for each row in the result set. We have student results for three subjects. We want to rank the result of students as per their marks in the subjects. For example, in the following screenshot, student Isabella got the highest marks in English subject and lowest marks in Maths subject. As per the marks, Isabella gets the first rank in English and 3rd place in Maths subject.

我们使用RANK（）SQL Rank函数为结果集中的每一行指定排名。 我们有三个科目的学生成绩。 我们希望根据学生在学科中的成绩对他们的成绩进行排名。 例如，在下面的屏幕截图中，学生Isabella在英语学科中得分最高，在数学学科中得分最低。 根据分数，伊莎贝拉（Isabella）在英语学科中排名第一，在数学学科中排名第三。

Execute the following query to get this result set. In this query, you can note the following things:

执行以下查询以获取该结果集。 在此查询中，您可以注意以下事项：

• BY Studentname clause to perform calculations on each student group BY Studentname子句对每个学生组进行计算
• Each subset should get rank as per their Marks in descending order
  每个子集应按照其标记的降序排列
• The result set uses Order By clause to sort results on Studentname and their rank
  结果集使用Order By子句对Studentname及其排名进行排序

SELECT Studentname,        Subject,        Marks,        RANK() OVER(PARTITION BY Studentname ORDER BY Marks DESC) RankFROM ExamResultORDER BY Studentname,          Rank;

Let’s execute the following query of SQL Rank function and look at the result set. In this query, we did not specify SQL PARTITION By clause to divide the data into a smaller subset. We use SQL Rank function with over clause on Marks clause ( in descending order) to get ranks for respective rows.

让我们执行以下SQL Rank函数查询，并查看结果集。 在此查询中，我们未指定SQL PARTITION By子句将数据划分为较小的子集。 我们对Marks子句的over子句使用SQL Rank函数（以降序排列）以获取各个行的排名。

SELECT Studentname,        Subject,        Marks,        RANK() OVER(ORDER BY Marks DESC) RankFROM ExamResultORDER BY Rank;

In the output, we can see each student get rank as per their marks irrespective of the specific subject. For example, the highest and lowest marks in the complete result set are 90 and 50 respectively. In the result set, the highest mark gets RANK 1, and the lowest mark gets RANK 9.

在输出中，我们可以看到每个学生都按照他们的分数获得排名，而与特定学科无关。 例如，完整结果集中的最高和最低标记分别为90和50。 在结果集中，最高分获得RANK 1，最低分获得RANK 9。

If two students get the same marks (in our example, ROW numbers 4 and 5), their ranks are also the same.

如果两个学生获得相同的分数（在我们的示例中，行号4和5），则他们的排名也相同。

DENSE_RANK（）SQL RANK函数 (DENSE_RANK() SQL RANK function)

We use DENSE_RANK() function to specify a unique rank number within the partition as per the specified column value. It is similar to the Rank function with a small difference.

我们使用DENSE_RANK（）函数根据指定的列值在分区内指定唯一的等级编号。 它与Rank函数相似，只是差别很小。

In the SQL RANK function DENSE_RANK(), if we have duplicate values, SQL assigns different ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar values.

在SQL RANK函数DENSE_RANK（）中，如果我们有重复的值，则SQL也会为这些行分配不同的等级。 理想情况下，对于重复或相似的值，我们应该获得相同的排名。

Let’s execute the following query with the DENSE_RANK() function.

让我们使用DENSE_RANK（）函数执行以下查询。

SELECT Studentname,        Subject,        Marks,        DENSE_RANK() OVER(ORDER BY Marks DESC) RankFROM ExamResultORDER BY Rank;

In the output, you can see we have the same rank for both Lily and Isabella who scored 70 marks.

在输出中，您可以看到Lily和Isabella得分均达到70分，我们的排名相同。

Let’s use DENSE_RANK function in combination with the SQL PARTITION BY clause.

让我们将DENSE_RANK函数与SQL PARTITION BY子句结合使用。

SELECT Studentname,        Subject,        Marks,        DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Marks DESC) RankFROM ExamResultORDER BY Studentname,          Rank;

We do not have two students with similar marks; therefore result set similar to RANK Function in this case.

我们没有两个学生的成绩相似。 因此，在这种情况下，结果集类似于RANK函数。

Let’s update the student mark with the following query and rerun the query.

让我们使用以下查询更新学生标记，然后重新运行查询。

Update Examresult set Marks=70 where Studentname='Isabella' and Subject='Maths'

We can see that in the student group, Isabella got similar marks in Maths and Science subjects. Rank is also the same for both subjects in this case.

我们可以看到，在学生团体中，伊莎贝拉在数学和自然科学科目中获得了类似的成绩。 在这种情况下，两个科目的排名也相同。

Let’s see the difference between RANK() and DENSE_RANK() SQL Rank function with the following query.

让我们用以下查询查看RANK（）和DENSE_RANK（）SQL Rank函数之间的区别。

• SELECT Studentname,        Subject,        Marks,        RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) RankFROM ExamResultORDER BY Studentname,          Rank;

• SELECT Studentname,        Subject,        Marks,        DENSE_RANK() OVER(PARTITION BY StudentName ORDER BY Marks ) RankFROM ExamResultORDER BY Studentname,          Rank;

In the output, you can see a gap in the rank function output within a partition. We do not have any gap in the DENSE_RANK function.

在输出中，您可以看到分区中的等级函数输出中的间隙。 DENSE_RANK函数没有任何间隙。

In the following screenshot, you can see that Isabella has similar numbers in the two subjects. A rank function assigns rank 1 for similar values however, internally ignores rank two, and the next row gets rank three.

在下面的屏幕截图中，您可以看到Isabella在两个主题中具有相似的数字。 等级函数将等级1分配给相似的值，但是内部忽略等级2，而下一行获得等级3。

In the Dense_Rank function, it maintains the rank and does not give any gap for the values.

在Dense_Rank函数中，它保持等级，并且不给值任何间隙。

NTILE（N）SQL RANK函数 (NTILE(N) SQL RANK function)

We use the NTILE(N) function to distribute the number of rows in the specified (N) number of groups. Each row group gets its rank as per the specified condition. We need to specify the value for the desired number of groups.

我们使用NTILE（N）函数来分配指定（N）个组中的行数。 每个行组均按照指定条件获得其排名。 我们需要为所需的组数指定值。

In my example, we have nine records in the ExamResult table. The NTILE(2) shows that we require a group of two records in the result.

在我的示例中，ExamResult表中有9条记录。 NTILE（2）表明我们需要一组两个记录的结果。

SELECT *,        NTILE(2) OVER(       ORDER BY Marks DESC) RankFROM ExamResultORDER BY rank;

In the output, we can see two groups. Group 1 contains five rows, and Group 2 contains four rows.

在输出中，我们可以看到两组。 组1包含五行，组2包含四行。

Similarly, NTILE(3) divides the number of rows of three groups having three records in each group.

同样，NTILE（3）将每组中具有三个记录的三个组的行数相除。

SELECT *,        NTILE(3) OVER(       ORDER BY Marks DESC) RankFROM ExamResultORDER BY rank;

We can use SQL PARTITION BY clause to have more than one partition. In the following query, each partition on subjects is divided into two groups.

我们可以使用SQL PARTITION BY子句来拥有多个分区。 在以下查询中，主题的每个分区都分为两组。

SELECT *,        NTILE(2) OVER(PARTITION  BY subject ORDER BY Marks DESC) RankFROM ExamResultORDER BY subject, rank;

SQL RANK函数的实际用法 (Practical usage of SQL RANK functions)

We can use SQL RANK function to fetch specific rows from the data. Suppose we want to get the data of the students from ranks 1 to 3. In the following query, we use common table expressions(CTE) to get data using ROW_NUMBER() function and later filtered the result from CTE to satisfy our condition.

我们可以使用SQL RANK函数从数据中获取特定行。 假设我们要获取排名1到3的学生的数据。在下面的查询中，我们使用ROW_NUMBER（）函数使用公用表表达式（CTE）来获取数据，然后从CTE中过滤结果以满足我们的条件。

WITH StudentRanks AS(  SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks  FROM ExamResult) SELECT StudentName , Marks FROM StudentRanksWHERE Ranks >= 1 and Ranks <=3ORDER BY Ranks

We can use the OFFSET FETCH command starting from SQL Server 2012 to fetch a specific number of records.

我们可以使用从SQL Server 2012开始的OFFSET FETCH命令来获取特定数量的记录。

WITH StudentRanks AS(  SELECT *, ROW_NUMBER() OVER( ORDER BY Marks) AS Ranks  FROM ExamResult) SELECT StudentName , Marks FROM StudentRanksORDER BY Ranks OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY;

SQL RANK函数快速摘要 (A quick summary of SQL RANK Functions)

ROW_Number	It assigns the sequential rank number to each unique record.
RANK	It assigns the rank number to each row in a partition. It skips the number for similar values.
Dense_RANK	It assigns the rank number to each row in a partition. It does not skip the number for similar values.
NTILE(N)	It divides the number of rows as per specified partition and assigns unique value in the partition.

ROW_Number	它将顺序等级编号分配给每个唯一记录。
秩	它将等级编号分配给分区中的每一行。 对于相似的值，它将跳过该数字。
Dense_RANK	它将等级编号分配给分区中的每一行。 对于相似的值，它不会跳过该数字。
NTILE（N）	它按指定分区划分行数，并在分区中分配唯一值。

结论 (Conclusion)

In this article, we explored SQL RANK functions and difference between these functions. It is helpful for sql developers to be familiar with these functions to explore and manage their data well. If you have any comments or questions, feel free to leave them in the comments below.

在本文中，我们探讨了SQL RANK函数以及这些函数之间的区别。 对于SQL开发人员来说，熟悉这些功能以很好地浏览和管理其数据很有帮助。 如果您有任何意见或疑问，请随时将其留在下面的评论中。

翻译自:

sql rank