mognoDB calculate average over a date range but my date is in ISO format and I need to match a day i-CodePudding

Goal ---> I want to calculate the daily average and also the average for a specific date range.

My date is in ISO format, so when I do the following aggregation it matches the exact date and then the average is wrong.

const avg = await Model.aggregate([
  {
    $group: {
      _id: '$date',
      avg: {$avg: '$quantityInLitres'},
    }
  }
])

Documents --->

[
  {
    date: 2022-05-25T18:30:00.000 00:00,
    quantityInLitres: 30
  },
  {
    date: 2022-07-09T11:32:11.543 00:00,
    quantityInLitres: 10
  },
  {
    date: 22022-07-09T11:32:35.747 00:00,
    quantityInLitres: 20
  }
]

Here average should be (30 10 20 / 2) = 30 but it returns 20.

Also How to calculate a monthly average.

I really appreciate your time & Thanks In Advance!!!

CodePudding user response：

To elaborate on why your original query returns an average of 20 rather than an average of 30 like you're expecting, It's because the average of those three numbers IS 20.

But I'm assuming the behaviour you want is to sum the litres for each day and then calculate the average.

Note that my below answer takes into consideration that for whatever reason you're needing to store the date as a string however, that string representing a date is assumed always valid. If the date is invalid the below solution will error which is the case in your example data set e.g. date: "22022-07-09T11:32:35.747 00:00".

So, to get the average for the entire data set you can use:

const avg = await Model.aggregate([
  {$group: {
    _id: {year: {$year: '$date'}, day: {$dayOfYear:  '$date'}},
    sum: {$sum: '$quantityInLitres'}
  }},
  {$group: {
    _id: null,
    avg: {$avg: '$sum'}
  }}
]);

Where the first $group stage in the pipeline is grouping by both the year and the day, and summing those values. And the second $group stage in the pipeline is grouping everything and then calculating the average.

And to get the average for each month of each year, you can use:

const avg = await Model.aggregate([
  {$group: {
    _id: {year: {$year: '$date'}, day: {$dayOfYear: '$date'}},
    sum: {$sum: '$quantityInLitres'},
    date: {$first: '$date'}
  }},
  {$group: {
    _id: {year: '$_id.year', month: {$month: '$date'}},
    avg: {$avg: '$sum'}
  }}
]);

Where the first $group stage in the pipeline is doing the same as the first query but also keeping the date field. And then the second $group stage in the pipeline is grouping by the year and the month, and then calculating the average.