locked
Is there a way to use both Aggregate and Analytic Functions in the same Stream Analytics query to get the result RRS feed

  • Question

  • I am receiving an IOT data input every minute which has an Integer and String Value fields. In my stream analytics query how can I get the average of last 5 minutes for integer value field and the 5th minute value of the String variable. Example: [A,1],[B,2],[C,3],[D,4],[E,5] Result: E,3 (Last String value =E & Average of 1,2,3,4,5=3). Individually I am able to get these values using Aggregate and Analytical Built-in Functions, but it would be great if there is a way to get the results in a single query. Let me know if that's possible ?

    Saturday, August 18, 2018 12:01 AM

All replies

  • Hi,

    You can use the LAST keyword to retrieve the latest value, as well as an aggregate. Here's an example:

    select avg(temperature), LAST(stringvalue) OVER (LIMIT DURATION(minute, 5)) as lastvalue FROM input

    GROUP BY LAST(stringvalue) OVER (LIMIT DURATION(minute, 5)) ,tumblingwindow(minute, 5)

    Let me know if you have any question.

    Monday, August 20, 2018 5:07 AM