azureazure-stream-analyticsstream-analytics

Is there a way to calculate the duration in stream analytics in case a input parameter has changed?


I am trying to calculate the duration of a signal (DI_1) from an datalogger which is connected to azure IoT Hub and stream analytics.

The input data looks like:

DateTime              Digital Input
22.04.2019 14:17:00        0
22.04.2019 14:16:00        1
22.04.2019 14:15:00        0
22.04.2019 14:14:00        0
22.04.2019 14:13:00        0
22.04.2019 14:12:00        0
22.04.2019 14:11:00        1
22.04.2019 14:10:00        1
22.04.2019 14:09:00        1
22.04.2019 14:08:00        1
22.04.2019 14:07:00        1
22.04.2019 14:06:00        0
22.04.2019 14:05:00        0
22.04.2019 14:04:00        0
22.04.2019 14:03:00        0
22.04.2019 14:02:00        1
22.04.2019 14:01:00        1
22.04.2019 14:00:00        0

And the result should looks like:

DateTime               Duration
22.04.2019 14:16:00    00:01:00
22.04.2019 14:07:00    00:05:00
22.04.2019 14:01:00    00:02:00

does anyone has an idea how to implement this in azure stream analytics?

at the moment ASA output is a dataset with time and digital input (1 or 0) when the digital input changes.

DateTime              Digital Input
22.04.2019 14:17:00        0
22.04.2019 14:16:00        1
22.04.2019 14:15:00        0
22.04.2019 14:14:00        1
22.04.2019 14:13:00        0
22.04.2019 14:12:00        1

realized with following code:

SELECT 
  PE, UID, system.timestamp AS Time,
  GetArrayElement(GetArrayElement(Record,0), 3) AS DI_0
INTO
   [PowerBI]
FROM
   [IoTHub]
WHERE
LAG(GetArrayElement(GetArrayElement(Record,0), 3), 1) 
OVER (LIMIT DURATION(minute, 10)) <> GetArrayElement(GetArrayElement(Record,0), 3)

Here I have to calculate the time difference in PowerBI. Now I want to calculate it direct in azure stream analytics to avoid calculation in PowerBI.

Thank you very much for your help!


Solution

  • I just solved it:

    SELECT
       UID,
       GetArrayElement(GetArrayElement(Record,0),3) AS DI_0,
       system.timestamp AS Start
    FROM
       [IotHub]
    WHERE
       LAG(GetArrayElement(GetArrayElement(Record,0), 3),1)
       OVER (LIMIT DURATION(minute, 10)) <> 
       GetArrayElement(GetArrayElement(Record,0), 3))
    
    SELECT   
       DATEDIFF (second, LAG(system.timestamp,1)
       OVER (LIMIT DURATION(minute, 10)), system.timestamp) AS zeitdiff,
       UID, Start, DI_0
    INTO
       [toPowerBI]
    FROM
       Startzeit 
    WHERE
       UID = 'WISE-4012_00D0C9E43D10'
    
    

    enter image description here