大坝安全监测系统预警值上下限设定

来源：互联网发布：mac sleepimage 编辑：程序博客网时间：2024/04/29 22:06

--在大坝安全监测系统中，如果根据历史值设定预警值的上下线，可用以下方法

SELECT devicecd ,

MAX(value) AS max_value ,
MIN(value) AS min_value
FROM [resos_tx].[dbo].[B_Device_R]
GROUP BY devicecd
ORDER BY devicecd

--把搜索结果保存为新表 newtable
--预警值 maxv为历史最大值增加10%，minv为历史最小值减小10%
--亚安全值上限submaxv和下限subminv 把预警值上下限平均分为三等分

UPDATE B_Device
SET B_Device.maxv = newtable.max_value * 1.1 ,
B_Device.minv = newtable.min_value * 1.1 ,
B_Device.submaxv = newtable.max_value * 1.1 * 2 / 3
+ newtable.min_value * 1.1 / 3 ,
B_Device.subminv = newtable.max_value * 1.1 / 3 + newtable.min_value
* 1.1 * 2 / 3
FROM newtable
WHERE B_Device.devicecd = newtable.devicecd
AND B_Device.maxv >= 0
AND B_Device.minv <= 0

UPDATE B_Device
SET B_Device.maxv = newtable.max_value * 1.1 ,
B_Device.minv = newtable.min_value * 0.9 ,
B_Device.submaxv = newtable.max_value * 1.1 * 2 / 3
+ newtable.min_value * 0.9 / 3 ,
B_Device.subminv = newtable.max_value * 1.1 / 3 + newtable.min_value
* 0.9 * 2 / 3
FROM newtable
WHERE B_Device.devicecd = newtable.devicecd
AND B_Device.maxv >= 0
AND B_Device.minv >= 0

UPDATE B_Device
SET B_Device.maxv = newtable.max_value * 0.9 ,
B_Device.minv = newtable.min_value * 1.1 ,
B_Device.submaxv = newtable.max_value * 0.9 * 2 / 3
+ newtable.min_value * 1.1 / 3 ,
B_Device.subminv = newtable.max_value * 0.9 / 3 + newtable.min_value
* 1.1 * 2 / 3
FROM newtable
WHERE B_Device.devicecd = newtable.devicecd
AND B_Device.maxv <= 0
AND B_Device.minv <= 0

--如果想按照设备编号devicecd分组，再取出每一组按照value排序后的最大3条数据，语句如下
--目的是为了查看是否有不合理的极值
SELECT * FROM (
SELECT
SN = ROW_NUMBER() OVER (partition BY devicecd ORDER BY value ), devicecd, value FROM B_device_R) tmp
WHERE tmp.SN <= 3 order by devicecd

0 0