Riempimento di spazi temporali e imputazione di valori mancanti
Importante
SQL Edge di Azure non supporta più la piattaforma ARM64.
Quando si gestiscono i dati delle serie temporali, è spesso possibile che i dati delle serie temporali contengano valori mancanti per gli attributi. È anche possibile che, a causa della natura dei dati o a causa di interruzioni nella raccolta dati, si verifichino lacune temporali nel set di dati.
Ad esempio, quando si raccolgono statistiche sull'utilizzo dell'energia per uno smart device, ogni volta che il dispositivo non è operativo, esistono lacune nelle statistiche di utilizzo. Analogamente, in uno scenario di raccolta dei dati di telemetria del computer, è possibile che i diversi sensori siano configurati per generare dati a frequenze diverse, causando valori mancanti per i sensori. Ad esempio, se sono presenti due sensori, tensione e pressione, configurati rispettivamente a 100 Hz e 10-Hz, il sensore di tensione emette dati ogni centesimo di secondo, mentre il sensore di pressione emette solo dati ogni decimo di secondo.
La tabella seguente descrive un set di dati di telemetria del computer, che è stato raccolto a un secondo intervallo.
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
Esistono due caratteristiche importanti del set di dati precedente.
- Il set di dati non contiene punti dati correlati a diversi timestamp
2020-09-07 06:14:47.000, ,2020-09-07 06:14:48.0002020-09-07 06:14:50.000,2020-09-07 06:14:53.000e2020-09-07 06:14:55.000. Questi timestamp sono gap nel set di dati. - Sono presenti valori mancanti, rappresentati come
null, per le letture di tensione e pressione.
Riempimento vuoto
Il riempimento di gap è una tecnica che consente di creare set di timestamp contigui e ordinati per semplificare l'analisi dei dati delle serie temporali. In SQL Edge di Azure il modo più semplice per colmare le lacune nel set di dati delle serie temporali consiste nel definire una tabella temporanea con la distribuzione temporale desiderata e quindi eseguire un'operazione LEFT OUTER JOINRIGHT OUTER JOIN o nella tabella del set di dati.
Prendendo i MachineTelemetry dati rappresentati in precedenza come esempio, è possibile usare la query seguente per generare set di timestamp contigui e ordinati per l'analisi.
Nota
La query seguente genera le righe mancanti, con i valori di timestamp e null i valori per gli attributi.
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 query precedente genera l'output seguente contenente tutti i timestamp di un secondo nell'intervallo specificato.
Il set di risultati è il seguente:
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
Attribuire i valori mancanti
La query precedente ha generato i timestamp mancanti per l'analisi dei dati, ma non ha sostituito nessuno dei valori mancanti (rappresentati come null) per voltage e pressure letture. In SQL Edge di Azure è stata aggiunta una nuova sintassi alle funzioni e FIRST_VALUE() T-SQLLAST_VALUE(), che forniscono meccanismi per imputare i valori mancanti, in base ai valori precedenti o seguenti nel set di dati.
La nuova sintassi aggiunge IGNORE NULLS la clausola e RESPECT NULLS alle LAST_VALUE() funzioni e FIRST_VALUE() . Una query seguente sul MachineTelemetry set di dati calcola i valori mancanti usando la funzione LAST_VALUE, in cui i valori mancanti vengono sostituiti con l'ultimo valore osservato nel set di dati.
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;
Il set di risultati è il seguente:
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
Nella query seguente vengono imputati i valori mancanti usando sia la LAST_VALUE() funzione che la FIRST_VALUE funzione . Per la colonna ImputedVoltagedi output , l'ultimo valore osservato sostituisce i valori mancanti, mentre per la colonna ImputedPressure di output i valori mancanti vengono sostituiti dal valore osservato successivo nel set di dati.
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;
Il set di risultati è il seguente:
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
Nota
La query precedente usa la FIRST_VALUE() funzione per sostituire i valori mancanti con il valore osservato successivo. Lo stesso risultato può essere ottenuto usando la LAST_VALUE() funzione con una ORDER BY <ordering_column> DESC clausola .