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

Question

我的学士论文有点卡住了，希望你能帮助我。为了评估光伏系统，我需要计算总能量之间的差异。这些在带有时间戳但没有 ID 号的 MySQL 表中自动更新。我需要自动获取这些能量之间的增量/差异作为额外的列，以便在 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

编辑:

如果你想把差异值放到一个新的表列中，你可以这样做:

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