Выполнение преобразования потока событий WITH Result AS SELECT DATEADD

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/*
Выполнение преобразования потока событий
*/
WITH Result
AS
(SELECT
DATEADD(hour, 3, System.TimeStamp) AS EventTime, events.sensorId as sensorId, AVG(events.mainValue) as MainValue, COUNT(*) as EventCount
FROM events
TIMESTAMP BY timeValue
GROUP BY events.sensorId, TUMBLINGWINDOW(minute, 1))
/*
Отправка отфильтрованных событий в базу данных
*/
SELECT
res.EventTime, res.sensorId, sl.Name, sl.SensorType, sl.Location, res.MainValue, res.EventCount
INTO outSql
FROM Result as res
JOIN SensorList as sl
ON sl.sensorId = res.sensorId
/*
Отправка отфильтрованных событий в систему powerBI
*/
SELECT
res.EventTime, res.SensorId, sl.Name, sl.SensorType, sl.Location, res.MainValue, res.EventCount
INTO outPowerbi
FROM Result as res
JOIN SensorList as sl
ON sl.sensorId = res.sensorId