英文:
calculate cummulative sum in azure stream analytics
问题
可以在Azure Stream Analytics中计算分组的累积总和吗?
这是输入数据:
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
这应该是输出结果:
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
很抱歉,我还没有找到解决方案。
英文:
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.
答案1
得分: 1
是的,在Azure Stream Analytics中可以计算分组的累积总和。您可以使用PARTITION BY
子句与SUM
函数结合使用来实现此目的。
以下是基于您提供的输入生成所需输出的查询。
查询:
SELECT
TMS, product, SUM(amount) OVER (PARTITION BY product LIMIT DURATION (minute, 5)) AS cum_sum
into
output
FROM
input
LIMIT DURATION
子句指定了从当前行中包括多少历史数据在组中。由于示例输入数据在五分钟内,因此我在上面的查询中使用了limit duration (minute,5)
。
输入:
输出:
参考文档:OVER (Azure Stream Analytics) - Stream Analytics Query | Microsoft Learn
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论