CODEWITHSUNDEEP
azure-data-explorerkqlkusto-explorer
RoeyB
RoeyB

Reputation: 43

Kusto Join Query - How to join 2 tables on $left.column1 < $right.column2

I have 2 tables in Kusto:

  1. windowScans - each row is from this format : windowStart:long, windowEnd:long
  2. files - each row is from this format: timestamp:long, fileId:string

I would like to join for each file all the windowScan rows that matches: timestamp > windowStart && timestamp < windowEnd. The result should be a table of all files, and for each file all the matching pairs of windowScans. It is possible that a windowScan row will appear in many files. Any idea how to perform the query?

Upvotes: 1

Views: 3575

Answers (1)

Avnera
Avnera

Reputation: 7618

Here is one solution:

let windowsScan = datatable(windowStart:long, windowEnd:long)[1,5, 6,8, 10, 14];
let files = datatable(timestamp:long, fileId:string)[3,"a", 4,"b", 4,"c", 6, "a", 11,"a", 13, "b"];
windowsScan
| extend timestamp = range(windowStart, windowEnd, 1)
| mv-expand timestamp to typeof(long)
| join kind=inner (files) on timestamp
| summarize take_any(windowStart, windowEnd) by fileId, timestamp

Results:

fileId timestamp windowStart windowEnd
a 3 1 5
b 4 1 5
c 4 1 5
a 6 6 8
a 11 10 14
b 13 10 14

Upvotes: 1

Related Questions