CODEWITHSUNDEEP
node.jsmongodbgroup-byaggregation
Chicky
Chicky

Reputation: 1257

Mongo aggregate $group by each day?

I have a collection of history, and want to create the exports data base on that collection

[{
_id: "...",
value: 10,
at: ISODate("2021-24-06T00:01:02.023")
}, {
_id: ...,
value: 13,
at: ISODate("2021-24-06T00:04:11.211")
}, {
_id: ...,
value: 12,
at: ISODate("2021-24-06T09:11:31.182")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-07T01:33:31.723")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-15T09:32:44.983")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-16T10:43:22.083")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-16T14:43:22.083")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-17T04:25:12.021")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-18T20:13:22.083")
}, {
_id: ...,
value: 40,
at: ISODate("2021-24-19T18:41:22.083")
}]

I have to expose 3 options group by: hours, day, week

I'm trying use $group to calculate mix/max or avg of value in a day but don't know how to $group them to each hour/day/weeks data

Can I group by $cond ? Because it very flexible for all the case.

Example: Group by hours, first moment = 2021-24-06T00:00:00.000 Expect:

[{
  at: ISODate("2021-24-06T00:00:00.000"),
  max: 13,
  min: 10,
  avg: 21.5
}, {
  at: ISODate("2021-24-06T01:00:00.000"),
}, ..., {
  at: ISODate("2021-24-06T09:00:00.000"),
  max: 12,
  min: 12,
  avg: 12
}]

Group By = day

Expect:

[{
  at: ISODate("2021-24-06T00:00:00.000"),
  max: 13,
  min: 10,
  avg: 11.7
}, {
  at: ISODate("2021-24-07T00:00:00.000"),
  max: 40,
  min 40,
  avg: 40,
}]

It is my idea. Create the first moments of each group, compare at with first moment and last moment of group.

Ex: group by day, first moment of first group is at: ISODate("2021-24-06T00:00:00.000") -> last moment = ISODate("2021-24-06T23:59:59.999") -> Group all record belong to this group

second group: ISODate("2021-24-06T00:00:00.000") + 1day = ISODate("2021-24-07T00:00:00.000") -> last moment = ISODate("2021-24-07T23:59:59.999") -> Group all record belong to this group

But how to implement that

Upvotes: 3

Views: 2974

Answers (1)

csharpbd
csharpbd

Reputation: 4066

You can use $dateToString in the Group By $group clause to group your data based on Day & Hour. You can use $week in the Group By $group clause to group your data based on Week.

Please take a look at the below code:

Sample Data Sample Data

Group By Week

db.collData.aggregate([
  {
    $group : {
       _id : { $week: '$at' },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

Group By Week

Group By Day

db.collData.aggregate([
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$at" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

Group By Day

Group By Hour

db.collData.aggregate([
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%dT%H", date: "$at" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

Group By Hour

See more details about the $group

See more details about the $dateToString

See more details about the $week

Update using TimeZone in the query

You can use timezone in the $dateToString expression. Please check the below examples:

Time Zone added in the group by

db.collData.aggregate([
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$at", timezone: "-05:00" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

db.collData.aggregate([
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%d", date: "$at", timezone: "+07:00" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

You will find more about the expression in the link below including the timezone.

MongoDB $dateToString

timezone added in the hour example:

db.collData.aggregate([
  {
    $group : {
       _id : { $dateToString: { format: "%Y-%m-%dT%H", date: "$at", timezone: "+00:30" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

timezone added in the week example:

db.collData.aggregate([
  {
    $group : {
       _id : { $week: { date: '$at', timezone: "-05:00" } },
       averageValue: { $avg: "$value" },
       count: { $sum: 1 }
    }
  },
  {
    $sort : { averageValue: -1 }
  }
])

Upvotes: 6

Related Questions