Preencher lacunas de tempo e imputar valores faltantes
Importante
O SQL do Azure no Edge encerrou o suporte à plataforma ARM64.
Ao lidar com os dados de série temporal, geralmente é possível que os dados da série temporal 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 computador, é possível que os diferentes sensores sejam configurados para emitir dados em diferentes frequências, resultando em valores ausentes para os sensores. Por exemplo, se houver dois sensores, tensão e pressão, configurados nas frequências 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 só emite dados a cada um décimo de segundo.
A tabela a seguir descreve um conjunto de dados telemétricos de computador, 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 no conjunto de dados anterior.
- O conjunto de dados não contém nenhum ponto de dados relacionado a vários carimbos de data/hora
2020-09-07 06:14:47.0002020-09-07 06:14:48.0002020-09-07 06:14:50.0002020-09-07 06:14:53.000e2020-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.
Preenchimento de lacuna
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érie temporal. No SQL do Azure no Edge, a maneira mais fácil de preencher as lacunas no conjunto de dados da série temporal é definir uma tabela temporária com a distribuição de tempo desejada e fazer uma operação LEFT OUTER JOIN ou RIGHT OUTER JOIN na tabela do conjunto de dados.
Tomando os dados representados anteriormente como exemplo, a consulta a seguir pode ser usada para gerar um conjunto contíguo MachineTelemetry e ordenado de carimbos de data/hora para análise.
Observação
A consulta a seguir gera as linhas ausentes, com os valores de carimbo de data/hora e null valores para os 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];
A consulta acima produz a saída a seguir contendo todos os carimbos de data/hora de um segundo no intervalo especificado.
Este é 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
Acrescentar valores ausentes
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 SQL do Azure no Edge, uma nova sintaxe foi adicionada às funções LAST_VALUE() e FIRST_VALUE() do T-SQL, que fornecem mecanismos para inserir valores ausentes, com base nos valores anteriores ou posteriores no conjunto de dados.
A nova sintaxe adiciona as cláusulas IGNORE NULLS eRESPECT NULLS às funções LAST_VALUE() e FIRST_VALUE(). Uma consulta a seguir no conjunto de dados calcula os valores ausentes usando a MachineTelemetry função LAST_VALUE, em que 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;
Este é 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 insere os valores ausentes usando as funções LAST_VALUE() e FIRST_VALUE. Para a coluna de saída, o último valor observado substitui os valores ausentes, enquanto para a coluna ImputedVoltageImputedPressure 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;
Este é 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
Observação
A consulta acima usa a função FIRST_VALUE() para substituir valores ausentes pelo próximo valor observado. O mesmo resultado pode ser obtido usando a função LAST_VALUE() com uma cláusula ORDER BY <ordering_column> DESC.