$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. $setWindowFields阶段sortBy字段值确定文档排名。For more information on how MongoDB compares fields with different types, see BSON comparison order.有关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提示
See also: 参阅：

$rank

Behavior行为

$rank and 和$denseRank differ in how they rank duplicate sortBy field values. 它们对重复sortBy字段值的排名方式不同。For example, with sortBy field values of 7, 9, 9, and 10:例如，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. 重复的9的值的排名为2，而10的值的排名为3。There is no gap in the ranks.排名中没有间隙。
  • $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. 重复的9个值的排序为2，10个值的排名为4。There is a gap in the ranks for 3.排名中有一个差距为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.请参阅稠密秩中的重复值、空值和缺失值示例，。

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输出每个州的蛋糕销售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.有用于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的日期来输出每个州的蛋糕销售的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.有用于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重复值、空值和缺失值的密集秩

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阶段中的$denseRank从每个州的cakeSalesWithDuplicates集合输出数量密集排名：

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.有用于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. 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不同（例如，请参阅包含重复值、空值或丢失数据的列分区）。
  • 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, "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 }
←  $degreesToRadians (aggregation)$derivative (aggregation) →