Database Manual / Reference / Query Language / Aggregation Stages

`$setWindowFields` (aggregation stage)（聚合阶段）

Definition定义

$setWindowFields

New in version 5.0.5.0版本中的新功能。

~~Performs operations on a specified span of documents in a collection, known as a window, and returns the results based on the chosen window operator.~~对集合（称为窗口）中指定范围的文档执行操作，并根据所选的窗口运算符返回结果。

~~For example, you can use the $setWindowFields stage to output the:~~例如，您可以使用$setWindowFields阶段输出：

~~Difference in sales between two documents in a collection.~~收款中两份单据之间的销售额差异。
~~Sales rankings.~~销售排名。
~~Cumulative sales totals.~~累计销售总额。
~~Analysis of complex time series information without exporting the data to an external database.~~分析复杂的时间序列信息，而无需将数据导出到外部数据库。

Syntax语法

~~The $setWindowFields stage syntax:~~$setWindowFields阶段语法：

{
   $setWindowFields: {
      partitionBy: <expression>,
      sortBy: {
         <sort field 1>: <sort order>,
         <sort field 2>: <sort order>,
         ...,
         <sort field n>: <sort order>
      },
      output: {
         <output field 1>: {
            <window operator>: <window operator parameters>,
            window: {
               documents: [ <lower boundary>, <upper boundary> ],
               range: [ <lower boundary>, <upper boundary> ],
               unit: <time unit>
            }
         },
         <output field 2>: { ... },
         ...
         <output field n>: { ... }
      }
   }
}

The $setWindowFields stage takes a document with these fields:

~~Field~~字段	~~Necessity~~必要性	~~Description~~描述
partitionBy	Optional	Specifies an expression to group the documents. In the `$setWindowFields` stage, the group of documents is known as a partition. Default is one partition for the entire collection.
sortBy	Required for some operators (see Restrictions)	Specifies the field(s) to sort the documents by in the partition. Uses the same syntax as the `$sort` stage. Default is no sorting.
output	Required	Specifies the field(s) to append to the documents in the output returned by the `$setWindowFields` stage. Each field is set to the result returned by the window operator. A field can contain dots to specify embedded document fields and array fields. The semantics for the embedded document dotted notation in the `$setWindowFields` stage are the same as the `$addFields` and `$set` stages. See embedded document $addFields example and embedded document $set example. The `window operator` is the window operator name to use in the `$setWindowFields` stage. The `window operator parameters` are the parameters to pass to the window operator.
window	Optional	Specifies the window boundaries and parameters. Window boundaries are inclusive. Default is an unbounded window, which includes all documents in the partition. Specify either a documents or range window.
documents	Optional	A window where the lower and upper boundaries are specified relative to the position of the current document read from the collection. The window boundaries are specified using a two element array containing a lower and upper limit string or integer. Use: The `"current"` string for the current document position in the output. The `"unbounded"` string for the first or last document position in the partition. An integer for a position relative to the current document. Use a negative integer for a position before the current document. Use a positive integer for a position after the current document. `0` is the current document position. See Documents Window Examples.
range	Optional	A window where the lower and upper boundaries are defined using a range of values based on the sortBy field in the current document. ~~The window boundaries are specified using a two element array containing a lower and upper limit string or number. Use:~~使用包含下限和上限字符串或数字的两元素数组指定窗口边界。用途： The `"current"` string for the current document position in the output. The `"unbounded"` string for the first or last document position in the partition. A number to add to the value of the sortBy field for the current document. A document is in the window if the sortBy field value is inclusively within the lower and upper boundaries. See Range Window Example.
unit	Optional	Specifies the units for time range window boundaries. Can be set to one of these strings: `"year"` `"quarter"` `"month"` `"week"` `"day"` `"hour"` `"minute"` `"second"` `"millisecond"` If omitted, default numeric range window boundaries are used. See Time Range Window Examples.

Tip

Examples

Behavior行为

The $setWindowFields stage appends new fields to existing documents. You can include one or more $setWindowFields stages in an aggregation operation.

Starting in MongoDB 5.3, you can use the $setWindowFields stage with transactions and the "snapshot" read concern.

The $setWindowFields stage doesn't guarantee the order of the returned documents.

Window Operators

These operators can be used with the $setWindowFields stage:

Accumulator operators: $addToSet, $avg, $bottom, $bottomN, $concatArrays, $count, $covariancePop, $covarianceSamp, $derivative, $expMovingAvg, $firstN, $integral, $lastN, $max, $maxN, $median, $min, $minN, $percentile, $push, $setUnion, $stdDevSamp, $stdDevPop, $sum, $top, $topN.

Gap filling operators: $linearFill and $locf.

Order operators: $first, $last, and $shift.

Rank operators: $denseRank, $documentNumber, and $rank.
Range operator: $minMaxScaler

Restrictions

Restrictions for the $setWindowFields stage:

Prior to MongoDB 5.3, the $setWindowFields stage cannot be used:
- Within transactions.
- With "snapshot" read concern.
sortBy is required for:
- Rank and order window operators.
- Bounded windows (either a documents window or a range window).
- $linearFill operator.
Range windows require all sortBy values to be numbers.
Time range windows require all sortBy values to be dates.
Range and time range windows can only contain one sortBy field and the sort must be ascending.
You cannot specify both a documents window and a range window.
These operators use an implicit window and return an error if you specify a window option:
- Rank operators.
- $shift operator.
For range windows, only numbers in the specified range are included in the window. Missing, undefined, and null values are excluded.
For time range windows:
- ~~Only date and time types are included in the window.~~窗口中仅包含日期和时间类型。
- Numeric boundary values must be integers. For example, you can use 2 hours as a boundary but you cannot use 1.5 hours.
For empty windows or windows with incompatible values (for example, using $sum on strings), the returned value depends on the operator:
- For $count and $sum, the returned value is 0.
- For $addToSet and $push, the returned value is an empty array.
- For all other operators, the returned value is null.

Examples

Create a cakeSales collection that contains cake sales in the states of California (CA) and Washington (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 }
] )

The following examples use the cakeSales collection.

Documents Window Examples

Use Documents Window to Obtain Cumulative Quantity for Each State使用文档窗口获取每个州的累计数量

This example uses a documents window in $setWindowFields to output the cumulative cake sales quantity for each state:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            cumulativeQuantityForState: {
               $sum: "$quantity",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.

output:
- Sets the cumulativeQuantityForState field to the cumulative quantity for each state, which increases by successive additions to the previous value in the partition.
- Calculates the cumulative quantity using the $sum operator run in a documents window.
  The window contains documents between an unbounded lower limit and the current document. This means $sum returns the cumulative quantity for the documents between the beginning of the partition and the current document.

In this example output, the cumulative quantity for CA and WA is shown in the cumulativeQuantityForState field:

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

Use Documents Window to Obtain Cumulative Quantity for Each Year

This example uses a documents window in $setWindowFields to output the cumulative cake sales quantity for each $year in orderDate:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: { $year: "$orderDate" },
         sortBy: { orderDate: 1 },
         output: {
            cumulativeQuantityForYear: {
               $sum: "$quantity",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: { $year: "$orderDate" } partitions the documents in the collection by $year in orderDate. There are are partitions for 2019, 2020, and 2021.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.
output:
- Sets the cumulativeQuantityForYear field to the cumulative quantity for each year, which increases by successive additions to the previous value in the partition.
- Calculates the cumulative quantity using the $sum operator run in a documents window.
  The window contains documents between an unbounded lower limit and the current document. This means $sum returns the cumulative quantity for the documents between the beginning of the partition and the current document.

In this example output, the cumulative quantity for each year is shown in the cumulativeQuantityForYear field:

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

Use Documents Window to Obtain Moving Average Quantity for Each Year

This example uses a documents window in $setWindowFields to output the moving average for the cake sales quantity:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: { $year: "$orderDate" },
         sortBy: { orderDate: 1 },
         output: {
            averageQuantity: {
               $avg: "$quantity",
               window: {
                  documents: [ -1, 0 ]
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$orderDate" partitions the documents in the collection by $year in orderDate. There are are partitions for 2019, 2020, and 2021.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.
output:
- Sets the averageQuantity field to the moving average quantity for each year.
- Calculates the moving average quantity using the $avg operator run in a documents window.
  The window contains documents between -1 and 0. This means $avg returns the moving average quantity between the document before the current document (-1) and the current document (0) in the partition.

In this example output, the moving average quantity is shown in the averageQuantity field:

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

Use Documents Window to Obtain Cumulative and Maximum Quantity for Each Year

This example uses a documents window in $setWindowFields to output the cumulative and maximum cake sales quantity values for each $year in orderDate:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: { $year: "$orderDate" },
         sortBy: { orderDate: 1 },
         output: {
            cumulativeQuantityForYear: {
               $sum: "$quantity",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            },
            maximumQuantityForYear: {
               $max: "$quantity",
               window: {
                  documents: [ "unbounded", "unbounded" ]
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$orderDate" partitions the documents in the collection by $year in orderDate. There are are partitions for 2019, 2020, and 2021.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.
output:
- Sets the cumulativeQuantityForYear field to the cumulative quantity for each year.
- Calculates the cumulative quantity using the $sum operator run in a documents window.
  The window contains documents between an unbounded lower limit and the current document. This means $sum returns the cumulative quantity for the documents between the beginning of the partition and the current document.
- Sets the maximumQuantityForYear field to the maximum quantity for each year.
- Calculates the maximum quantity of all the documents using the $max operator run in a documents window.
  The window contains documents between an unbounded lower and upper limit. This means $max returns the maximum quantity for the documents in the partition.

In this example output, the cumulative quantity is shown in the cumulativeQuantityForYear field and the maximum quantity is shown in the maximumQuantityForYear field:

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

Range Window Example

This example uses a range window in $setWindowFields to return the sum of the quantity values of cakes sold for orders within plus or minus 10 dollars of the current document's price value:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { price: 1 },
         output: {
            quantityFromSimilarOrders: {
               $sum: "$quantity",
               window: {
                  range: [ -10, 10 ]
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.
sortBy: { price: 1 } sorts the documents in each partition by price in ascending order (1), so the lowest price is first.
output sets the quantityFromSimilarOrders field to the sum of the quantity values from the documents in a range window.
- The window contains documents between a lower limit of -10 and an upper limit of 10. The range is inclusive.
- $sum returns the sum of quantity values contained in a range of plus or minus 10 dollars of the current document's price value.

In this example output, the sum of the quantity values for documents in the window is shown in the quantityFromSimilarOrders field:

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

Time Range Window Examples

Use a Time Range Window with a Positive Upper Bound

The following example uses a window with a positive upper bound time range unit in $setWindowFields. The pipeline outputs an array of orderDate values for each state that match the specified time range.

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            recentOrders: {
               $push: "$orderDate",
               window: {
                  range: [ "unbounded", 10 ],
                  unit: "month"
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.
output:
- Sets the recentOrders field to an array of orderDate values for the documents in each state. The array elements are expanded with additions to the previous elements in the array.
- Uses $push to return an array of orderDate values from the documents in a range window.

The window contains documents between an unbounded lower limit and an upper limit set to 10 (10 months after the current document's orderDate value) using a time range unit.
$push returns the array of orderDate values for the documents between the beginning of the partition and the documents with orderDate values inclusively in a range of the current document's orderDate value plus 10 months.

In this example output, the array of orderDate values for CA and WA is shown in the recentOrders field:

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

Use a Time Range Window with a Negative Upper Bound

The following example uses a window with a negative upper bound time range unit in $setWindowFields. The pipeline outputs an array of orderDate values for each state that match the specified time range.

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            recentOrders: {
               $push: "$orderDate",
               window: {
                  range: [ "unbounded", -10 ],
                  unit: "month"
               }
            }
         }
      }
   }
] )

In the example:

partitionBy: "$state" partitions the documents in the collection by state. There are partitions for CA and WA.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.
output:
- Sets the recentOrders field to an array of orderDate values for the documents in each state. The array elements are expanded with additions to the previous elements in the array.
- Uses $push to return an array of orderDate values from the documents in a range window.

The window contains documents between an unbounded lower limit and an upper limit set to -10 (10 months before the current document's orderDate value) using a time range unit.
$push returns the array of orderDate values for the documents between the beginning of the partition and the documents with orderDate values inclusively in a range of the current document's orderDate value minus 10 months.

In this example output, the array of orderDate values for CA and WA is shown in the recentOrders field:

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

Comparison with Previous Values Example

The following example uses $setWindowFields with the $shift operator and the $set stage to compare fields in a collection.

db.cakeSales.aggregate( [
  {
    $setWindowFields: {
      partitionBy: "$type",
      sortBy: { orderDate: 1 },
      output: {
        previousPrice: {
          $shift: {
            output: "$price",
            by: -1
          }
        }
      }
    }
  },
  {
    $set: {
      priceComparison: {
        $cond: [
          { $eq: ["$price", "$previousPrice"] },
          "same",
          {
            $cond: [
              { $gt: ["$price", "$previousPrice"] },
              "higher",
              "lower"
            ]
          }
        ]
      }
    }
  },
] )

In the example:

partitionBy: "$type" partitions the documents in the collection by type so each type's calculations are kept separate.
sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1).
output.previousPrice uses the $shift operator to capture the price from the immediately preceding order within the same year.
$set adds a new field called priceComparison, and sets that field's value to higher, lower, or same based on the comparison to previousPrice.

In this example output, the comparison result is shown in the priceComparison field:

[
  {
    _id: 0,
    type: 'chocolate',
    orderDate: ISODate('2020-05-18T14:10:30.000Z'),
    state: 'CA',
    price: 13,
    quantity: 120,
    previousPrice: null,
    priceComparison: 'higher'
  },
  {
    _id: 1,
    type: 'chocolate',
    orderDate: ISODate('2021-03-20T11:30:05.000Z'),
    state: 'WA',
    price: 14,
    quantity: 140,
    previousPrice: 13,
    priceComparison: 'higher'
  },
  {
    _id: 5,
    type: 'strawberry',
    orderDate: ISODate('2019-01-08T06:12:03.000Z'),
    state: 'WA',
    price: 43,
    quantity: 134,
    previousPrice: null,
    priceComparison: 'higher'
  },
  {
    _id: 4,
    type: 'strawberry',
    orderDate: ISODate('2019-05-18T16:09:01.000Z'),
    state: 'CA',
    price: 41,
    quantity: 162,
    previousPrice: 43,
    priceComparison: 'lower'
  },
  {
    _id: 3,
    type: 'vanilla',
    orderDate: ISODate('2020-02-08T13:13:23.000Z'),
    state: 'WA',
    price: 13,
    quantity: 104,
    previousPrice: null,
    priceComparison: 'higher'
  },
  {
    _id: 2,
    type: 'vanilla',
    orderDate: ISODate('2021-01-11T06:31:15.000Z'),
    state: 'CA',
    price: 12,
    quantity: 145,
    previousPrice: 13,
    priceComparison: 'lower'
  }
]

The following WeatherMeasurement class represents documents in a collection of weather measurements:

public class WeatherMeasurement
{
    public Guid Id { get; set; }
    
    public string LocalityId { get; set; }
    
    public DateTime MeasurementDateTime { get; set; }
  
    public float Rainfall { get; set; }
    
    public float Temperature { get; set; }
}

To use the MongoDB .NET/C# driver to add a $setWindowFields stage to an aggregation pipeline, call the SetWindowFields() method on a PipelineDefinition object.

The following example creates a pipeline stage that uses the Rainfall and Temperature fields to compute the accumulated rainfall and a moving average temperature over the past month for each locality:

var pipeline = new EmptyPipelineDefinition<WeatherMeasurement>()
    .SetWindowFields(
        partitionBy: w => w.LocalityId,
        sortBy: Builders<WeatherMeasurement>.Sort.Ascending(
            w => w.MeasurementDateTime),
        output: o => new
        {
            MonthlyRainfall = o.Sum(
                w => w.Rainfall, RangeWindow.Create(
                    RangeWindow.Months(-1),
                    RangeWindow.Current)
            ),
            TemperatureAvg = o.Average(
                w => w.Temperature, RangeWindow.Create(
                    RangeWindow.Months(-1),
                    RangeWindow.Current)
            )
        }
    );

The Node.js examples on this page use the sample_weatherdata.data collection from the Atlas sample datasets. To learn how to create a free MongoDB Atlas cluster and load the sample datasets, see Get Started in the MongoDB Node.js driver documentation.

To use the MongoDB Node.js driver to add a $setWindowFields stage to an aggregation pipeline, use the $setWindowFields operator in a pipeline object.

The following example creates a pipeline stage that calculates the average airTemperature.value and the total waveMeasurement.waves.height for each unique value of callLetters over the past month. The example then runs the aggregation pipeline:

const pipeline = [
  {
    $setWindowFields: {
      partitionBy: "$callLetters",
      sortBy: { ts: 1 },
      output: {
        temperatureAvg: {
          $avg: "$airTemperature.value",
          window: {
            range: [-1, "current"],
            unit: "month"
          }
        },
        totalWaveHeight: {
          $sum: "$waveMeasurement.waves.height",
          window: {
            range: [-1, "current"],
            unit: "month"
          }
        }
      }
    }
  },
];

const cursor = collection.aggregate(pipeline);
return cursor;

Tip

For an additional example about IOT Power Consumption, see the Practical MongoDB Aggregations e-book.

Back

$set

$shardedDataDistribution

$setWindowFields (aggregation stage)（聚合阶段）

Definition定义

Syntax语法

Tip

Behavior行为

Window Operators

Restrictions

Examples

Documents Window Examples

Use Documents Window to Obtain Cumulative Quantity for Each State使用文档窗口获取每个州的累计数量

Use Documents Window to Obtain Cumulative Quantity for Each Year

Use Documents Window to Obtain Moving Average Quantity for Each Year

Use Documents Window to Obtain Cumulative and Maximum Quantity for Each Year

Range Window Example

Time Range Window Examples

Use a Time Range Window with a Positive Upper Bound

Use a Time Range Window with a Negative Upper Bound

Comparison with Previous Values Example

Tip

`$setWindowFields` (aggregation stage)（聚合阶段）