Window functions in aggregation: lessons from production

Opening: Why window functions matter in real-world aggregation

We've all hit that moment in production where a simple $group stage isn't enough. You need running totals across time series data, or moving averages for each customer, or rank-ordered results within product categories. Window functions in aggregation—specifically MongoDB's $setWindowFields stage—solve this elegantly without fetching data into application code. In this article, we'll explore window functions in aggregation: lessons from production, covering how to partition by user or product, sort within partitions, and apply calculations that feel natural to time series analysis.

Understanding $setWindowFields

The $setWindowFields stage, introduced in MongoDB 5.0, lets you compute values across a subset of documents—a window—for each partition. Think of it as a sliding or expanding view over your data, ordered in a way you define. Unlike $group, which collapses documents, window functions preserve all documents and add new computed fields to each one.

Here's the basic structure:

db.collection.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$customerId",
      sortBy: { timestamp: 1 },
      output: {
        runningTotal: {
          $sum: "$amount",
          window: { range: ["unbounded", "current"] }
        }
      }
    }
  }
])

The key components are:

• partitionBy: Groups documents logically (e.g., by user or product) without collapsing them.
• sortBy: Defines the order within each partition.
• output: Defines computed window functions to add as new fields.
• window: Specifies the range of documents to include in the calculation (e.g., all previous + current, last 7 days, etc.).

Partitioning by user or product

Partitioning is where window functions shine in production scenarios. Instead of running separate queries for each customer or product, you compute results for all partitions in a single pass.

Real-world scenario: An e-commerce platform tracks order amounts per customer. You need the cumulative spend for each customer, sorted by order date, to identify tier-based discounts or loyalty rewards.

db.orders.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$customerId",
      sortBy: { createdAt: 1 },
      output: {
        cumulativeSpend: {
          $sum: "$total",
          window: { range: ["unbounded", "current"] }
        },
        orderCount: {
          $sum: 1,
          window: { range: ["unbounded", "current"] }
        }
      }
    }
  },
  {
    $match: { cumulativeSpend: { $gte: 1000 } }
  }
])

Each document in the result now has a cumulativeSpend field showing the running total up to that order. If a customer has three orders worth $200, $300, and $500, the output would show $200, $500, and $1000 respectively.

For product-level partitioning, imagine tracking inventory movements. You might partition by skuId and compute stock levels as you move through warehouse transactions:

db.inventory_log.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$skuId",
      sortBy: { timestamp: 1 },
      output: {
        stockLevel: {
          $sum: "$quantityChange",
          window: { range: ["unbounded", "current"] }
        }
      }
    }
  }
])

This gives you a running balance for every transaction without post-processing.

Sorting within each partition

The sortBy field is critical. It determines the order in which documents are considered within the partition, directly affecting window calculations. Multiple sort criteria are common in production.

Scenario: A SaaS platform analyzes user activity. For each user, you want to compute metrics over a rolling window of events, ordered by timestamp, then by event type priority.

db.events.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$userId",
      sortBy: { timestamp: 1, eventPriority: -1 },
      output: {
        rollingEventCount: {
          $sum: 1,
          window: { range: [-3600, 0], unit: "second" } // last hour
        },
        lastEventTime: {
          $max: "$timestamp",
          window: { range: ["unbounded", "current"] }
        }
      }
    }
  }
])

The range with unit specifies a time-based window. Here, we count events in the trailing 3600 seconds for each user's event stream.

When you don't specify a window, it defaults to range: ["unbounded", "unbounded"]—the entire partition. This is useful for global partition statistics:

db.sales.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$region",
      sortBy: { date: 1 },
      output: {
        totalRegionalSales: {
          $sum: "$revenue"
        },
        percentOfRegional: {
          $divide: [
            "$revenue",
            { $sum: "$revenue" }
          ]
        }
      }
    }
  }
])

Great for time series analysis

Time series data is where window functions really demonstrate their power. Financial platforms, IoT systems, and analytics pipelines all rely on these calculations in production.

Moving averages: One of the most common use cases. For stock price analysis, compute a 7-day moving average to smooth out daily volatility:

db.stock_prices.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$ticker",
      sortBy: { date: 1 },
      output: {
        movingAverage7d: {
          $avg: "$closePrice",
          window: { range: [-6, 0] } // 7 days inclusive
        },
        movingAverage30d: {
          $avg: "$closePrice",
          window: { range: [-29, 0] } // 30 days inclusive
        }
      }
    }
  },
  { $sort: { ticker: 1, date: -1 } }
])

Rate of change: Computing deltas between consecutive observations is easier with window functions than application logic. For API latency monitoring:

db.metrics.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$endpoint",
      sortBy: { recordedAt: 1 },
      output: {
        previousLatency: {
          $shift: { output: "$latency", by: -1 }
        },
        latencyChange: {
          $subtract: [
            "$latency",
            { $shift: { output: "$latency", by: -1 } }
          ]
        }
      }
    }
  }
])

The $shift operator retrieves values from a neighboring document in the window, making delta calculations straightforward.

Anomaly detection setup: Window functions can compute rolling statistics that power anomaly detection. Flag data points that deviate significantly from a rolling mean:

db.sensor_data.aggregate([
  {
    $setWindowFields: {
      partitionBy: "$sensorId",
      sortBy: { timestamp: 1 },
      output: {
        rollingMean: {
          $avg: "$value",
          window: { range: [-59, 0], unit: "second" } // 1 minute window
        },
        rollingStdDev: {
          $stdDevSamp: "$value",
          window: { range: [-59, 0], unit: "second" }
        }
      }
    }
  },
  {
    $project: {
      value: 1,
      rollingMean: 1,
      rollingStdDev: 1,
      zscore: {
        $divide: [
          { $subtract: ["$value", "$rollingMean"] },
          { $cond: [{ $eq: ["$rollingStdDev", 0] }, 1, "$rollingStdDev"] }
        ]
      }
    }
  },
  {
    $match: { zscore: { $gte: 2 } }
  }
])

Common mistakes

1. Forgetting to define sortBy for order-dependent windows: If you don't specify sortBy, partition order is undefined. Time-series calculations become unreliable. Always include sortBy when using positional windows like range: [-5, 0].

2. Mixing unbounded and time-based ranges: range: ["unbounded", "current"] works with document offsets; range: [-3600, 0] requires unit: "second" and is for numeric fields. Mixing them causes errors. Choose one consistently per output field.

3. Over-partitioning: Partitioning by too-granular fields (e.g., every order ID instead of customer ID) defeats the purpose. The partition should logically group related documents you want to compute over together.

4. Ignoring memory constraints: Large partitions can consume significant memory. In production, combine $setWindowFields with $match to filter early, or break analysis into smaller time windows before aggregation.

5. Not indexing sort fields: Without an index on the sortBy field, the aggregation engine must sort in memory for every partition. Add indexes on commonly sorted fields: db.collection.createIndex({ partitionField: 1, sortField: 1 }).

Key takeaways

• $setWindowFields preserves all documents while computing windowed metrics—ideal for time series and ranking scenarios.
• Partition by meaningful groupings (customer, product, sensor) to avoid redundant calculations across your entire collection.
• Always define sortBy when order matters; use document-offset ranges for logical ordering and unit-based ranges for time-bound windows.
• Common window functions include $sum, $avg, $shift, $first, $last, and $rank—each solves different production problems.
• Combine window functions with $match and $project stages to filter and compute derived metrics efficiently in a single pipeline.
• Index partition and sort fields to avoid in-memory bottlenecks, especially with large datasets or many partitions.