I am trying to calculate some linear regression with Azure Stream Analytics.
The Setup: There is a Sensor sending temperature and time to an IoT-Hub, the Stream-Analytics is listening to the IoT-Hub with a SlidingWindow and should calculate the trend of the temperature in this window (with linear regression).
An event looks like this:
{"deviceId":"sensor_1","temp":322.3376736446427,"time":1517500183940}
The SQL I got so far is this:
WITH Step1 AS
(
select time AS x, avg(time) over () AS x_bar,
temp AS y, avg(temp) over () AS y_bar
FROM inputStream
GROUP BY SlidingWindow(second,10)
),
Step2 AS (
SELECT (sum((x - x_bar) * (y - y_bar)) / sum((x - x_bar) * (x - x_bar))) AS slope,
max(x_bar) AS x_bar_max,
max(y_bar) AS y_bar_max
FROM Step1
),
FinalStep AS (
SELECT slope,
y_bar_max - x_bar_max * slope AS intercept
FROM Step2
)
SELECT * INTO outputEventHub FROM FinalStep
The original Linear Regression SQL-Template from here looked like this:
select slope,
y_bar_max - x_bar_max * slope as intercept
from (
select sum((x - x_bar) * (y - y_bar)) / sum((x - x_bar) * (x - x_bar)) as slope,
max(x_bar) as x_bar_max,
max(y_bar) as y_bar_max
from (
select x, avg(x) over () as x_bar,
y, avg(y) over () as y_bar
from ols) s;
)
If you know some better way to do this without Stream Analytics I am also fine. Please share your thoughts and thanks in advance!