按分钟原始数据每小时汇总MySQL数据 [英] Aggregating MySQL data on hourly basis from minute-wise raw data

问题描述

我有一张表table_1，每分钟都有数据，如下所示：

  + ----- ---------------- + --------- + 
 | date_time |值| 
 + --------------------- + --------- + 
 | 2015-06-05 18:00:00 | 222.663 | 
 | 2015-06-05 18:01:00 | 222.749 | 
 | 2015-06-05 18:02:00 | 222.957 | 
 | 2015-06-05 18:03:00 | | 223.063 | 
 | 2015-06-05 18:04:00 | 223.117 | 
 + --------------------- + --------- + 
  

我希望获取每小时最小值和最大值。例如：

  + --------------------- + --------- + 
 | date_time |值| 
 + --------------------- + --------- + 
 | 2015-06-05 18:10:00 | 200.663 | 
 | 2015-06-05 18:45:00 | 222.749 | 
 | 2015-06-05 19:10:00 | | 190.957 | 
 | 2015-06-05 19:33:00 | 400.063 | 
 + --------------------- + --------- + 
  

目前，我正在PHP中创建一个for循环并运行每个小时的查询。然而，这显然不是最好的办法，因此是非常耗时的。例如：

  for（$ temp = $ st; $ temp< = $ et; $ temp = $ temp + 60 ）{//循环每个小时
 
 $ sql1 =select * from table_1其中value 
（从table_1中选择max（value）
 where date_time> =' .date（'Ymd H：i：s'，$ st）。'
和date_time<'.date（'Ymd H：i：s'，$ temp）。'）
和date_time> ='.date（'Ymd H：i：s'，$ st）。'
和date_time<'.date（'Ymd H：i：s'，$ temp ）。'limit 1; 
 
 $ rs1 = mysql_query（$ sql1）; 
 
 $ row1 = mysql_fetch_assoc（$ rs1）
 $ val1 = $ row1 ['value']; 
 $ date1 = $ row1 ['date_time']; 
 
 
  

解决方案

问题

  select DATE_FORMAT（date_time，'％Y-％m-％d％H：00：00'）h，分钟（价值），最高（价值）
从
组到$ h 
  

的 1。如何为6小时或15分钟的时间间隔做同样的事情。

$ b

b

 从t1 
中选择date_time，min（value），max（value）
（unix_timestamp（date_time）/（15 * 60）） 
  

6小时的间隔变为（6 * 60 * 60）

2。上面的结果不显示对应于最小值和最大值的原始时间戳。如何获得这些信息。

这样的查询会给你想要的。但是，例如，如果在一个区间内有一些最小值，那么得到这么多行有多少这样的值

  select date_time，价值
从t1，
（选择DATE_FORMAT（date_time，'％Y-％m-％d％H：00：00'）h，
 min（value）min，max ）最大
从t1 
组到h 
）t 
其中（min，max）
和DATE_FORMAT（date_time，'％Y-％m-％ d％H：00：00'）= h 
  

I have a table, table_1, which has data for EACH minute and looks like:

+---------------------+---------+
| date_time           | value   |
+---------------------+---------+
| 2015-06-05 18:00:00 | 222.663 |
| 2015-06-05 18:01:00 | 222.749 |
| 2015-06-05 18:02:00 | 222.957 |
| 2015-06-05 18:03:00 | 223.063 |
| 2015-06-05 18:04:00 | 223.117 |
+---------------------+---------+

I wish to fetch hourly min and max values. Something like:

+---------------------+---------+
| date_time           | value   |
+---------------------+---------+
| 2015-06-05 18:10:00 | 200.663 |
| 2015-06-05 18:45:00 | 222.749 |
| 2015-06-05 19:10:00 | 190.957 |
| 2015-06-05 19:33:00 | 400.063 |
+---------------------+---------+

Currently I am creating a for loop in PHP and runs a query for EACH hour. However, it is obviously not the best way to do it, and is thus VERY TIME CONSUMING. For example:

for($temp = $st; $temp <= $et; $temp = $temp + 60){//looping for each hour

 $sql1 = "select * from table_1 where value in 
 (select max(value) from table_1 
 where date_time >= '".date('Y-m-d H:i:s',$st)."' 
 and date_time < '".date('Y-m-d H:i:s',$temp)."' ) 
 and date_time >= '".date('Y-m-d H:i:s',$st)."' 
 and date_time < '".date('Y-m-d H:i:s',$temp)."' limit 1";

 $rs1 = mysql_query($sql1);

 $row1 = mysql_fetch_assoc($rs1)
 $val1 = $row1['value']; 
 $date1 = $row1['date_time'];

} 

解决方案

Anawer for the question

select DATE_FORMAT(date_time,'%Y-%m-%d %H:00:00') h, min(value), max(value)
    from thetable 
  group by h

1. How to do the same thing for intervals like 6 hours or 15 minutes.

for 15 minute interval:

select date_time, min(value), max(value) 
     from t1 
  group by round(unix_timestamp(date_time)/(15 * 60))

For 6 hours interval change to (6 * 60 * 60)

2. The result above does NOT show the original time stamp corresponding to the min and max values. How to get those.

Such query give you what you want. But if, for example, there are some minimum values in an interval, get so many rows how many such values

select date_time, value 
    from t1, 
         (select DATE_FORMAT(date_time,'%Y-%m-%d %H:00:00') h, 
                 min(value) min, max(value) max 
              from t1 
            group by h
         ) t 
  where value in (min, max) 
    and DATE_FORMAT(date_time,'%Y-%m-%d %H:00:00') = h

这篇关于按分钟原始数据每小时汇总MySQL数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！