JOIN (Azure Stream Analytics)

  • Artikel

Wie standard T-SQL werden JOIN in der Azure Stream Analytics-Abfragesprache verwendet, um Datensätze aus zwei oder mehr Eingabequellen zu kombinieren. JOIN in Azure Stream Analytics ist zeitlicher Natur, was bedeutet, dass jeder JOIN einige Grenzwerte für die Zeitliche Trennung der übereinstimmenden Zeilen bereitstellen muss. Für instance "TollBoothEntry-Ereignisse mit TollBoothExit-Ereignissen beitreten, wenn sie auf demselben LicensePlate und TollId auftreten und innerhalb von 5 Minuten voneinander" ist legitim. "TollBoothEntry-Ereignisse mit TollBoothExit-Ereignissen verknüpfen, wenn sie auf der LicensePlate und TollId auftreten" ist nicht – es würde jedem TollBoothEntry eine ungebundene und potenziell unendliche Sammlung aller TollBoothExit-Ereignisse mit derselben LicensePlate und Derselben TollId entsprechen.

Die Zeitgrenzen für die Beziehung werden in der ON-Klausel der JOIN-Funktion mithilfe der FUNKTION DATEDIFF angegeben. Die maximale DATIFF-Größe beträgt sieben Tage. Weitere Informationen zur allgemeinen Verwendung finden Sie unter DATEDIFF (Azure Stream Analytics). Wenn DATEDIFF innerhalb der JOIN-Bedingung verwendet wird, erfordern der zweite und dritte Parameter eine besondere Behandlung.

Darüber hinaus kann SELECT * nicht in JOIN-Anweisungen verwendet werden.

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

Argumente

<input_source>

Gibt die Quelle der Eingabedaten an.

<reference_data>

Die Verweisdaten, mit denen Sie Ihre Eingabequelle (input_source) verknüpfen möchten. Weitere Informationen finden Sie im Abschnitt "Verweisdatenverknüpfung".

<join_type>

Gibt den Typ der Joinoperation an.

JOIN

Gibt an, dass die angegebene JOIN-Operation zwischen den angegebenen Eingabequellen und/oder Verweisdaten erfolgen soll. Alle Zeilen aus der linken und rechten Tabelle, die die JOIN-Bedingung erfüllen, werden im Resultset berücksichtigt.

Warnung

Wenn die JOIN-Quellen partitioniert sind, muss das JOIN-Prädikat eine Bedingung enthalten, die den Partitionsschlüsseln beider Quellen entspricht.

[ LINKER ÄUßERER JOIN ]

Gibt an, dass alle Zeilen der linken Tabelle, die die angegebene Joinbedingung nicht erfüllen, im Resultset enthalten sind. Die Ausgabespalten der anderen Tabelle werden auf NULL gesetzt. Dies erfolgt zusätzlich zu allen Zeilen, die von INNER JOIN zurückgegeben werden.

ON <join_condition>

Gibt die Bedingung an, auf der der Join basiert. Die Joinbedingung muss über einen zeitgebundenen oder einen temporalen Wackelraum verfügen, der für die Beziehung definiert ist und in der ON-Klausel der JOIN-Klausel angegeben wird, wobei die spezielle Syntax der Special DATEDIFF Function for JOIN-Funktionverwendet wird.

Beispiele

In Azure Stream Analytics sind alle Ereignisse mit einem klar definierten Zeitstempel gekennzeichnet. Daher muss der Benutzer Zeilenalias direkt in der FUNKTION DATEDIFF wie folgt verwenden:

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

Die obige JOIN-Bedingung führt nur dann zu einer Übereinstimmung, wenn die ExitTime nach der EntryTime auftritt, wobei der zeitliche Abstand nicht mehr als 15 Minuten betragen darf.

Hinweis

DATEDIFF, die in der SELECT-Anweisung verwendet wird, verwendet die allgemeine Syntax, bei der eine datetime-Spalte oder ein Ausdruck als zweiter und dritter Parameter übergeben wird. Wenn die DATIFF-Funktion jedoch innerhalb der JOIN-Bedingung verwendet wird, wird der input_source Name oder sein Alias verwendet. Intern wird der für jedes Ereignis in dieser Quelle zugeordnete Zeitstempel ausgewählt.

Zeitgebundene Bedingungen können miteinander und mit anderen Bedingungen in der ON-Klausel kombiniert werden. Beispiel:

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

Beim Verknüpfen von drei oder mehr Tabellen gelten dieselben Regeln, --- Zeitgrenzen sicherstellen müssen, dass alle übereinstimmend auftretenden Ereignisse innerhalb einer endlichen Zeitspanne voneinander auftreten. Für instance kann man sagen, um alle Fehler zu finden, die zwischen Transaktionsstart und Transaktionsendereignis aufgetreten sind:

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

Beim Verknüpfen von partitionierten Quellen muss das JOIN-Prädikat eine Bedingung enthalten, die den Partitionsschlüsseln beider Quellen entspricht.

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

Schließlich unterstützt Azure Stream Analytics sowohl inneren Join (Standard) als auch LEFT outer join. Für eine innere Verknüpfung wird nur dann ein Ergebnis zurückgegeben, wenn eine Übereinstimmung gefunden wird. Wenn jedoch ein Ereignis von der linken Seite der Verknüpfung keine Übereinstimmung besitzt, wird bei LEFT OUTER JOIN für alle Spalten der rechten Zeile eine Zeile mit NULL zurückgegeben. Für instance finden Sie hier ein Beispiel, um das Fehlen von Ereignissen zu ermitteln. In der folgenden Abfrage werden die Zeilen zurückgegeben, in denen ein Fahrzeug eine Mautstelle angefahren, diese jedoch nicht innerhalb von 15 Minuten verlassen hat.

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

Spezielle DATEDIFF-Funktion für JOIN

Syntax

DATEDIFF ( datepart , input_source1, input_source2 )

Argumente

dateparts

Beispiel: "second", "millisecond", "minute" usw.)

input_source1

Die erste Eingabequelle in der Verknüpfung. Der den Ereignissen dieser Eingabequelle (input_source) zugeordnete Zeitstempel wird intern an die Funktion übergeben.

input_source2

Die zweite Eingabequelle in der Verknüpfung. Der den Ereignissen dieser Eingabequelle (input_source) zugeordnete Zeitstempel wird intern an die Funktion übergeben.

Rückgabetyp

Gibt die Anzahl der Einheiten in DateParts zurück, die zwischen dem Zeitstempel der ersten Eingabequelle (input_source1) bis zum Zeitstempel der zweiten Eingabequelle (input_source2) verstrichen sind. Der zurückgegebene Wert kann negativ sein, wenn der Zeitstempel der zweiten input_source größer als der erste ist.