MongoDB聚合：计算车辆利用率时如何加入怠速时间

Got it, let's tweak your MongoDB aggregation pipeline to include the time_idling field and calculate the correct fleet utilization percentage as per your formula.

Modified $group Stage #

Here's the updated aggregation stage that combines both time_moving and time_idling into your calculation:

{
  "$group": {
    "_id": {
      "customer": "$customer",
      "date": "$date"
    },
    "percentage": {
      "$avg": {
        "$divide": [
          { "$add": ["$time_moving", "$time_idling"] }, // Sum driving + idling time
          864 // Matches your formula: (86400 seconds/day) / 100
        ]
      }
    }
  }
}

Key Changes Explained #

• Combine Active Time: We swapped the single $time_moving value with the $add operator to sum both $time_moving and $time_idling—this gives the total active time for each document, which is what your formula requires.
• Preserve Formula Logic: The denominator stays 864 since that's exactly 86400 / 100, aligning perfectly with how you want to calculate the utilization percentage.

Optional: Handle Missing Fields #

If some documents might be missing time_moving or time_idling (e.g., fields are null), use $ifNull to treat missing values as 0 and avoid calculation errors:

{
  "$group": {
    "_id": {
      "customer": "$customer",
      "date": "$date"
    },
    "percentage": {
      "$avg": {
        "$divide": [
          {
            "$add": [
              { "$ifNull": ["$time_moving", 0] },
              { "$ifNull": ["$time_idling", 0] }
            ]
          },
          864
        ]
      }
    }
  }
}

This ensures your aggregation won't break if any documents have missing time fields, and it'll still compute an accurate average utilization percentage per customer per date.

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