Partager via

JOIN (Azure Stream Analytics)

  • Article

Comme T-SQL standard, JOIN dans le langage de requête Azure Stream Analytics est utilisé pour combiner des enregistrements de deux sources d’entrée ou plus. JOIN dans Azure Stream Analytics sont temporelles par nature, ce qui signifie que chaque JOIN doit fournir des limites sur la façon dont les lignes correspondantes peuvent être séparées dans le temps. Pour instance, Dire « joindre des événements TollBoothEntry à des événements TollBoothExit lorsqu’ils se produisent sur les mêmes LicensePlate et TollId et dans les 5 minutes les uns des autres » est légitime; mais « joindre des événements TollBoothEntry aux événements TollBoothExit lorsqu’ils se produisent sur licensePlate et TollId » n’est pas : cela correspond à chaque TollBoothEntry avec une collection illimitée et potentiellement infinie de tous les TollBoothExit à la même LicensePlate et TollId.

Les limites de temps de la relation sont spécifiées à l’intérieur de la clause ON de join, à l’aide de la fonction DATEDIFF. La taille MAXIMALE DE DATEDIFF est de sept jours. Pour plus d’informations sur son utilisation générale, consultez DATEDIFF (Azure Stream Analytics). Quand la fonction DATEDIFF est utilisée à l'intérieur de la condition JOIN, les deuxième et troisième paramètres font l'objet d'un traitement spécial.

En outre, SELECT * ne peut pas être utilisé dans les instructions JOIN.

Syntaxe

[ 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

Arguments

<input_source>

Spécifie la source de données d'entrée.

<reference_data>

Données de référence auxquelles vous voulez joindre votre input_source. Pour plus d'informations, consultez la section Jointure de données de référence.

<join_type>

Spécifie le type d'opération de jointure.

JOIN

Indique que l'opération de jointure spécifiée doit avoir lieu entre les sources d'entrée et/ou les données de référence spécifiées. Toutes les lignes, à partir de la gauche et de la droite, qui respectent la condition de jointure sont incluses dans le jeu de résultats.

Avertissement

Si les sources JOIN sont partitionnés, le prédicat JOIN doit inclure une condition correspondant aux clés de partition des deux sources.

[ LEFT OUTER JOIN ]

Spécifie que toutes les lignes de la table de gauche qui ne respectent pas la condition de jointure sont incluses dans le jeu de résultats et que les colonnes de sortie de l'autre table ont des valeurs NULL en plus de toutes les lignes retournées par la jointure interne.

ON <join_condition>

Spécifie la condition sur laquelle se base la jointure. La condition de jointure doit avoir une limite temporelle ou une salle de perruque temporelle définie pour la relation. Elle est spécifiée à l’intérieur de la clause ON de join, à l’aide de la syntaxe spéciale de la fonction SPECIAL DATEDIFF pour JOIN.

Exemples

Dans Azure Stream Analytics, tous les événements ont un horodatage bien défini. Par conséquent, l’utilisateur doit utiliser des alias de ligne directement dans la fonction DATEDIFF, comme suit :

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

La condition de jointure ci-dessus entraîne une correspondance uniquement si l'instant ExitTime est postérieur à l'instant EntryTime, mais pas de plus de 15 minutes.

Notes

DATEDIFF utilisé dans l’instruction SELECT utilise la syntaxe générale où une colonne ou une expression datetime est passée en tant que deuxième et troisième paramètre. Toutefois, lorsque la fonction DATEDIFF est utilisée dans la condition JOIN, le nom input_source ou son alias est utilisé. En interne, l'horodatage associé à chaque événement dans cette source est sélectionné.

Les conditions de limite de temps peuvent être combinées entre elles et avec d'autres conditions à l'intérieur de la clause ON, par exemple :

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

Lors de la jointure de trois tables ou plus, les mêmes règles s’appliquent --- les limites de temps doivent garantir que tous les événements correspondants se produisent dans un laps de temps fini entre eux. Pour instance, pour rechercher toutes les erreurs qui se sont produites entre le début de la transaction et l’événement de fin de transaction, on peut dire :

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

Lors de la jointure de sources partitionnée, le prédicat JOIN doit inclure une condition correspondant aux clés de partition des deux sources.

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

Enfin, Azure Stream Analytics prend en charge la jointure interne (par défaut) et la jointure externe LEFT. Pour une jointure interne, un résultat n’est renvoyé que si une correspondance est trouvée. Par contre, pour une jointure LEFT OUTER , si un événement du côté gauche de la jointure n’a pas de correspondance, une ligne contenant la valeur NULL pour toutes les colonnes de la ligne de droite est retournée. Pour instance, voici un exemple pour trouver l’absence d’événements. La requête suivante renvoie les lignes où un véhicule est entré dans une gare de péage mais n'en est pas ressorti après 15 minutes.

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

Fonction DATEDIFF spéciale pour JOIN

Syntaxe

DATEDIFF ( datepart , input_source1, input_source2 )

Arguments

dateparts

Exemple. 'second', 'millisecond', 'minute', etc.)

input_source1

Première source d'entrée dans la jointure. En interne, l'horodatage associé aux événements à partir de cette input_source est transmis à la fonction.

input_source2

Deuxième source d'entrée dans la jointure. En interne, l'horodatage associé aux événements à partir de cette input_source est transmis à la fonction.

Type de retour

Renvoie le nombre d'unités dans les dateparts qui se sont écoulées entre l'horodatage d'input_source1 et l'horodatage d'input_source2. La valeur retournée peut être négative si l’horodatage du deuxième input_source est supérieur au premier.