![]() Select date_trunc('day', t.date) as date, max(t.temp) as temp …a possible solution could be to use a WITH clause (or Common Table Expression ) to extract the maximum temperature per day and then join back to the original data for getting all the timestamps, like so: test=> with ttt as ![]() If you have an equivalent data-set in a traditional SQL database like PostgreSQL… test=> select * from temperatures order by date $tempMax refers to the maximum temperature field determined in the previous grouping stageįrom the filtered docs in variable maxOccur, only the date field is mapped to the resulting array using $$maxOccur.date as the mapping expression.$$i.temp refers to the temperature field of all elements in the items array which has variable name i here. ![]() Note that $ is used to reference root document fields whereas $$ is used to reference variables. This part of the query constructs a new field called tempMaxDates in the projection stage.įor that, the items field from the grouping stage is first filtered to get only the documents of the group where the temperature equals the maximum. Selecting only the max temperature per day can be easily achieved with a trivial $group aggregation pipeline stage, like this: db.temperatures.aggregate( The task now is to query for the maximal temperature for every day and the time(s), this temperature happened the day. Let’s assume you have the following documents with hourly measured temperatures in a collection called temperatures in your MongoDB… [ Query task: initial data situation & first trivial approach Bonus: SQL solution based on PostgreSQL.Projecting non-aggregated fields from collected raw data.Solution: projecting non-aggregated fields to a group using $push, $map and $filter.Query task: initial data situation & first trivial approach.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |