英文:
Kusto join two tables based on latest available date
问题
有没有办法在Kusto上将两个表连接起来，并根据第二个表中的最新可用日期连接值？
假设我们从第一个表中获取不同的名称，并希望根据最新可用日期连接第二个表中的值。
我还只会保留左列的匹配项。
表1
名称
-----
亚历克斯
约翰
玛丽
表2
名称 体重 日期
----- ------ ------
亚历克斯 160. 2023-01-20
亚历克斯 168. 2023-01-28
玛丽 120. 2022-08-28
玛丽 140. 2020-09-17
示例代码：
表1
|distinct 名称
|join kind=inner 表2 on $left.名称==$right.名称
英文:
Is there a way to join two tables on Kusto, and join values based on latest available date from the second table?
Let's say we get distinct names from first table, and want to join values from the second table based on latest available dates.
I would also only keep matches from left column.
table1
names
-----
Alex
John
Mary
table2
name weight date
----- ------ ------
Alex. 160. 2023-01-20
Alex. 168. 2023-01-28
Mary. 120. 2022-08-28
Mary. 140. 2020-09-17
Sample code:
table1
|distinct names
|join kind=inner table2 on $left.names==$right.name
答案1
得分: 1
let table1 = datatable(names:string)
[
"Alex"
,"John"
,"Mary"
];
let table2 = datatable(name:string, weight:real ,["date"]:datetime)
[
"Alex" ,160 ,datetime(2023-01-20)
,"Alex" ,168 ,datetime(2023-01-28)
,"Mary" ,120 ,datetime(2022-08-28)
,"Mary" ,140 ,datetime(2020-09-17)
];
table1
| distinct names
| join kind=inner (table2 | summarize arg_max(['date'], *) by name) on $left.names==$right.name
| names | name | date | weight |
|---|---|---|---|
| Mary | Mary | 2022-08-28T00:00:00Z | 120 |
| Alex | Alex | 2023-01-28T00:00:00Z | 168 |
<details>
<summary>英文:</summary>
let table1 = datatable(names:string)
[
"Alex"
,"John"
,"Mary"
];
let table2 = datatable(name:string, weight:real ,["date"]:datetime)
[
"Alex" ,160 ,datetime(2023-01-20)
,"Alex" ,168 ,datetime(2023-01-28)
,"Mary" ,120 ,datetime(2022-08-28)
,"Mary" ,140 ,datetime(2020-09-17)
];
table1
| distinct names
| join kind=inner (table2 | summarize arg_max(['date'], *) by name) on $left.names==$right.name
| names | name | date | weight |
|-------|------|----------------------|--------|
| Mary | Mary | 2022-08-28T00:00:00Z | 120 |
| Alex | Alex | 2023-01-28T00:00:00Z | 168 |
[Fiddle][1]
[1]: https://dataexplorer.azure.com/clusters/help/databases/Samples?query=H4sIAAAAAAAAA22QUUvDMBSF3wv9D5cwWCOJpFG2WumDr4K/oBTJ7LWLpimkETfZj7fNWpFuecm5ycc5yTHowaudwRQKqJVXYUisarHPe%20%200bWgclXEEwyJPBg8kaEaeu72d9Ytyx0FXj3FkZkN5YTj5MfhG3ex97lAZYCUZKCRVPm5et7jMA5ZuBLD5OpFC3nGRcinoFP%20HZdewjP5/5YDJhZvkIruC3S8wwcUDT7f0/M9zaXF0glr3Xts3D6G08eSj0xY%20ta0LbS06SKZCTtB/ta1y%20gdBuea1VYekXI8Z64rBDYXdMZhQ6CysDL772%20BZFCs3NhamXza%20mJSyAQAA
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论