$setWindowFields, Ranking, Moving Windows, Time-Based Analytics, and Partitioning

Learning objectives

1. Window functions preserve row grain

$setWindowFields divides input documents into partitions, orders documents within each partition when required, and appends calculated fields based on a window around each current document. Unlike $group, it normally preserves one output document per input document. This is ideal for running totals, moving windows, ranks, and time-based comparisons.

2. Seed two store partitions

docker rm -f atlasmart-mongo-ch09-l4 2>/dev/null || truedocker volume rm atlasmart-mongo-ch09-l4-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch09-l4 \  -p 127.0.0.1:27060:27017 \  -v atlasmart-mongo-ch09-l4-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27060/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1,allowDiskUseByDefault:1}))' 

const c=db.daily_store_sales_ch09_l4;c.drop();c.insertMany([ {_id:"w1-d1",storeId:"west",region:"west",day:ISODate("2026-08-01T00:00:00Z"),revenueCents:1000}, {_id:"w1-d2",storeId:"west",region:"west",day:ISODate("2026-08-02T00:00:00Z"),revenueCents:1800}, {_id:"w1-d3",storeId:"west",region:"west",day:ISODate("2026-08-03T00:00:00Z"),revenueCents:1400}, {_id:"w1-d4",storeId:"west",region:"west",day:ISODate("2026-08-04T00:00:00Z"),revenueCents:2200}, {_id:"e1-d1",storeId:"east",region:"east",day:ISODate("2026-08-01T00:00:00Z"),revenueCents:900}, {_id:"e1-d2",storeId:"east",region:"east",day:ISODate("2026-08-02T00:00:00Z"),revenueCents:1600}, {_id:"e1-d3",storeId:"east",region:"east",day:ISODate("2026-08-03T00:00:00Z"),revenueCents:2500}, {_id:"e1-d4",storeId:"east",region:"east",day:ISODate("2026-08-04T00:00:00Z"),revenueCents:1300}]);c.createIndex({storeId:1,day:1});printjson({count:c.countDocuments({}),indexes:c.getIndexes()});

3. Running total and trailing three-day time window

const p=[ {$setWindowFields:{   partitionBy:"$storeId",   sortBy:{day:1},   output:{     runningRevenueCents:{$sum:"$revenueCents",window:{documents:["unbounded","current"]}},     trailing3DayRevenueCents:{$sum:"$revenueCents",window:{range:[-2,0],unit:"day"}}   } }}, {$sort:{storeId:1,day:1}}, {$project:{_id:0,storeId:1,day:1,revenueCents:1,runningRevenueCents:1,trailing3DayRevenueCents:1}}];printjson(c.aggregate(p,{allowDiskUse:true}).toArray());

2026-08-01 revenue=1000 running=1000 trailing3day=10002026-08-02 revenue=1800 running=2800 trailing3day=28002026-08-03 revenue=1400 running=4200 trailing3day=42002026-08-04 revenue=2200 running=6400 trailing3day=5400  // Aug 2-4

The running total uses a document window from unbounded to current. The trailing metric uses a time range of two days before the current day through the current day, so it is based on date values, not “the previous two rows” if dates are missing.

4. Ranking is a different order contract

Revenue ranking needs revenue-descending order, not day order, so use a separate window stage/pipeline. Rank operators use an implicit window and must not be given an explicit window option.

printjson(c.aggregate([ {$setWindowFields:{partitionBy:"$region",sortBy:{revenueCents:-1},output:{   rank:{$rank:{}},denseRank:{$denseRank:{}},rowNumber:{$documentNumber:{}} }}}, {$sort:{region:1,rank:1,_id:1}}, {$project:{_id:1,region:1,revenueCents:1,rank:1,denseRank:1,rowNumber:1}}]).toArray());

Our fixture has unique revenue values, so rank and denseRank happen to be equal. Add ties in a test fixture if tie behavior is business-critical. rowNumber is document position in the defined order, not a stable identifier.

5. Controlled failures expose sort requirements

try { printjson(c.aggregate([{$setWindowFields:{partitionBy:"$storeId",output:{   trailing:{$sum:"$revenueCents",window:{documents:[-2,0]}} }}}]).toArray());} catch(e) { print("expected missing-sort failure:",e.codeName||e.name,e.message); }try { printjson(c.aggregate([{$setWindowFields:{partitionBy:"$storeId",sortBy:{day:1,_id:1},output:{   trailing:{$sum:"$revenueCents",window:{range:[-2,0],unit:"day"}} }}}]).toArray());} catch(e) { print("expected time-range sort restriction:",e.codeName||e.name,e.message); }

6. Memory and explain in MongoDB 8.3

$setWindowFields can retain substantial state for large partitions and is among stages governed by aggregation memory/disk-use rules. MongoDB 8.3 adds peakTrackedMemBytes to explain output for window execution, giving a direct signal for maximum tracked memory. Treat that as workload evidence, not a universal threshold.

const e=c.explain("executionStats").aggregate([ {$setWindowFields:{partitionBy:"$storeId",sortBy:{day:1},output:{   trailing3DayRevenueCents:{$sum:"$revenueCents",window:{range:[-2,0],unit:"day"}} }}}, {$sort:{storeId:1,day:1}}],{allowDiskUse:true});printjson(e);

7. Verification, cleanup, and production judgment

Verification checklist

• Eight rows exist, four in each store partition.
• West running and trailing values match the listed arithmetic.
• The final $sort is present because $setWindowFields itself does not guarantee returned order.
• Ranking is computed in a separate revenue-descending order contract.
• Both deliberate invalid-window examples raise errors.
• Explain is inspected for memory evidence; no spill is fabricated from the tiny fixture.

Production judgment. Window functions are appropriate when analytic context is needed without collapsing row grain. Cost is dominated by partition cardinality, ordering, window width, indexes, memory, spill I/O, and skew. Avoid one giant unpartitioned window for a naturally partitionable workload. Tenant identity often belongs in partitionBy or an earlier filter. On sharded systems, measure where sorting/window work occurs and the network/merge cost. Changes are read-only in this lesson, so rollback is an application pipeline deployment rollback; any cached/materialized derived results must be rebuilt separately.

Lesson 5 makes that last point concrete by writing derived results back to collections with $merge and $out.

docker rm -f atlasmart-mongo-ch09-l4docker volume rm atlasmart-mongo-ch09-l4-data

Authoritative references

• MongoDB 8.3 release notes — Current 8.3 behavior and version-sensitive aggregation changes; re-check before reproducing.
• MongoDB aggregation pipeline — Ordered-stage execution model used throughout the chapter.
• Aggregation pipeline limits — Memory, disk-spill, stage-count, and 16 MiB output-document constraints.
• mongosh release notes — mongosh version used for the chapter commands.
• $setWindowFields stage — Partitions, sort/window restrictions, output-order non-guarantee, rank operators, and current 8.3 explain memory evidence.
• $rank expression — Ranking semantics and tie behavior.
• $denseRank expression — Dense ranking semantics.
• $documentNumber expression — Sequential document numbering within a window order.