azure-data-explorer - 如果 Term 到达某个点，Kusto row_cumsum 修改 Term

Question

我有一个按以下顺序列出的员工姓名和薪水列表

我需要按以下格式创建输出表。即，每当累计工资总额超过 3000 时，我必须检测到并标记该行。

我试图做 row_cumsum 并在它超过 3000 时重置 Term，但它在第二次迭代中不起作用。

datatable (name:string, month:int, salary:long)
[
    "Alice", 1, 1000,
    "Alice", 2, 2000,
    "Alice", 3, 1400,
    "Alice", 3, 1400,
    "Alice", 3, 1400,
]
| order by name asc, month asc
| extend total=row_cumsum(salary) 
| extend total=iff(total >=3000,total-prev(total),total)

Answer 1

现在可以使用 scan operator :

datatable (name:string, salary:long)
[
    "Alice", 1000,
    "Alice", 2000,
    "Alice", 1400,
    "Alice", 1400,
    "Alice", 1400,
    "Alice", 1000,
    "Bob", 2400,
    "Bob", 1000,
    "Bob", 1000
]
| sort by name asc
| scan declare (total:long) with 
(
    step s: true => total = iff(isnull(s.total) or name != s.name, salary, iff(s.total < 3000, s.total + salary, salary));
)
| extend boundary_detected = iff(total >= 3000, 1, long(null))

姓名
薪水
全部的
检测到边界


爱丽丝
1000
1000

爱丽丝
2000年
3000
1

爱丽丝
1400
1400

爱丽丝
1400
2800

爱丽丝
1400
4200
1

爱丽丝
1000
1000

鲍勃
2400
2400

鲍勃
1000
3400
1

鲍勃
1000
1000