MongoDB实现单场比赛中每个购买事件前的累计击杀数统计

Great question—this is totally achievable with MongoDB's aggregation framework! Let me break down the approach step by step, assuming your event data follows a common structure (I’ll start with a sample model to make this concrete).

Sample Data Structure #

First, let’s assume each game event is stored as a separate document, like this:

{
  "_id": ObjectId("..."),
  "matchId": "match_123",
  "eventType": "kill", // or "purchase"
  "timestamp": ISODate("2024-05-20T14:30:00Z"), // can also be an in-game tick number
  // Optional fields: killerId, victimId, itemPurchased, playerId, etc.
}

Aggregation Pipeline Solution #

We’ll use MongoDB’s aggregation tools to group events by match, sort them chronologically, then calculate running kill totals as we iterate through events—capturing that total every time a purchase occurs.

Here’s the full pipeline with comments explaining each step:

db.gameEvents.aggregate([
  // Step 1: Group all events by match, and collect relevant fields into an array
  {
    $group: {
      _id: "$matchId",
      events: {
        $push: {
          eventType: "$eventType",
          timestamp: "$timestamp",
          itemPurchased: "$itemPurchased" // Include any purchase-specific fields you need
        }
      }
    }
  },
  // Step 2: Sort events in each match by timestamp (critical for accurate running totals)
  {
    $addFields: {
      events: {
        $sortArray: {
          input: "$events",
          sortBy: { timestamp: 1 } // 1 = ascending (earliest to latest)
        }
      }
    }
  },
  // Step 3: Use $reduce to track running kill counts and capture purchase events
  {
    $addFields: {
      purchaseKillData: {
        $reduce: {
          input: "$events",
          initialValue: {
            totalKills: 0,
            purchases: []
          },
          in: {
            $cond: {
              if: { $eq: ["$$this.eventType", "kill"] },
              // If it's a kill: increment the total count, keep purchases list unchanged
              then: {
                totalKills: { $add: ["$$value.totalKills", 1] },
                purchases: "$$value.purchases"
              },
              // If it's a purchase: add the current kill count to the purchase entry
              else: {
                totalKills: "$$value.totalKills",
                purchases: {
                  $concatArrays: [
                    "$$value.purchases",
                    [{
                      purchaseDetails: "$$this",
                      killsBeforePurchase: "$$value.totalKills"
                    }]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  // Optional: Clean up output to focus on the data you care about
  {
    $project: {
      matchId: "$_id",
      purchaseEvents: "$purchaseKillData.purchases",
      _id: 0
    }
  },
  // Optional: Unwind the purchases array to get a flat list per purchase event
  { $unwind: "$purchaseEvents" },
  // Optional: Reshape final documents for readability
  {
    $project: {
      matchId: 1,
      itemPurchased: "$purchaseEvents.purchaseDetails.itemPurchased",
      purchaseTime: "$purchaseEvents.purchaseDetails.timestamp",
      killsBeforePurchase: "$purchaseEvents.killsBeforePurchase"
    }
  }
])

Key Breakdown #

• Grouping & Sorting: We first group events by matchId and sort them by timestamp. This ensures we process events in the exact order they happened in the game—essential for accurate running totals.
• $reduce for Running Totals: The $reduce operator iterates through each sorted event. We maintain a running totalKills count:
  • For every kill event, we increment the count.
  • For every purchase event, we add an entry to the purchases array that captures the current kill count (the number of kills before this purchase) alongside the purchase details.
• Optional Cleanup: The final $project and $unwind stages are just to refine the output—adjust them based on which fields you need to retain.

Quick Notes #

• If you use in-game tick numbers instead of timestamps, just update the $sortArray to sort by your tick field instead of timestamp.
• To filter for specific matches first, add a $match stage at the start (e.g., { $match: { matchId: "match_456" } }).

This approach will give you exactly what you’re looking for: a record of every purchase event in each match, paired with the total number of kills that occurred before it.

内容的提问来源于stack exchange，提问作者DwayneHart