Mysql JOIN две таблицы на основе асинхронного времени и средних значений

Я записал значения с датчиков температуры и влажности, например:

mysql> SELECT date,valeur FROM temperature WHERE date >= NOW() - INTERVAL 2 HOUR;
+---------------------+--------+
| date                | valeur |
+---------------------+--------+
| 2013-09-26 11:30:40 |   25.2 |
| 2013-09-26 11:33:19 |   25.4 |
| 2013-09-26 11:34:12 |   25.5 |
| 2013-09-26 11:38:37 |   25.4 |
| 2013-09-26 11:39:30 |   25.4 |
| 2013-09-26 11:40:23 |   25.4 |
| 2013-09-26 11:43:02 |   25.4 |
| 2013-09-26 11:45:41 |   25.3 |
| 2013-09-26 11:47:33 |   25.3 |
| 2013-09-26 11:51:07 |   25.4 |
| 2013-09-26 11:51:52 |   25.3 |
| 2013-09-26 11:53:38 |   25.4 |
...
| 2013-09-26 12:26:20 |   25.5 |
| 2013-09-26 12:27:12 |   25.5 |
| 2013-09-26 12:32:30 |   25.4 |
| 2013-09-26 12:35:09 |   25.5 |
| 2013-09-26 12:36:02 |   25.5 |
| 2013-09-26 12:37:50 |   25.6 |
| 2013-09-26 12:40:27 |   25.6 |
| 2013-09-26 12:49:18 |   25.6 |
| 2013-09-26 12:52:54 |   25.6 |
...
| 2013-09-26 13:16:40 |   25.5 |
| 2013-09-26 13:17:33 |   25.6 |
| 2013-09-26 13:22:05 |   25.5 |
| 2013-09-26 13:22:51 |   25.5 |
| 2013-09-26 13:23:44 |   25.5 |
| 2013-09-26 13:24:37 |   25.5 |
| 2013-09-26 13:25:30 |   25.5 |
| 2013-09-26 13:28:09 |   25.6 |
+---------------------+--------+

mysql> SELECT date,valeur FROM humidite WHERE date >= NOW() - INTERVAL 2 HOUR;
+---------------------+--------+
| date                | valeur |
+---------------------+--------+
| 2013-09-26 11:30:40 |     78 |
| 2013-09-26 11:33:19 |     78 |
| 2013-09-26 11:34:12 |     78 |
| 2013-09-26 11:38:37 |     78 |
| 2013-09-26 11:39:30 |     78 |
| 2013-09-26 11:40:23 |     78 |
| 2013-09-26 11:43:02 |     79 |
| 2013-09-26 11:45:41 |     78 |
| 2013-09-26 11:47:33 |     78 |
| 2013-09-26 11:51:07 |     79 |
| 2013-09-26 11:51:52 |     79 |
| 2013-09-26 11:53:38 |     79 |
...
| 2013-09-26 12:22:48 |     78 |
| 2013-09-26 12:26:20 |     78 |
| 2013-09-26 12:27:12 |     78 |
| 2013-09-26 12:32:30 |     77 |
| 2013-09-26 12:35:09 |     77 |
| 2013-09-26 12:36:02 |     77 |
| 2013-09-26 12:37:50 |     78 |
| 2013-09-26 12:40:27 |     77 |
| 2013-09-26 12:43:06 |     78 |
| 2013-09-26 13:22:05 |     78 |
...
| 2013-09-26 13:22:51 |     78 |
| 2013-09-26 13:23:44 |     78 |
| 2013-09-26 13:24:37 |     78 |
| 2013-09-26 13:25:30 |     78 |
| 2013-09-26 13:28:09 |     78 |
+---------------------+--------+

Из этот пост я использовал эту команду mysql для соединения двух таблиц и получить одно значение за каждый час с 24 часов:

SELECT DATE_FORMAT(timeTable.minuteTime, '%Y-%m-%d %k:%i') date,
T2.valeur temp,
P2.valeur hum
FROM
(
    SELECT minuteTime.minuteTime minuteTime,
    ( SELECT MAX(date) FROM temperature WHERE date <= minuteTime.minuteTime AND date >= NOW() - INTERVAL 1 DAY) tempTime,
    ( SELECT MAX(date) FROM humidite WHERE date <= minuteTime.minuteTime AND date >= NOW() - INTERVAL 1 DAY) humTime
    FROM
    (
        SELECT DATE(date) + INTERVAL (HOUR(date) DIV 1 * 1) HOUR minuteTime
        FROM humidite
        WHERE date >= NOW() - INTERVAL 1 DAY AND date <= NOW()
        UNION SELECT DATE(date) + INTERVAL (HOUR(date) DIV 1 * 1) HOUR
        FROM temperature
        WHERE date >= NOW() - INTERVAL 1 DAY AND date <= NOW()
        GROUP BY 1
    ) minuteTime
) timeTable
LEFT JOIN temperature T2 ON T2.date = timeTable.tempTime
LEFT JOIN humidite P2 ON P2.date = timeTable.humTime
ORDER BY minuteTime ASC;

Это работает хорошо, но я не очень понимаю, как выбирается значение (случайное в интервале?). Вместо этого я бы предпочел среднее значение за каждый час:

+------------------+------+------+
| date             | temp | hum  |
+------------------+------+------+
| 2013-09-25 13:00 | NULL | NULL |
| 2013-09-25 14:00 | 25.1 |   80 |
| 2013-09-25 15:00 | 25.3 |   78 |
| 2013-09-25 16:00 | 25.6 |   75 |
| 2013-09-25 17:00 | 25.5 |   75 |
| 2013-09-25 18:00 | 25.0 |   78 |
| 2013-09-25 19:00 | 24.2 |   80 |
| 2013-09-25 20:00 | 23.9 |   84 |
| 2013-09-25 21:00 | 23.9 |   84 |
| 2013-09-25 22:00 | 24.1 |   83 |
| 2013-09-25 23:00 | 24.1 |   83 |
| 2013-09-26 0:00  | 24.0 |   82 |
| 2013-09-26 1:00  | 23.9 |   84 |
| 2013-09-26 2:00  | 23.7 |   86 |
| 2013-09-26 3:00  | 23.7 |   84 |
| 2013-09-26 4:00  | 23.7 |   85 |
| 2013-09-26 5:00  | 23.4 |   85 |
| 2013-09-26 6:00  | 23.6 |   85 |
| 2013-09-26 7:00  | 23.7 |   85 |
| 2013-09-26 8:00  | 24.0 |   84 |
| 2013-09-26 9:00  | 24.4 |   82 |
| 2013-09-26 10:00 | 24.6 |   81 |
| 2013-09-26 11:00 | 25.2 |   79 |
| 2013-09-26 12:00 | 25.4 |   79 |
| 2013-09-26 13:00 | 25.6 |   78 |
+------------------+------+------+
25 rows in set (1.11 sec)

Вы знаете, как поместить AVG() в этот код?

Бонусный вопрос: я изменил код, чтобы получать одно значение за каждый час. Вы знаете, как получить 1 значение каждые 30 минут?

Спасибо за вашу помощь


mysql join average weather
person iero    schedule 26.09.2013    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Попробуй это:

select FROM_UNIXTIME( TRUNCATE(UNIX_TIMESTAMP(timeTable.minuteTime) / 1800,0)*1800) as halfHour,
avg(T2.valeur) as avgTemp,
avg(P2.valeur) as avgHum
FROM
(
    SELECT minuteTime.minuteTime minuteTime,
    ( SELECT MAX(date) FROM temperature WHERE date <= minuteTime.minuteTime AND date >= NOW() - INTERVAL 1 DAY) tempTime,
    ( SELECT MAX(date) FROM humidite WHERE date <= minuteTime.minuteTime AND date >= NOW() - INTERVAL 1 DAY) humTime
    FROM
    (
        SELECT DATE(date) + INTERVAL (HOUR(date) DIV 1 * 1) HOUR minuteTime
        FROM humidite
        WHERE date >= NOW() - INTERVAL 1 DAY AND date <= NOW()
        UNION SELECT DATE(date) + INTERVAL (HOUR(date) DIV 1 * 1) HOUR
        FROM temperature
        WHERE date >= NOW() - INTERVAL 1 DAY AND date <= NOW()
        GROUP BY 1
    ) minuteTime
) timeTable
LEFT JOIN temperature T2 ON T2.date = timeTable.tempTime
LEFT JOIN humidite P2 ON P2.date = timeTable.humTime
group by FROM_UNIXTIME( TRUNCATE(UNIX_TIMESTAMP(timeTable.minuteTime) / 1800,0)*1800)
ORDER BY 1 ASC;
person Tom Mac    schedule 26.09.2013
comment
Спасибо Том Мак за ваш ответ. Среднее значение работает хорошо, но вывод по-прежнему составляет одну строку в час, а не каждые 30 минут. Может быть, мне нужно изменить DATE (дата) + INTERVAL HOUR (date) HOUR minuteTime на что-то еще? - person iero; 26.09.2013
comment
Я проверил снова, и среднее значение дает странные значения: температура всегда что-то вроде ab.c0000, а влажность всегда de.0. Это кажется слишком округленным, чтобы быть настоящим средним! Идея ? - person iero; 26.09.2013

arrow_upward
0
arrow_downward

Благодаря Тому Маку и другим полезным сообщениям на этом сайте я получил этот код:

select DATE_FORMAT(timeTable.minuteTime, '%Y-%m-%d %k:%i') as date,
  (UNIX_TIMESTAMP(timeTable.minuteTime)*1000) AS dte,
  T2.valeur as avgTemp,
  P2.valeur as avgHum
  FROM ( 
    SELECT minuteTime.minuteTime minuteTime,      
    ( SELECT MAX(date) FROM temperature WHERE date <= minuteTime.minuteTime AND
     date >= NOW() - INTERVAL 170 HOUR) tempTime,
    ( SELECT MAX(date) FROM humidite WHERE date <= minuteTime.minuteTime AND
     date >= NOW() - INTERVAL 170 HOUR) humTime
    FROM( 
     SELECT date + INTERVAL 59 - SECOND( date ) SECOND minuteTime
     FROM humidite
     WHERE date >= NOW() - INTERVAL 7 DAY AND date <= NOW()
     UNION SELECT date + INTERVAL 59 - SECOND( date ) SECOND
     FROM temperature
     WHERE date >= NOW() - INTERVAL 7 DAY AND date <= NOW()
     GROUP BY 1
    ) minuteTime
  ) timeTable
  LEFT JOIN temperature T2 ON T2.date = timeTable.tempTime
  LEFT JOIN humidite P2 ON P2.date = timeTable.humTime
  group by FROM_UNIXTIME( TRUNCATE(UNIX_TIMESTAMP(timeTable.minuteTime) / 1800,0)*1800)
  ORDER BY minuteTime ASC;

Теперь я могу вычислять точки росы для каждого часа, используя специальный php-код, который я разместил здесь (для информации):

function calculateDewPoint($temperature, $humidity) {
    $a = 17.27 ;
    $b = 237.7 ;

    $c = (($a * $temperature) / ($b + $temperature) ) + Log($humidity/100) ;
    $d = (($b * $c)/($a - $c));
    return number_format($d, 1);
}

Следующим обновлением будет размещение этого кода непосредственно в запросе mysql.

С наилучшими пожеланиями

person iero    schedule 10.10.2013