MongoDB聚合日期字段滑动窗口操作：计算用户购买间隔时长

Hey there! Let's figure out how to calculate the time between a user's consecutive purchases and adjust your document structure to include these intervals, since you're working with MongoDB 3.6.0.

First, let's assume your purchase collection has documents that look like this (feel free to tweak this to match your actual schema):

{
  "_id": ObjectId("60d21b4667d0d8992e610c85"),
  "userId": "user_123",
  "purchaseTime": ISODate("2024-05-20T09:15:00Z"),
  "product": "Wireless Headphones",
  "amount": 99.99
},
{
  "_id": ObjectId("60d21b8967d0d8992e610c86"),
  "userId": "user_123",
  "purchaseTime": ISODate("2024-05-20T10:30:00Z"),
  "product": "Phone Case",
  "amount": 19.99
},
{
  "_id": ObjectId("60d21bc167d0d8992e610c87"),
  "userId": "user_456",
  "purchaseTime": ISODate("2024-05-21T14:45:00Z"),
  "product": "Bluetooth Speaker",
  "amount": 49.99
}

Since MongoDB 3.6 doesn't have the later $windowFunctions feature, we'll use a combination of aggregation stages like $sort, $group, $zip, and $map to get the job done. Here's a complete aggregation pipeline that calculates both seconds and minutes between consecutive purchases, then adjusts the document structure:

db.purchases.aggregate([
  // Step 1: Sort records by user and purchase time to ensure order
  {
    $sort: {
      userId: 1,
      purchaseTime: 1
    }
  },
  // Step 2: Group all purchases by user into an ordered array
  {
    $group: {
      _id: "$userId",
      purchases: {
        $push: {
          purchaseId: "$_id",
          purchaseTime: "$purchaseTime",
          product: "$product",
          amount: "$amount"
          // Add any other fields you need to retain here
        }
      }
    }
  },
  // Step 3: Prepare pairs of consecutive purchases + isolate the first purchase
  {
    $addFields: {
      purchasePairs: {
        $zip: {
          inputs: ["$purchases", { $slice: ["$purchases", 1, { $size: "$purchases" }] }],
          useLongestLength: false
        }
      },
      firstPurchase: { $arrayElemAt: ["$purchases", 0] }
    }
  },
  // Step 4: Calculate time intervals and build the processed purchase array
  {
    $addFields: {
      processedPurchases: {
        $concatArrays: [
          // First purchase has no prior purchase, so intervals are null
          [{
            $mergeObjects: ["$firstPurchase", {
              timeSinceLastPurchaseSec: null,
              timeSinceLastPurchaseMin: null
            }]
          }],
          // Calculate intervals for subsequent purchases
          {
            $map: {
              input: "$purchasePairs",
              as: "pair",
              in: {
                $mergeObjects: ["$$pair.1", {
                  timeSinceLastPurchaseSec: {
                    $divide: [
                      { $subtract: ["$$pair.1.purchaseTime", "$$pair.0.purchaseTime"] },
                      1000 // Convert milliseconds to seconds
                    ]
                  },
                  timeSinceLastPurchaseMin: {
                    $divide: [
                      { $subtract: ["$$pair.1.purchaseTime", "$$pair.0.purchaseTime"] },
                      60000 // Convert milliseconds to minutes
                    ]
                  }
                }]
              }
            }
          }
        ]
      }
    }
  },
  // Step 5: Unwind the processed array back to individual documents
  {
    $unwind: "$processedPurchases"
  },
  // Step 6: Restructure the document to put userId back at the top level
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: ["$processedPurchases", { userId: "$_id" }]
      }
    }
  },
  // Optional: Re-sort for readability
  {
    $sort: { userId: 1, purchaseTime: 1 }
  }
])

Let's break down what each stage does: #

• $sort: Ensures that all purchases for the same user are ordered chronologically, which is critical for calculating accurate intervals.
• $group: Collects all purchases per user into an array—since we sorted first, this array stays in time order.
• $addFields: Creates pairs of consecutive purchases (current + next) using $zip, and pulls out the first purchase separately (since it has no prior purchase to compare to).
• $concatArrays: Combines the first purchase (with null intervals) and the processed subsequent purchases (with calculated intervals) into one complete array.
• $unwind: Expands the array back into individual documents, so you have one document per purchase, just like your original collection.
• $replaceRoot: Adjusts the final document structure to include userId at the top level, making it consistent with your original schema plus the new interval fields.

Customization Tips: #

• If your original documents have different fields, just update the $push stage in the $group step to include the fields you want to keep.
• If you prefer to set the first purchase's interval to 0 instead of null, just replace null with 0 in the $mergeObjects for the first purchase.
• You can round the minute/second values using $round if you don't need decimal precision—for example: $round: [{ $divide: [...], 2 }] to round to two decimals.

After running this pipeline, your output documents will look like this (for the first user's second purchase):

{
  "purchaseId": ObjectId("60d21b8967d0d8992e610c86"),
  "purchaseTime": ISODate("2024-05-20T10:30:00Z"),
  "product": "Phone Case",
  "amount": 19.99,
  "timeSinceLastPurchaseSec": 4500,
  "timeSinceLastPurchaseMin": 75,
  "userId": "user_123"
}

That should give you exactly what you need—adjusted documents with the time intervals between consecutive user purchases!

内容的提问来源于stack exchange，提问作者Pedropablo López Amaya