calculate cummulative sum in azure stream analytics

Question

Is it possible to calculate the cummulative sum over groups in azure stream analytics?

This is the input:

TMS                       product      amount
2023-06-01 10:00:00          P1          10
2023-06-01 10:00:20          P2          40
2023-06-01 10:00:25          P2          30
2023-06-01 10:01:05          P1          20
2023-06-01 10:01:20          P2          60
2023-06-01 10:01:25          P1          70

This should be the output;

TMS                       product      cum_sum
2023-06-01 10:00:00          P1          10
2023-06-01 10:00:20          P2          40
2023-06-01 10:00:25          P2          70
2023-06-01 10:01:05          P1          30
2023-06-01 10:01:20          P2          140
2023-06-01 10:01:25          P1          100

Unfortunately I have not found a solution yet.

Answer 1

Yes, it is possible to calculate the cumulative sum over groups in Azure Stream Analytics. You can use the PARTITION BY clause in combination with the SUM function to achieve this.

Here is the query that produces the desired output based on the input you provided.

Query:

SELECT
TMS, product,  SUM(amount)  OVER  (PARTITION  BY product LIMIT  DURATION  (minute, 5))  AS cum_sum
into 
output
FROM
input

LIMIT DURATION clause specifies how much history from the current row is included in the group. Since the sample input data is within five minutes, I have given limit duration (minute,5) in the above query.

Input:

Output:

Reference: OVER (Azure Stream Analytics) - Stream Analytics Query | Microsoft Learn