英文:
Kusto to construct a new table based on values from another table
问题
需要在Kusto中根据另一个表中的值构建新表的帮助：
我有一个这样的表格：
月末日期 值2023-04-30 02023-05-31 12023-06-30 0
我需要构建类似这样的日期：
打印日期部分=范围(todatetime('2023-05-28'), todatetime('2023-07-03'), 1d)| mvexpand日期部分
并使用我的先前表格中的这些新日期范围来填写值。意思是，如果日期>=2023-06-30或最新日期，则用值列中的值0填写，如果在2023-06-30和2023-05-31之间，则用2023-05-31中的相应值1填写，如果小于2023-05-31，则用2023-04-30中的相应值0填写。date_part的构建可以不同，可以只使用extend并根据指定的范围添加日期，以便更好地实现结果。
英文:
Need help in Kusto to construct a new table based on values from another table:
I have a table like this:
month_end_date value2023-04-30 02023-05-31 12023-06-30 0
I need to construct dates like this:
print date_part=range(todatetime('2023-05-28'), todatetime('2023-07-03), 1d)| mvexpand date_part
and fill out values from my previous table with these new date ranges. meaning if the date is >=2023-06-30 or the latest date, fill out with the value in value column which is 0, and if it is between 2023-06-30 and 2023-05-31, with respected value in 2023-05-31 which is 1, and if less than 2023-05-31, with respected value for 2023-04-30 which is 0.
construction of date_part could be different like just using extend and add dates based on the range specified, whatever helps achieve better result.
答案1
得分: 1
我在我的环境中复制并获得了如下期望的结果：
以下是对我有效的KQL查询：
let originalTable = datatable(month_end_date: datetime, value: dynamic)[datetime(2023-04-30), 0,datetime(2023-05-31), 1,datetime(2023-06-30), 0];let dateRange=range x from todatetime('2023-05-28') to todatetime('2023-07-03') step 1d;let r=originalTable| join kind=fullouter (dateRange) on $left.month_end_date == $right.x| extend date1=iff(isnull(month_end_date),x,month_end_date)| order by date1 asc| project-away month_end_date,x| sort by date1 asc| extend rn=row_number();r| project date1,value| summarize c = make_list_with_nulls(value)| extend d = series_fill_forward(c)| mv-expand d = d| project-away c| extend valuess= toint(d)| extend new_column = 0| sort by new_column asc| extend rn=row_number()| join kind=inner r on rn| project-away rn, rn1,value,new_column,d
输出:
第一部分
第二部分
尝试按照上述查询来获取我所得到的期望结果。
英文:
I have reproduced in my environment and got expected results as below:
Below is the KQL query which worked for me:
let originalTable = datatable(month_end_date: datetime, value: dynamic)[datetime(2023-04-30), 0,datetime(2023-05-31), 1,datetime(2023-06-30), 0];let dateRange=range x from todatetime('2023-05-28') to todatetime('2023-07-03') step 1d;let r=originalTable| join kind=fullouter (dateRange) on $left.month_end_date == $right.x| extend date1=iff(isnull(month_end_date),x,month_end_date)|order by date1 asc|project-away month_end_date,x| sort by date1 asc| extend rn=row_number();r| project date1,value| summarize c = make_list_with_nulls(value)| extend d = series_fill_forward(c)| mv-expand d = d|project-away c|extend valuess= toint(d)| extend new_column = 0| sort by new_column asc| extend rn=row_number()| join kind=inner r on rn|project-away rn, rn1,value,new_column,d
Output:
1st part
2nd part
Try to follow to follow above query to get desired result as I have got.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论