mysql - SQL 组结果为 "sequence"

Question

我有一个数据库 (MySQL)，其中有一个这样的表:

列定义:

ID (pk , long , auto_increment) ,
raw_data (double) ,
numDevice (int) ,
numVar (int) ,
timemillis (long)  , 
date (timestamp) 

数据示例:

ID |RAW_DATA           |numDevice | numVar |TIMEMILLIS   |   TIMESTAMP
 01| 86.23775482177734 |    1     | 1      |1424180019706|2015-02-17 13:33:39
 02| 91.85794067382812 |    1     | 1      |1424180022857|2015-02-17 13:34:42
 03| 88.02635192871094 |    1     | 1      |1424180026008|2015-02-17 13:35:46
 04| 89.89857482910156 |    1     | 1      |1424180029159|2015-02-17 15:22:49
 05| 86.82943725585938 |    1     | 1      |1424180035460|2015-02-17 15:23:55
 06| 93.07737731933594 |    1     | 1      |1424180038613|2015-02-17 15:24:58
 07| 87.77403259277344 |    1     | 1      |1424180041764|2015-02-17 18:34:01
 08| 92.69800567626953 |    1     | 1      |1424180044915|2015-02-17 18:34:04
 09| 90.60401916503906 |    1     | 1      |1424180048067|2015-02-17 18:34:08
 10| 88.20098876953125 |    1     | 1      |1424180051218|2015-02-17 18:34:11

......

我搜索查询以按日期对结果进行分组，例如，最大间隔为 1 分钟，此查询的结果应为:

第 1 组:

 01| 86.23775482177734 |    1     | 1      |1424180019706|2015-02-17 13:33:39
 02| 91.85794067382812 |    1     | 1      |1424180022857|2015-02-17 13:34:42
 03| 88.02635192871094 |    1     | 1      |1424180026008|2015-02-17 13:35:46

第 2 组:

 04| 89.89857482910156 |    1     | 1      |1424180029159|2015-02-17 15:22:49
 05| 86.82943725585938 |    1     | 1      |1424180035460|2015-02-17 15:23:55
 06| 93.07737731933594 |    1     | 1      |1424180038613|2015-02-17 15:24:58

第 3 组

 07| 87.77403259277344 |    1     | 1      |1424180041764|2015-02-17 18:34:01
 08| 92.69800567626953 |    1     | 1      |1424180044915|2015-02-17 18:34:04
 09| 90.60401916503906 |    1     | 1      |1424180048067|2015-02-17 18:34:08
 10| 88.20098876953125 |    1     | 1      |1424180051218|2015-02-17 18:34:11

我在网上搜索了两天没有成功，欢迎大家帮忙。谢谢

Answer 1

相反，考虑每个“组”一行:

SELECT  CONCAT(MIN(id), '-', MAX(id)) AS id_range,
        SUM(RAW_DATA) AS sum_raw_data,
        CONCAT(MIN(TIMESTAMP), ' - ', RIGHT(MAX(TIMESTAMP), 8)) AS time_span
    FROM  tbl
    GROUP BY  LEFT(TIMESTAMP, 15);

那应该给你类似的东西:

01-03   266.666666   2015-02-17 13:33:39 - 13:35:46
04-06   277.888888   ...