Kusto基于另一个表的值构建新表。

huangapple go评论111阅读模式
英文:

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

输出:

第一部分
Kusto基于另一个表的值构建新表。

第二部分
Kusto基于另一个表的值构建新表。

Fiddle

尝试按照上述查询来获取我所得到的期望结果。

英文:

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

Kusto基于另一个表的值构建新表。

Output:

1st part
Kusto基于另一个表的值构建新表。

2nd part

Kusto基于另一个表的值构建新表。

Fiddle.

Try to follow to follow above query to get desired result as I have got.

huangapple
  • 本文由 发表于 2023年7月11日 05:18:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76657407.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定