mysql - 使用 SQL 数据库查询以不同的时间间隔减去相同的列值

Question

在 MySQL 中，我想根据另一列“时间戳”在不同的时间间隔减去一个列值。

table structure is : 
id | generator_id | timestamp             | generated_value
1  |  1           | 2019-05-27 06:55:20   | 123456     
2  |  1           | 2019-05-27 07:55:20   | 234566       
3  |  1           | 2019-05-27 08:55:20   | 333456       
..
..    
20  |  1           | 2019-05-27 19:55:20   | 9876908       

从上表中，我想获取 generate_value 列值，该值应该是当天的第一个时间戳和当天最后一个值的时间戳的差异。

在上面的示例中，我正在查找应该给我输出为 9,753,452 (9876908 - 123456) 的查询。

一般来说，要获取我使用以下查询的第一个值和最后一个值的单个记录

// Below will give me end day value
SELECT * FROM generator_meters where generator_id=1 and timestamp like '2019-05-27%' order by timestamp desc limit 1 ;

//this will give me last day value 
SELECT * FROM generator_meters where generator_id=1 and timestamp like '2019-05-27%' order by timestamp  limit 1 ;

问题是我应该如何通过从一天的最后一个值减去一天的第一个值来获得最终的生成值。

Expected Output 
 generator_id | generated_value
  1           | 9753452     

提前致谢 ！！

标签： mysqlsql

Answer 1

以下脚本将返回过滤后的 ID 和日期的预期结果-

SELECT generator_id,CAST(timestamp AS DATE) ,
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = max(timestamp)
)
-
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = min(timestamp)
) AS Diff
FROM sun_electric.generator_meters 
WHERE generator_id = 1
AND CAST(timestamp AS DATE) = '2019-05-27'
GROUP BY generator_id,CAST(timestamp AS DATE) ; 

如果您想要与 GROUP BY ID 和 Date 相同的结果，只需删除过滤器，如下所示 -

SELECT generator_id,CAST(timestamp AS DATE) ,
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = max(timestamp)
)
-
(
    SELECT generated_value 
    FROM sun_electric.generator_meters B 
    WHERE timestamp = min(timestamp)
) AS Diff
FROM sun_electric.generator_meters 
GROUP BY generator_id,CAST(timestamp AS DATE) ;