$covarianceSamp (aggregation)

Definition

$covarianceSamp

Returns the sample covariance of two numeric expressions that are evaluated using documents in the $setWindowFields stage window.

$covarianceSamp is only available in the $setWindowFields stage.

$covarianceSamp syntax:

{
   $covarianceSamp:
   [
      <numeric expression 1>,
      <numeric expression 2>
   ]
}

Behavior

$covarianceSamp behavior:

• Ignores non-numeric values, null values, and missing fields in a window.

• If the window contains one document, returns null. (Compare to $covariancePop, which returns 0 if the window contains one document.)

• If the window is empty, returns null.

• If the window contains a NaN value, returns NaN.

• If the window contains one or more Infinity value(s) that are all positive or all negative, returns Infinity. The returned Infinity value has the same sign as the Infinity values in the window.

• If the window contains Infinity values with different signs, returns NaN.

• If the window contains a decimal value, returns a decimal value.

• If none of the previous points apply, returns a double value.

The returned values in order of precedence are as follows:

• NaN

• Infinity

• decimal

• double

Example

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

This example uses $covarianceSamp in the $setWindowFields stage to output the sample covariance values for the cake sales orderDate year and quantity values:

db.cakeSales.aggregate( [
   {
      $setWindowFields: {
         partitionBy: "$state",
         sortBy: { orderDate: 1 },
         output: {
            covarianceSampForState: {
               $covarianceSamp: [ { $year: "$orderDate" }, "$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 sample covariance values for the orderDate year and quantity values using $covarianceSamp run in a documents window.

  The window contains documents between an unbounded lower limit and the current document in the output. This means $covarianceSamp sets the covarianceSampForState field to the sample covariance values for the documents between the beginning of the partition and the current document.

In this output, the sample covariance is shown in the covarianceSampForState field:

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