Docs Home → MongoDB Manual

$denseRank (aggregation)

On this page本页内容

Definition定义

New in version 5.0. 5.0版新增。

$denseRank

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

The $setWindowFields stage sortBy field value determines the document rank. For more information on how MongoDB compares fields with different types, see BSON comparison order.$setWindowFields阶段sortBy字段值决定文档的级别。有关MongoDB如何比较不同类型的字段的更多信息，请参阅BSON比较顺序。

If multiple documents occupy the same rank, $denseRank places the document with the subsequent value at the next rank without any gaps (see Behavior).如果多个文档占用同一排名，$denseBank会将具有后续值的文档放置在下一排名中，没有任何间隙（请参见行为）。

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

$denseRank syntax:语法：

{ $denseRank: { } }

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

Tip

See also: 另请参阅：

$rank

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和$denseBank对重复的sortBy字段值进行排名的方式不同。例如，如果sortBy字段值为7、9、9和10：

  • $denseRank ranks the values as 1, 2, 2, and 3. 将值排名为1、2、2和3。The duplicate 9 values have a rank of 2, and 10 has a rank of 3. There is no gap in the ranks.重复的两个值9的排名为2，而10的排名为3。排名没有间隙。
  • $rank ranks the values as 1, 2, 2, and 4. 将值排名为1、2、2和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.重复的两个值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.sortBy字段为null值的文档或缺少sortBy字段的文档将根据BSON比较顺序分配一个排名。

See the example in Dense Rank for Duplicate, Null, and Missing Values.请参阅密集排名中的重复值、Null值和缺失值的示例。

Examples实例

Dense Rank Partitions by an Integer Field整数字段的密集排名分区

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

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 }
] )

This example uses $denseRank in the $setWindowFields stage to output the quantity dense rank of the cake sales for each state:本例使用$setWindowFields阶段中的$denseRank来输出每个state蛋糕销售的quantity密集排名：

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

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 denseRankOrderDateForState field to the orderDate dense rank using $denseRank, as shown in the following results.使用$denseBank将denseBankOrderDateForState字段设置为orderDate密集排名，如以下结果所示。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "denseRankQuantityForState" : 1 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "denseRankQuantityForState" : 2 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "denseRankQuantityForState" : 3 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "denseRankQuantityForState" : 1 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "denseRankQuantityForState" : 2 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "denseRankQuantityForState" : 3 }

Dense Rank Partitions by a Date Field按日期字段分区的密集排名

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

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

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 denseRankOrderDateForState field to the orderDate rank using $denseRank, as shown in the following results.使用$denseBank将denseBankOrderDateForState字段设置为orderDate排名，如以下结果所示。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "denseRankOrderDateForState" : 1 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "denseRankOrderDateForState" : 2 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "denseRankOrderDateForState" : 3 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "denseRankOrderDateForState" : 1 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "denseRankOrderDateForState" : 2 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "denseRankOrderDateForState" : 3 }

Dense Rank for Duplicate, Null, and Missing Values重复值、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具有与文档5相同的quantity和state。
  • Document 9 has the same quantity and state as document 4.文档9具有与文档4相同的quantity和state。
  • 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 $denseRank in the $setWindowFields stage to output the quantity dense rank from the cakeSalesWithDuplicates collection for each state:本例使用$setWindowFields阶段中的$denseBank从cakeSalesWithDuplicates集合中为每个state输出quantity密集排名：

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

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 denseRankQuantityForState field to the quantity dense rank using $denseRank.使用$denseBank将denseBankQuantityForState字段设置为quantity密集排名。

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

  • The documents with the same quantity and state have the same rank and there is no gap between the ranks. quantity和state相同的文件具有相同的级别，级别之间没有差距。This differs from $rank that has a gap between the ranks (for an example, see Rank Partitions Containing Duplicate Values, Nulls, or Missing Data).这与排名之间有间隙的$rank不同（例如，请参阅包含重复值、null或缺少数据的排名分区）。
  • 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. This sorting is the result of the BSON comparison order, which sorts null and missing values after number values in this example.quantity为null的文档和quantity缺失的文档在CA分区的输出中排名最低。这种排序是BSON比较顺序的结果，在本例中，它将null和缺失值排序在数值之后。
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "denseRankQuantityForState" : 1 }
{ "_id" : 9, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "quantity" : 162, "denseRankQuantityForState" : 1 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "denseRankQuantityForState" : 2 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "denseRankQuantityForState" : 3 }
{ "_id" : 10, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "quantity" : null, "denseRankQuantityForState" : 4 }
{ "_id" : 11, "type" : "strawberry", "orderDate" : ISODate("2020-05-11T16:09:01Z"),
  "state" : "CA", "price" : 39, "denseRankQuantityForState" : 5 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "denseRankQuantityForState" : 1 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "denseRankQuantityForState" : 2 }
{ "_id" : 6, "type" : "strawberry", "orderDate" : ISODate("2020-01-08T06:12:03Z"),
  "state" : "WA", "price" : 41, "quantity" : 134, "denseRankQuantityForState" : 2 }
{ "_id" : 7, "type" : "strawberry", "orderDate" : ISODate("2020-01-01T06:12:03Z"),
  "state" : "WA", "price" : 34, "quantity" : 134, "denseRankQuantityForState" : 2 }
{ "_id" : 8, "type" : "strawberry", "orderDate" : ISODate("2020-01-02T06:12:03Z"),
  "state" : "WA", "price" : 40, "quantity" : 134, "denseRankQuantityForState" : 2 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "denseRankQuantityForState" : 3 }