如何编写聚合单查询：计算同SwitchID下LIGHTS状态平均时长差

Got it, let's build out that aggregation query to get the difference between the average LIGHTS OFF duration and average LIGHTS ON duration for each SwitchID. Here's the complete solution, including explanations for each stage:

db.lights.aggregate([
  // 1. Filter documents within your target date range
  {
    $match: {
      CREATE_DATE: {
        $gte: ISODate("2018-01-24T20:05:30.000Z"),
        $lt: ISODate("2018-02-24T20:05:30.000Z")
      }
    }
  },
  // 2. Sort documents by SwitchID and timestamp to ensure correct state order
  {
    $sort: { SwitchID: 1, CREATE_DATE: 1 }
  },
  // 3. Group all documents by SwitchID, preserving the ordered list of state changes
  {
    $group: {
      _id: "$SwitchID",
      stateChanges: { $push: { status: "$status", timestamp: "$CREATE_DATE" } }
    }
  },
  // 4. Unwind the stateChanges array with index to access the next state change
  {
    $unwind: {
      path: "$stateChanges",
      includeArrayIndex: "index"
    }
  },
  // 5. Fetch the next state change to calculate duration
  {
    $addFields: {
      nextState: {
        $arrayElemAt: ["$stateChanges", { $add: ["$index", 1] }]
      },
      currentStatus: "$stateChanges.status",
      currentTimestamp: "$stateChanges.timestamp"
    }
  },
  // 6. Filter out the last state change (no next state to calculate duration)
  {
    $match: { nextState: { $exists: true } }
  },
  // 7. Calculate how long the current state lasted (in milliseconds)
  {
    $addFields: {
      duration: { $subtract: ["$nextState.timestamp", "$currentTimestamp"] }
    }
  },
  // 8. Compute average duration per SwitchID and state
  {
    $group: {
      _id: { SwitchID: "$_id", status: "$currentStatus" },
      avgDuration: { $avg: "$duration" }
    }
  },
  // 9. Pivot data to get both OFF and ON averages in one document per SwitchID
  {
    $group: {
      _id: "$_id.SwitchID",
      avgOffDuration: {
        $max: { $cond: [{ $eq: ["$_id.status", "LIGHTS OFF"] }, "$avgDuration", 0] }
      },
      avgOnDuration: {
        $max: { $cond: [{ $eq: ["$_id.status", "LIGHTS ON"] }, "$avgDuration", 0] }
      }
    }
  },
  // 10. Calculate the final difference: OFF average minus ON average
  {
    $addFields: {
      offMinusOnAvgDuration: { $subtract: ["$avgOffDuration", "$avgOnDuration"] }
    }
  },
  // Optional: Convert durations to human-readable seconds (adjust as needed)
  {
    $addFields: {
      avgOffDurationSeconds: { $divide: ["$avgOffDuration", 1000] },
      avgOnDurationSeconds: { $divide: ["$avgOnDuration", 1000] },
      offMinusOnAvgDurationSeconds: { $divide: ["$offMinusOnAvgDuration", 1000] }
    }
  }
])

Key Stage Breakdown: #

• Sort & Group (Stages 2-3): Critical for ensuring state changes are processed in chronological order—without this, duration calculations will be incorrect.
• Unwind with Index: Lets us pair each state with the next one in the sequence using $arrayElemAt, so we can calculate how long each state persisted.
• Duration Calculation: Subtracts the current state's timestamp from the next state's timestamp to get the total time the current state was active.
• Pivoting: Uses $cond and $max to pull average durations for OFF and ON states into separate fields per SwitchID, making the final subtraction straightforward.
• Final Difference: Computes the exact value you need: average OFF duration minus average ON duration.

Note: Replace "status" with your actual state field name if it's different, and adjust the duration unit conversion (milliseconds to seconds/minutes/hours) in the optional stage based on your preferences.

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