JOIN (Azure Stream Analytics)

  • Artikel

Precis som standard-T-SQL används JOIN i Azure Stream Analytics-frågespråket för att kombinera poster från två eller flera indatakällor. JOIN i Azure Stream Analytics är temporala, vilket innebär att varje JOIN måste ange vissa gränser för hur långt matchande rader kan avgränsas i tid. Till exempel är "join TollBoothEntry events with TollBoothExit events when they occur on the same LicensePlate and TollId and within 5 minutes of each other" legitimt. men "join TollBoothEntry events with TollBoothExit events when they occur on the LicensePlate and TollId" is not – it would match each TollBoothEntry with an unbounded and potentially infinite collection of all TollBoothExit to the same LicensePlate and TollId.

Tidsintervallen för relationen anges i ON-satsen i JOIN med hjälp av funktionen DATEDIFF. Den maximala DATEIFF-storleken är sju dagar. Mer information om dess allmänna användning finns i DATEDIFF (Azure Stream Analytics). När DATEDIFF används i JOIN-villkoret får den andra och tredje parametern särskild behandling.

Det går dessutom inte att använda SELECT * i JOIN-instruktioner.

Syntax

[ FROM { <input_source> } [ ,...n ] ]  
<input_source> ::=   
{  
    input_name [ [ AS ] input_alias ]   
    | <joined_table>   
}  
  
<joined_table> ::=   
{  
    <input_source> <join_type> <input_source> ON <join_condition>   
    | [ <input_source> <join_type> <reference_data> ON <join_condition> ]  
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ { INNER | LEFT [ OUTER ] } ] JOIN

Argument

<input_source>

Anger indatakällan.

<reference_data>

Referensdata som du vill ansluta till input_source. Mer information finns i avsnittet Referensdataanslutning.

<join_type>

Anger typen av kopplingsåtgärd.

Ansluta sig till

Anger att den angivna kopplingsåtgärden ska ske mellan de angivna indatakällorna och /eller referensdata. Alla rader från vänster och höger som uppfyller kopplingsvillkoret ingår i resultatuppsättningen.

Varning

Om JOIN-källorna är partitionerade måste JOIN-predikatet innehålla ett villkor som matchar partitionsnycklarna för båda källorna.

[ VÄNSTER YTTRE KOPPLING ]

Anger att alla rader från den vänstra tabellen som inte uppfyller kopplingsvillkoret ingår i resultatuppsättningen, och utdatakolumner från den andra tabellen är inställda på NULL utöver alla rader som returneras av den inre kopplingen.

PÅ <join_condition>

Anger det villkor som kopplingen baseras på. Kopplingsvillkoret måste ha ett tidsbundet eller ett tidsmässigt svängrum som definierats för relationen och anges i ON-satsen i JOIN, med hjälp av specialsyntaxen för funktionen Special DATEDIFF för JOIN.

Exempel

I Azure Stream Analytics har alla händelser en väldefinierad tidsstämpel. Användaren måste därför använda radalias direkt i funktionen DATEDIFF enligt följande:

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15

Kopplingsvillkoret ovan resulterar i en matchning om och endast om ExitTime inträffar efter EntryTime, men inte mer än 15 minuter senare.

Anteckning

DATEDIFF som används i SELECT-instruktionen använder den allmänna syntaxen där en datetime-kolumn eller ett uttryck skickas som den andra och tredje parametern. Men när funktionen DATEDIFF används i JOIN-villkoret används input_source namn eller alias. Internt väljs tidsstämpeln som är associerad för varje händelse i källan.

Tidsbundna villkor kan kombineras med varandra och med andra villkor i ON-satsen, t.ex.

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15

När du ansluter till tre eller flera tabeller gäller samma regler --- tidsintervall måste se till att alla matchade händelser inträffar inom en begränsad tidsperiod från varandra. För att till exempel hitta alla fel som inträffade mellan transaktionens start- och transaktionssluthändelse kan man säga:

SELECT TS.Id, TS.Name, TS.Amount, E.ErrorCode, E.Description   
FROM TStart TS TIMESTAMP BY TStartTime   
JOIN TEnd TE TIMESTAMP BY TEndTime  
ON DATEDIFF(second, TS, TE) BETWEEEN 0 AND 5  
AND TS.Id = TE.Id  
JOIN Error E TIMESTAMP BY ErrorTime  
ON DATEDIFF(second, TS, E) BETWEEN 0 AND 5
AND DATEDIFF(second, TE, E) < 0
AND E.TId = TS.Id

När du ansluter källor som är partitionerade måste JOIN-predikatet innehålla ett villkor som matchar partitionsnycklarna för båda källorna.

SELECT I1.TollId, I1.EntryTime,I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationInMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime PARTITION BY PartitionId  
JOIN Input2 I2 TIMESTAMP BY ExitTime PARTITION BY PartitionId  
ON I1.PartitionId = I2.PartitionId AND DATEDIFF(minute,I1,I2) BETWEEN 0 AND 15

Slutligen stöder Azure Stream Analytics både inre koppling (standard) och vänster yttre koppling. För en inre koppling returneras resultatet bara om en matchning hittas. Men om en händelse från vänster sida i kopplingen är omatchad i en LEFT OUTER-koppling (vänster yttre) returneras en rad med Null för alla kolumner i den högra raden. Här är till exempel ett exempel för att hitta frånvaron av händelser. Följande fråga returnerar de rader där ett fordon har gått in i en avgiftsbelagd monter men inte har lämnat Booth inom 15 minuter.

SELECT I1.TollId, I1.EntryTime, I2.ExitTime, I1.LicensePlate, DATEDIFF(minute,I1.EntryTime,I2.ExitTime) AS DurationinMinutes   
FROM Input1 I1 TIMESTAMP BY EntryTime   
LEFT OUTER JOIN Input2 I2 TIMESTAMP BY ExitTime  
ON I1.TollId=I2.TollId  
AND I1.LicensePlate=I2.LicensePlate  
AND DATEDIFF( minute , I1 , I2 ) BETWEEN 0 AND 15   
WHERE I2.TollId IS NULL

Särskild DATEDIFF-funktion för JOIN

Syntax

DATEDIFF ( datepart , input_source1, input_source2 )

Argument

dateparts

Exempel: "second", "millisekunder", "minut" osv.)

input_source1

Den första indatakällan i joinen. Internt skickas tidsstämpeln som är associerad med händelserna från den här input_source till funktionen.

input_source2

Den andra indatakällan i joinen. Internt skickas tidsstämpeln som är associerad med händelserna från den här input_source till funktionen.

Returtyp

Returnerar antalet enheter i dateparts som förflutit från tidsstämpeln för input_source1 till tidsstämpeln för input_source2. Det returnerade värdet kan vara negativt om tidsstämpeln för andra input_source är större än den första.