I have a MySQL database with lots of entrys with columns like "local_time", "temp", "wind", etc.
What I want to archive is get in just one query the MIN value for temp, the MAX value for temp in each day with the datetime for min and max.
eg:
local_time | temp
2022-12-01 12:20 | 10
2022-12-01 12:40 |20
2022-12-02 19:00 | 12
2022-12-02 20:05 | 8
2022-12-03 22:22 | 14
2022-12-03 22:30 | 16
Result I wanted for query:
date | min | min_date | max | max_date| avg
2022-12-01 | 10 | datetime | 20 | datetime | avg_value
2022-12-02 | 8 | datetime | 12 | datetime | avg_value
2022-12-03 | 14 | datetime | 16 | datetime | avg_value
For now, I'm using this query:
select DATE(local_time) as local_time, MIN(temp) as max, MAX(temp) as min, AVG(temp) as avg FROM data GROUP BY DATE(local_time);
But I need the datetime (local_time) for the MIN value and MAX value, for every single day.
Any help is welcome.
For now, I'm using this query:
select DATE(local_time) as local_time, MIN(temp) as max, MAX(temp) as min, AVG(temp) as avg FROM data GROUP BY DATE(local_time);
But I need the datetime (local_time) for the MIN value and MAX value, for every single day.
I din't get it working in one go so I joined two queries: a group by query to get your min, max and average and one using window functions to get mindate for min and maxdate for max:
select a.local_date, a.min, b.mindatetime , a.max, b.maxdatetime, a.avg from
(select DATE(local_time) as local_date,
MIN(temp) as min,
MAX(temp) as max, AVG(temp) as avg
FROM data GROUP BY DATE(local_time)) a
join
(select distinct DATE(local_time) as local_date,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp asc) as mindatetime,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp desc) as maxdatetime
from data) b on a.local_date = b.local_date
https://www.db-fiddle.com/f/owZiLJuKhqc4DF6XYa3aJV/0
I guess the query with window functions leaves space for optimizing...
EDIT
Just wondered if a version only with window functions would perform better(It's nicer to look at anyway):
select distinct DATE(local_time) as local_date,
MIN(temp) OVER ( PARTITION BY DATE(local_time) ) as min,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp asc) as mindatetime,
MAX(temp) OVER ( PARTITION BY DATE(local_time) ) as max,
first_value(local_time) OVER ( PARTITION BY DATE(local_time) order by temp desc) as maxdatetime,
AVG(temp) OVER ( PARTITION BY DATE(local_time) ) as avg
from data