CODEWITHSUNDEEP
streamazure-stream-analyticsstream-analytics
Kurt
Kurt

Reputation: 3

Stream analytics getting average for 1 year from history

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?

Upvotes: 0

Views: 298

Answers (2)

Jay Gong
Jay Gong

Reputation: 23782

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.

Upvotes: 1

Vignesh Chandramohan
Vignesh Chandramohan

Reputation: 1306

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.

Upvotes: 0

Related Questions