Entrainement
Module
Écrire des requêtes qui utilisent des fonctions de fenêtre - Training
Ce contenu fait partie du module Écrire des requêtes qui utilisent des fonctions de fenêtre.
Ce navigateur n’est plus pris en charge.
Effectuez une mise à niveau vers Microsoft Edge pour tirer parti des dernières fonctionnalités, des mises à jour de sécurité et du support technique.
Les requêtes dans Azure Stream Analytics sont exprimées dans un langage de requête de type SQL. Les constructions de langage sont documentées dans le guide Stream Analytics query language reference (Informations de référence sur le langage de requête Stream Analytics).
La conception de requêtes peut exprimer une logique de transmission simple pour déplacer des données d’événement d’un flux d’entrée à un magasin de données de sortie. Elle peut également effectuer une analyse enrichie temporelle et de critères spéciaux, afin de calculer les agrégats dans différentes fenêtres de temps, comme dans le guide Créer une solution IoT à l’aide de Stream Analytics. Vous pouvez joindre des données provenant de plusieurs entrées pour combiner des événements de streaming, et effectuer également des recherches sur les données de référence statiques pour enrichir les valeurs des événements. Vous pouvez aussi écrire des données vers plusieurs sorties.
Cet article décrit des solutions pour plusieurs modèles de requête courants, inspirés de scénarios réels.
Azure Stream Analytics prend en charge le traitement d’événements dans les formats de données CSV, JSON et Avro. Les formats JSON et Avro peuvent contenir des types complexes, comme des objets imbriqués (enregistrements) ou des tableaux. Pour plus d’informations sur l’utilisation de ces types de données complexes, consultez Analyse des données JSON et AVRO.
Vous pouvez utiliser plusieurs instructions SELECT pour sortir des données vers différents récepteurs de sortie. Par exemple, une instruction SELECT peut produire une alerte basée sur un seuil, tandis qu’une autre peut produire des événements dans un stockage de blobs.
Examinons l’entrée suivante :
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make1 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:01.0000000Z |
| Make2 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:03.0000000Z |
Vous souhaitez également les deux sorties suivantes de la requête :
ArchiveOutput:
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make1 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:01.0000000Z |
| Make2 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:03.0000000Z |
AlertOutput:
| Make | Time | Count |
| --- | --- | --- |
| Make2 |2023-01-01T00:00:10.0000000Z |3 |
Requête avec deux instructions SELECT avec une sortie Archive et une sortie Alerte comme sorties :
SELECT
*
INTO
ArchiveOutput
FROM
Input TIMESTAMP BY Time
SELECT
Make,
System.TimeStamp() AS Time,
COUNT(*) AS [Count]
INTO
AlertOutput
FROM
Input TIMESTAMP BY Time
GROUP BY
Make,
TumblingWindow(second, 10)
HAVING
[Count] >= 3
La clause INTO indique au service Stream Analytics la sortie sur laquelle écrire les données. La première instruction SELECT définit une requête directe qui reçoit des données de l’entrée et les envoie à la sortie nommée ArchiveOutput. La deuxième requête agrège et filtre les données avant d’envoyer les résultats à une sortie de système d’alerte en aval appelée AlertOutput.
La clause WITH peut être utilisée pour définir plusieurs blocs de sous-requête. Cette option a l’avantage d’ouvrir moins de lecteurs sur la source d’entrée.
Requête :
WITH ReaderQuery AS (
SELECT
*
FROM
Input TIMESTAMP BY Time
)
SELECT * INTO ArchiveOutput FROM ReaderQuery
SELECT
Make,
System.TimeStamp() AS Time,
COUNT(*) AS [Count]
INTO AlertOutput
FROM ReaderQuery
GROUP BY
Make,
TumblingWindow(second, 10)
HAVING [Count] >= 3
Pour plus d’informations, consultez Clause WITH.
Une requête directe simple peut être utilisée pour copier les données d’un flux d’entrée dans la sortie. Par exemple, si un flux de données contenant des informations en temps réel sur un véhicule doit être enregistré dans une base de données SQL en vue d’être analysé ultérieurement, une requête directe simple suffit.
Examinons l’entrée suivante :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |"1000" |
| Make1 |2023-01-01T00:00:02.0000000Z |"2000" |
Vous souhaitez que la sortie soit identique à l’entrée :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |"1000" |
| Make1 |2023-01-01T00:00:02.0000000Z |"2000" |
Voici la requête :
SELECT
*
INTO Output
FROM Input
Cette requête SELECT * projette tous les champs d’un événement entrant et les envoie à la sortie. Au lieu de cela, vous pouvez projeter uniquement les champs obligatoires dans une instruction SELECT . Dans l’exemple suivant, l’instruction SELECT projette uniquement les champs Make (Marque) et Time (Heure) des données d’entrée.
Examinons l’entrée suivante :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |1000 |
| Make1 |2023-01-01T00:00:02.0000000Z |2000 |
| Make2 |2023-01-01T00:00:04.0000000Z |1500 |
Vous souhaitez que la sortie ne contienne que les champs Make et Time :
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make1 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:04.0000000Z |
Voici la requête qui projette uniquement les champs obligatoires :
SELECT
Make, Time
INTO Output
FROM Input
LIKE et NOT LIKE peuvent être utilisés pour vérifier si un champ correspond à un certain modèle. Par exemple, vous pouvez utiliser un filtre pour retourner seulement les plaques d’immatriculation qui commencent par la lettre A
et se terminent par le chiffre 9
.
Examinons l’entrée suivante :
| Make | License_plate | Time |
| --- | --- | --- |
| Make1 |ABC-123 |2023-01-01T00:00:01.0000000Z |
| Make2 |AAA-999 |2023-01-01T00:00:02.0000000Z |
| Make3 |ABC-369 |2023-01-01T00:00:03.0000000Z |
Vous souhaitez que la sortie contienne les plaques d’immatriculation qui commencent par la lettre A
et se terminent par le nombre 9
:
| Make | License_plate | Time |
| --- | --- | --- |
| Make2 |AAA-999 |2023-01-01T00:00:02.0000000Z |
| Make3 |ABC-369 |2023-01-01T00:00:03.0000000Z |
Voici la requête qui utilise l’opérateur LIKE :
SELECT
*
FROM
Input TIMESTAMP BY Time
WHERE
License_plate LIKE 'A%9'
Utilisez l’instruction LIKE pour vérifier la valeur du champ License_plate. Elle doit commencer par la lettre A
, puis avoir une chaîne de zéro, un ou plusieurs caractères, et se terminer par le chiffre 9.
La fonction LAG peut être utilisée pour examiner des événements antérieurs dans une fenêtre de temps et les comparer à l’événement actuel. Par exemple, la marque de voiture actuelle peut être placée dans la sortie si elle est différente de la marque de la dernière voiture qui a passé le péage.
Exemple d’entrée :
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make2 |2023-01-01T00:00:02.0000000Z |
Exemple de sortie :
| Make | Time |
| --- | --- |
| Make2 |2023-01-01T00:00:02.0000000Z |
Exemple de requête :
SELECT
Make,
Time
FROM
Input TIMESTAMP BY Time
WHERE
LAG(Make, 1) OVER (LIMIT DURATION(minute, 1)) <> Make
Utilisez LAG pour regarder un événement précédent dans le flux d’entrée, en récupérant la valeur de Make et en la comparant à la valeur de Make de l’événement actuel, et pour placer l’événement dans la sortie.
Pour plus d'informations, consultez LAG.
Les événements étant consommés par le système en temps réel, aucune fonction ne peut déterminer si un événement est le dernier à arriver dans cette fenêtre de temps. Pour faire cela, le flux d’entrée doit être joint à un autre quand l’heure d’un événement est l’heure maximale pour tous les événements de cette fenêtre.
Exemple d’entrée :
| License_plate | Make | Time |
| --- | --- | --- |
| DXE 5291 |Make1 |2023-07-27T00:00:05.0000000Z |
| YZK 5704 |Make3 |2023-07-27T00:02:17.0000000Z |
| RMV 8282 |Make1 |2023-07-27T00:05:01.0000000Z |
| YHN 6970 |Make2 |2023-07-27T00:06:00.0000000Z |
| VFE 1616 |Make2 |2023-07-27T00:09:31.0000000Z |
| QYF 9358 |Make1 |2023-07-27T00:12:02.0000000Z |
| MDR 6128 |Make4 |2023-07-27T00:13:45.0000000Z |
Exemple de sortie avec les informations sur les dernières voitures dans deux fenêtres de temps de dix minutes :
| License_plate | Make | Time |
| --- | --- | --- |
| VFE 1616 |Make2 |2023-07-27T00:09:31.0000000Z |
| MDR 6128 |Make4 |2023-07-27T00:13:45.0000000Z |
Exemple de requête :
WITH LastInWindow AS
(
SELECT
MAX(Time) AS LastEventTime
FROM
Input TIMESTAMP BY Time
GROUP BY
TumblingWindow(minute, 10)
)
SELECT
Input.License_plate,
Input.Make,
Input.Time
FROM
Input TIMESTAMP BY Time
INNER JOIN LastInWindow
ON DATEDIFF(minute, Input, LastInWindow) BETWEEN 0 AND 10
AND Input.Time = LastInWindow.LastEventTime
La première étape de la requête recherche l’horodatage maximal dans les fenêtres de 10 minutes, c’est-à-dire l’horodatage du dernier événement de cette fenêtre. La deuxième joint les résultats de la première requête avec le flux d’origine pour rechercher l’événement qui correspond aux derniers horodatages dans chaque fenêtre.
DATEDIFF est une fonction spécifique aux dates qui compare et retourne la différence de temps entre deux champs DateTime. Pour plus d’informations, consultez Fonctions de date.
Pour plus d’informations sur la jointure de flux, consultez JOIN.
Pour calculer les informations sur une fenêtre de temps, vous pouvez agréger les données. Dans cet exemple, l’instruction calcule un total sur les 10 dernières secondes pour chaque marque de voiture.
Exemple d’entrée :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |1000 |
| Make1 |2023-01-01T00:00:02.0000000Z |2000 |
| Make2 |2023-01-01T00:00:04.0000000Z |1500 |
Exemple de sortie :
| Make | Count |
| --- | --- |
| Make1 | 2 |
| Make2 | 1 |
Requête :
SELECT
Make,
COUNT(*) AS Count
FROM
Input TIMESTAMP BY Time
GROUP BY
Make,
TumblingWindow(second, 10)
Cette agrégation regroupe les voitures par Make (Marque) et les totalise toutes les 10 secondes. La sortie contient les champs Make (Marque) et Count (Total) des voitures qui sont passées au péage.
TumblingWindow est une fonction de fenêtrage utilisée pour regrouper des événements. Une agrégation peut être appliquée à tous les événements regroupés. Pour plus d’informations, consultez Fonctions de fenêtrage.
Pour plus d’informations sur l’agrégation, consultez Fonctions d’agrégation.
Quand des événements sont irréguliers ou manquants, une sortie à intervalle régulier peut être générée à partir d’une entrée de données plus éparses. Par exemple, générer toutes les cinq secondes un événement qui indique le point de données le plus récemment observé.
Exemple d’entrée :
| Time | Value |
| --- | --- |
| "2014-01-01T06:01:00" |1 |
| "2014-01-01T06:01:05" |2 |
| "2014-01-01T06:01:10" |3 |
| "2014-01-01T06:01:15" |4 |
| "2014-01-01T06:01:30" |5 |
| "2014-01-01T06:01:35" |6 |
Exemple de sortie (10 premières lignes) :
| Window_end | Last_event.Time | Last_event.Value |
| --- | --- | --- |
| 2014-01-01T14:01:00.000Z |2014-01-01T14:01:00.000Z |1 |
| 2014-01-01T14:01:05.000Z |2014-01-01T14:01:05.000Z |2 |
| 2014-01-01T14:01:10.000Z |2014-01-01T14:01:10.000Z |3 |
| 2014-01-01T14:01:15.000Z |2014-01-01T14:01:15.000Z |4 |
| 2014-01-01T14:01:20.000Z |2014-01-01T14:01:15.000Z |4 |
| 2014-01-01T14:01:25.000Z |2014-01-01T14:01:15.000Z |4 |
| 2014-01-01T14:01:30.000Z |2014-01-01T14:01:30.000Z |5 |
| 2014-01-01T14:01:35.000Z |2014-01-01T14:01:35.000Z |6 |
| 2014-01-01T14:01:40.000Z |2014-01-01T14:01:35.000Z |6 |
| 2014-01-01T14:01:45.000Z |2014-01-01T14:01:35.000Z |6 |
Exemple de requête :
SELECT
System.Timestamp() AS Window_end,
TopOne() OVER (ORDER BY Time DESC) AS Last_event
FROM
Input TIMESTAMP BY Time
GROUP BY
HOPPINGWINDOW(second, 300, 5)
Cette requête génère des événements toutes les 5 secondes et produit le dernier événement précédemment reçu. La durée HOPPINGWINDOW détermine la période d’antériorité où la requête recherche l’événement le plus récent.
Pour plus d’informations, consultez Fenêtre récurrente.
La corrélation d’événements dans le même flux peut être effectuée en examinant des événements antérieurs avec la fonction LAG. Par exemple, une sortie peut être générée chaque fois que deux voitures consécutives de la même marque (Make) passent le péage au cours des 90 dernières secondes.
Exemple d’entrée :
| Make | License_plate | Time |
| --- | --- | --- |
| Make1 |ABC-123 |2023-01-01T00:00:01.0000000Z |
| Make1 |AAA-999 |2023-01-01T00:00:02.0000000Z |
| Make2 |DEF-987 |2023-01-01T00:00:03.0000000Z |
| Make1 |GHI-345 |2023-01-01T00:00:04.0000000Z |
Exemple de sortie :
| Make | Time | Current_car_license_plate | First_car_license_plate | First_car_time |
| --- | --- | --- | --- | --- |
| Make1 |2023-01-01T00:00:02.0000000Z |AAA-999 |ABC-123 |2023-01-01T00:00:01.0000000Z |
Exemple de requête :
SELECT
Make,
Time,
License_plate AS Current_car_license_plate,
LAG(License_plate, 1) OVER (LIMIT DURATION(second, 90)) AS First_car_license_plate,
LAG(Time, 1) OVER (LIMIT DURATION(second, 90)) AS First_car_time
FROM
Input TIMESTAMP BY Time
WHERE
LAG(Make, 1) OVER (LIMIT DURATION(second, 90)) = Make
La fonction LAG peut rechercher dans le flux d’entrée un événement antérieur et récupérer la valeur de Make, en comparant celle-ci à la valeur de Make de l’événement actuel. Si la condition est remplie, les données de l’événement antérieur peuvent être projetées en utilisant LAG dans l’instruction SELECT.
Pour plus d'informations, consultez LAG.
La durée d’un événement peut être calculée en examinant le dernier événement Start (Début) après la réception d’un événement End (Fin). Cette requête peut être pratique pour déterminer le temps passé par un utilisateur sur une page ou une fonctionnalité.
Exemple d’entrée :
| User | Feature | Event | Time |
| --- | --- | --- | --- |
| user@location.com |RightMenu |Start |2023-01-01T00:00:01.0000000Z |
| user@location.com |RightMenu |End |2023-01-01T00:00:08.0000000Z |
Exemple de sortie :
| User | Feature | Duration |
| --- | --- | --- |
| user@location.com |RightMenu |7 |
Exemple de requête :
SELECT
[user],
feature,
DATEDIFF(
second,
LAST(Time) OVER (PARTITION BY [user], feature LIMIT DURATION(hour, 1) WHEN Event = 'start'),
Time) as duration
FROM input TIMESTAMP BY Time
WHERE
Event = 'end'
La fonction LAST peut être utilisée pour récupérer le dernier événement avec une condition spécifique. Dans cet exemple, la condition est un événement de type Start (Début), qui partitionne la recherche par utilisateur et par fonctionnalité avec PARTITION BY. De cette façon, chaque utilisateur et chaque fonctionnalité sont traités indépendamment lors de la recherche de l’événement Start (Début). LIMIT DURATION limite la recherche dans l’antériorité à 1 heure entre les événements Start (Début) et End (Fin).
COUNT et DISTINCT peuvent être utilisés pour compter le nombre de valeurs de champ uniques qui apparaissent dans le flux au cours d’une fenêtre de temps. Vous pouvez créer une requête pour calculer le nombre de voitures d’une même marque (Make) ont franchi le péage dans une fenêtre de temps de 2 secondes.
Exemple d’entrée :
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make1 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:01.0000000Z |
| Make2 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:03.0000000Z |
Exemple de sortie :
| Count_make | Time |
| --- | --- |
| 2 |2023-01-01T00:00:02.000Z |
| 1 |2023-01-01T00:00:04.000Z |
Exemple de requête :
SELECT
COUNT(DISTINCT Make) AS Count_make,
System.TIMESTAMP() AS Time
FROM Input TIMESTAMP BY TIME
GROUP BY
TumblingWindow(second, 2)
COUNT(DISTINCT Make) retourne le nombre de valeurs distinctes de la colonne Make dans une fenêtre de temps. Pour plus d’informations, consultez Fonction d’agrégation COUNT.
Vous pouvez utiliser IsFirst
pour récupérer le premier événement dans une fenêtre de temps. Par exemple, produire en sortie les informations de la première voiture à chaque intervalle de 10 minutes.
Exemple d’entrée :
| License_plate | Make | Time |
| --- | --- | --- |
| DXE 5291 |Make1 |2023-07-27T00:00:05.0000000Z |
| YZK 5704 |Make3 |2023-07-27T00:02:17.0000000Z |
| RMV 8282 |Make1 |2023-07-27T00:05:01.0000000Z |
| YHN 6970 |Make2 |2023-07-27T00:06:00.0000000Z |
| VFE 1616 |Make2 |2023-07-27T00:09:31.0000000Z |
| QYF 9358 |Make1 |2023-07-27T00:12:02.0000000Z |
| MDR 6128 |Make4 |2023-07-27T00:13:45.0000000Z |
Exemple de sortie :
| License_plate | Make | Time |
| --- | --- | --- |
| DXE 5291 |Make1 |2023-07-27T00:00:05.0000000Z |
| QYF 9358 |Make1 |2023-07-27T00:12:02.0000000Z |
Exemple de requête :
SELECT
License_plate,
Make,
Time
FROM
Input TIMESTAMP BY Time
WHERE
IsFirst(minute, 10) = 1
IsFirst peut également partitionner les données et calculer le premier événement sur chaque Make (Marque) de voiture spécifique trouvée à chaque intervalle de 10 minutes.
Exemple de sortie :
| License_plate | Make | Time |
| --- | --- | --- |
| DXE 5291 |Make1 |2023-07-27T00:00:05.0000000Z |
| YZK 5704 |Make3 |2023-07-27T00:02:17.0000000Z |
| YHN 6970 |Make2 |2023-07-27T00:06:00.0000000Z |
| QYF 9358 |Make1 |2023-07-27T00:12:02.0000000Z |
| MDR 6128 |Make4 |2023-07-27T00:13:45.0000000Z |
Exemple de requête :
SELECT
License_plate,
Make,
Time
FROM
Input TIMESTAMP BY Time
WHERE
IsFirst(minute, 10) OVER (PARTITION BY Make) = 1
Pour plus d’informations, consultez IsFirst.
Quand vous effectuez une opération comme calculer des moyennes de plusieurs événements dans une fenêtre de temps donnée, les événements en double doivent être filtrés. Dans l’exemple suivant, le deuxième événement est un doublon du premier.
Exemple d’entrée :
| DeviceId | Time | Attribute | Value |
| --- | --- | --- | --- |
| 1 |2018-07-27T00:00:01.0000000Z |Temperature |50 |
| 1 |2018-07-27T00:00:01.0000000Z |Temperature |50 |
| 2 |2018-07-27T00:00:01.0000000Z |Temperature |40 |
| 1 |2018-07-27T00:00:05.0000000Z |Temperature |60 |
| 2 |2018-07-27T00:00:05.0000000Z |Temperature |50 |
| 1 |2018-07-27T00:00:10.0000000Z |Temperature |100 |
Exemple de sortie :
| AverageValue | DeviceId |
| --- | --- |
| 70 | 1 |
|45 | 2 |
Exemple de requête :
WITH Temp AS (
SELECT Value, DeviceId
FROM Input TIMESTAMP BY Time
GROUP BY Value, DeviceId, System.Timestamp()
)
SELECT
AVG(Value) AS AverageValue, DeviceId
INTO Output
FROM Temp
GROUP BY DeviceId,TumblingWindow(minute, 5)
Lorsque la première instruction s’exécute, les enregistrements en double sont fusionnés en un seul, car les champs de la clause group by sont tous les mêmes. C’est pourquoi elle supprime les doublons.
Les instructions CASE peuvent fournir des calculs différents pour différents champs, en fonction d’un critère particulier. Par exemple, affectez la voie A
aux voitures Make1
et la voie B
aux autres marques.
Exemple d’entrée :
| Make | Time |
| --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |
| Make2 |2023-01-01T00:00:02.0000000Z |
| Make2 |2023-01-01T00:00:03.0000000Z |
Exemple de sortie :
| Make |Dispatch_to_lane | Time |
| --- | --- | --- |
| Make1 |"A" |2023-01-01T00:00:01.0000000Z |
| Make2 |"B" |2023-01-01T00:00:02.0000000Z |
Exemple de requête :
SELECT
Make
CASE
WHEN Make = "Make1" THEN "A"
ELSE "B"
END AS Dispatch_to_lane,
System.TimeStamp() AS Time
FROM
Input TIMESTAMP BY Time
L’expression CASE compare une expression à un ensemble d’expressions simples pour déterminer son résultat. Dans cet exemple, les véhicules Make1
sont affectés à la voie A
, tandis que les véhicules des autres marques sont affectés à la voie B
.
Pour plus d’informations, consultez Expression case.
Les données peuvent être converties en temps réel avec la méthode CAST. Par exemple, le poids d’une voiture peut être converti du type nvarchar(max) en type bigint et être utilisé dans un calcul numérique.
Exemple d’entrée :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |"1000" |
| Make1 |2023-01-01T00:00:02.0000000Z |"2000" |
Exemple de sortie :
| Make | Weight |
| --- | --- |
| Make1 |3000 |
Exemple de requête :
SELECT
Make,
SUM(CAST(Weight AS BIGINT)) AS Weight
FROM
Input TIMESTAMP BY Time
GROUP BY
Make,
TumblingWindow(second, 10)
Utilisez une instruction CAST pour spécifier son type de données. Consultez la liste des types de données pris en charge dans Types de données (Azure Stream Analytics).
Pour plus d’informations, consultez Fonctions de conversion des données.
Pour les conditions qui s’étendent sur plusieurs événements, la fonction LAG peut être utilisée pour identifier la durée de cette condition. Par exemple, supposons qu’à la suite d’un bogue, le poids de toutes les voitures soit incorrect (supérieur 20 000 livres) et que la durée de ce bogue doive être calculée.
Exemple d’entrée :
| Make | Time | Weight |
| --- | --- | --- |
| Make1 |2023-01-01T00:00:01.0000000Z |2000 |
| Make2 |2023-01-01T00:00:02.0000000Z |25000 |
| Make1 |2023-01-01T00:00:03.0000000Z |26000 |
| Make2 |2023-01-01T00:00:04.0000000Z |25000 |
| Make1 |2023-01-01T00:00:05.0000000Z |26000 |
| Make2 |2023-01-01T00:00:06.0000000Z |25000 |
| Make1 |2023-01-01T00:00:07.0000000Z |26000 |
| Make2 |2023-01-01T00:00:08.0000000Z |2000 |
Exemple de sortie :
| Start_fault | End_fault |
| --- | --- |
| 2023-01-01T00:00:02.000Z |2023-01-01T00:00:07.000Z |
Exemple de requête :
WITH SelectPreviousEvent AS
(
SELECT
*,
LAG([time]) OVER (LIMIT DURATION(hour, 24)) as previous_time,
LAG([weight]) OVER (LIMIT DURATION(hour, 24)) as previous_weight
FROM input TIMESTAMP BY [time]
)
SELECT
LAG(time) OVER (LIMIT DURATION(hour, 24) WHEN previous_weight < 20000 ) [Start_fault],
previous_time [End_fault]
FROM SelectPreviousEvent
WHERE
[weight] < 20000
AND previous_weight > 20000
La première instruction SELECT met en corrélation la mesure de poids actuelle avec la mesure précédente, en la projetant avec la mesure actuelle. Le deuxième SELECT regarde le dernier événement où le previous_weight est inférieur à 20 000, où le poids actuel est inférieur à 20 000 et où le previous_weight de l’événement actuel était supérieur à 20 000.
End_fault est l’événement de non-défaillance actuel où l’événement précédent était une défaillance, et Start_fault est le dernier événement de non-défaillance avant cela.
Les événements peuvent arriver en retard ou dans le désordre en raison des décalages des horloges entre les producteurs d’événements, des décalages des horloges entre les partitions ou de la latence du réseau. Dans l’exemple suivant, l’horloge de l’appareil pour TollID 2 a cinq secondes de retard par rapport à TollID 1, et l’horloge de l’appareil pour TollID 3 a 10 secondes de retard par rapport à TollID 1. Un calcul peut être effectué de façon indépendante pour chaque péage, en prenant seulement en compte les données de sa propre horloge comme horodatage.
Exemple d’entrée :
| LicensePlate | Make | Time | TollID |
| --- | --- | --- | --- |
| DXE 5291 |Make1 |2023-07-27T00:00:01.0000000Z | 1 |
| YHN 6970 |Make2 |2023-07-27T00:00:05.0000000Z | 1 |
| QYF 9358 |Make1 |2023-07-27T00:00:01.0000000Z | 2 |
| GXF 9462 |Make3 |2023-07-27T00:00:04.0000000Z | 2 |
| VFE 1616 |Make2 |2023-07-27T00:00:10.0000000Z | 1 |
| RMV 8282 |Make1 |2023-07-27T00:00:03.0000000Z | 3 |
| MDR 6128 |Make3 |2023-07-27T00:00:11.0000000Z | 2 |
| YZK 5704 |Make4 |2023-07-27T00:00:07.0000000Z | 3 |
Exemple de sortie :
| TollID | Count |
| --- | --- |
| 1 | 2 |
| 2 | 2 |
| 1 | 1 |
| 3 | 1 |
| 2 | 1 |
| 3 | 1 |
Exemple de requête :
SELECT
TollId,
COUNT(*) AS Count
FROM input
TIMESTAMP BY Time OVER TollId
GROUP BY TUMBLINGWINDOW(second, 5), TollId
La clause TIMESTAMP OVER BY examine séparément la chronologie de chaque appareil en utilisant des sous-flux. L’événement de sortie pour chacun des TollID est généré à mesure qu’ils sont calculés : les événements sont donc dans l’ordre relativement à chaque TollID, au lieu d’être réorganisés comme si tous les appareils avaient la même horloge.
Pour plus d’informations, consultez TIMESTAMP BY OVER.
Une fenêtre de session est une fenêtre qui s’étend au fur et à mesure que des événements se produisent et qui se ferme pour le calcul si aucun événement n’est reçu après un laps de temps spécifique ou si la fenêtre atteint sa durée maximale. Cette fenêtre est particulièrement pratique lors du calcul de données d’interaction de l’utilisateur. Une fenêtre commence quand un utilisateur commence à interagir avec le système et se ferme quand aucun autre événement n’est observé, ce qui signifie que l’utilisateur a cessé d’interagir. Par exemple, un utilisateur interagit avec une page web dans laquelle le nombre de clics est journalisé : une fenêtre de session peut être utilisée pour déterminer la durée de l’interaction de l’utilisateur avec le site.
Exemple d’entrée :
| User_id | Time | URL |
| --- | --- | --- |
| 0 | 2017-01-26T00:00:00.0000000Z | "www.example.com/a.html" |
| 0 | 2017-01-26T00:00:20.0000000Z | "www.example.com/b.html" |
| 1 | 2017-01-26T00:00:55.0000000Z | "www.example.com/c.html" |
| 0 | 2017-01-26T00:01:10.0000000Z | "www.example.com/d.html" |
| 1 | 2017-01-26T00:01:15.0000000Z | "www.example.com/e.html" |
Exemple de sortie :
| User_id | StartTime | EndTime | Duration_in_seconds |
| --- | --- | --- | --- |
| 0 | 2017-01-26T00:00:00.0000000Z | 2017-01-26T00:01:10.0000000Z | 70 |
| 1 | 2017-01-26T00:00:55.0000000Z | 2017-01-26T00:01:15.0000000Z | 20 |
Exemple de requête :
SELECT
user_id,
MIN(time) as StartTime,
MAX(time) as EndTime,
DATEDIFF(second, MIN(time), MAX(time)) AS duration_in_seconds
FROM input TIMESTAMP BY time
GROUP BY
user_id,
SessionWindow(minute, 1, 60) OVER (PARTITION BY user_id)
Le SELECT projette les données relatives à l’interaction de l’utilisateur, ainsi que la durée de l’interaction. Regroupement des données par utilisateur et une SessionWindow qui se ferme si aucune interaction ne se produit pendant 1 minute, avec une taille de fenêtre maximale de 60 minutes.
Pour plus d’informations sur SessionWindow, consultez Fenêtre de session.
Le langage de requête Azure Stream Analytics peut être étendu avec des fonctions personnalisées écrites en langages JavaScript ou C#. Les fonctions définies par l’utilisateur (UDF) sont des calculs personnalisés/complexes qui ne peuvent pas être exprimés facilement avec le langage SQL. Ces fonctions définies par l’utilisateur peuvent être définies une seule fois et utilisées plusieurs fois dans une requête. Par exemple, une fonction définie par l’utilisateur peut être utilisée pour convertir une valeur hexadécimale nvarchar(max) en une valeur bigint.
Exemple d’entrée :
| Device_id | HexValue |
| --- | --- |
| 1 | "B4" |
| 2 | "11B" |
| 3 | "121" |
Exemple de sortie :
| Device_id | Decimal |
| --- | --- |
| 1 | 180 |
| 2 | 283 |
| 3 | 289 |
function hex2Int(hexValue){
return parseInt(hexValue, 16);
}
public static class MyUdfClass {
public static long Hex2Int(string hexValue){
return int.Parse(hexValue, System.Globalization.NumberStyles.HexNumber);
}
}
SELECT
Device_id,
udf.Hex2Int(HexValue) AS Decimal
From
Input
La fonction définie par l’utilisateur calcule la valeur bigint à partir de la valeur hexadécimale (HexValue) sur chaque événement consommé.
Pour plus d’informations, consultez JavaScript et C#.
MATCH_RECOGNIZE est un mécanisme de correspondance de modèle avancé qui peut être utilisé pour faire correspondre une séquence d’événements à un modèle d’expression régulière bien défini. Par exemple, la défaillance éventuelle d’un ATM est surveillée en temps réel : pendant le fonctionnement de l’ATM, s’il y a deux messages d’avertissement consécutifs, l’administrateur doit être averti.
Entrée :
| ATM_id | Operation_id | Return_Code | Time |
| --- | --- | --- | --- |
| 1 | "Entering Pin" | "Success" | 2017-01-26T00:10:00.0000000Z |
| 2 | "Opening Money Slot" | "Success" | 2017-01-26T00:10:07.0000000Z |
| 2 | "Closing Money Slot" | "Success" | 2017-01-26T00:10:11.0000000Z |
| 1 | "Entering Withdraw Quantity" | "Success" | 2017-01-26T00:10:08.0000000Z |
| 1 | "Opening Money Slot" | "Warning" | 2017-01-26T00:10:14.0000000Z |
| 1 | "Printing Bank Balance" | "Warning" | 2017-01-26T00:10:19.0000000Z |
Sortie :
| ATM_id | First_Warning_Operation_id | Warning_Time |
| --- | --- | --- |
| 1 | "Opening Money Slot" | 2017-01-26T00:10:14.0000000Z |
SELECT *
FROM input TIMESTAMP BY time OVER ATM_id
MATCH_RECOGNIZE (
LIMIT DURATION(minute, 1)
PARTITION BY ATM_id
MEASURES
First(Warning.ATM_id) AS ATM_id,
First(Warning.Operation_Id) AS First_Warning_Operation_id,
First(Warning.Time) AS Warning_Time
AFTER MATCH SKIP TO NEXT ROW
PATTERN (Success+ Warning{2,})
DEFINE
Success AS Success.Return_Code = 'Success',
Warning AS Warning.Return_Code <> 'Success'
) AS patternMatch
Cette requête correspond à au moins deux événements d’échec consécutifs et génère une alarme quand les conditions sont remplies. PATTERN définit l’expression régulière à utiliser sur la correspondance. Dans le cas présent, au moins deux avertissements consécutifs après au moins une opération réussie. La réussite et l’avertissement sont définis avec la valeur Return_Code et, une fois la condition remplie, les mesures (MEASURES) sont projetées avec ATM_id, l’opération de premier avertissement et l’heure du premier avertissement.
Pour plus d’informations, consultez MATCH_RECOGNIZE.
Azure Stream Analytics fournit des fonctions géospatiales intégrées qui peuvent être utilisées pour implémenter des scénarios, tels que la gestion de flottes, le covoiturage, les voitures connectées et le suivi de ressources. Les données géospatiales peuvent être ingérées au format GeoJSON ou WKT en tant que données de référence ou de flux d’événements. Par exemple, une entreprise spécialisée dans la fabrication de machines pour l’impression de passeports loue ses machines aux gouvernements et aux consulats. L’emplacement de ces machines est étroitement contrôlé de façon à en éviter un placement erroné et une possible utilisation pour la contrefaçon de passeports. Chaque machine est équipée d’un dispositif de suivi GPS. Ces informations sont relayées vers un travail Azure Stream Analytics. La manufacture souhaite effectuer le suivi de l’emplacement de ces machines et être alertée si une d’entre elles quitte une zone autorisée : de cette façon, elle peut la désactiver à distance, alerter les autorités et récupérer l’équipement.
Entrée :
| Equipment_id | Equipment_current_location | Time |
| --- | --- | --- |
| 1 | "POINT(-122.13288797982818 47.64082002051315)" | 2017-01-26T00:10:00.0000000Z |
| 1 | "POINT(-122.13307252987875 47.64081350934929)" | 2017-01-26T00:11:00.0000000Z |
| 1 | "POINT(-122.13308862313283 47.6406508603241)" | 2017-01-26T00:12:00.0000000Z |
| 1 | "POINT(-122.13341048821462 47.64043760861279)" | 2017-01-26T00:13:00.0000000Z |
Entrée de données de référence :
| Equipment_id | Equipment_lease_location |
| --- | --- |
| 1 | "POLYGON((-122.13326028450979 47.6409833866794,-122.13261655434621 47.6409833866794,-122.13261655434621 47.64061471602751,-122.13326028450979 47.64061471602751,-122.13326028450979 47.6409833866794))" |
Sortie :
| Equipment_id | Equipment_alert_location | Time |
| --- | --- | --- |
| 1 | "POINT(-122.13341048821462 47.64043760861279)" | 2017-01-26T00:13:00.0000000Z |
SELECT
input.Equipment_id AS Equipment_id,
input.Equipment_current_location AS Equipment_current_location,
input.Time AS Time
FROM input TIMESTAMP BY time
JOIN
referenceInput
ON input.Equipment_id = referenceInput.Equipment_id
WHERE
ST_WITHIN(input.Equipment_current_location, referenceInput.Equipment_lease_location) = 1
La requête permet au fabricant de surveiller automatiquement l’emplacement des machines, en recevant des alertes quand une machine quitte la limite géographique autorisée. La fonction géospatiale intégrée permet aux utilisateurs d’utiliser des données GPS dans la requête sans recourir à des bibliothèques tierces.
Pour plus d’informations, consultez l’article Scénarios de geofencing et d’agrégation géospatiale avec Azure Stream Analytics.
Pour obtenir de l’aide supplémentaire, essayez notre page de questions Microsoft Q&A pour Azure Stream Analytics.
Entrainement
Module
Écrire des requêtes qui utilisent des fonctions de fenêtre - Training
Ce contenu fait partie du module Écrire des requêtes qui utilisent des fonctions de fenêtre.