Rellenar espacios de tiempo e imputar valores que faltan

  • Artículo

Importante

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 uso de energía para un dispositivo inteligente, siempre que el dispositivo no esté operativo, haya lagunas en las estadísticas de uso. 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 a 100 Hz y frecuencia de 10 Hz respectivamente, el sensor de voltaje emite datos cada centésima de segundo, mientras que el sensor de presión solo emite 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.000 y 2020-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 MachineTelemetry datos representados anteriormente como ejemplo, la consulta siguiente se puede usar para generar un conjunto de marcas de tiempo contiguas y ordenadas para el análisis.

Nota:

La consulta siguiente genera las filas que faltan, con los valores de marca de tiempo y null los valores de 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

La consulta anterior generó las marcas de tiempo que faltan para el análisis de datos, pero no reemplazó ninguno de los valores que faltan (representados como NULL) para voltage y pressure lecturas. 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(). Una consulta siguiente en el MachineTelemetry conjunto de datos calcula 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 ImputedVoltagede salida , el último valor observado reemplaza los valores que faltan, mientras que para la columna ImputedPressure de salida los valores que faltan se reemplazan 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.

Pasos siguientes