MongoDB中用户平均响应时长的计算与存储最优方案咨询

Hey there! Let's break down the best approaches to handle this problem—balancing accuracy, resource efficiency, and real-time visibility for your user profiles. Here are my top recommendations:

1. Incremental Calculation (Most Resource-Friendly, Recommended) #

Instead of recalculating the average from scratch every time (or via Cron), maintain running totals and counts in your users schema. This turns every update into an O(1) operation, which is super lightweight.

Step 1: Update the Users Schema #

Add two new fields to track cumulative data:

var users = new Schema({
  _id: ObjectId,
  name: String,
  averageResponseTime: Number,
  totalResponseTime: Number, // Sum of all response times
  responseCount: Number      // Total number of responses
});

Step 2: Update on New Response #

Whenever a new userResponseTime document is created, update the corresponding user's totals and recalculate the average in a single database operation:

// When a new response is recorded
const recordResponseTime = async (userId, respondToUserId, responseTime) => {
  // First, create the response time record
  await UserResponseTime.create({
    userId,
    respondToUserId,
    responseTime,
    createdAt: new Date() // Add a createdAt field if you don't have one already
  });

  // Then update the user's cumulative data and average
  await User.updateOne(
    { _id: userId },
    [
      {
        $set: {
          // Initialize totals to 0 if they don't exist
          totalResponseTime: { $add: ["$totalResponseTime", responseTime] },
          responseCount: { $add: ["$responseCount", 1] }
        }
      },
      {
        $set: {
          averageResponseTime: { $divide: ["$totalResponseTime", "$responseCount"] }
        }
      }
    ],
    { upsert: true } // Handle users with no prior responses
  );
};

This way, you never have to scan all response records for a user—just update the totals and average in one go.

2. Optimized Cron Job (If You Prefer Batch Processing) #

If you want to stick with a Cron-based approach but reduce resource usage, don't recalculate for every user. Only target users who have new response times since the last Cron run.

Cron Job Logic #

// Run every 5 minutes
const updateAverageResponseTimes = async () => {
  // Get users who have new responses in the last 5 minutes
  const recentlyActiveUserIds = await UserResponseTime.find({
    createdAt: { $gte: new Date(Date.now() - 5 * 60 * 1000) }
  }).distinct('userId');

  // Calculate and update average only for these users
  for (const userId of recentlyActiveUserIds) {
    const avgResult = await UserResponseTime.aggregate([
      { $match: { userId: userId } },
      { $group: { _id: null, avgTime: { $avg: '$responseTime' } } }
    ]);

    if (avgResult.length > 0) {
      await User.updateOne(
        { _id: userId },
        { averageResponseTime: avgResult[0].avgTime }
      );
    }
  }
};

This cuts down on unnecessary computations by only processing users who actually have new data.

3. Hybrid Batch + Incremental (For High Traffic) #

For applications with extremely high concurrency, combine incremental updates with a periodic batch sync. Here's how:

• Use the incremental approach to keep averageResponseTime up-to-date for most cases.
• Run a daily/weekly Cron job to recalculate the average from scratch for all users, fixing any discrepancies that might arise from edge cases (like failed updates).

Key Considerations #

• Edge Cases: Handle users with no responses by setting averageResponseTime to null or showing "No data yet" on their profile instead of 0.
• Performance: The incremental approach is by far the most efficient—no full table scans, no heavy aggregations. It’s perfect for most use cases.
• Real-Time Visibility: If you need the average to update instantly when a new response is sent, incremental is the way to go. The optimized Cron will have a 5-minute delay, which might be acceptable depending on your use case.

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