使用SQL数据库查询以在一天的不同间隔中减去相同的列值

PAR 发表于 Dev

通过

在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 ;

问题是如何从天的最后一个值减去天的第一个值来获得最终的generate_value。

Expected Output 
 generator_id | generated_value
  1           | 9753452

提前致谢！！

托斯滕·凯特纳

在您的示例中，价值变得越来越大。如果可以保证的话，您可以使用

select max(generated_value) - min(generated_value) as result
from sun_electric.generator_meters
where generator_id = 1
and date(timestamp) = date '2019-05-27';

或多个ID：

select generator_id, max(generated_value) - min(generated_value) as result
from sun_electric.generator_meters
and date(timestamp) = date '2019-05-27'
group by generator_id
order by generator_id;

如果该值未递增，则可以对ID 1使用以下查询：

select last_row.generated_value - first_row.generated_value as result
from 
(
  select * 
  from sun_electric.generator_meters
  where generator_id = 1
  and date(timestamp) = date '2019-05-27'
  order by timestamp
  limit 1
) first_row
cross join
(
  select * 
  from sun_electric.generator_meters
  where generator_id = 1
  and date(timestamp) = date '2019-05-27'
  order by timestamp desc
  limit 1
) last_row;

这是获取多个ID的结果的一种方法：

select
  minmax.generator_id,
  (
    select generated_value 
    from sun_electric.generator_meters gm
    where gm.generator_id = minmax.generator_id
    and gm.timestamp = minmax.max_ts
  ) -
  (
    select generated_value 
    from sun_electric.generator_meters gm
    where gm.generator_id = minmax.generator_id
    and gm.timestamp = minmax.min_ts
  ) as result
from
(
  select generator_id, min(timestamp) as min_ts, max(timestamp) as max_ts
  from sun_electric.generator_meters
  where date(timestamp) = date '2019-05-27'
  group by generator_id
) minmax
order by minmax.generator_id;

如果您更喜欢，也可以将子查询移至from子句并加入它们。还有另一种方法是使用MySQL 8中提供的窗口函数。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。