0Pricing
MongoDB Academy · レッスン

$setWindowFieldsによるウィンドウ関数

$setWindowFieldsステージを使い、順序付けしたパーティションに対して累計、順位、移動平均を計算します。

「$setWindowFieldsによるウィンドウ関数」はCoddyKit上の無料MongoDB Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはMongoDB Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 MongoDB Academyコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

What Are Window Functions?

Window functions compute values over a set of documents related to the current document—a 'window'—without collapsing them into a single group like $group does. They originated in SQL (SQL:2003) and were added to MongoDB in version 5.0 through the $setWindowFields pipeline stage. Common use cases include running totals, moving averages, rank, and cumulative metrics.

The $setWindowFields Stage Structure

The $setWindowFields stage has three main configuration keys: partitionBy defines how to divide documents into independent windows (like GROUP BY in SQL), sortBy orders documents within each partition, and output specifies the new fields to compute along with their window operator and window bounds.

db.dailySales.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$region',        // separate window per region
      sortBy: { saleDate: 1 },       // order by date within each region
      output: {
        runningTotal: {
          $sum: '$amount',
          window: { documents: ['unbounded', 'current'] }
        }
      }
    }
  }
])

Document-Based Window Bounds

Window bounds define which documents contribute to the computation for each row. Document-based bounds use the documents key with a two-element array: the start position and end position relative to the current document. 'unbounded' means 'from the beginning (or to the end)', 'current' means the current document, and numeric offsets like -1 mean 'one document before'. Common patterns: ['unbounded', 'current'] for a running total, [-1, 1] for a 3-document moving window.

// Running total: all documents from the start up to the current row
window: { documents: ['unbounded', 'current'] }

// 3-document moving window: previous, current, and next document
window: { documents: [-1, 1] }

// Cumulative (all documents from start to end)
window: { documents: ['unbounded', 'unbounded'] }

Range-Based Window Bounds

Range-based bounds define the window using value ranges on the sort key rather than document offsets. This is especially useful for time-series data where you want 'the last 7 days' rather than 'the last 7 documents'. Use the range key with a unit for date fields. This correctly handles gaps in data where days might be missing.

db.temperatures.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$station',
      sortBy: { readingDate: 1 },
      output: {
        sevenDayAvgTemp: {
          $avg: '$temperature',
          window: {
            range: [-6, 0], // 6 days before up to current day
            unit: 'day'
          }
        }
      }
    }
  }
])

Computing Running Totals

A running total (cumulative sum) is computed by setting the window to span from the first document in the partition to the current document. As MongoDB processes each document in sort order, it adds that document's value to all previous values. This produces a monotonically increasing total per partition, useful for cumulative revenue or progressive download counts.

db.transactions.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$accountId',
      sortBy: { date: 1 },
      output: {
        runningBalance: {
          $sum: '$amount',
          window: { documents: ['unbounded', 'current'] }
        }
      }
    }
  },
  { $project: { accountId: 1, date: 1, amount: 1, runningBalance: 1 } }
])

Moving Averages for Smoothing Data

A moving average smooths out short-term fluctuations in time-series data to reveal underlying trends. Configure the window to span a fixed number of periods in both directions (or only backwards for a 'trailing' average). Moving averages are common in financial charts, performance monitoring dashboards, and IoT sensor analysis.

db.stockPrices.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$ticker',
      sortBy: { date: 1 },
      output: {
        movingAvg5Day: {
          $avg: '$closePrice',
          window: { documents: [-4, 0] } // current + 4 previous = 5 day average
        },
        movingAvg10Day: {
          $avg: '$closePrice',
          window: { documents: [-9, 0] } // 10-day trailing average
        }
      }
    }
  }
])

Ranking With $rank and $denseRank

The $rank operator assigns a rank number to each document within its partition based on the sort order. Tied documents receive the same rank, and the next rank skips accordingly (1, 2, 2, 4). $denseRank assigns consecutive ranks without gaps for ties (1, 2, 2, 3). Neither takes a window specification—they always rank across the full partition.

db.leaderboard.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$gameId',
      sortBy: { score: -1 }, // highest score = rank 1
      output: {
        rank: { $rank: {} },
        denseRank: { $denseRank: {} }
      }
    }
  },
  { $match: { rank: { $lte: 10 } } } // top 10 per game
])

$documentNumber: Row Numbering Within Partition

$documentNumber assigns a sequential integer starting from 1 to each document within its partition, in sort order. Unlike $rank, it never repeats numbers—every document gets a unique number. This is useful for pagination, sequence numbering, or when you need to identify which row within a partition a document occupies.

db.orders.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$customerId',
      sortBy: { orderDate: 1 },
      output: {
        orderSequence: { $documentNumber: {} } // 1st order, 2nd order, etc.
      }
    }
  },
  // Find customers' 3rd orders
  { $match: { orderSequence: 3 } }
])

$shift: Accessing Adjacent Documents

$shift returns the value of an expression from a document at a specified offset relative to the current document within the partition. Use by: -1 to access the previous document's value (e.g., yesterday's price), by: 1 for the next document, and specify a default for when the offset falls outside the partition boundary.

db.dailyMetrics.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$metricName',
      sortBy: { date: 1 },
      output: {
        previousValue: {
          $shift: {
            output: '$value',
            by: -1,
            default: null
          }
        },
        // Compute day-over-day change using $shift
        dayOverDayChange: {
          $subtract: [
            '$value',
            { $shift: { output: '$value', by: -1, default: '$value' } }
          ]
        }
      }
    }
  }
])

Performance and Index Usage

$setWindowFields benefits from indexes on the partition and sort fields. An index that covers both the partition key and the sort key allows MongoDB to efficiently retrieve each partition's documents in sorted order without a full collection scan. Without an index, MongoDB must sort in-memory (up to allowDiskUse limits). For large collections, ensure indexes align with your window functions' partition and sort specifications.

// For this $setWindowFields:
// partitionBy: '$accountId', sortBy: { date: 1 }
// Create a compound index:
db.transactions.createIndex({ accountId: 1, date: 1 })
// MongoDB can now efficiently scan per-partition in date order

Practical Example: Sales Performance Report

A real-world sales report might need each salesperson's transactions enriched with their running total, their rank within their team, and the team cumulative total—all computed in a single pipeline without multiple joins or application-side computation. This is exactly the kind of analytical query $setWindowFields was built for.

db.sales.aggregate([
  {
    $setWindowFields: {
      partitionBy: '$teamId',
      sortBy: { amount: -1 },
      output: {
        rankInTeam: { $rank: {} },
        runningTeamTotal: {
          $sum: '$amount',
          window: { documents: ['unbounded', 'current'] }
        },
        teamTotal: {
          $sum: '$amount',
          window: { documents: ['unbounded', 'unbounded'] }
        }
      }
    }
  }
])

Quick Check

Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.

Lesson Recap

In this lesson you learned: $setWindowFields computes values over a sliding window of related documents without collapsing them like $group, document-based and range-based window bounds control which documents contribute to each computation, and operators like $rank, $denseRank, $documentNumber, and $shift enable ranking, numbering, and cross-row comparisons. Next up we explore ACID guarantees in MongoDB's distributed document store.

よくある質問

「$setWindowFieldsによるウィンドウ関数」レッスンは無料ですか？

はい。「$setWindowFieldsによるウィンドウ関数」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、MongoDB Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 MongoDB Academyコースには全4レッスンが含まれています。

「$setWindowFieldsによるウィンドウ関数」で何を学びますか？

$setWindowFieldsステージを使い、順序付けしたパーティションに対して累計、順位、移動平均を計算します。 ブラウザで直接実行するハンズオンコードでMongoDB Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

MongoDB Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのMongoDB Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「$setWindowFieldsによるウィンドウ関数」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このMongoDB Academyレッスンでコードを書いて実行できますか？

はい。すべてのMongoDB Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. $sum、$avg、$min、$max：数値の集約
  2. $pushと$addToSet：グループ内での配列の構築
  3. $first、$last、$top/$bottom累計演算子
  4. $setWindowFieldsによるウィンドウ関数
← MongoDB Academyに戻る