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?