在SQL中查找给定日期的最高温度

Question

My tables:

hourly_weather                 electrical_readings
----------------               -----------------------
meter | time_read | temp       meter | time      | kwh
----------------               -----------------------
1       1316044800  55         1       1316136250  19.24
1       1316138400  56         1       1316044320  18.29
(...)                          (...)

I want to retrieve two important values from this data:

1) I want the total KW for a given day

2) And I want the max temperature for that day

The query I'm using takes WAYYYY too long to run but I can't think of another way to do it. Like, several hours for 100,000 rows of data in both tables.

SELECT * FROM (
SELECT * , SUM(kwh) AS sumkwh, 
           DATE( FROM_UNIXTIME( r.time_read ) ) AS datex, 
           UNIX_TIMESTAMP( DATE( FROM_UNIXTIME( r.time_read ) ) ) AS datey, 
           (
               SELECT MAX( temp )
               FROM hourly_weather hw
               WHERE hw.meter = 1
                 AND time_read >= datey
                 AND time_read < datey + 86400
           ) AS temp
FROM electrical_readings r
WHERE id = 1
GROUP BY datex
) as t1
WHERE t1.temp != '';

Answer 1

SELECT DATE(FROM_UNIXTIME(r.time_read)) AS datex, 
  SUM(r.kwh) AS sumkwh, MAX(hw.temp) AS temp
FROM electrical_readings r
LEFT OUTER JOIN hourly_weather hw
  ON DATE(FROM_UNIXTIME(r.time_read)) = DATE(FROM_UNIXTIME(hw.time_read)) 
  AND hw.meter = 1
WHERE r.id = 1
GROUP BY datex
HAVING temp IS NOT NULL

This will still be a problem for performance, because this uses expressions for the joins. It therefore has to read every row of both tables, to evaluate the expressions before it can tell if the join is satisfied.

It would therefore be much better if you could add an extra column to both tables for the date (with no time) and index those columns.

ALTER TABLE electrical_readings ADD COLUMN date_read DATE, ADD KEY (date_read);
UPDATE electrical_readings SET date_read = DATE(FROM_UNIXTIME(time_read));

ALTER TABLE hourly_weather ADD COLUMN date_read DATE, ADD KEY (date_read);
UPDATE hourly_weather SET date_read = DATE(FROM_UNIXTIME(time_read));

SELECT r.date_read, 
  SUM(r.kwh) AS sumkwh, MAX(hw.temp) AS temp
FROM electrical_readings r
LEFT OUTER JOIN hourly_weather hw
  ON r.date_read = hw.date_read 
  AND hw.meter = 1
WHERE r.id = 1
GROUP BY r.date_read
HAVING temp IS NOT NULL

In any case, adding SELECT * to either of these queries is not a good idea, because the results will be arbitrary.

---

Re your comment, sorry, the sum is multiplied by the number of matching rows in hourly_weather.

We can compensate by doing the aggregate for hourly_weather in a derived table subquery.

SELECT r.date_read, 
  SUM(r.kwh) AS sumkwh, hw.temp
FROM electrical_readings r
LEFT OUTER JOIN (
  SELECT date_read, MAX(temp) AS temp
  FROM hourly_weather
  WHERE meter = 1
  GROUP BY date_read) AS hw
    ON r.date_read = hw.date_read 
WHERE r.id = 1
GROUP BY r.date_read
HAVING temp IS NOT NULL

It would be good to create an index on hourly_weather:

ALTER TABLE hourly_weather ADD KEY (date_read, meter, temp);