记录事件，如果最近3天内值已更改 [英] Record events if values have changed in the last 3 days

问题描述

我有一个问题，我有一个重要的表格.但是这些值之间的差是3天.

I have a question, I have a table of importance. But the difference between these values is 3 days.

select 
    er.name,
    er.temp as real_temp, 
    der.temp as old_temp
from TEMPR_SILO er
    left join TEMPR_SILO der
    on er.name = der.name
where der.ID_TRANS in (select max(ID_TRANS) - 72 from TEMPR_SILO) 
    and er.ID_TRANS  in (select max(ID_TRANS) from TEMPR_SILO)

我的答案

NAME	      REAL_TEMP	OLD_TEMP
SENSOR0001	7.98	    9.66
SENSOR0002	8.04	    9.91
SENSOR0003	7.91	    0.41
SENSOR0004	9.54	   -0.27
SENSOR0005	9.85	   -1.09
SENSOR0006	9.35	   -1.59
SENSOR0007	8.54	   -1.34

也就是说，在答案中我有传感器的名称，并且温度是真实的和古老的，已经有3天了.我有一个问题，我能做到吗，如果温度下降到5，那么我有一个备用的ALARM表，

That is, in the answer I have the name of the sensor, and the temperature is real and old which is already 3 days. I have a question, can I make it so that if the temperature drops to 5, then I have a spare ALARM table that

 INSERT INTO ALARM ( NAME, INFO)    VALUES ( er.name ,'The temperature has greatly changed')

该表将始终具有600个值.谢谢您的帮助) 我想在每小时执行一次的后台任务中实现此代码)

The table will have 600 values always. Thanks for the help) I want to implement this code in a background task that will be called every hour)

推荐答案

您可以在后台任务中添加以下查询以达到目的.

You may add following query in your background task that will serve the purpose.

INSERT INTO IMS.ALARM (NAME,INFO)  
select er.name,'The temperature has significantly dropped'
from IMS.TEMPR_SILO er
    left join IMS.TEMPR_SILO der on er.name = der.name
where der.ID_TRANS in (select max(ID_TRANS) - 72 from IMS.TEMPR_SILO) 
and er.ID_TRANS  in (select max(ID_TRANS) from IMS.TEMPR_SILO)
and der.temp-er.temp>=6;

我认为，如果您在触发器中而不是通过后台进程来管理警报"内容，则解决方案将几乎没有改善，因为这种方式存在一些缺陷，例如

In my opinion, your solution can be little improved if you manage Alarming stuff in Trigger instead of some background process because there are some drawbacks in this way e.g.

• 连续的后台进程还将每小时额外执行DB I/O 消耗处理能力

• Continuous background process will be doing hourly extra DB I/O in addition to consuming processing power

由于任何给定的原因，如果没有新记录出现，则程序可能会根据过时的数据注册/插入新警报

For any given reasons, if new records are not coming then program may register/insert new alarms on the basis of obsolete data

但是，如果使用上述查询在同一表TEMPR_SILO上写入触发器"ON INSERT"事件，则:

However, if you write a trigger 'ON INSERT' event on same table TEMPR_SILO by using above mentioned query then:

• 它将在插入新记录时自动执行
• 可以避免错误警报
• 不需要后台处理

我希望这个解决方案对您有用.

I hope this solution will work for you.

这篇关于记录事件，如果最近3天内值已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！