Azure Monitor日志中的日期时间差(Kusto查询语言) [英] Date time difference within a column in Azure Monitor Logs (Kusto Query Language)
问题描述
我的Azure Monitor日志中的Clickstream数据采用以下格式:
I have clickstream data in Azure Monitor Logs in this format:
Timestamp Category Session_ID Step_Name
10/22/2019, 9:28:14.868 AM A ++9Ti step 1
10/22/2019, 9:28:18.034 AM A ++9Ti step 2
10/22/2019, 9:28:22.487 AM A ++9Ti step 3
10/23/2019, 7:02:02.527 AM B ++MoY step 1
10/23/2019, 7:02:09.244 AM B ++MoY step 2
10/23/2019, 7:02:21.156 AM B ++MoY step 3 <--
10/23/2019, 7:02:27.195 AM B ++MoY step 3 <--
10/23/2019, 7:15:13.544 AM A ++0a3 step 1
10/23/2019, 7:15:35.438 AM A ++0a3 step 2
我需要获取消费者在类别"中每个步骤上花费的平均时间
I need to get the mean time that a consumer spends on each step in a Category
此外,当重复执行步骤时(例如session_ID ='++ MoY'中的步骤3),我们需要在计算平均值时采用最新的时间戳.
Also, when steps are repeated (like step 3 in session_ID = '++MoY'), we need to take the latest timestamp while calculating the mean.
示例:类别A在步骤2上花费的平均时间为(3.166 + 21.894)/2 = 12.53秒. (注意:时间戳记给出了完成步骤的时间)
Example : Mean time spent on step 2 in category A is (3.166 + 21.894)/2 = 12.53 seconds. (Note : timestamp gives time at which step is completed)
推荐答案
您可以尝试以下操作
a)使用arg_max()
按步骤/类别获取最新信息
a) using arg_max()
to take the latest by step/category
b)在order by
之后使用prev()
来计算每个步骤的持续时间
b) using prev()
after order by
to calculate the duration for each step
datatable(Timestamp:datetime, Category:string, Session_ID:string, Step_Name:string)
[
datetime(10/22/2019, 9:28:14.868 AM), 'A', '++9Ti', 'step 1',
datetime(10/22/2019, 9:28:18.034 AM), 'A', '++9Ti', 'step 2',
datetime(10/22/2019, 9:28:22.487 AM), 'A', '++9Ti', 'step 3',
datetime(10/23/2019, 7:02:02.527 AM), 'B', '++MoY', 'step 1',
datetime(10/23/2019, 7:02:09.244 AM), 'B', '++MoY', 'step 2',
datetime(10/23/2019, 7:02:21.156 AM), 'B', '++MoY', 'step 3',
datetime(10/23/2019, 7:02:27.195 AM), 'B', '++MoY', 'step 3',
datetime(10/23/2019, 7:15:13.544 AM), 'A', '++0a3', 'step 1',
datetime(10/23/2019, 7:15:35.438 AM), 'A', '++0a3', 'step 2',
]
| summarize arg_max(Timestamp, *) by Step_Name, Session_ID
| order by Session_ID asc, Timestamp asc
| extend duration = iff(Session_ID == prev(Session_ID), Timestamp - prev(Timestamp), 0s)
| summarize avg(duration) by Step_Name, Category
| where Step_Name == "step 2" and Category == "A"
这篇关于Azure Monitor日志中的日期时间差(Kusto查询语言)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!