mysql - 汇总每日到每月的数据并计算 delta mysql

Question

大家好，我从 mysql 得到的结果如下所示:

+---------------------+------+
|      timestamp      | data |  
+---------------------+------+
| 2015-06-01 04:25:18 |   10 |  
| 2015-06-15 04:25:18 |   20 |  
| 2015-06-30 04:25:18 |   30 |  
| 2015-07-01 04:25:18 |   50 |  
| 2015-07-15 04:25:18 |   60 |  
| 2015-07-30 04:25:18 |   70 |  
| 2015-08-01 04:25:18 |   80 |  
| 2015-08-15 04:25:18 |   90 |  
| 2015-08-30 04:25:18 |  100 |  
+---------------------+------+

我可以使用查询来计算增量，如下所示:

+---------------------+------+------+
|      timestamp      | data | delta |
+---------------------+------+------+
| 2015-06-01 04:25:18 |   10 |    0 |
| 2015-06-15 04:25:18 |   20 |   10 |
| 2015-06-30 04:25:18 |   30 |   10 |
| 2015-07-01 04:25:18 |   50 |   20 |
| 2015-07-15 04:25:18 |   60 |   10 |
| 2015-07-30 04:25:18 |   70 |   10 |
| 2015-08-01 04:25:18 |   80 |   10 |
| 2015-08-15 04:25:18 |   90 |   10 |
| 2015-08-30 04:25:18 |  100 |   10 |
+---------------------+------+------+

我想要的是按月对增量列进行分组:

+-------+-------------+
| month | consumption |
+-------+-------------+
| 6/15  |          20 |
| 7/15  |          40 |
| 8/15  |          30 |
+-------+-------------+

我认为我必须尝试按月分组(时间戳)，但它不会聚合增量。

有什么想法吗？

编辑

为了澄清增量是一个计算列，这里是一个修改后的查询，以向您展示我的意思:

 SELECT 
node_time,
   node_read - @prev AS delta,
  @prev := waveflow_data.node_read
FROM
  meter_data
INNER JOIN waveflow_data ON meter_data.node_address = waveflow_data.node_address 
CROSS JOIN (SELECT @prev := (SELECT node_read FROM waveflow_data ORDER BY `node_time` DESC LIMIT 1)) variable_initialization_query_alias
WHERE
   meter_data.node_address = '10164E998976' 
ORDER BY waveflow_data.`node_time` DESC

Answer 1

您只需像下面这样sum(delta):

select time_stamp,sum(delta) as Consumption from test 
        group by month(time_stamp);

检查SQL FIDDLE DEMO