I have a simple database with radiation readings from the PRM-8000. I can load all of values into the database. What I can't do is get the max and min values at each time per day. I've seen examples where people use first and last value in groups but it isn't working with my set.
My table:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| r_time | time | YES | | NULL | |
| r_date | date | YES | | NULL | |
| r_value | mediumint(9) | YES | | NULL | |
| r_label | char(3) | YES | | NULL | |
| r_location | varchar(128) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
I can get MIN and MAX values per date just fine:
select r_date, MIN(r_value), MAX(r_value) FROM readings GROUP BY r_date;
+------------+--------------+--------------+
| r_date | MIN(r_value) | MAX(r_value) |
+------------+--------------+--------------+
| 2016-01-20 | 0 | 22 |
| 2016-01-21 | 0 | 26 |
| 2016-01-22 | 3 | 23 |
| 2016-01-23 | 2 | 23 |
| 2016-01-24 | 2 | 24 |
| 2016-01-25 | 2 | 23 |
| 2016-01-26 | 1 | 23 |
| 2016-01-27 | 1 | 23 |
| 2016-01-28 | 1 | 23 |
| 2016-01-29 | 2 | 23 |
| 2016-01-30 | 2 | 22 |
| 2016-01-31 | 1 | 25 |
| 2016-02-01 | 3 | 23 |
| 2016-02-02 | 3 | 23 |
| 2016-02-03 | 3 | 23 |
| 2016-02-04 | 2 | 24 |
| 2016-02-05 | 2 | 22 |
| 2016-02-06 | 2 | 22 |
| 2016-02-07 | 3 | 25 |
| 2016-02-08 | 2 | 22 |
| 2016-02-09 | 3 | 21 |
| 2016-02-10 | 2 | 22 |
| 2016-02-11 | 2 | 23 |
| 2016-02-12 | 2 | 23 |
| 2016-02-13 | 2 | 24 |
| 2016-02-14 | 2 | 26 |
| 2016-02-15 | 3 | 24 |
| 2016-02-16 | 0 | 23 |
| 2016-02-17 | 3 | 24 |
| 2016-02-18 | 2 | 29 |
| 2016-02-19 | 2 | 23 |
| 2016-02-20 | 2 | 24 |
| 2016-02-21 | 1 | 23 |
| 2016-02-22 | 1 | 23 |
| 2016-02-23 | 1 | 23 |
| 2016-02-24 | 2 | 24 |
| 2016-02-25 | 1 | 23 |
| 2016-02-26 | 2 | 23 |
| 2016-05-27 | 7 | 12 |
+------------+--------------+--------------+
However I can't get the times the mins and max's occured. I have removed duplicate lines for readings, and as far as I understand with MySQL, I need to partiton by date and get the min and max. Any ideas? This is the kind of query someone would use to get the min and max temperature which occurred at each time at each day.Do I need to JOIN from two tables with max and min or else something?
Here is some sample data for one day:
+-------+----------+------------+---------+---------+------------+ | Id | r_time | r_date | r_value | r_label | r_location | +-------+----------+------------+---------+---------+------------+ | 40892 | 04:46:00 | 2016-02-18 | 29 | CPM | NULL | | 40838 | 03:52:00 | 2016-02-18 | 25 | CPM | NULL | | 41309 | 11:43:00 | 2016-02-18 | 23 | CPM | NULL | | 41524 | 15:18:00 | 2016-02-18 | 22 | CPM | NULL | | 41176 | 09:30:00 | 2016-02-18 | 21 | CPM | NULL | +-------+----------+------------+---------+---------+------------+ +-------+----------+------------+---------+---------+------------+ | Id | r_time | r_date | r_value | r_label | r_location | +-------+----------+------------+---------+---------+------------+ | 41343 | 12:17:00 | 2016-02-18 | 2 | CPM | NULL | | 40698 | 01:32:00 | 2016-02-18 | 2 | CPM | NULL | | 40694 | 01:28:00 | 2016-02-18 | 3 | CPM | NULL | | 41308 | 11:42:00 | 2016-02-18 | 3 | CPM | NULL | | 41315 | 11:49:00 | 2016-02-18 | 3 | CPM | NULL | +-------+----------+------------+---------+---------+------------+
And this is a sample output which provides the date, minimum radiation value with time recorded, and then the maximum radiation value with time recorded.
+------------+--------+----------+--------+----------+ | r_date | maxval | maxtime | minval | mintime | +------------+--------+----------+--------+----------+ | 2016-01-20 | 22 | 19:48:00 | 0 | 18:45:00 | | 2016-01-20 | 22 | 19:48:00 | 0 | 18:47:00 | | 2016-01-21 | 26 | 17:42:00 | 0 | 18:18:00 | | 2016-01-22 | 23 | 05:56:00 | 3 | 05:14:00 | | 2016-01-22 | 23 | 05:56:00 | 3 | 08:39:00 | | 2016-01-22 | 23 | 05:56:00 | 3 | 17:08:00 | | 2016-01-22 | 23 | 05:56:00 | 3 | 17:45:00 | | 2016-01-22 | 23 | 05:56:00 | 3 | 21:02:00 |
Aucun commentaire:
Enregistrer un commentaire