$denseRank (aggregation)

項目一覧

定義
動作
例

定義

バージョン 5.0 で追加

$denseRank

$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.

If multiple documents occupy the same rank, $denseRank places the document with the subsequent value at the next rank without any gaps (see 動作).

$denseRankは$setWindowFieldsステージでのみ使用可能です。

$denseRank構文:

{ $denseRank: { } }

$denseRankはパラメータを受け入れません。

以下も参照してください。

$rank

動作

$rank and $denseRank differ in how they rank duplicate sortBy field values. For example, with sortBy field values of 7, 9, 9, and 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.
- $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.
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 重複値、null、または欠損データを含むランクパーティション.
Starting in MongoDB 8.0, null and missing field values in $denseRank and $rank sortBy operations are treated the same when calculating rankings. This change makes the behavior of denseRank and rank consistent with $sort.

See the example in Dense Rank for Duplicate, Null, and Missing Values.

例

Dense Rank Partitions by an Integer Field

カリフォルニア州（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:

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

この例では、次のことが行われます。

partitionBy: "$state" はコレクション内のドキュメントを state でパーティショニングします。CA と WA 用のパーティションがあります。
sortBy: { quantity: -1 }は各パーティション内のドキュメントをquantityで降順（ -1 ）にソートするため、最も高いquantityが先頭になります。

output sets the denseRankOrderDateForState field to the orderDate dense rank using $denseRank, as shown in the following results.

{ "_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:

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

この例では、次のことが行われます。

partitionBy: "$state" はコレクション内のドキュメントを state でパーティショニングします。CA と WA 用のパーティションがあります。
sortBy: { orderDate: 1 } は、各パーティション内のドキュメントを orderDate を基準に昇順（1）にソートするため、最も古い orderDate が最初になります。

output sets the denseRankOrderDateForState field to the orderDate rank using $denseRank, as shown in the following results.

{ "_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

以下の条件に一致するcakeSalesWithDuplicatesコレクションを作成します。

ケーキ販売はカリフォルニア州（ CA ）とワシントン州（ WA ）に配置されます。
ドキュメント 6 から 8 のquantityとstateはドキュメント 5 と同じです。
ドキュメント 9 のquantityとstateはドキュメント 4 と同じです。
ドキュメント 10 にはnull 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:

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

この例では、次のことが行われます。

partitionBy: "$state" はコレクション内のドキュメントを state でパーティショニングします。CA と WA 用のパーティションがあります。
sortBy: { quantity: -1 }は各パーティション内のドキュメントをquantityで降順（ -1 ）にソートするため、最も高いquantityが先頭になります。

output sets the denseRankQuantityForState field to the quantity dense rank using $denseRank.

次の出力例では、次のようになります。

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 重複値、null、または欠損データを含むランクパーティション).
null quantityを持つドキュメントと、次に欠落している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 }

戻る

$degreesToRadians

$derivative