英文:
I want to generate 24 hour data(by filtering the date column to 24 hours) in KUSTO by adding custom start time and endtime values
问题
我有一个表格中的日期和状态数值，我想选择一个为期24小时的日期范围，我想获取所有状态数值，包括开始和结束日期数值。但这些数值不在表格中。必须从以前的记录中获取。
我已经在截图中附上了示例数据，以便更好地理解。
我尝试通过创建一个带有prev()状态值的列来实现，但通过这种方式我无法获取最后一条记录及其状态值。
您能帮助我如何获取开始和结束的自定义日期值吗？
此外，如果在所选时间段内找不到数据，是否可以显示上一条记录？这是可能的吗？
英文:
I have date and status values in a table, i want to select the date range for a period of 24h, I want to get all the status values including starting and ending date values. But those values were not present in the table. It has to get it from previous record.
I have attached the sample data in the screenshot for better understanding.
I have tried by creating a column with prev() staus value and by doing this i am not able to get the last record and its status value.
Can you help how can i get both starting and ending custom date values.
Also for selected time period if there is no data found, it should display the previous record. Is it possible?
答案1
得分: 0
我已使用一些虚拟数据创建了一个表格。UDF（用户定义函数）创建期望 starttime 和 endtime 参数以实现问题中所需的期望结果。
代码/查询
let myFunc = (CustomStartDate: datetime, CustomEndDate: datetime) {
// 创建名为 "tbl" 的临时表
let tbl = datatable(Time: datetime, Status: string)
[
datetime(2023-03-16 07:14:58.4785756), "Running",
datetime(2023-03-16 11:15:49.7803968), "Stopped",
datetime(2023-03-16 13:46:31.8371005), "Idle",
datetime(2023-03-16 21:00:00.0000000), "Stopped",
datetime(2023-03-16 23:00:00.0000000), "Running",
datetime(2023-03-17 05:16:51.9252773), "Stopped",
datetime(2023-03-17 13:16:51.9252773), "Running"
];
// 选择在起始日期和结束日期之间的记录并创建新的临时表 "tbl1"
let tbl1 = tbl | where Time between (CustomStartDate .. CustomEndDate);
// 创建两个变量来存储在自定义时间范围内的最小和最大时间值，以获取先前和当前的状态
let minDate = tbl1 | summarize min(Time);
let maxDate = tbl1 | summarize max(Time);
let PreStatus = tbl | where Time < toscalar(minDate) | order by Time desc | project Status | take 1;
let CustomEndStatus = tbl1 | where Time == toscalar(maxDate) | project Status;
// 将自定义日期和状态与 tbl1 进行合并，并按时间升序排序表格
tbl1
| union (print Time = CustomStartDate, Status = toscalar(PreStatus))
| union (print Time = CustomEndDate, Status = toscalar(CustomEndStatus))
| order by Time asc
};
myFunc(datetime(2023-03-16 20:00:00.0000000), datetime(2023-03-17 12:00:00.0000000))
结果
英文:
I have used some dummy data to create a table. The UDF created expects starttime and endtime parameters to achieve the desired result asked in question.
Code/Query
let myFunc = (CustomStartDate : datetime ,CustomEndDate : datetime )
{
//Created temporary table named "tbl"
let tbl = datatable(Time: datetime , Status: string )
[
datetime(2023-03-16 07:14:58.4785756), "Running",
datetime(2023-03-16 11:15:49.7803968), "Stopped",
datetime(2023-03-16 13:46:31.8371005), "Idle",
datetime(2023-03-16 21:00:00.0000000),"Stopped",
datetime(2023-03-16 23:00:00.0000000),"Running",
datetime(2023-03-17 05:16:51.9252773), "Stopped",
datetime(2023-03-17 13:16:51.9252773), "Running"
];
//selecting records which falls between start date and end date and creating new temporary table named "tbl1"
let tbl1 = tbl | where Time between ( CustomStartDate .. CustomEndDate);
//creating 2 variables to store minimum and maximum time values within the custom time provided to get the previous and current status
let minDate = tbl1 | summarize min(Time);
let maxDate = tbl1 | summarize max(Time);
let PreStatus = tbl | where Time < toscalar(minDate)| order by Time desc | project Status | take 1;
let CustomEndStatus = tbl1 | where Time == toscalar(maxDate) |project Status ;
//Union custom dates and status with tbl1 and sorted the table on basis of Time ascending order.
tbl1
| union (print Time = CustomStartDate, Status = toscalar(PreStatus))
| union (print Time = CustomEndDate, Status = toscalar(CustomEndStatus ))
| order by Time asc
};
myFunc(datetime(2023-03-16 20:00:00.0000000),datetime(2023-03-17 12:00:00.0000000))
Result
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论