Relleno de intervalos de tiempo e imputación de los valores que faltan
Importante
Azure SQL Edge se retirará el 30 de septiembre de 2025. Para obtener más información y opciones de migración, consulte el aviso de retirada.
Nota:
Azure SQL Edge ya no admite la plataforma ARM64.
Cuando se trata con datos de series temporales, a menudo es posible que para los atributos de estos datos haya valores que faltan. También es posible que, debido a la naturaleza de los datos o a las interrupciones en la recopilación de datos, haya intervalos de tiempo en el conjunto de datos.
Por ejemplo, al recopilar estadísticas de utilización de energía para un Smart Device, siempre que el dispositivo no esté operativo, habrá intervalos en las estadísticas de utilización. De forma similar, en un escenario de recopilación de datos de telemetría de máquina, es posible que los diferentes sensores estén configurados para emitir datos con diferentes frecuencias, lo que da lugar a que en los sensores falten valores. Por ejemplo, si hay dos sensores, voltaje y presión, configurados con una frecuencia de 100 Hz y 10 Hz respectivamente, el sensor de voltaje emitirá los datos cada centésima de segundo, mientras que el sensor de presión solo emitirá datos cada décima de segundo.
En la tabla siguiente se describe un conjunto de elementos de telemetría de máquina, que se ha recopilado en un intervalo de un segundo.
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
Hay dos características importantes en el conjunto de datos anterior.
- El conjunto de datos no contiene ningún punto de datos relacionado con varias marcas de tiempo
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.000y2020-09-07 06:14:55.000. Estas marcas de tiempo son intervalos en el conjunto de datos. - Faltan valores, representados como
null, para las lecturas de voltaje y presión.
Relleno de intervalos
El relleno de intervalos es una técnica que permite crear un conjunto ordenado y contiguo de marcas de tiempo para facilitar el análisis de datos de series temporales. En Azure SQL Edge, la manera más fácil de rellenar los intervalos en el conjunto de datos de la serie temporal es definir una tabla temporal con la distribución temporal deseada y, después, realizar una operación LEFT OUTER JOIN o RIGHT OUTER JOIN en la tabla del conjunto de datos.
Tomando los datos MachineTelemetry representados anteriormente como ejemplo, se puede usar la consulta siguiente a fin de generar un conjunto ordenado y contiguo de marcas de tiempo para el análisis.
Nota:
En la consulta siguiente se generan las filas que faltan, con los valores de marca de tiempo y los valores de null para los atributos.
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];
En la consulta anterior se genera el resultado siguiente que contiene todas las marcas de tiempo de un segundo en el rango especificado.
Este es el conjunto de resultados:
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
Atribución de valores que faltan
En la consulta anterior se han generado las marcas de tiempo que faltan para el análisis de datos; sin embargo, no se ha sustituido ninguno de los valores que faltan (representados como NULL) para las lecturas de voltage y pressure. En Azure SQL Edge, se ha agregado una nueva sintaxis a las funciones de T-SQL LAST_VALUE() y FIRST_VALUE(), que proporcionan mecanismos para imputar valores que faltan, en función de los valores anteriores o posteriores del conjunto de datos.
La sintaxis nueva agrega las cláusulas IGNORE NULLS y RESPECT NULLS a las funciones LAST_VALUE() y FIRST_VALUE(). En la consulta siguiente, del conjunto de datos MachineTelemetry, se calculan los valores que faltan mediante la función LAST_VALUE, donde los valores que faltan se reemplazan por el último valor observado en el conjunto de datos.
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;
Este es el conjunto de resultados:
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
En la consulta siguiente se imputarán los valores que faltan mediante las funciones LAST_VALUE() y FIRST_VALUE. Para la columna de salida ImputedVoltage, el último valor observado sustituye a los valores que faltan, mientras que para la columna de salida ImputedPressure los valores que faltan se sustituyen por el siguiente valor observado en el conjunto de datos.
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;
Este es el conjunto de resultados:
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:
En la consulta anterior se utiliza la función FIRST_VALUE() para reemplazar los valores que faltan por el valor observado siguiente. Se puede lograr el mismo resultado mediante el uso de la función LAST_VALUE() con una cláusula ORDER BY <ordering_column> DESC.