Сбор показаний давления WITH GetPressure AS SELECT DATEADD hour System

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*
Сбор показаний давления
*/
WITH GetPressure
AS( SELECT
DATEADD(hour, 3, System.TimeStamp) AS EventTime,
mainValue
FROM input TIMESTAMP BY eventTime
WHERE sensorId = 2)
/*
Соединение показаний давления и показаний влажности
*/
SELECT DATEADD(hour, 3, System.TimeStamp) AS EventTime, i.Location,
message = CASE WHEN AVG(p.mainValue)<30 and AVG(p.mainValue)>70
THEN '!!!Watek leak!!!' ElSE 'Normal' END
FROM GetPressure as p
JOIN (SELECT DATEADD(hour, 3, System.TimeStamp) AS EventTime,
mainValue
FROM input TIMESTAMP BY eventTime
WHERE sensorId = 3) as i
ON p.EventTime = i.EventTime AND DATEDIFF( minute , p , i) BETWEEN 0 AND 15
GROUP BY SlidingWindow(minute, 1)
HAVING COUNT(*) > 2