Riempimento di intervalli di tempo e imputazione di valori mancanti
Importante
SQL Edge di Azure verrà ritirato il 30 settembre 2025. Per altre informazioni e per le opzioni di migrazione, vedere l'annuncio di ritiro.
Nota
SQL Edge di Azure non supporta più la piattaforma ARM64.
Quando si gestiscono i dati della serie temporale, è spesso possibile che tali dati contengano valori mancanti per gli attributi. È anche possibile che, a causa della natura dei dati o 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, si presentano 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 dati solo ogni decimo di secondo.
La tabella seguente descrive un set di dati di telemetria del computer, che è stato raccolto a un intervallo di un secondo.
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.000,2020-09-07 06:14:50.000,2020-09-07 06:14:53.000e2020-09-07 06:14:55.000. Questi timestamp sono intervalli nel set di dati. - Sono presenti valori mancanti, rappresentati come
null, per le letture di tensione e pressione.
Riempimento intervalli
Il riempimento di intervalli è 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 riempire gli intervalli 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 JOIN oRIGHT OUTER JOIN nella tabella del set di dati.
Prendendo come esempio i dati MachineTelemetry rappresentati in precedenza, è possibile usare la query seguente per generare set di timestamp contigui e ordinati per l'analisi.
Nota
La seguente query genera le righe mancanti, con i valori di timestamp e i valori null 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 suddetta query 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 le letture voltage e pressure. In SQL Edge di Azure è stata aggiunta una nuova sintassi alle funzioni T-SQL LAST_VALUE() e FIRST_VALUE(), che forniscono meccanismi per imputare i valori mancanti, in base ai valori precedenti o seguenti nel set di dati.
La nuova sintassi aggiunge la clausola IGNORE NULLS e RESPECT NULLS alle funzioni LAST_VALUE() e FIRST_VALUE(). Una query seguente sul set di dati MachineTelemetry 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 funzione LAST_VALUE() che la FIRST_VALUE. Per la colonna di output ImputedVoltage, l'ultimo valore osservato sostituisce i valori mancanti, mentre per la colonna di output ImputedPressure 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 funzione FIRST_VALUE() per sostituire i valori mancanti con il valore osservato successivo. Lo stesso risultato può essere ottenuto usando la funzione LAST_VALUE() con una clausola ORDER BY <ordering_column> DESC.