/ /

$percentile (window function)

Definition

$percentile

Returns an array of percentile values for the specified percentiles within a window.

Note

Disambiguation

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

You can also use $percentile in these other contexts:

$percentile (accumulator), which returns an aggregated array of percentile values across a group of input documents.
$percentile (expression), which calculates percentile values over an array of input values.

Syntax

{
   $setWindowFields: {
      ...
      output: {
         <field>: {
            $percentile: {
               input: <expression>,
               p: [ <expression1>, <expression2>, ... ],
               method: <string>
            },
         }
      }
   }
}

Options

Field	Type	Description
`input`	Expression	The field over which `$percentile` calculates percentile values. `input` must be an expression that evaluates to a numeric type. If the expression does not evaluate to a numeric type, the `$percentile` calculation ignores that value.
`p`	Expression	`$percentile` calculates a percentile value for each element in `p`. These elements specify the quantiles to compute and must be numeric values in the range `0.0` to `1.0`, inclusive. `$percentile` returns results in the same order as the elements in `p`.
`method`	String	The method that MongoDB uses to calculate the percentile value. The method must be `'approximate'`.

Behavior

In $setWindowFields, $percentile returns a result for each document, but the results are computed over a window of documents.

Calculation Considerations

When used as a window function, $percentile always uses an accurate percentile calculation algorithm rather than an approximate one, even when method: 'approximate' is specified.

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.

The following example uses $percentile in a $setWindowFields stage to find Action movies in the top 10% of IMDB ratings for each year from 2002 to 2004.

db.movies.aggregate( [
   {
      $match: {
         year: { $in: [ 2002, 2003, 2004 ] },
         genres: "Action",
         "imdb.votes": { $gte: 5000 },
      }
   },
   {
      $setWindowFields: {
         partitionBy: "$year",
         output: {
            top10PctRatingForYear: {
               $percentile: {
                  input: "$imdb.rating",
                  p: [ 0.9 ],
                  method: "approximate"
               },
               window: { documents: [ "unbounded", "unbounded" ] }
            }
         }
      }
   },
   {
      $match: {
         $expr: {
            $gte: [
               "$imdb.rating",
               { $arrayElemAt: [ "$top10PctRatingForYear", 0 ] }
            ]
         }
      }
   },
   {
      $project: {
         _id: 0,
         title: 1,
         year: 1,
         imdb_rating: "$imdb.rating",
         top10PctRatingForYear: 1
      }
   },
   {
      $sort: { year: 1, imdb_rating: -1 }
   }
] )

[
  {
    title: 'Company',
    year: 2002,
    top10PctRatingForYear: [
      7.5
    ],
    imdb_rating: 8.1
  },
  {
    year: 2002,
    title: 'Hero',
    top10PctRatingForYear: [
      7.5
    ],
    imdb_rating: 7.9
  },
  {
    year: 2002,
    title: 'The Bourne Identity',
    top10PctRatingForYear: [
      7.5
    ],
    imdb_rating: 7.9
  },
  {
    year: 2002,
    title: 'Minority Report',
    top10PctRatingForYear: [
      7.5
    ],
    imdb_rating: 7.7
  },
  {
    year: 2002,
    title: 'Equilibrium',
    top10PctRatingForYear: [
      7.5
    ],
    imdb_rating: 7.5
  },
  {
    title: 'Battlestar Galactica',
    year: 2003,
    top10PctRatingForYear: [
      7.6
    ],
    imdb_rating: 8.7
  },
  {
    year: 2003,
    title: 'Pirates of the Caribbean: The Curse of the Black Pearl',
    top10PctRatingForYear: [
      7.6
    ],
    imdb_rating: 8.1
  },
  {
    year: 2003,
    title: 'Kill Bill: Vol. 1',
    top10PctRatingForYear: [
      7.6
    ],
    imdb_rating: 8.1
  },
  {
    year: 2003,
    title: 'The Last Samurai',
    top10PctRatingForYear: [
      7.6
    ],
    imdb_rating: 7.7
  },
  {
    title: 'The Blind Swordsman: Zatoichi',
    year: 2003,
    top10PctRatingForYear: [
      7.6
    ],
    imdb_rating: 7.6
  },
  {
    title: 'Tae Guk Gi: The Brotherhood of War',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 8.2
  },
  {
    title: 'Farscape: The Peacekeeper Wars',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 8.2
  },
  {
    title: 'Kill Bill: Vol. 2',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 8
  },
  {
    title: 'The Incredibles',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 8
  },
  {
    title: 'Kung Fu Hustle',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 7.8
  },
  {
    title: 'The Bourne Supremacy',
    year: 2004,
    top10PctRatingForYear: [
      7.8
    ],
    imdb_rating: 7.8
  }
]

The preceding pipeline:

Uses $match to filter for Action movies released in 2002, 2003, or 2004 with at least 5,000 IMDB votes.
Uses $setWindowFields to partition movies by year. Within each partition, the $percentile operator computes the 90th percentile IMDB rating using p: [0.9] and an unbounded window, stored in top10PctRatingForYear.
Uses $match with $expr to filter for movies whose IMDB rating is greater than or equal to the 90th percentile rating for their year.
Uses $project to include the title, year, imdb_rating, and top10PctRatingForYear fields in the output.
Uses $sort to order the results by year ascending, then by IMDB rating descending.

Back

$minN

$push