mysql查询平均加入1个表中的3个查询 [英] mysql query to join 3 queries in 1 table while averaging

问题描述

我有3个非常大的表，每分钟都会记录一次值， 下面是这些表的提取.

I have 3 very large tables, with values getting logged every minute, below is an extract of these tables.

我想获取这些表的 1天时段的每小时平均值，并根据时间加入它们，请注意，ph和温度的记录时间之间有几秒钟的差距

I would like to get hourly averages for a period of 1 Day of these tables and join them with respect to time, please note there is a couple of seconds gap between log time for ph and temperature

表PH (仅用于提取，该表非常大，具有超过130,000个值)

Table PH (extract only, this table is very large with more than 130,000 values)

    ID      time                Ph

    72176   2013-04-06 03:29:34 7.58
    72177   2013-04-06 03:30:34 7.58
    72178   2013-04-06 03:31:34 7.54
    72179   2013-04-06 03:32:34 7.58
    72180   2013-04-06 03:33:34 7.58
    72181   2013-04-06 03:34:34 7.58
    72182   2013-04-06 03:35:34 7.54
    72183   2013-04-06 03:36:34 7.58
    72184   2013-04-06 03:37:34 7.54
    72185   2013-04-06 03:38:34 7.58
    72186   2013-04-06 03:39:34 7.58

表温度1 (仅提取，此表非常大，具有超过130,000个值)

Table temperature1 (extract only, this table is very large with more than 130,000 values)

    ID      time            temperature

133312  2013-04-06 03:29:36 25.37
133313  2013-04-06 03:30:36 25.37
133314  2013-04-06 03:31:36 25.37
133315  2013-04-06 03:32:36 25.31
133316  2013-04-06 03:33:36 25.31
133317  2013-04-06 03:34:36 25.31
133318  2013-04-06 03:35:36 25.37
133319  2013-04-06 03:36:36 25.31
133320  2013-04-06 03:37:36 25.31
133321  2013-04-06 03:38:36 25.31
133322  2013-04-06 03:39:36 25.37

表格实体 (仅提取，此表格非常大，具有超过130,000个值)

Table solids (extract only, this table is very large with more than 130,000 values)

    ID      time            solids

123791  2013-04-06 03:29:49 140
123792  2013-04-06 03:30:49 140
123793  2013-04-06 03:31:49 143
123794  2013-04-06 03:32:49 140
123795  2013-04-06 03:33:49 140
123796  2013-04-06 03:34:49 140
123797  2013-04-06 03:35:49 140
123798  2013-04-06 03:36:49 143
123799  2013-04-06 03:37:49 140
123800  2013-04-06 03:38:49 140
123801  2013-04-06 03:39:49 140

我目前正在使用下面的查询获取每小时平均值

I am currently getting hourly averages using the query below

SELECT DATE_FORMAT(x.time,'%Y-%m-%d %H:00:00')
     , avg(x.solids) avg_solids
  FROM solids x where time >= NOW() - INTERVAL 1 DAY
 GROUP 
    BY DATE_FORMAT(x.time,'%Y-%m-%d %H:00:00'); 

对于每个要在1个表格中显示的传感器(x3)，我如何有效地结合(相对于时间)上面的查询结果

how can I efficiently join (with respect to time) the results of the query above for each sensor (x3) to be displayed in 1 table

==============================

===============================

下面的该查询获取每小时的值，但不确定如何对其进行周获取以获取每小时的平均值

this query below gets the hourly values, but not sure how to tweek it to get averages per hour

SELECT DATE_FORMAT(timeTable.minuteTime, '%Y-%m-%d %k:%i') time,
(oT2.temperature) temperature,
(T2.temperature) temp,
(S2.solids) solids,
(P2.Ph) Ph


FROM
(
    SELECT minuteTime.minuteTime minuteTime,
    ( SELECT MAX(time) FROM outside_temperature WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 DAY) otempTime, 
    ( SELECT MAX(time) FROM temperature1 WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 DAY) tempTime, 
    ( SELECT MAX(time) FROM Ph WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 DAY) phTime,  
    ( SELECT MAX(time) FROM solids WHERE time <= minuteTime.minuteTime AND time >= NOW() - INTERVAL 1 DAY) solidsTime

    FROM  
    (
        SELECT DATE(time) + INTERVAL (HOUR(time) DIV 1 *1 ) HOUR minuteTime
        FROM Ph
        WHERE time >= NOW() - INTERVAL 1 DAY AND time <= NOW()
        UNION SELECT DATE(time) + INTERVAL (HOUR(time) DIV 1 *1) HOUR
        FROM solids
        WHERE time >= NOW() - INTERVAL 1 DAY AND time <= NOW()
        UNION SELECT DATE(time) + INTERVAL (HOUR(time) DIV 1 *1) HOUR
        FROM outside_temperature
        WHERE time >= NOW() - INTERVAL 1 DAY AND time <= NOW()
        UNION SELECT DATE(time) + INTERVAL (HOUR(time) DIV 1 *1) HOUR
        FROM temperature1
        WHERE time >= NOW() - INTERVAL 1 DAY AND time <= NOW()
        GROUP BY 1
    ) minuteTime
) timeTable
LEFT JOIN outside_temperature oT2 ON oT2.time = timeTable.otempTime
LEFT JOIN temperature1 T2 ON T2.time = timeTable.tempTime
LEFT JOIN solids S2 ON S2.time = timeTable.solidsTime
LEFT JOIN Ph P2 ON P2.time = timeTable.phTime


GROUP BY DATE_FORMAT(timeTable.minuteTime, '%Y-%m-%d %k:%i') 
ORDER BY minuteTime ASC

推荐答案

Hour() seems that it would be a useful function for this, since you are only looking at a single day. Perhaps something like this would work for you:

SELECT * FROM
 (SELECT HOUR(time) hour, avg(ph) AS avg_ph
  FROM ph 
  WHERE time >= NOW() - INTERVAL 1 DAY
  GROUP BY hour) p
JOIN 
 (SELECT HOUR(time) hour, avg(temperature) AS avg_temp
  FROM temperature1 
  WHERE time >= NOW() - INTERVAL 1 DAY
  GROUP BY hour) t ON t.hour = p.hour
JOIN 
 (SELECT HOUR(time) hour, avg(solids) AS avg_solids
  FROM solids 
  WHERE time >= NOW() - INTERVAL 1 DAY
  GROUP BY hour) s ON s.hour = p.hour;

由于它正在使用内部联接，因此我假设一个小时内每个表中总是至少有一个记录，但这似乎是一个合理的假设.

Being that it is using inner joins, I'm making the assumption that there will always be at least one records in each table for the hour, but it seems like a reasonable assumption.

这篇关于mysql查询平均加入1个表中的3个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！