Condividi tramite

Join dell'intervallo di tempo

  • Articolo

Si applica a: ✅Microsoft Fabric✅Esplora dati di Azure✅Monitoraggio di Azure✅Microsoft Sentinel

Spesso è utile creare un join tra due set di dati di grandi dimensioni su una chiave di cardinalità elevata, ad esempio un ID operazione o un ID sessione, e limitare ulteriormente i record di destra ($right) che devono corrispondere a ogni record lato sinistro ($left) aggiungendo una restrizione sulla "distanza di tempo" tra datetime colonne a sinistra e a destra.

L'operazione precedente differisce dall'operazione di join consueta, poiché per la parte equi-join della corrispondenza della chiave di cardinalità elevata tra i set di dati sinistro e destro, il sistema può anche applicare una funzione di distanza e usarla per velocizzare notevolmente il join.

Nota

Una funzione distance non si comporta come l'uguaglianza ( ovvero quando sia dist(x,y) che dist(y,z) sono true non segue che anche dist(x,z) è true. Questo viene talvolta definito "join diagonale".

Esempio per identificare le sequenze di eventi senza intervallo di tempo

Per identificare le sequenze di eventi all'interno di un intervallo di tempo relativamente piccolo, in questo esempio viene usata una tabella T con lo schema seguente:

  • SessionId: colonna di tipo string con ID di correlazione.
  • EventType: colonna di tipo string che identifica il tipo di evento del record.
  • Timestamp: colonna di tipo datetime indica quando si è verificato l'evento descritto dal record.
SessionId EventType Timestamp
0 Un 2017-10-01T00:00:00Z
0 B 2017-10-01T00:01:00Z
1 B 2017-10-01T00:02:00Z
1 Un 2017-10-01T00:03:00Z
3 Un 2017-10-01T00:04:00Z
3 B 2017-10-01T00:10:00Z

La query seguente crea il set di dati e quindi identifica tutti gli ID sessione in cui il tipo di evento A è stato seguito da un tipo di evento B all'interno di un intervallo di tempo 1min.

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
T
| where EventType == 'A'
| project SessionId, Start=Timestamp
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp
    ) on SessionId
| where (End - Start) between (0min .. 1min)
| project SessionId, Start, End

output

SessionId Inizio Fine
0 2017-10-01 00:00:00.0000000 2017-10-01 00:01:00.0000000

Esempio ottimizzato con intervallo di tempo

Per ottimizzare questa query, è possibile riscriverla per tenere conto dell'intervallo di tempo. Intervallo di tempo espresso come chiave di join. Riscrivere la query in modo che i valori datetime vengano "discretizzati" in bucket le cui dimensioni sono metà della dimensione dell'intervallo di tempo. Usare equi-join per confrontare gli ID bucket.

La query trova coppie di eventi all'interno della stessa sessione (SessionId) in cui un evento 'A' è seguito da un evento 'B' entro 1 minuto. Proietta l'ID sessione, l'ora di inizio dell'evento 'A' e l'ora di fine dell'evento 'B'.

let T = datatable(SessionId:string, EventType:string, Timestamp:datetime)
[
    '0', 'A', datetime(2017-10-01 00:00:00),
    '0', 'B', datetime(2017-10-01 00:01:00),
    '1', 'B', datetime(2017-10-01 00:02:00),
    '1', 'A', datetime(2017-10-01 00:03:00),
    '3', 'A', datetime(2017-10-01 00:04:00),
    '3', 'B', datetime(2017-10-01 00:10:00),
];
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Timestamp, TimeKey = bin(Timestamp, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Timestamp,
              TimeKey = range(bin(Timestamp-lookupWindow, lookupBin),
                              bin(Timestamp, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End

output

SessionId Inizio Fine
0 2017-10-01 00:00:00.0000000 2017-10-01 00:01:00.0000000

5 milioni di query di dati

La query successiva emula un set di dati esteso di 5M record e circa 1M ID sessione ed esegue la query con la tecnica dell'intervallo di tempo.

let T = range x from 1 to 5000000 step 1
| extend SessionId = rand(1000000), EventType = rand(3), Time=datetime(2017-01-01)+(x * 10ms)
| extend EventType = case(EventType < 1, "A",
                          EventType < 2, "B",
                          "C");
let lookupWindow = 1min;
let lookupBin = lookupWindow / 2.0;
T 
| where EventType == 'A'
| project SessionId, Start=Time, TimeKey = bin(Time, lookupBin)
| join kind=inner
    (
    T 
    | where EventType == 'B'
    | project SessionId, End=Time, 
              TimeKey = range(bin(Time-lookupWindow, lookupBin), 
                              bin(Time, lookupBin),
                              lookupBin)
    | mv-expand TimeKey to typeof(datetime)
    ) on SessionId, TimeKey 
| where (End - Start) between (0min .. lookupWindow)
| project SessionId, Start, End 
| count

output

Contare
3344

Contenuto correlato