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.

CodePudding user response：

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...

CodePudding user response：

If you multiple identical Min values or MAX values for that matter, you need to adjust the subqueries

Schema (MySQL v8.0)

CREATE TABLE data (
  local_time datetime,
  temp INT
,INDEX(local_time)
);
INSERT INTO data  VALUES ('2022-12-01 12:20' , 10);
INSERT INTO data  VALUES ('2022-12-01 12:40' ,20);

INSERT INTO data  VALUES ('2022-12-02 19:00' , 12);
INSERT INTO data  VALUES ('2022-12-02 20:05' , 8);

INSERT INTO data  VALUES ('2022-12-03 22:22' , 14);
INSERT INTO data  VALUES ('2022-12-03 22:30' , 16);

Query #1

SELECT 
    DATE(d.local_time) AS local_time,
    MAX(d.temp) AS max,
    MAX((SELECT local_time FROM data WHERE DATE(local_time) = DATE(d.local_time) ORDEr BY temp DESC LIMIT 1)) max_datetime,
    MIN(d.temp) AS min,
    MIN((SELECT local_time FROM data WHERE DATE(local_time) = DATE(d.local_time) ORDEr BY temp ASC LIMIT 1)) min_datetime,
    AVG(d.temp) AS avg
FROM
    data d
  GROUP BY DATE(d.local_time);

View on DB Fiddle