英文:
Kusto to construct a new table based on values from another table
问题
需要在Kusto中根据另一个表中的值构建新表的帮助:
我有一个这样的表格:
月末日期 值
2023-04-30 0
2023-05-31 1
2023-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 value
2023-04-30 0
2023-05-31 1
2023-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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论