0

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?

sandrews
  • 1
  • 2
  • Hi, do you want to return count of the "1" for the last hour and return this count anytime you see a new event for this device? I would suggest to use COUNT ... OVER in the second query. If you have any sample data I can check if this will work for you. It will be something like: SELECT assetsignaltag, COUNT (*) OVER (PARTITION BY IoTData.ConnectionString LIMIT DURATION(hour,1) WHEN value=1) FROM IoTData – Jean-Sébastien Oct 21 '20 at 21:08
  • That's similar to what we have. So in our dataset we have: { "value": 1, "time":"10/21/2020 20:38PM", "assetsignaltag":"sandrews_test" } And we are doing a check for if that is equal to a predefined value. This in the full query actually does a check by a value in a reference table and exports that as ConditionResult. So in the HISTORY With statement above we are checking if it is equal to 1, if so export HISTORY.ConditionResult = 1. That's what we're trying to count, and it won't count just the values that are = 1 – sandrews Oct 22 '20 at 00:38

0 Answers0