Combler les intervalles de temps et imputer les valeurs manquantes

  • Article

Important

Azure SQL Edge ne prend plus en charge la plateforme ARM64.

Lors du traitement de données de série chronologique, il arrive souvent de constater des valeurs d’attributs manquantes dans les données. Il est également possible d’observer des écarts temporels dans le jeu de données, qui sont dus à la nature même des données ou à des interruptions dans la collecte des données.

Par exemple, lors de la collecte des statistiques d’utilisation de l’énergie pour un appareil intelligent, chaque fois que l’appareil n’est pas opérationnel, il existe des lacunes dans les statistiques d’utilisation. De la même façon, dans un scénario de collecte de données de télémétrie sur une machine, il est possible que les différents capteurs soient configurés pour émettre des données à des fréquences différentes, ce qui entraîne des valeurs de capteurs manquantes. Par exemple, s’il existe deux capteurs, la tension et la pression, configurés à 100 Hz et 10-Hz respectivement, le capteur de tension émet des données toutes les centièmes de seconde, tandis que le capteur de pression émet uniquement les données toutes les dix-dix secondes.

Le tableau suivant montre un jeu de données de télémétrie d’une machine, qui ont été collectées à des intervalles d’une seconde.

timestamp               VoltageReading  PressureReading
----------------------- --------------- ----------------
2020-09-07 06:14:41.000 164.990400      97.223600
2020-09-07 06:14:42.000 162.241300      93.992800
2020-09-07 06:14:43.000 163.271200      NULL
2020-09-07 06:14:44.000 161.368100      93.403700
2020-09-07 06:14:45.000 NULL            NULL
2020-09-07 06:14:46.000 NULL            98.364800
2020-09-07 06:14:49.000 NULL            94.098300
2020-09-07 06:14:51.000 157.695700      103.359100
2020-09-07 06:14:52.000 157.019200      NULL
2020-09-07 06:14:54.000 NULL            95.352000
2020-09-07 06:14:56.000 159.183500      100.748200

Le jeu de données précédent présente deux caractéristiques importantes.

  • Le jeu de données ne contient pas de points de données pour plusieurs horodatages (2020-09-07 06:14:47.000, 2020-09-07 06:14:48.000, 2020-09-07 06:14:50.000, 2020-09-07 06:14:53.000 et 2020-09-07 06:14:55.000). Ces horodatages constituent des écarts dans le jeu de données.
  • Il y a des valeurs manquantes, représentées par la valeur null, pour les lectures de tension et de pression.

Comblement des écarts

Le comblement des écarts est une technique qui permet de créer un ensemble d’horodatages contigus et ordonnés pour faciliter l’analyse des données de série chronologique. Dans Azure SQL Edge, le moyen le plus simple de combler les écarts dans le jeu de données de série chronologique consiste à définir une table temporaire et la distribution de temps souhaitée, puis à effectuer une opération LEFT OUTER JOIN ou RIGHT OUTER JOIN sur la table du jeu de données.

En prenant les MachineTelemetry données représentées précédemment comme exemple, la requête suivante peut être utilisée pour générer un ensemble contigu et ordonné d’horodatages à des fins d’analyse.

Remarque

La requête suivante génère les lignes manquantes, avec les valeurs d’horodatage et null les valeurs des attributs.

CREATE TABLE #SeriesGenerate (dt DATETIME PRIMARY KEY CLUSTERED)
GO

DECLARE @startdate DATETIME = '2020-09-07 06:14:41.000',
    @endtime DATETIME = '2020-09-07 06:14:56.000'

WHILE (@startdate <= @endtime)
BEGIN
    INSERT INTO #SeriesGenerate
    VALUES (@startdate)

    SET @startdate = DATEADD(SECOND, 1, @startdate)
END

SELECT a.dt AS TIMESTAMP,
    b.VoltageReading,
    b.PressureReading
FROM #SeriesGenerate a
LEFT JOIN MachineTelemetry b
    ON a.dt = b.[timestamp];

La requête ci-dessus retourne la sortie suivante, qui contient tous les horodatages d’une seconde dans la plage spécifiée.

Voici le jeu de résultats :

timestamp               VoltageReading    PressureReading
----------------------- ----------------- ----------------
2020-09-07 06:14:41.000 164.990400        97.223600
2020-09-07 06:14:42.000 162.241300        93.992800
2020-09-07 06:14:43.000 163.271200        NULL
2020-09-07 06:14:44.000 161.368100        93.403700
2020-09-07 06:14:45.000 NULL              NULL
2020-09-07 06:14:46.000 NULL              98.364800
2020-09-07 06:14:47.000 NULL              NULL
2020-09-07 06:14:48.000 NULL              NULL
2020-09-07 06:14:49.000 NULL              94.098300
2020-09-07 06:14:50.000 NULL              NULL
2020-09-07 06:14:51.000 157.695700        103.359100
2020-09-07 06:14:52.000 157.019200        NULL
2020-09-07 06:14:53.000 NULL              NULL
2020-09-07 06:14:54.000 NULL              95.352000
2020-09-07 06:14:55.000 NULL              NULL
2020-09-07 06:14:56.000 159.183500        100.748200

Imputer des valeurs manquantes

La requête précédente a généré les horodatages manquants pour l’analyse des données, mais il n’a pas remplacé les valeurs manquantes (représentées comme null) pour voltage et pressure les lectures. Dans Azure SQL Edge, une nouvelle syntaxe a été ajoutée aux fonctions T-SQL LAST_VALUE() et FIRST_VALUE(), qui fournissent des mécanismes d’imputation des valeurs manquantes sur la base des valeurs précédentes ou suivantes dans le jeu de données.

La nouvelle syntaxe ajoute la clause IGNORE NULLS et RESPECT NULLS aux fonctions LAST_VALUE() et FIRST_VALUE(). Une requête suivante sur le MachineTelemetry jeu de données calcule les valeurs manquantes à l’aide de la fonction LAST_VALUE, où les valeurs manquantes sont remplacées par la dernière valeur observée dans le jeu de données.

SELECT timestamp,
    VoltageReading AS OriginalVoltageValues,
    LAST_VALUE(VoltageReading) IGNORE NULLS OVER (
        ORDER BY timestamp
        ) AS ImputedUsingLastValue,
    PressureReading AS OriginalPressureValues,
    LAST_VALUE(PressureReading) IGNORE NULLS OVER (
        ORDER BY timestamp
        ) AS ImputedUsingLastValue
FROM MachineTelemetry
ORDER BY timestamp;

Voici le jeu de résultats :

timestamp               OrigVoltageVals  ImputedVoltage OrigPressureVals  ImputedPressure
----------------------- ---------------- -------------- ----------------- ----------------
2020-09-07 06:14:41.000 164.990400       164.990400     97.223600         97.223600
2020-09-07 06:14:42.000 162.241300       162.241300     93.992800         93.992800
2020-09-07 06:14:43.000 163.271200       163.271200     NULL              93.992800
2020-09-07 06:14:44.000 161.368100       161.368100     93.403700         93.403700
2020-09-07 06:14:45.000 NULL             161.368100     NULL              93.403700
2020-09-07 06:14:46.000 NULL             161.368100     98.364800         98.364800
2020-09-07 06:14:49.000 NULL             161.368100     94.098300         94.098300
2020-09-07 06:14:51.000 157.695700       157.695700     103.359100        103.359100
2020-09-07 06:14:52.000 157.019200       157.019200     NULL              103.359100
2020-09-07 06:14:54.000 NULL             157.019200     95.352000         95.352000
2020-09-07 06:14:56.000 159.183500       159.183500     100.748200        100.748200

La requête suivante impute les valeurs manquantes à l’aide des deux fonctions LAST_VALUE() et FIRST_VALUE. Pour la colonne ImputedVoltagede sortie, la dernière valeur observée remplace les valeurs manquantes, tandis que pour la colonne ImputedPressure de sortie, les valeurs manquantes sont remplacées par la valeur observée suivante dans le jeu de données.

SELECT dt AS [timestamp],
    VoltageReading AS OrigVoltageVals,
    LAST_VALUE(VoltageReading) IGNORE NULLS OVER (
        ORDER BY dt
        ) AS ImputedVoltage,
    PressureReading AS OrigPressureVals,
    FIRST_VALUE(PressureReading) IGNORE NULLS OVER (
        ORDER BY dt ROWS BETWEEN CURRENT ROW
                AND UNBOUNDED FOLLOWING
        ) AS ImputedPressure
FROM (
    SELECT a.dt,
        b.VoltageReading,
        b.PressureReading
    FROM #SeriesGenerate a
    LEFT JOIN MachineTelemetry b
        ON a.dt = b.[timestamp]
    ) A
ORDER BY timestamp;

Voici le jeu de résultats :

timestamp               OrigVoltageVals  ImputedVoltage  OrigPressureVals  ImputedPressure
----------------------- ---------------- --------------- ----------------- ---------------
2020-09-07 06:14:41.000 164.990400       164.990400      97.223600         97.223600
2020-09-07 06:14:42.000 162.241300       162.241300      93.992800         93.992800
2020-09-07 06:14:43.000 163.271200       163.271200      NULL              93.403700
2020-09-07 06:14:44.000 161.368100       161.368100      93.403700         93.403700
2020-09-07 06:14:45.000 NULL             161.368100      NULL              98.364800
2020-09-07 06:14:46.000 NULL             161.368100      98.364800         98.364800
2020-09-07 06:14:47.000 NULL             161.368100      NULL              94.098300
2020-09-07 06:14:48.000 NULL             161.368100      NULL              94.098300
2020-09-07 06:14:49.000 NULL             161.368100      94.098300         94.098300
2020-09-07 06:14:50.000 NULL             161.368100      NULL              103.359100
2020-09-07 06:14:51.000 157.695700       157.695700      103.359100        103.359100
2020-09-07 06:14:52.000 157.019200       157.019200      NULL              95.352000
2020-09-07 06:14:53.000 NULL             157.019200      NULL              95.352000
2020-09-07 06:14:54.000 NULL             157.019200      95.352000         95.352000
2020-09-07 06:14:55.000 NULL             157.019200      NULL              100.748200
2020-09-07 06:14:56.000 159.183500       159.183500      100.748200        100.748200

Remarque

La requête ci-dessus utilise la fonction FIRST_VALUE() pour remplacer les valeurs manquantes par la valeur observée suivante. Le même résultat est possible en utilisant une fonction LAST_VALUE() avec une clause ORDER BY <ordering_column> DESC.

Étapes suivantes