Database Manual / Reference / Query Language / Expressions

$rank (Window Function)（窗口函数）

Definition定义

New in version 5.0.在版本5.0中新增。

$rank

Returns the document position (known as the rank) relative to other documents in the $setWindowFields stage partition.返回文档相对于$setWindowFields阶段分区中其他文档的位置（称为排名）。

The sortBy field value in the $setWindowFields stage determines the document rank. $setWindowFields阶段中的sortBy字段值决定了文档排名。When used with the $rank operator, sortBy can only take one field as its value. For more information on how MongoDB compares fields with different types, see BSON comparison order.当与$rank运算符一起使用时，sortBy只能接受一个字段作为其值。有关MongoDB如何比较不同类型字段的更多信息，请参阅BSON比较顺序。

If multiple documents occupy the same rank, $rank places the document with the subsequent value at a rank with a gap (see Behavior).如果多个文档占据相同的排名，$rank会将具有后续值的文档放置在一个有间隙的排名中（请参阅行为）。

$rank is only available in the $setWindowFields stage.$rank仅在$setWindowFields阶段可用。

$rank syntax:语法：

{ $rank: { } }

$rank does not accept any parameters.不接受任何参数。

Tip

$denseRank

Behavior行为

  • $rank and $denseRank differ in how they rank duplicate sortBy field values. For example, with sortBy field values of 7, 9, 9, and 10:$rank和$denseRank在对重复的sortBy字段值进行排名方面有所不同。例如，当sortBy字段值为7、9、9和10时：

    • $denseRank ranks the values as 1, 2, 2, and 3. The duplicate 9 values have a rank of 2, and 10 has a rank of 3. There is no gap in the ranks.$denseRank将值排列为1、2、2和3。重复的9个值的排名为2，10的排名为3。队伍中没有差距。
    • $rank ranks the values as 1, 2, 2, and 4. The duplicate 9 values have a rank of 2, and 10 has a rank of 4. There is a gap in the ranks for 3.将值排序为1、2、2和4。重复的9个值的排名为2，10的排名为4。队伍中有3人的差距。
  • Documents with a null value for a sortBy field or documents missing the sortBy field are assigned a rank based on the BSON comparison order. See the example in Rank Partitions Containing Duplicate Values, Nulls, or Missing Data.根据BSON比较顺序，为sortBy字段值为空的文档或缺少sortBy字段的文档分配一个排名。请参阅包含重复值、null值或缺失数据的排名分区中的示例。
  • Starting in MongoDB 8.0, null and missing field values in $denseRank and $rank sortBy operations are treated the same when calculating rankings. 从MongoDB 8.0开始，在计算排名时，$denseRank和$rank sortBy操作中的null和缺失字段值被视为相同。This change makes the behavior of denseRank and rank consistent with $sort.此更改使denseRank和rank的行为与$sort一致。

Examples示例

Create a cakeSales collection that contains cake sales in the states of California (CA) and Washington (WA):创建一个cakeSales集合，其中包含加利福尼亚州（CA）和华盛顿州（WA）的蛋糕销售：

db.cakeSales.insertMany( [
   { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"),
     state: "CA", price: 13, quantity: 120 },
   { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"),
     state: "WA", price: 14, quantity: 140 },
   { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"),
     state: "CA", price: 12, quantity: 145 },
   { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"),
     state: "WA", price: 13, quantity: 104 },
   { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"),
     state: "CA", price: 41, quantity: 162 },
   { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"),
     state: "WA", price: 43, quantity: 134 }
] )

Rank Partitions by an Integer Field按整数字段进行排名划分

This example uses $rank in the $setWindowFields stage to output the quantity rank of the cake sales for each state:此示例在$setWindowFields阶段使用$rank输出每个state的蛋糕销售quantity排名：

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { quantity: -1 },
         output: {
            rankQuantityForState: {
               $rank: {}
            }
         }
      }
   }
] )

In the example:在示例中：

  • partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.按state对集合中的文档进行分区。CA和WA有分区。
  • sortBy: { quantity: -1 } sorts the documents in each partition by quantity in descending order (-1), so the highest quantity is first.按quantity降序（-1）对每个分区中的文档进行排序，因此quantity最多的文档位居前列。
  • output sets the rankQuantityForState field to the quantity rank using $rank, as shown in the following results.使用$rank将rankQuantityForState字段设置为quantity排名，如以下结果所示。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "rankQuantityForState" : 1 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "rankQuantityForState" : 2 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "rankQuantityForState" : 3 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "rankQuantityForState" : 1 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "rankQuantityForState" : 2 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "rankQuantityForState" : 3 }

Rank Partitions by a Date Field按日期字段对分区进行排名

This example shows how to use dates with $rank in the $setWindowFields stage to output the orderDate rank of the cake sales for each state:此示例显示了如何在$setWindowFields阶段使用带有$rank的日期来输出每个state蛋糕销售的orderDate排名：

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            rankOrderDateForState: {
               $rank: {}
            }
         }
      }
   }
] )

In the example:在示例中：

  • partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.按state对集合中的文档进行分区。CA和WA有分区。
  • sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.按orderDate升序（1）对每个分区中的文档进行排序，因此最早的orderDate位居前列。
  • output sets the rankOrderDateForState field to the orderDate rank using $rank, as shown in the following results.使用$rank将rankOrderDateForState字段设置为orderDate排名，如以下结果所示。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "rankOrderDateForState" : 1 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "rankOrderDateForState" : 2 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "rankOrderDateForState" : 3 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "rankOrderDateForState" : 1 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "rankOrderDateForState" : 2 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "rankOrderDateForState" : 3 }

Rank Partitions Containing Duplicate Values, Nulls, or Missing Data包含重复值、null值或缺失数据的排名分区

Create a cakeSalesWithDuplicates collection where:创建cakeSalesWithDuplicates集合，其中：

  • Cake sales are placed in the state of California (CA) and Washington (WA).蛋糕销售位于加利福尼亚州（CA）和华盛顿州（WA）。
  • Documents 6 to 8 have the same quantity and state as document 5.文件6至8的quantity和state与文件5相同。
  • Document 9 has the same quantity and state as document 4.文件9的quantity和state与文件4相同。
  • Document 10 has a null quantity.文档10的quantity为null。
  • Document 11 is missing the quantity.文件11缺少quantity。
db.cakeSalesWithDuplicates.insertMany( [
   { _id: 0, type: "chocolate", orderDate: new Date("2020-05-18T14:10:30Z"),
     state: "CA", price: 13, quantity: 120 },
   { _id: 1, type: "chocolate", orderDate: new Date("2021-03-20T11:30:05Z"),
     state: "WA", price: 14, quantity: 140 },
   { _id: 2, type: "vanilla", orderDate: new Date("2021-01-11T06:31:15Z"),
     state: "CA", price: 12, quantity: 145 },
   { _id: 3, type: "vanilla", orderDate: new Date("2020-02-08T13:13:23Z"),
     state: "WA", price: 13, quantity: 104 },
   { _id: 4, type: "strawberry", orderDate: new Date("2019-05-18T16:09:01Z"),
     state: "CA", price: 41, quantity: 162 },
   { _id: 5, type: "strawberry", orderDate: new Date("2019-01-08T06:12:03Z"),
     state: "WA", price: 43, quantity: 134 },
   { _id: 6, type: "strawberry", orderDate: new Date("2020-01-08T06:12:03Z"),
     state: "WA", price: 41, quantity: 134 },
   { _id: 7, type: "strawberry", orderDate: new Date("2020-01-01T06:12:03Z"),
     state: "WA", price: 34, quantity: 134 },
   { _id: 8, type: "strawberry", orderDate: new Date("2020-01-02T06:12:03Z"),
     state: "WA", price: 40, quantity: 134 },
   { _id: 9, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"),
     state: "CA", price: 39, quantity: 162 },
   { _id: 10, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"),
     state: "CA", price: 39, quantity: null },
   { _id: 11, type: "strawberry", orderDate: new Date("2020-05-11T16:09:01Z"),
     state: "CA", price: 39 }
] )

This example uses $rank in the $setWindowFields stage to output the quantity rank from the cakeSalesWithDuplicates collection for each state:此示例在$setWindowFields阶段使用$rank输出每个州的cakeSalesWithDuplicates集合中的quantity排名：

db.cakeSalesWithDuplicates.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { quantity: -1 },
         output: {
            rankQuantityForState: {
               $rank: {}
            }
         }
      }
   }
] )

In the example:在示例中：

  • partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.按state对集合中的文档进行分区。CA和WA有分区。
  • sortBy: { quantity: -1 } sorts the documents in each partition by quantity in descending order (-1), so the highest quantity is first.按quantity降序（-1）对每个分区中的文档进行排序，因此quantity最多的文档位居前列。
  • output sets the rankOrderDateForState field to the quantity rank using $rank.output使用$rank将rankOrderDateForState字段设置为quantity排名。

In the following example output:在以下示例输出中：

  • Documents with the same quantity and state have the same rank. If documents have the same rank, there is a gap between that rank and the next rank.quantity和state相同的文件具有相同的排名。如果文档具有相同的排名，则该排名与下一个排名之间存在差距。
  • The document with the null quantity and then the document with the missing quantity are ranked the lowest in the output for the CA partition. 在CA分区的输出中，quantity为null的文档和quantity缺失的文档排名最低。This sorting is the result of the BSON comparison order, which sorts null and missing values after number values in this example.此排序是BSON比较顺序的结果，在本例中，它将null值和缺失值排序在数值之后。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "rankQuantityForState" : 1 }
{ "_id" : 9, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "quantity" : 162, "rankQuantityForState" : 1 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "rankQuantityForState" : 3 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "rankQuantityForState" : 4 }
{ "_id" : 10, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "quantity" : null, "rankQuantityForState" : 5 }
{ "_id" : 11, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "rankQuantityForState" : 5 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "rankQuantityForState" : 1 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "rankQuantityForState" : 2 }
{ "_id" : 6, "type" : "strawberry", "orderDate" : ISODate("2020-01-08T06:12:03Z"),
  "state" : "WA", "price" : 41, "quantity" : 134, "rankQuantityForState" : 2 }
{ "_id" : 7, "type" : "strawberry", "orderDate" : ISODate("2020-01-01T06:12:03Z"),
  "state" : "WA", "price" : 34, "quantity" : 134, "rankQuantityForState" : 2 }
{ "_id" : 8, "type" : "strawberry", "orderDate" : ISODate("2020-01-02T06:12:03Z"),
  "state" : "WA", "price" : 40, "quantity" : 134, "rankQuantityForState" : 2 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "rankQuantityForState" : 6 }