JOIN (Azure Stream Analytics)
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.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour