Stream analytics getting average for 1 year from history

Question

I have Stream Analytics job with INPUTS:

1) "InputStreamCSV" - linked to Event hub and recievies data . InputStreamHistory
2) "InputStreamHistory" - Input stream linked BlobStorage. InputStreamCSV

OUTPUTS:
1) "AlertOUT" - linked to table storage and inserts alarm event as row in table

I want to calculate AVERAGE amount for all transactions for year 2018(one number - 5,2) and compare it with transaction, that is comming in 2019:
If new transaction amount is bigger than average - put that transaction in "AlertOUT" output.

I am calculating average as :

SELECT AVG(Amount) AS TresholdAmount
FROM InputStreamHistory 
group by TumblingWindow(minute, 1)

Recieving new transaction as:

SELECT * INTO AlertOUT FROM InputStreamCSV TIMESTAMP BY EventTime

How can I combine this 2 queries to be able to check if new transaction amount is bigger than average transactions amount for last year?

The inputStreamHistory is history data,so it's stable,right?If so,you could use it as reference data and join it to the second sql. — Jay Gong, Jan 25 '19 at 09:30

score 1 · Answer 1 · answered Jan 25 '19 at 09:50

Please use JOIN operator in ASA sql,you could refer to below sql to try to combine the 2 query sql.

WITH 
t2 AS
(
    SELECT AVG(Amount) AS TresholdAmount
    FROM jsoninput2 
    group by TumblingWindow(minute, 1)
)
select t2.TresholdAmount
from jsoninput t1 TIMESTAMP BY EntryTime   
JOIN t2 
ON DATEDIFF(minute,t1,t2) BETWEEN 0 AND 5  
where t1.Amount > t2.TresholdAmount

If the history data is stable, you also could join the history data as reference data.Please refer to official sample.

score 0 · Accepted Answer · answered Jan 25 '19 at 09:50

If you are comparing last year's average with current stream, it would be better to use reference data. Compute the averages for 2018 using either asa itself or a different query engine to a storage blob. After that you can use the blob as reference data in asa query - it will replace the average computation in your example.

After that you can do a reference data join with inputStreamCsv to produce alerts.

Even if you would like to update the averages once in a while, above pattern would work. Based on the refresh frequency, you can either use another asa job or a batch analytics solution.

Stream analytics getting average for 1 year from history

2 Answers2