使用 MySQL (PV) 计算值之间的差异

Question

我有点困在我的学士学位论文上，希望你能帮助我。 为了评估光伏系统，我需要计算总能量之间的差异。这些在MySQL表中自动更新，带有时间戳，但没有ID号。 我需要自动获取这些能量量之间的增量/差异作为额外的列，以便在 Grafana 中可视化它。

******************************************
Timestamp            | SB1_AC_Total       | Needed information (delta)
******************************************
2020-06-24 09:32:45  | 11.326.302         |   23
2020-06-24 09:32:02  | 11.326.279         |   22
2020-06-24 09:31:20  | 11.326.257         |   ...

这个清单持续了数周。 我真的希望你能帮助我，因为我不知道，这是我第一次使用 MySQL。

Answer 1

请考虑以下代码并将其用作示例：

create table test (time timestamp, total int);
insert into test values (current_timestamp() - interval 2 day, 100);
insert into test values (current_timestamp() - interval 1 day, 120);
insert into test values (current_timestamp(),                  125);
select i.*, i.total - t_outer.total as diff 
from (select t.*, (select max(time) 
from test t1 where t1.time < t.time) as last_timestamp
from test t
) as i 
left join test t_outer
on i.last_timestamp = t_outer.time;

结果你会得到类似的东西：

time    total   last_timestamp  diff
"2020-06-23 10:58:21"   120 "2020-06-22 10:58:19"   20
"2020-06-24 10:58:22"   125 "2020-06-23 10:58:21"   5
"2020-06-22 10:58:19"   100 NULL    NULL

编辑：

如果要将 diff 值放入新的表列中，可以这样做：

alter table test add column diff int default null;
create table select_bkp as -- you know the code below already
select i.time, i.total - t_outer.total as diff 
from (select t.*, (select max(time) 
from test t1 where t1.time < t.time) as last_timestamp
from test t
) as i 
left join test t_outer
on i.last_timestamp = t_outer.time;
SET SQL_SAFE_UPDATES = 0; -- so all rows can be updated at once
update test t
join select_bkp b
on t.time = b.time
set t.diff = b.diff;
SET SQL_SAFE_UPDATES = 1; -- enable secure updates

Answer 2

我能够使用以下代码回答我的问题：

<小时 />

ALTER TABLE TABLENAME  ADD SB1_AC_GES_DIFF INT;
DELIMITER $$
CREATE TRIGGER TABLENAME_Trigger
BEFORE INSERT
ON TABLENAME FOR EACH ROW
BEGIN
DECLARE SB1_AC_GES_old INT;  
SELECT max(SB1_AC_GES) INTO SB1_AC_GES_old FROM TABLENAME;
SET NEW.SB1_AC_GES_Diff = New.SB1_AC_GES - SB1_AC_GES_old;
END$$    
DELIMITER ;

<小时 />

Answer 3

您如何处理这取决于您使用各种工具的技能。例如，我在"C"中很老，但在 SQL 中是绿色的。

现在：假设您已经有一个数据库，并且想要创建一个包含额外字段的新数据库，这大致是我处理它的方式。

创建一个新的空数据库，其中包含所需的字段。

open both databases 
Read all the old database into a program coded in whatever language you are happy with. 
Typically you will access this by executing an SQL statement like
SELECT * from table1 order by timestamp
In general you can now step through the data base line by line. 
The algorithm you want is this
set a variable 'delta' to zero
set a variable 'flag to zero
while(more lines)
read 'timestamp'
read 'SB1_AC_Total'
write 'timestamp', 'SB1_AC_Total', and 'delta' to the new database using sql INSERT command.
If 'flag' is not zero, set 'delta' to ('SB1_AC_Total' - 'old_data');  endif
set 'flag' to 1
set a variable 'old_data' to 'SB1_AC_Total'
endwhile 

这将使用新列复制数据库，该列的值为当前值减去前一个值。

我怀疑有一种SQL方法使用两个选择来生成两个临时表，然后从一个和另一个读取并将结果组合成第三个，但它超出了我的范围。