Füllen von Zeitlücken und Berechnung fehlender Werte
Wichtig
Azure SQL Edge unterstützt die ARM64-Plattform nicht mehr.
Wenn Zeitreihendaten verarbeitet werden, ist es häufig möglich, dass in den Zeitreihendaten Werte für die Attribute fehlen. Es kann auch vorkommen, dass es wegen der Art der Daten oder aufgrund von Unterbrechungen bei der Datensammlung Zeitlücken im Dataset gibt.
Wenn sie z. B. Statistiken zur Energienutzung für ein intelligentes Gerät sammeln, gibt es immer dann Lücken in der Nutzungsstatistik, wenn das Gerät nicht betriebsbereit ist. In ähnlicher Weise ist es in einem Szenario zur Erfassung von Maschinentelemetriedaten möglich, dass die verschiedenen Sensoren so konfiguriert sind, dass sie Daten in unterschiedlichen Frequenzen senden, was zu fehlenden Werten für die Sensoren führt. Wenn z. B. zwei Sensoren, Spannung und Druck vorhanden sind, die jeweils mit einer Frequenz von 100 Hz und 10 Hz konfiguriert sind, sendet der Spannungssensor Daten alle einhundert Sekunden, während der Drucksensor nur daten alle zehntel Sekunden ausgibt.
In der folgenden Tabelle ist ein Maschinentelemetriedataset beschrieben, das in einem Eine-Sekunde-Intervall erfasst wurde.
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
Es gibt zwei wichtige Merkmale des voranstehenden Datasets.
- Das Dataset enthält keine Datenpunkte für die Zeitstempel
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
und2020-09-07 06:14:55.000
. Diese Zeitstempel sind Lücken im Dataset. - Für die Spannungs- und Druckmesswerte gibt es fehlende Werte, die als
null
dargestellt sind.
Auffüllen von Lücken
Das Auffüllen von Lücken ist eine Technik, mit der ein zusammenhängender, geordneter Satz von Zeitstempeln erstellt werden kann, um die Analyse von Zeitreihendaten zu vereinfachen. In Azure SQL Edge besteht die einfachste Möglichkeit, Lücken im Zeitreihendataset aufzufüllen, darin, eine temporäre Tabelle mit der gewünschten Zeitverteilung zu definieren und dann einen LEFT OUTER JOIN
- oder RIGHT OUTER JOIN
-Vorgang für die Datasettabelle auszuführen.
Mit den MachineTelemetry
zuvor als Beispiel dargestellten Daten kann die folgende Abfrage verwendet werden, um zusammenhängende, sortierte Zeitstempel für die Analyse zu generieren.
Hinweis
Die folgende Abfrage generiert die fehlenden Zeilen mit den Zeitstempelwerten und null
Werten für die Attribute.
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];
Die obige Abfrage erzeugt die folgende Ausgabe, die alle sekündlichen Zeitstempel im angegebenen Bereich enthält.
Das Ergebnis lautet wie folgt:
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
Imputieren von fehlenden Werten
Die vorangehende Abfrage generierte die fehlenden Zeitstempel für die Datenanalyse, aber sie ersetzte keine der fehlenden Werte (dargestellt als NULL) für voltage
und pressure
Dies. In Azure SQL Edge wurde den T-SQL-Funktionen LAST_VALUE()
und FIRST_VALUE()
eine neue Syntax hinzugefügt, die Mechanismen bereitstellt, um fehlende Werte anhand der vorangehenden oder folgenden Werte im Dataset zu ergänzen.
Mit der neuen Syntax werden den Funktionen LAST_VALUE()
und FIRST_VALUE()
die Klauseln IGNORE NULLS
und RESPECT NULLS
hinzugefügt. Eine folgende Abfrage des MachineTelemetry
Datasets berechnet die fehlenden Werte mithilfe der LAST_VALUE-Funktion, wobei fehlende Werte durch den letzten beobachteten Wert im Dataset ersetzt werden.
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;
Das Ergebnis lautet wie folgt:
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
In der folgenden Abfrage werden die fehlenden Werte über die Funktionen LAST_VALUE()
und FIRST_VALUE
ergänzt. Für die Ausgabespalte ImputedVoltage
ersetzt der letzte beobachtete Wert die fehlenden Werte, während für die Ausgabespalte ImputedPressure
die fehlenden Werte durch den nächsten beobachteten Wert im Dataset ersetzt werden.
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;
Das Ergebnis lautet wie folgt:
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
Hinweis
In der obigen Abfrage wird die FIRST_VALUE()
-Funktion verwendet, um fehlende Werte durch den nächsten beobachteten Wert zu ersetzen. Dasselbe Ergebnis kann erzielt werden, indem die LAST_VALUE()
-Funktion mit einer ORDER BY <ordering_column> DESC
-Klausel verwendet wird.