A group summary can tell you a store's total sales, but it collapses individual sales into one result. A report may instead need every sale plus the total accumulated up to that sale. Window functions add those contextual calculations while keeping the rows. This reference targets MongoDB 8.0, where $setWindowFields supports the demonstrated operations. The complete mongosh fixtures use store partitions, an explicit sequence order, a running sum, and a two-row moving average. A separate ranking exercise includes tied scores so the difference between rank and dense rank can be observed rather than described only in an abstract definition.
Partition and order before defining the frame
partitionBy separates independent sequences. A store-A row should not include store-B amounts in its running total, so the fixture partitions by store. sortBy defines the order used for window calculations within each partition. The main data gives each store a unique sequence number, making the running position unambiguous. If the sort values tie, decide whether the operation needs a unique ordering key or intentional peer behavior. Do not assume insertion order supplies a missing contract.
Partitioning is not the same as grouping. Each input row remains available with added output fields. A later $group can still collapse rows if the report needs it, but that is another stage. A missing or null partition key can collect rows into a shared partition according to the expression result, so validate grouping fields when that would be inappropriate. Choose partition expressions from the report's actual question: per store, per account, or per device are different sequences even if all rows contain the same amount field.
Choose a document frame for running and moving values
A documents frame uses positions relative to the current row. The frame from unbounded to current includes the partition's beginning through the current row, producing a running total. The frame [-1, 0] includes the previous row and the current row, producing a two-row moving average. At the first row there is no preceding row, so the average uses the available current row rather than inventing a zero. This edge behavior affects the initial report values.
Do not confuse two rows with two days. If events are irregular, a two-row frame can span minutes or months. A range frame operates over ordered values and has additional requirements, including numeric or date behavior and restrictions on sort shape. Pick the frame that matches the analytic meaning. Missing or nonnumeric amounts also need an explicit data-quality policy because accumulator behavior is not a replacement for validating a financial metric. The fixture uses small integers so its exact averages can be checked without decimal-rounding ambiguity.
Distinguish position from tied ranking
$documentNumber gives a row position in its partition. $rank assigns equal ranks to tied sort values and leaves gaps after ties. $denseRank assigns equal ranks but does not leave those gaps. In the practice scores ninety, ninety, and eighty have ranks one, one, three, while dense ranks are one, one, two. Adding a unique identifier to the ranking key would change the definition of a tie, so do not add it casually just to make display ordering convenient.
The demonstrated rank operators use their documented sort requirements. Keep the score as the analytic ranking key and use a later ordinary sort to make the display order of tied rows deterministic. That separates ranking semantics from presentation. A leaderboard may also need rules for disqualified entries, minimum activity, or partitioning by season. Apply those rules before ranking if the rank is meant to exclude those rows. Filtering after ranking answers a different question because removed rows can already have influenced positions and gaps.
Finalize output and resource policy
$setWindowFields does not replace an explicit final sort when the application needs a guaranteed output order. Add that sort after the window stage and project only report fields. The fixture maps the result into a stable property order before printing JSON, keeping its expected output focused on the analytic values. Added window fields can have names colliding with source fields, so choose intentional names and avoid accidentally overwriting values needed later.
Sorting and large partitions consume resources. Apply selective filters early when that matches the intended population, consider relevant indexes, and inspect plans with realistic data. Disk-use options and server limits are execution controls, not permission to run an unlimited report. A partition containing millions of events may need a narrower reporting period or precomputed design. Running totals also depend on the selected history: filtering to the current month before calculation starts the total within that month. If a report needs a lifetime balance, obtain the earlier balance or include the required history deliberately.
MongoDB documentation and version notes
MongoDB Window Functions Examples
Each example shows the code, the output it produces and what each part does.
Computing per-store running totals and moving averages
Run the complete fixture with mongosh on MongoDB 8.0. Store A has three amounts and store B has two. The running sum restarts for each store. The moving average considers at most the current and preceding row, so A's third value is 12.5 rather than its lifetime average. documentNumber restarts within each partition as well. A final sort defines report order independently of the window stage. The JSON projection prints only store, sequence, running total, moving average, and position. Those small values make boundary and partition behavior directly reviewable.
const sales = db.getSiblingDB('codevra_ref_windows').getCollection('sales');
sales.deleteMany({});
sales.insertMany([{ _id: 1, store: 'A', seq: 1, amount: 10 },
{ _id: 2, store: 'A', seq: 2, amount: 20 }, { _id: 3, store: 'A', seq: 3, amount: 5 },
{ _id: 4, store: 'B', seq: 1, amount: 7 }, { _id: 5, store: 'B', seq: 2, amount: 3 }]);
const report = sales.aggregate([
{ $setWindowFields: { partitionBy: '$store', sortBy: { seq: 1 }, output: {
running: { $sum: '$amount', window: { documents: ['unbounded', 'current'] } },
moving: { $avg: '$amount', window: { documents: [-1, 0] } },
position: { $documentNumber: {} }
} } },
{ $sort: { store: 1, seq: 1 } }
]).toArray().map(x => ({ store: x.store, seq: x.seq, running: x.running, moving: x.moving, position: x.position }));
print(JSON.stringify(report));[{"store":"A","seq":1,"running":10,"moving":10,"position":1},{"store":"A","seq":2,"running":30,"moving":15,"position":2},{"store":"A","seq":3,"running":35,"moving":12.5,"position":3},{"store":"B","seq":1,"running":7,"moving":7,"position":1},{"store":"B","seq":2,"running":10,"moving":5,"position":2}]MongoDB Window Functions in Real Projects
Rank tied scores with rank and denseRank
Rank three scores, including a tie for first place. Compute rank and denseRank from score descending, then apply a deterministic display sort using the identifier. The ranking stage deliberately does not add _id to the score key because that would alter which rows count as tied. The solution prints identifier, rank, and dense rank in a small array. This distinction matters when a product promises competition-style ranks with gaps or consecutive rank groups without gaps. Choose the required rule first, then use the operator that implements it rather than treating both names as interchangeable.
const scores = db.getSiblingDB('codevra_ref_windows').getCollection('scores');
scores.deleteMany({});
scores.insertMany([{ _id: 1, score: 90 }, { _id: 2, score: 90 }, { _id: 3, score: 80 }]);
const report = scores.aggregate([
{ $setWindowFields: { sortBy: { score: -1 }, output: {
rank: { $rank: {} }, dense: { $denseRank: {} }
} } },
{ $sort: { score: -1, _id: 1 } }
]).toArray().map(x => ({ id: x._id, rank: x.rank, dense: x.dense }));
print(JSON.stringify(report));[{"id":1,"rank":1,"dense":1},{"id":2,"rank":1,"dense":1},{"id":3,"rank":3,"dense":2}]MongoDB Window Functions FAQ
How is a window different from group?
A group collapses input rows into summaries, while a window calculation can retain each row and attach contextual values. Use a window for a sale plus its running store total. Use group for one total per store when individual rows are unnecessary. A pipeline can use both deliberately, but adding a later group changes the output shape again. Choose the operation from the report's required rows.
Does [-1, 0] mean the last two days?
No. It is a document-position frame containing the previous and current row. Irregular event timing can make those rows span a very different duration. Use a suitable range frame and its documented date-unit rules for a time-based window. The first row's moving average uses only available rows, so it does not automatically average the current amount with an invented zero.
Why does rank skip from one to three?
Two tied rows occupy the first two competition positions, so the next distinct score receives rank three. denseRank instead counts distinct rank groups and assigns two. The practice shows both from the same score key. Do not include a unique identifier in that ranking key unless the product intends to break the tie. A later display sort can order tied rows without changing rank semantics.
Should I add a final sort after the window stage?
Yes when the output contract requires an explicit order. The window's analytic ordering does not replace that final presentation guarantee. Keep the final sort consistent with the report, and retain fields needed for it until afterward. Also test realistic partition sizes and resource behavior. A correct small fixture proves the calculation, not the performance or capacity of an unlimited production report.
MongoDB Window Functions Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
What rank and dense rank does score 80 receive after two tied scores of 90?
MongoDB Window Functions Quiz
2 questions to check what you learned.
Why does store B running total start at seven?
- partitionBy separates B from A before window calculation
- The final sort deletes store A amounts
- Every window always starts at zero and discards its first row
What does a running frame from unbounded to current include?
- The partition beginning through the current row
- Only the current and next row
- Every row in every partition regardless of order