/ /

$stdDevSamp (window function)

Definition

$stdDevSamp

Returns the sample standard deviation of values in a window.

Note

Disambiguation

This page describes $stdDevSamp when used as a window function.

You can also use $stdDevSamp in these other contexts:

$stdDevSamp (accumulator), which calculates the sample standard deviation of values across a group of documents.
$stdDevSamp (expression), which calculates the sample standard deviation of numeric values in an array.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $stdDevSamp: <expression>
         }
      }
   }
}

Behavior

Result Type

$stdDevSamp returns the sample standard deviation of the input values as a double.

Non-Numeric and Missing Values

The $stdDevSamp window function ignores non-numeric values, including missing values. If the window contains no numeric values, $stdDevSamp returns null.

Single Value

If the window contains only one document, $stdDevSamp returns null.

Example

The examples on this page use data from the sample_mflix dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the Sample Dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

This example uses $setWindowFields to calculate a running sample standard deviation field for each Musical movie from 2009 and 2010. Each document receives the $stdDevSamp value computed from the beginning of its yearly partition through the current document.

db.movies.aggregate( [
   {
      $match: {
         genres: "Musical",
         year: { $in: [ 2009, 2010 ] },
         "imdb.rating": { $type: "double" }
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         sortBy: { title: 1 },
         output: {
            stdDevSampRatingInYear: {
               $stdDevSamp: "$imdb.rating",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            },
            rankInYear: {
               $documentNumber: {}
            }
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         "imdb.rating": 1,
         stdDevSampRatingInYear: 1,
         rankInYear: 1
      }
   },
   { $sort: { year: 1, title: 1 } }
] )

[
  {
    imdb: { rating: 6.3 },
    year: 2009,
    title: 'Bran Nue Dae',
    stdDevSampRatingInYear: null,
    rankInYear: 1
  },
  {
    title: 'Face',
    year: 2009,
    imdb: { rating: 5.9 },
    stdDevSampRatingInYear: 0.28284271247461834,
    rankInYear: 2
  },
  {
    imdb: { rating: 5 },
    year: 2009,
    title: 'Fame',
    stdDevSampRatingInYear: 0.6658328118479391,
    rankInYear: 3
  },
  {
    title: 'Guy and Madeline on a Park Bench',
    year: 2009,
    imdb: { rating: 6.1 },
    stdDevSampRatingInYear: 0.5737304826019499,
    rankInYear: 4
  },
  {
    title: 'Lo',
    year: 2009,
    imdb: { rating: 6.6 },
    stdDevSampRatingInYear: 0.605805249234438,
    rankInYear: 5
  },
  {
    imdb: { rating: 5.9 },
    year: 2009,
    title: 'Nine',
    stdDevSampRatingInYear: 0.5428320796219271,
    rankInYear: 6
  },
  {
    title: 'Oxygen',
    year: 2009,
    imdb: { rating: 6.8 },
    stdDevSampRatingInYear: 0.5871642911161274,
    rankInYear: 7
  },
  {
    title: 'Ricky Rapper and the Bicycle Thief',
    year: 2009,
    imdb: { rating: 5.3 },
    stdDevSampRatingInYear: 0.6104740546727364,
    rankInYear: 8
  },
  {
    title: 'Stingray Sam',
    year: 2009,
    imdb: { rating: 7.2 },
    stdDevSampRatingInYear: 0.6996030620605118,
    rankInYear: 9
  },
  {
    title: 'Anjaana Anjaani',
    year: 2010,
    imdb: { rating: 5.8 },
    stdDevSampRatingInYear: null,
    rankInYear: 1
  },
  {
    title: 'Basilicata Coast to Coast',
    year: 2010,
    imdb: { rating: 6.7 },
    stdDevSampRatingInYear: 0.636396103067893,
    rankInYear: 2
  },
  {
    title: 'Camp Rock 2: The Final Jam',
    year: 2010,
    imdb: { rating: 5.1 },
    stdDevSampRatingInYear: 0.8020806277010645,
    rankInYear: 3
  },
  {
    title: 'Pure Country 2: The Gift',
    year: 2010,
    imdb: { rating: 4.9 },
    stdDevSampRatingInYear: 0.8139410298049852,
    rankInYear: 4
  },
  {
    title: 'Score: A Hockey Musical',
    year: 2010,
    imdb: { rating: 4.8 },
    stdDevSampRatingInYear: 0.7956129712366433,
    rankInYear: 5
  },
  {
    title: 'StarStruck',
    year: 2010,
    imdb: { rating: 6.2 },
    stdDevSampRatingInYear: 0.7730890418746516,
    rankInYear: 6
  },
  {
    title: 'The Ugly Duckling',
    year: 2010,
    imdb: { rating: 6.7 },
    stdDevSampRatingInYear: 0.8223080178318075,
    rankInYear: 7
  }
]

The preceding pipeline:

Uses $match to filter for Musical movies from 2009 and 2010 with a numeric IMDb rating.
Uses $setWindowFields to partition movies by year and sort them by title. The stage uses $stdDevSamp with a documents window of [ "unbounded", "current" ] to create the following fields for each document:
- stdDevSampRatingInYear: the running sample standard deviation of imdb.rating.
- rankInYear: a per-partition row number assigned by $documentNumber.
The first document in each partition has only one value in its window, so stdDevSampRatingInYear is null. Each subsequent document adds one more value to the window.
Uses $project to include title, year, imdb, stdDevSampRatingInYear, and rankInYear in the output.
Uses $sort to sort results by year and title.

Learn More

Back

$stdDevPop

$sum