在Azure Stream Analytics中计算累积总和。

huangapple go评论62阅读模式
英文:

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)

输入:
在Azure Stream Analytics中计算累积总和。

输出:
在Azure Stream Analytics中计算累积总和。

参考文档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:
在Azure Stream Analytics中计算累积总和。

Output:
在Azure Stream Analytics中计算累积总和。

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

huangapple
  • 本文由 发表于 2023年6月26日 20:57:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76556920.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定