0

From reading various articles out there, I believe this should be possible, but I'm not sure where exactly to start.

This is what I'm trying to do:

I want to run a query, where it finds all documents createdate within the last 24 hours, and groups all of them by hour, and since each hour document has a AVG "PM10" value and last 24 hours MAX_PM10 value,last 24 hours AVG_PM10_value.I want to run a query Iam getting each hour AVG_PM10 value remining how to calculate each hour avg_PM10 value and total_PM10_avg value ,toatal_PM10_max value please any one suggest me

Here's a sample of the collection:

    {
        "_id" : ObjectId("5ad768309ee4f03480792b20"),
        "CREATEDATE" : ISODate("2018-04-25T15:45:52.779Z"),
        "PM10" : 176.024749755859,
        "PM2DOT5" : 60.0020217895508,
    }
 {
        "_id" : ObjectId("5ad768309ee4f03480792b30"),
        "CREATEDATE" : ISODate("2018-04-25T16:45:52.779Z"),
        "PM10" : 176.024749755859,
        "PM2DOT5" : 60.0020217895508,
    }

And the aggregation query:

    db.collection1.aggregate(
     {$match: {
       CREATEDATE: {$gt: new Date(new Date(ISODate().getTime() - 1000*60*60*24))}
     }},
     {$project: {  "h":{"$hour":"$CREATEDATE"}, "PM10":1 }},
     {$group:{ 
       "_id":"$h",
       "Max_PM10": {"$max":"$PM10"},
       "PM10": {$avg:"$PM10"} 
     }}
   ])
Expected output:

    {
        "Hour":15,
        "AVG_PM10_VALUE":154.64,

    }
   {
        "Hour":16,
        "AVG_PM10_VALUE":157.64,

    }


  "TOTAL_PM10_MAX_VALUE":157.89,
  "TOTAL_PM10_AVG_VALUE":156.14
hema g
  • 21
  • 5
  • So what is the problem? You're telling us what you want, and you're showing us what you have written, so now tell us what is wrong with it exactly – Neil Lunn Apr 26 '18 at 06:35
  • I want to run a query Iam getting only each hour PM10 value .remining "LAST_24HOURS_PM10_MAX_VALUE":177.89, "LAST_24HOURS_PM10_AVG_VALUE":144.67 was not found please suggest me – hema g Apr 26 '18 at 06:43
  • I've tried to explain to you before that we don't understand what you are asking because you are not actually asking anything. What does `""LAST_24HOURS_PM10_AVG_VALUE"` mean? What is "last 24 hours Average Value"? And how can that appear "grouped by hour"? Are you expecting it to be the same for every hour returned? We just don't know. – Neil Lunn Apr 26 '18 at 06:48
  • how to calculate each hour avg_PM10 value and all document avg_pm10 value and Max_pm10 value – hema g Apr 26 '18 at 06:53
  • @Neil Lunn how to calculate each hour avg_PM10 value and total_PM10_avg value ,toatal_PM10_max value – hema g Apr 26 '18 at 06:55
  • So you want the same number in every document in the output then? i.e `{ "hour": 1, "max": 2. "last24avg": 4 }, { "hour": 2, "max": 6, "last24avg": 4 }` etc. So the "all results average" is `4` and it appears in every result. – Neil Lunn Apr 26 '18 at 06:56
  • Iam expecting out pur is{ "hour"1,"avg_pm10":154.23,"total_avg_pm10":120.87,"Total_max_pm10":186.8 } – hema g Apr 26 '18 at 07:02
  • @ Neil Lunn { "hour": 1, "max": 2. "last24avg": 4 }, { "hour": 2, "max": 6, "last24avg": 4 } etc is write please suggest me – hema g Apr 26 '18 at 07:05
  • 1
    You cannot have a "total from all documents" as a value within "each document". Grouping just does not work like that. If you want it to appear that way in your final result, then run another query to get that "total average" and then alter each document obtained from this one to include that number. Can't be done in a single aggregation pipeline. Well not in any scalable way anyhow. Run two queries instead. – Neil Lunn Apr 26 '18 at 07:12
  • Does this answer your question? [MongoDB: Only fetch documents created in the last 24hrs?](https://stackoverflow.com/questions/13314678/mongodb-only-fetch-documents-created-in-the-last-24hrs) – carkod Nov 01 '20 at 03:19

0 Answers0