Azure Stream Analytics - Count True Values

221 Views Asked by At

I am trying to do a count of results in Azure Stream Analytics that are a certain value using the following query:

WITH IoTData AS
( SELECT *,
    CAST(iothub.time AS datetime) AS time,
    TRY_CAST(iothub.value AS float) AS value,
    ref.AssetSignalTag AS assetsignaltag

    FROM iothub TIMESTAMP BY iothub.time

    JOIN masterdatasql ref 
    ON ref.[assetsignaltag] = iothub.assetsignaltag
    WHERE iothub.value IS NOT NULL
),

HISTORY AS ( 
SELECT assetsignaltag,
value,
COUNT(*) AS Count
CASE
 WHEN value = 1 THEN 1
 ELSE 0
END AS ConditionResult,
**LAG(IoTData.value) OVER (PARTITION BY IoTData.AssetSignalTag LIMIT DURATION(hour,1)) AS PreviousValue**
--this lag is used to get rid of duplicate entries coming in every minute

FROM IoTData
)


SELECT
    time,
    value,
    assetsignaltag,
    HISTORY.ConditionResult,
    HISTORY.Count,
    HISTORY.PreviousValue,
    **COUNT(*) OVER (PARTITION BY IoTData.assetsignaltag LIMIT DURATION(minute,30) WHEN HISTORY.ConditionResult=1) AS CountThirty,**
--this COUNT should only count when I get a HISTORY.ConditionResult = 1.
    AVG(HISTORY.ConditionResultAVG) OVER(PARTITION BY IoTData.assetsignaltag LIMIT DURATION(minute,10)) AS AverageOverTen

INTO eventhub
FROM IoTData 
INNER JOIN HISTORY
ON HISTORY.assetsignaltag = IoTData.assetsignaltag
AND DATEDIFF(millisecond, IoTData, HISTORY) BETWEEN 0 AND 500

The issue is when an asset submits {0, 1, 0, 1, 0}, instead of getting a count of 2, which is the count of the 1s that were submitted, I get 5, which is the total amount of values that come in. Additionally, it will set all values in HISTORY.ConditionResult as 1 in the second statement, even if they were 0 in the WITH statement.

Is this a result of the LAG function in the HISTORY statement, and if so how would I get a count of true values?

0

There are 0 best solutions below