Delen via

JOIN (Azure Stream Analytics)

  • Artikel

Net als standaard T-SQL wordt JOIN in de Azure Stream Analytics-querytaal gebruikt om records uit twee of meer invoerbronnen te combineren. JOIN in Azure Stream Analytics is tijdelijk van aard, wat betekent dat elke JOIN een aantal limieten moet bieden voor hoe ver de overeenkomende rijen in de tijd kunnen worden gescheiden. Bijvoorbeeld: "Join TollBoothEntry events with TollBoothExit events when they occur on the same LicensePlate and TollId and within 5 minutes of each other" (Bijvoorbeeld: 'Join TollBoothEntry events with TollBoothExit events when they occur on the same LicensePlate and TollId and within 5 minutes of each other' is legitiem; Maar 'Join TollBoothEntry events with TollBoothExit events when they occur on the LicensePlate and TollId' is niet. Het zou overeenkomen met elke TollBoothEntry met een niet-gebonden en potentieel oneindige verzameling van alle TollBoothExit op dezelfde LicensePlate en TollId.

De tijdsgrenzen voor de relatie worden opgegeven in de ON-component van de JOIN, met behulp van de functie DATEDIFF. De maximale DATEDIFF-grootte is zeven dagen. Zie DATEDIFF (Azure Stream Analytics) voor meer informatie over het algemene gebruik. Wanneer DATEDIFF wordt gebruikt binnen de JOIN-voorwaarde, krijgen de tweede en derde parameter een speciale behandeling.

Bovendien kan SELECT * niet worden gebruikt in JOIN-instructies.

Syntaxis

[ 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

Argumenten

<input_source>

Hiermee geeft u de invoergegevensbron op.

<reference_data>

De referentiegegevens waaraan u uw input_source wilt toevoegen. Zie de sectie Referentiegegevens koppelen voor meer informatie.

<join_type>

Hiermee geeft u het type joinbewerking op.

JOIN

Geeft aan dat de opgegeven koppelingsbewerking moet plaatsvinden tussen de opgegeven invoerbronnen en /of referentiegegevens. Alle rijen van links en rechts die voldoen aan de join-voorwaarde worden opgenomen in de resultatenset.

Waarschuwing

Als de JOIN-bronnen zijn gepartitioneerd, moet het JOIN-predicaat een voorwaarde bevatten die overeenkomt met de partitiesleutels van beide bronnen.

[ LEFT OUTER JOIN ]

Hiermee geeft u op dat alle rijen van de linkertabel die niet voldoen aan de joinvoorwaarde worden opgenomen in de resultatenset en dat uitvoerkolommen van de andere tabel worden ingesteld op NULL naast alle rijen die worden geretourneerd door de inner join.

OP <join_condition>

Hiermee geeft u de voorwaarde op waarop de join is gebaseerd. De joinvoorwaarde moet een tijdgebonden of tijdelijke wiggle-ruimte hebben die is gedefinieerd voor de relatie en wordt opgegeven in de ON-component van de JOIN, met behulp van de speciale syntaxis van de functie Speciale DATEDIFF voor DE FUNCTIE JOIN.

Voorbeelden

In Azure Stream Analytics hebben alle gebeurtenissen een goed gedefinieerd tijdstempel. De gebruiker moet dus rijaliassen rechtstreeks in de functie DATEDIFF gebruiken, als volgt:

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

De bovenstaande joinvoorwaarde resulteert in een overeenkomst als en alleen als de ExitTime plaatsvindt na de EntryTime, maar niet meer dan 15 minuten later.

Notitie

DATEDIFF die wordt gebruikt in de SELECT-instructie maakt gebruik van de algemene syntaxis waarbij een datum/tijd-kolom of expressie wordt doorgegeven als de tweede en derde parameter. Wanneer de functie DATEDIFF echter wordt gebruikt binnen de JOIN-voorwaarde, wordt de input_source naam of de alias ervan gebruikt. Intern wordt de tijdstempel gekozen die is gekoppeld aan elke gebeurtenis in die bron.

Tijdsgebonden voorwaarden kunnen met elkaar en met andere voorwaarden binnen de ON-component worden gecombineerd, bijvoorbeeld:

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

Wanneer u drie of meer tabellen samenvoegt, gelden dezelfde regels --- tijdsgrenzen ervoor moeten zorgen dat alle overeenkomende gebeurtenissen binnen een beperkte tijd van elkaar plaatsvinden. Als u bijvoorbeeld alle fouten wilt vinden die zijn opgetreden tussen het begin en het einde van de transactie, kunt u zeggen:

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

Bij het samenvoegen van gepartitioneerde bronnen moet het JOIN-predicaat een voorwaarde bevatten die overeenkomt met de partitiesleutels van beide bronnen.

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

Ten slotte ondersteunt Azure Stream Analytics zowel inner join (de standaardinstelling) als LEFT outer join. Voor een inner join wordt alleen een resultaat geretourneerd wanneer een overeenkomst is gevonden. Maar als in geval van een LEFT OUTER-join geen overeenkomst wordt gevonden met een gebeurtenis vanaf de linkerkant van de join, wordt een rij met NULL voor alle kolommen in de rechterrij geretourneerd. Hier volgt bijvoorbeeld een voorbeeld om de afwezigheid van gebeurtenissen te vinden. De volgende query retourneert de rijen waarin een voertuig een tolpoort is binnengegaan, maar de stand niet binnen 15 minuten heeft verlaten.

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

Speciale DATEDIFF-functie voor JOIN

Syntaxis

DATEDIFF ( datepart , input_source1, input_source2 )

Argumenten

dateparts

Voorbeeld. "second", "millisecond", "minute", enz.)

input_source1

De eerste invoerbron in de Join. Intern wordt de tijdstempel die is gekoppeld aan de gebeurtenissen van deze input_source doorgegeven aan de functie.

input_source2

De tweede invoerbron in de Join. Intern wordt de tijdstempel die is gekoppeld aan de gebeurtenissen van deze input_source doorgegeven aan de functie.

Retourtype

Retourneert het aantal eenheden in datumonderdelen dat is verstreken van de tijdstempel van input_source1 tot de tijdstempel van input_source2. De geretourneerde waarde kan negatief zijn als de tijdstempel van de tweede input_source groter is dan de eerste.