Database Manual / Reference / Query Language / Expressions

$denseRank (aggregation operator)（聚合运算符）

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).如果多个文档占据相同的排名，$denseRank会将具有后续值的文档放置在下一个排名中，没有任何间隙（请参阅行为）。

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

$denseRank syntax:语法：

{ $denseRank: { } }

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

Tip

$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和$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.将值排序为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. 根据BSON比较顺序，为sortBy字段值为空的文档或缺少sortBy字段的文档分配一个排名。See the example in Rank Partitions Containing Duplicate Values, Nulls, or Missing Data.请参阅包含重复值、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一致。

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):创建一个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 }
] )

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.使用$denseRank将denseRankOrderDateForState字段设置为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.使用$denseRank将denseRankOrderDateForState字段设置为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的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 $denseRank in the $setWindowFields stage to output the quantity dense rank from the cakeSalesWithDuplicates collection for each state:此示例在$setWindowFields阶段使用$denseRank输出每个state的cakeSalesWithDuplicates集合中的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.使用$denseRank将denseRankQuantityForState字段设置为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. This differs from $rank that has a gap between the ranks (for an example, see Rank Partitions Containing Duplicate Values, Nulls, or Missing Data).quantity和state相同的文件具有相同的等级，等级之间没有差距。这与$rank不同，$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.在CA分区的输出中，quantity为null的文档和quantity缺失的文档排名最低。此排序是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 }