我有一个 SQL 表，其中记录了电表(计数器)的绝对值。每增加 5 个值或最迟每 15 分钟自动添加新行。

表格数据可能看起来像这样:

id   value     time
1    2500      21.12.16 01:09:52
2    2505      21.12.16 01:10:23
3    2510      21.12.16 01:11:00
4    2512      21.12.16 01:26:00
...
1500 4502      22.12.16 00:00:03
1501 4507      22.12.16 00:00:58
...
3456 6528      23.12.16 00:00:10
3457 6533      23.12.16 00:05:22

给定开始时间、结束时间和采样时间，是否可以得到值之间的差异？

例如，

start_time = 21.12.16 02:00:00
stop_time  = 21.12.16 03:15:00
sampling_time = 15 minutes

应执行以下操作:

Get value at start_time (or closest to it) = value1
Get value at start_time + sampling_time (or closest to it) = value2
Select difference between value1 and value 2
Get value at start_time + 2*sampling_time (or closest to it) = value3
Select difference between value 2 and value 3
... so on, until start_time + n*sampling_time > stop_time

预期结果可能是这样的:

20     //Difference between value 1 at 2:00 (or closest) and value 2 at 2:15 (or closest)
18     //Diff. between value 2 (2:15) and value 3 (2:30)...
23     
15
45     //... Diff. between value n-1 (3:00) and value n (3:15)

用纯 SQL 可以吗？或者我应该以不同的方式记录消耗的能量吗？

最佳答案

我首先创建表格并插入前几行，以便您了解我正在使用的表格架构。请注意，id 被视为 PRIMARY KEY 值。

CREATE table t1(
  `id` int PRIMARY KEY, 
  `value` int,
  `time` timestamp
)Engine=InnoDB;

INSERT INTO t1 VALUES
   (1,2500,'21.12.16 01:09:52'),
   (2,2505,'21.12.16 01:10:23'),
   (3,2510,'21.12.16 01:11:00'),
   (4,2512,'21.12.16 01:26:00'); 

您要求的查询是两个不同查询之间的减法:

查询 1:从时间 = **smaller_timestamp** 的 t1 中选择值

查询 2:从时间 = **larger_timestamp** 的 t1 中选择值

获取结果:SELECT query1 - query2;

示例:

-- smaller timestamp 
mysql> select value from t1 where time ='21.12.16 01:09:52'; 
+-------+
| value |
+-------+
|  2500 |
+-------+
1 row in set (0.00 sec)

-- larger timestamp
mysql> select value from t1 where time ='21.12.16 01:26:00'; 
+-------+
| value |
+-------+
|  2512 |
+-------+
1 row in set (0.00 sec)


mysql> SELECT (select value from t1 where time ='21.12.16 01:26:00')-(select value from t1 where time ='21.12.16 01:09:52') AS value_difference; 
+------------------+
| value_difference |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

更一般地说，您可以使用 DATE_ADD(date, INTERVAL) 等方法利用不同的开始/结束时间。

示例:对于这种情况，我使用了 time='21.12.16 01:11:00' 和 INTERVAL=15 分钟。

mysql> SELECT 
    -> (select value from t1 where time =DATE_ADD('21.12.16 01:11:00', INTERVAL 15 minute))-
    -> (select value from t1 where time='21.12.16 01:11:00') 
    -> AS 
    -> value_difference;
+------------------+
| value_difference |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

我应该让你知道，如果你想做“最接近”，那么你应该使用大于/小于比较、ORDER BY 和函数 LIMIT 而不仅仅是选择。

示例:

mysql> SELECT 
    -> (select value from t1 where time<=DATE_ADD('21.12.16 01:11:00', INTERVAL 15 minute) ORDER BY value DESC LIMIT 1)-
    -> (select value from t1 where time<='21.12.16 01:09:52' ORDER by value ASC LIMIT 1)
    -> AS
    -> value_difference;
+------------------+
| value_difference |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

应@bishop 的要求，以下是这些查询作为存储过程的示例。

第一种情况是有 2 个时间戳可供比较:

DELIMITER //
CREATE PROCEDURE calculateValue(startTime TIMESTAMP, endTime TIMESTAMP)
BEGIN
  set @startTime = startTime;
  set @endTime = endTime;

  set @resultStart=concat("SELECT value FROM t1 WHERE time <='",@startTime,"' ORDER BY value DESC LIMIT 1");
  set @resultEnd=concat("SELECT value FROM t1 WHERE time <= '",@endTIME,"' ORDER BY value DESC LIMIT 1");
  set @query=concat("SELECT (",@resultEnd,")-(",@resultStart,") AS 'value_difference'");
  prepare stmt from @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

第二种情况是使用DATE_ADD 函数。

DELIMITER //
CREATE PROCEDURE calculateValue(startTime TIMESTAMP, timeInterval VARCHAR)
BEGIN
  set @startTime = startTime;
  set @timeInterval= timeInterval;

  set @resultStart=concat("SELECT value FROM t1 WHERE time <=DATE_ADD('",@startTime,"',INTERVAL ",@timeInterval,") ORDER BY value DESC LIMIT 1");
  set @resultEnd=concat("SELECT value FROM t1 WHERE time <= '",@endTIME,"' ORDER BY value DESC LIMIT 1");
  set @query=concat("SELECT (",@resultEnd,")-(",@resultStart,") AS 'value_difference'");
  prepare stmt from @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

关于MySQL 选择给定采样时间的能量计数器变化率，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41253513/