英文:
KQl- RowCumsum detection
问题
| UserId | T | status | StatusDuration(seconds) |
|---|---|---|---|
| 1 | 2023-03-20T17:09:33.572Z | 运行 | 0 |
| 1 | 2023-03-20T17:09:28.572Z | 运行 | 5 |
| 1 | 2023-03-20T17:09:23.568Z | 空闲 | 5 |
| 1 | 2023-03-20T17:09:18.628Z | 停止 | 5 |
| 1 | 2023-03-20T17:09:13.564Z | 运行 | 5 |
| 2 | 2023-03-20T17:09:08.56Z | 离线 | 0 |
| 2 | 2023-03-20T17:09:03.764Z | 离线 | 5 |
| 2 | 2023-03-20T17:08:58.556Z | 离线 | 10 |
| 2 | 2023-03-20T17:08:53.596Z | 运行 | 10 |
| 2 | 2023-03-20T17:08:48.552Z | 空闲 | 10 |
英文:
| UserId | T | status | StatusDuration(seconds) |
|---|---|---|---|
| 1 | 2023-03-20T17:09:33.572Z | RUNNING | 0 |
| 1 | 2023-03-20T17:09:28.572Z | RUNNING | 5 |
| 1 | 2023-03-20T17:09:23.568Z | Idle | 5 |
| 1 | 2023-03-20T17:09:18.628Z | stop | 5 |
| 1 | 2023-03-20T17:09:13.564Z | RUNNING | 5 |
| 2 | 2023-03-20T17:09:08.56Z | offline | 0 |
| 2 | 2023-03-20T17:09:03.764Z | offline | 5 |
| 2 | 2023-03-20T17:08:58.556Z | offline | 10 |
| 2 | 2023-03-20T17:08:53.596Z | RUNNING | 10 |
| 2 | 2023-03-20T17:08:48.552Z | Idle | 10 |
In above table have two userIds, within the userIds if status changing from one to another status at very first then remaining records in the userIds has the same time(T) difference when the first status changed timedifference ,, How to find the StatusDuration for like above ??
答案1
得分: 0
以下是代码部分的中文翻译：
我已使用问题中发布的示例数据创建了一个表，并使用以下代码计算了 StatusDuration(seconds) 列的值。
代码/查询
let tbl = datatable(UserId: string, T: datetime, Status: string)
[
1, datetime(2023-03-20T17:09:33.572Z), "RUNNING",
1, datetime(2023-03-20T17:09:28.572Z), "RUNNING",
1, datetime(2023-03-20T17:09:23.568Z), "Idle",
1, datetime(2023-03-20T17:09:18.628Z), "stop",
1, datetime(2023-03-20T17:09:13.564Z), "RUNNING",
2, datetime(2023-03-20T17:09:08.56Z), "offline",
2, datetime(2023-03-20T17:09:03.764Z), "offline",
2, datetime(2023-03-20T17:08:58.556Z), "offline",
2, datetime(2023-03-20T17:08:53.596Z), "RUNNING",
2, datetime(2023-03-20T17:08:48.552Z), "Idle"
];
tbl
| serialize
| order by T
| extend a = UserId != prev(UserId)
| extend ["StatusDuration(seconds)"] = case(a == true, 0, datetime_diff('second', prev(T), T))
| project-away a;
结果
英文:
I have used sample data posted in question to create a table and used below code to calculate value for **
StatusDuration(seconds)** column.
Code/Query
let tbl = datatable(UserId: string, T: datetime , Status: string )
[
1,datetime(2023-03-20T17:09:33.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:28.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:23.568Z), "Idle",
1,datetime(2023-03-20T17:09:18.628Z),"stop",
1,datetime(2023-03-20T17:09:13.564Z),"RUNNING",
2,datetime(2023-03-20T17:09:08.56Z), "offline",
2,datetime(2023-03-20T17:09:03.764Z), "offline",
2,datetime(2023-03-20T17:08:58.556Z), "offline",
2,datetime(2023-03-20T17:08:53.596Z), "RUNNING",
2,datetime(2023-03-20T17:08:48.552Z), "Idle"
];
tbl
| serialize
| order by T
| extend a = UserId != prev(UserId)
| extend ["StatusDuration(seconds)"] = case (a == true ,0 ,datetime_diff('second',prev(T),T) )
| project-away a;
Result
答案2
得分: -1
I will translate the code portion for you:
let tbl = datatable(UserId: string, T: datetime , Status: string )
[
1, datetime(2023-03-20T17:09:33.572Z), "RUNNING",
1, datetime(2023-03-20T17:09:28.572Z), "RUNNING",
1, datetime(2023-03-20T17:09:23.568Z), "Idle",
1, datetime(2023-03-20T17:09:18.628Z), "stop",
1, datetime(2023-03-20T17:09:13.564Z), "RUNNING",
2, datetime(2023-03-20T17:09:08.56Z), "offline",
2, datetime(2023-03-20T17:09:03.764Z), "offline",
2, datetime(2023-03-20T17:08:58.556Z), "offline",
2, datetime(2023-03-20T17:08:53.596Z), "RUNNING",
2, datetime(2023-03-20T17:08:48.552Z), "Idle"
];
tbl
| serialize
| partition hint.strategy= native by UserId
(
order by T desc
| extend batchId = row_rank(Status)
| where batchId == 1
| extend ["Duration status"] = row_cumsum(iff(UserId== prev(UserId) and Status == prev(Status),datetime_diff('second',prev(T),T),0))
)
I have translated the code portion as requested.
英文:
let tbl = datatable(UserId: string, T: datetime , Status: string )
[
1,datetime(2023-03-20T17:09:33.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:28.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:23.568Z), "Idle",
1,datetime(2023-03-20T17:09:18.628Z),"stop",
1,datetime(2023-03-20T17:09:13.564Z),"RUNNING",
2,datetime(2023-03-20T17:09:08.56Z), "offline",
2,datetime(2023-03-20T17:09:03.764Z), "offline",
2,datetime(2023-03-20T17:08:58.556Z), "offline",
2,datetime(2023-03-20T17:08:53.596Z), "RUNNING",
2,datetime(2023-03-20T17:08:48.552Z), "Idle"
];
tbl
| serialize
| partition hint.strategy= native by UserId
(
order by T desc
| extend batchId = row_rank(Status)
| where batchId == 1
| extend ["Duration status"] = row_cumsum(iff(UserId== prev(UserId) and Status == prev(Status),datetime_diff('second',prev(T),T),0))
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论