将多个SQL查询组合到MySQL中的单个查询中

Question

MySQL Version 15.1 Distrib 10.0.7-MariaDB, running on Debian-linux.

I'm collecting 5-minute power generation data into a MySQL database and want to modify a daily report SQL query. A typical days data in the GenerationData table would look like:

--------++------------+---------------------+
| id     | Generation | DispatchInterval    |
+--------+------------+---------------------+
| 321888 |        0.0 | 2014-01-28 22:15:00 |
| 321887 |        0.0 | 2014-01-28 22:10:00 |
| 321886 |      72 .0 | 2014-01-28 22:05:00 |
| 321885 |      99.37 | 2014-01-28 22:00:00 |
.       .       .       
.       .       .       
| 321679 |     51.375 | 2014-01-28 10:50:00 |
| 321678 |     30.375 | 2014-01-28 10:45:00 |
| 321677 |         12 | 2014-01-28 10:40:00 |
| 321676 |        0.0 | 2014-01-28 10:35:00 |
| 321675 |        0.0 | 2014-01-28 10:30:00 |
+--------+------------+---------------------+

So each day when there was any generation at all there will be 288 records starting at "2014-01-28 00:00:00" and finishing at "2014-01-28 23:55:00", each with a unique id and Generation reading (and some other fields I've excluded for clarity)

I create a daily generation report with a query like

select date(DispatchInterval) as RunDate, max(Generation) as MaxGeneration,      
sum(Generation)/12 as MWH 
from GenerationData 
group by date(DispatchInterval) 
order by date(DispatchInterval) desc LIMIT 0, 4;

+------------+---------------+---------+
| RunDate    | MaxGeneration | MWH     |
+------------+---------------+---------+
| 2014-01-28 |        494.25 | 4334.12 |
| 2014-01-27 |        506.25 | 3032.56 |
| 2014-01-17 |         466.5 | 6178.75 |
| 2014-01-16 |       500.625 | 7733.72 |
+------------+---------------+---------+

What I want to do is extend the query to include Startup and Shutdown times for each day, for example:

+------------+---------------+---------+----------+----------+
| RunDate    | MaxGeneration |   MWH   | Startup  | Shutdown |
+------------+---------------+---------+----------+----------+
| 2014-01-28 |        494.25 | 4334.12 | 10:40:00 | 22:10:00 |
| 2014-01-27 |        506.25 | 3032.56 | 06:25:00 | 23:30:00 |
| 2014-01-17 |         466.5 | 6178.75 | 11:40:00 | 22:05:00 |
| 2014-01-16 |       500.625 | 7733.72 | 03:15:00 | 19:55:00 |
+------------+---------------+---------+----------+----------+

There may be multiple Startups and Shutdowns each day but I'd be happy with the first Startup and last Shutdown. I can get the Startup or Shutdown times (but not with same query) by looking for the transition of the Generation value from <2 to >2 (to allow for noise in the readings) with

select date(a.DispatchInterval), time(b.DispatchInterval) as StartTime
from GenerationData a
join GenerationData b on 
    b.DispatchInterval=DATE_ADD(a.DispatchInterval,INTERVAL 5 MINUTE)
    and b.Generation>2 and a.Generation<2
group by date(a.DispatchInterval)
order by date(a.DispatchInterval) desc limit 0,4

+--------------------------+---------------------+
| date(a.DispatchInterval) | StartTime           |
+--------------------------+---------------------+
| 2014-01-28               | 10:40:00            |
| 2014-01-27               | 06:25:00            |
| 2014-01-15               | 06:00:00            |
| 2014-01-14               | 09:55:00            |
+--------------------------+---------------------+

but I've had no luck with various joins, perhaps because of the "Group by" clause for "MWH". Obviously I am an SQL noob.

So my question is how can I combine the queries (or perhaps come up with a completely different strategy) to achieve the desired result? The query will be executed from a PHP script and I have considered using PHP to detect startup and shutdown times and add them to the response, but I'd prefer to exhaust all SQL options first.

Any hints you may be able to provide would be much appreciated.

Answer 1

Solved it! I was looking for complexity where there was no need for it, joins were completely unnecessary in this case but having gone down that path I was blind to the obvious. The simple query was

select date(DispatchInterval) as RunDate, max(Generation) as MaxGeneration,
sum(Generation)/12 as MWH,
time(min(DispatchInterval)) as StartUp,
time(max(DispatchInterval)) as Shutdown
from GenerationData group by RunDate desc

+------------+---------------+------------+----------+----------+
| RunDate    | MaxGeneration | MWH        | StartUp  | Shutdown |
+------------+---------------+------------+----------+----------+
| 2014-02-02 |       499.875 |   1025.375 | 00:00:00 | 15:10:00 |
| 2014-01-28 |        494.25 |   4334.125 | 00:00:00 | 23:55:00 |
| 2014-01-27 |        506.25 |  3032.5625 | 00:00:00 | 23:55:00 |
| 2014-01-17 |         466.5 |    6178.75 | 00:00:00 | 23:55:00 |
| 2014-01-16 |       500.625 | 7733.71875 | 00:00:00 | 23:55:00 |
+------------+---------------+------------+----------+----------+

Sometimes you've just got to go away and do something else for a while.