/*
Выполнение преобразования потока событий
*/
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