I have a database which contains forecasts for 2 different locations for three days. For every day there are many forecasts every hour. I want to calculate the average temperature for every location for the last 3 forecasts of every day. Location is saved as "location_id", the day is in the column with name "applicable_date" and the "created" contains the the hours.
Here you can see an export

{
"location": "London",
"weather_state_name": "Heavy Rain",
"weather_state_abbr": "hr",
"wind_direction_compass": "WSW",
"created": "2021-09-27T00:59:15.571283Z",
"applicable_date": "2021-10-05",
"min_temp": "11.58",
"max_temp": "14.38",
"the_temp": "13.24",
"wind_speed": "5.312723693629206",
"wind_direction": "237.0",
"air_pressure": "996.0",
"humidity": "70",
"visibility": null,
"predictability": "77"
},
{
"location": "London",
"weather_state_name": "Light Cloud",
"weather_state_abbr": "lc",
"wind_direction_compass": "WNW",
"created": "2021-09-28T00:59:14.295872Z",
"applicable_date": "2021-10-06",
"min_temp": "7.83",
"max_temp": "13.27",
"the_temp": "12.48",
"wind_speed": "2.709178398154776",
"wind_direction": "298.0",
"air_pressure": "1022.0",
"humidity": "45",
"visibility": null,
"predictability": "70"
},
{
"location": "London",
"weather_state_name": "Heavy Rain",
"weather_state_abbr": "hr",
"wind_direction_compass": "S",
"created": "2021-09-29T00:59:13.083990Z",
"applicable_date": "2021-10-07",
"min_temp": "9.36",
"max_temp": "15.19",
"the_temp": "15.19",
"wind_speed": "2.5911178716296828",
"wind_direction": "183.99999999999997",
"air_pressure": "1021.0",
"humidity": "57",
"visibility": null,
"predictability": "77"
},

CodePudding user response：

MySql supports window functions since 8.0. To get last 3 items for location_id and applicable_datetry

select *
from (
   select *,
     row_number() over(partition by location_id, applicable_date order by created desc) rn
) t
where rn <= 3

Apply an aggregation as needed.