Window Functions and Advanced Analytics

$setWindowFields for rankings, running totals and moving averages — and when to reach for a dedicated analytics system instead

One doc in, one doc outunlike $group, $setWindowFields never collapses documents
$rank vs. $denseRankgaps after a tie, or no gaps
window bounds the calculationturns a group total into a running or moving one
Know the ceilingad hoc, huge, or heavily-joined analytics belongs in a warehouse

1.$setWindowFields fundamentals

$setWindowFields computes a value across a "window" of related documents — like the running total so far, or this row's rank within its group — while still outputting one document per input document, unlike $group which collapses documents together.

Key features

partitionBy divides documents into independent groups the window is computed within, similar to $group's _id
sortBy determines the order documents are considered in — required for rank/running-total style calculations
output names the new field(s) and the window operator/expression computing each one
window (documents or range) controls how many neighboring documents are included — default is the whole partition
Unlike $group, the document count going in equals the document count coming out

$group vs. $setWindowFields

$group

collapses N documents into 1 per group
original document fields are gone unless re-added
answers "what is the total per group?"

$setWindowFields

keeps all N documents, one output per input
every original field is still there
answers "what is this row's running total / rank?"

Command

text

{ $setWindowFields: { partitionBy, sortBy, output: { <newField>: { <windowOperator>: <expr>, window? } } } }

$setWindowFields keeps one output document per input, annotated with a value computed over a window of related, ordered documents — reach for it when $group would lose the per-row detail you need.

2.Window operators: rank, running total, moving average

$setWindowFields supports two kinds of operators: rank-style operators ($rank, $denseRank, $documentNumber) that need only sortBy, and accumulator-style operators ($sum, $avg, $min, $max) reused from $group but scoped to a window instead of a whole group.

Key features

$rank / $denseRank: position within the partition by sortBy order — $rank skips numbers after a tie, $denseRank does not
$documentNumber: a sequential position with no ties considered at all, always unique per document
A window: { documents: ["unbounded", "current"] } makes any accumulator a running total/average up to the current row
A window: { documents: [-2, 0] } makes any accumulator a moving calculation over the current document and the 2 before it
$shift looks at another document in the partition by relative position, useful for "value vs. previous row"

Common window operators

Common window operators
Operator	Needs a window?	Computes
$rank	no — uses sortBy directly	position, with gaps after ties
$denseRank	no — uses sortBy directly	position, no gaps after ties
$sum / $avg	yes, to bound it	running total / moving average
$shift	no — takes an offset instead	value from a nearby row (e.g. previous)

Command

text

output: { <field>: { $rank: {} } } · output: { <field>: { $sum: <expr>, window: { documents: [<start>, <end>] } } }

Rank operators ($rank, $denseRank) need only sortBy; accumulator operators ($sum, $avg, …) need an explicit window to turn a group-wide total into a running or moving one.

3.Aggregation vs. a dedicated analytics system

The aggregation framework, with $setWindowFields and $group, comfortably covers dashboards, reports, and per-tenant summaries over operational data. Ad hoc exploratory queries across billions of rows, or joins across many large datasets, usually belong in a dedicated warehouse instead.

Key features

Aggregation fits: reports/dashboards over the same data the application already writes, moderate data volume, known query shapes
A warehouse fits: ad hoc exploratory analytics, very large historical volumes, heavy joins across many large datasets, or workloads that would compete with production traffic for resources
Aggregation pipelines have a memory limit per stage (100MB by default, extendable with allowDiskUse) — a signal a query has outgrown in-place aggregation
Change streams are a common bridge: stream operational writes into a warehouse for the heavier analytical workloads, without querying production for them
The decision is not permanent — teams commonly start with aggregation and introduce a warehouse only once a specific workload actually needs it

Command

text

// Signals to reconsider: exploratory ad hoc queries, heavy cross-dataset joins, or aggregations routinely hitting the memory limit

Aggregation is the right tool for known-shape reporting over live operational data — reach for a dedicated warehouse once the workload is ad hoc, very large, heavy on cross-dataset joins, or starts competing with production for resources.

Spotted a mistake on this page? Tell us.

Read Sheet