MongoDB中基于ISO格式日期计算日均值、指定日期范围均值及月均值的技术问题

Hey there! Let's sort out your aggregation issues step by step. The problem with your current code is that you're grouping by the full ISO date (including hours, minutes, and seconds), so entries from the same day but different times get split into separate groups. That's why your average calculation is off. Let's break down the solutions:

Per-Day Quantity Averages #

To get the average quantity for each calendar day, you need to truncate the date field to just the day component. Use MongoDB's $dateTrunc operator to strip out the time portion:

const dailyAvg = await Model.aggregate([
  {
    $group: {
      _id: { $dateTrunc: { date: '$date', unit: 'day' } }, // Groups by full date (YYYY-MM-DD)
      avgQuantity: { $avg: '$quantityInLitres' }
    }
  },
  { $sort: { _id: 1 } } // Optional: sorts results by date ascending
])

For your sample data, this will return two groups:

• _id: 2022-05-25T00:00:00.000Z with avgQuantity: 30
• _id: 2022-07-09T00:00:00.000Z with avgQuantity: 15

Overall Average of Daily Averages #

If you want the average of all those daily averages (like your intended calculation of combining daily values), add a second $group stage:

const overallDailyAvg = await Model.aggregate([
  // First, calculate each day's average
  {
    $group: {
      _id: { $dateTrunc: { date: '$date', unit: 'day' } },
      dailyAvg: { $avg: '$quantityInLitres' }
    }
  },
  // Then, average those daily values
  {
    $group: {
      _id: null,
      overallAvg: { $avg: '$dailyAvg' }
    }
  }
])

To narrow down to a date range, add a $match stage at the start to filter records before grouping.

Daily Averages in a Date Range #

For example, get daily averages between July 1, 2022 and July 10, 2022:

const dateRangeDailyAvg = await Model.aggregate([
  {
    $match: {
      date: {
        $gte: new Date('2022-07-01T00:00:00.000Z'),
        $lt: new Date('2022-07-11T00:00:00.000Z') // Use $lt to avoid including July 11
      }
    }
  },
  {
    $group: {
      _id: { $dateTrunc: { date: '$date', unit: 'day' } },
      avgQuantity: { $avg: '$quantityInLitres' }
    }
  },
  { $sort: { _id: 1 } }
])

Overall Average in a Date Range #

If you just want the average of all records in the range (not per day), skip the daily grouping:

const dateRangeOverallAvg = await Model.aggregate([
  {
    $match: {
      date: {
        $gte: new Date('2022-07-01T00:00:00.000Z'),
        $lt: new Date('2022-07-11T00:00:00.000Z')
      }
    }
  },
  {
    $group: {
      _id: null,
      overallAvg: { $avg: '$quantityInLitres' }
    }
  }
])

This works almost identically to daily averages—just adjust the grouping to target month-level dates:

Using $dateTrunc #

const monthlyAvg = await Model.aggregate([
  {
    $group: {
      _id: { $dateTrunc: { date: '$date', unit: 'month' } }, // Groups by first day of the month
      avgQuantity: { $avg: '$quantityInLitres' }
    }
  },
  { $sort: { _id: 1 } }
])

Using Year/Month as Group ID #

If you prefer a more explicit group key (like { year: 2022, month: 5 }):

const monthlyAvg = await Model.aggregate([
  {
    $group: {
      _id: {
        year: { $year: '$date' },
        month: { $month: '$date' }
      },
      avgQuantity: { $avg: '$quantityInLitres' }
    }
  },
  { $sort: { '_id.year': 1, '_id.month': 1 } }
])

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