Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
WITH AggregationStep AS
(
SELECT
System.Timestamp as tumblingWindowEnd,
AVG(temperature) as avgTemperature
FROM TemperatureSensor
GROUP BY TumblingWindow(second, 5)
),
FillInMissingValuesStep AS
(
SELECT
TopOne() OVER (ORDER BY tumblingWindowEnd DESC) AS lastEvent
FROM AggregationStep
GROUP BY HOPPINGWINDOW(second, 300, 5)
),
AnomalyDetectionStep AS
(
SELECT
lastEvent.tumblingWindowEnd as anomalyTime,
system.timestamp as anomalyDetectedTime,
lastEvent.avgTemperature as avgTemperature,
ANOMALYDETECTION(lastEvent.avgTemperature) OVER (LIMIT DURATION(minute, 10)) as scores
FROM FillInMissingValuesStep
),
OutputSet AS
(
SELECT
anomalyTime,
anomalyDetectedTime,
avgTemperature,
CAST(GetRecordPropertyValue(scores, 'BiLevelChangeScore') as float) as [Bi Level Change],
CAST(GetRecordPropertyValue(scores, 'SlowPosTrendScore') as float) as [Slow Postive Trend],
CAST(GetRecordPropertyValue(scores, 'SlowNegTrendScore') as float) as [Slow Negative Trend]
FROM AnomalyDetectionStep
)
SELECT
anomalyTime,
anomalyDetectedTime,
avgTemperature,
[Bi Level Change],
[Slow Postive Trend],
[Slow Negative Trend],
CASE
WHEN [Bi Level Change]>3.25 AND [Bi Level Change]> [Slow Postive Trend] AND [Bi Level Change]> [Slow Negative Trend]
THEN 'Bi Level Change'
WHEN [Slow Postive Trend]>3.25 AND [Bi Level Change]< [Slow Postive Trend] AND [Slow Postive Trend]> [Slow Negative Trend]
THEN 'Slow Postive Trend'
WHEN [Slow Postive Trend]>3.25 AND [Bi Level Change]< [Slow Negative Trend] AND [Slow Postive Trend]< [Slow Negative Trend]
THEN 'Slow Negative Trend'
ELSE ''
END AS anomalyType
INTO TelemetryAlert
FROM OutputSet
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.