Preencher lacunas de tempo e imputar valores em falta

Ao lidar com dados de séries temporais, geralmente é possível que os dados de séries temporais tenham valores ausentes para os atributos. Também é possível que, devido à natureza dos dados, ou devido a interrupções na coleta de dados, haja lacunas de tempo no conjunto de dados.

Por exemplo, ao coletar estatísticas de uso de energia para um dispositivo inteligente, sempre que o dispositivo não estiver operacional, há lacunas nas estatísticas de uso. Da mesma forma, em um cenário de coleta de dados de telemetria de máquina, é possível que os diferentes sensores sejam configurados para emitir dados em frequências diferentes, resultando em valores ausentes para os sensores. Por exemplo, se houver dois sensores, tensão e pressão, configurados na frequência de 100 Hz e 10 Hz, respectivamente, o sensor de tensão emite dados a cada centésimo de segundo, enquanto o sensor de pressão emite dados apenas a cada um décimo de segundo.

A tabela a seguir descreve um conjunto de dados de telemetria de máquina, que foi coletado em um intervalo de um 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

Há duas características importantes do conjunto de dados anterior.

• O conjunto de dados não contém nenhum ponto de dados relacionado a vários carimbos de 2020-09-07 06:14:47.000data/hora , 2020-09-07 06:14:48.000, 2020-09-07 06:14:50.0002020-09-07 06:14:53.000, e 2020-09-07 06:14:55.000. Esses carimbos de data/hora são lacunas no conjunto de dados.
• Há valores ausentes, representados como null, para as leituras de tensão e pressão.

Colmatar lacunas

O preenchimento de lacunas é uma técnica que ajuda a criar um conjunto contíguo e ordenado de carimbos de data/hora para facilitar a análise de dados de séries temporais. No SQL Edge do Azure, a maneira mais fácil de preencher lacunas no conjunto de dados de séries temporais é definir uma tabela temporária com a distribuição de tempo desejada e, em seguida, executar uma LEFT OUTER JOIN ou uma RIGHT OUTER JOIN operação na tabela do conjunto de dados.

Tomando os MachineTelemetry dados representados anteriormente como exemplo, a consulta a seguir pode ser usada para gerar um conjunto contíguo e ordenado de carimbos de data/hora para análise.

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];

A consulta acima produz a seguinte saída contendo todos os carimbos de data/hora de um segundo no intervalo especificado.

Aqui está o 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

Imputar valores em falta

A consulta anterior gerou os carimbos de data/hora ausentes para análise de dados, no entanto, não substituiu nenhum dos valores ausentes (representados como nulos) para voltage e pressure leituras. No Azure SQL Edge, uma nova sintaxe foi adicionada ao T-SQL LAST_VALUE() e FIRST_VALUE() funções, que fornecem mecanismos para imputar valores ausentes, com base nos valores anteriores ou seguintes no conjunto de dados.

A nova sintaxe adiciona IGNORE NULLS e RESPECT NULLS cláusula para as LAST_VALUE() funções e FIRST_VALUE() . Uma consulta a MachineTelemetry seguir no conjunto de dados calcula os valores ausentes usando a função LAST_VALUE, onde os valores ausentes são substituídos pelo último valor observado no conjunto de dados.

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;

Aqui está o 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

A consulta a seguir imputa os valores ausentes usando o LAST_VALUE() e a FIRST_VALUE função. Para a coluna ImputedVoltagede saída, o último valor observado substitui os valores ausentes, enquanto para a coluna ImputedPressure de saída os valores ausentes são substituídos pelo próximo valor observado no conjunto de dados.

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;

Aqui está o 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

Conteúdos relacionados

• FIRST_VALUE (Transact-SQL)
• LAST_VALUE (Transact-SQL)
• DATE_BUCKET (Transact-SQL)
• Funções de agregação (Transact-SQL)