Vyplňování časových mezer a imputing chybějících hodnot
Při práci s daty časových řad je často možné, že v datech časové řady chybí hodnoty atributů. Je také možné, že kvůli povaze dat nebo přerušení shromažďování dat jsou v datové sadě časové mezery.
Když například shromažďujete statistiky o využití energie pro inteligentní zařízení, pokaždé, když zařízení není funkční, budou ve statistikách využití mezery. Podobně ve scénáři shromažďování telemetrických dat počítače je možné, že různé senzory jsou nakonfigurované tak, aby vysílaly data s různou frekvencí, což vede k chybějícím hodnotám senzorů. Pokud jsou například k dispozici dva snímače napětí a tlaku nakonfigurované na frekvenci 100 Hz a 10 Hz, bude snímač napětí vysílat data každou setvenou sekundu, zatímco snímač tlaku bude vysílat data pouze každých desetiny sekundy.
Následující tabulka popisuje datovou sadu telemetrie počítače, která se shromáždila v intervalu jedné sekundy.
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
Předchozí datová sada má dvě důležité charakteristiky.
- Datová sada neobsahuje žádné datové body související s několika časovými razítky
2020-09-07 06:14:47.000, , , a2020-09-07 06:14:48.0002020-09-07 06:14:50.0002020-09-07 06:14:53.0002020-09-07 06:14:55.000. Tato časová razítka jsou mezery v datové sadě. - Pro odečty napětí a tlaku chybí hodnoty
nullreprezentované jako .
Vyplňování mezer
Vyplňování mezer je technika, která pomáhá vytvořit souvislá seřazená sada časových razítek pro usnadnění analýzy dat časových řad. V Azure SQL Edge je nejjednodušším způsobem, jak vyplnit mezery v datové sadě časové řady, definovat dočasnou tabulku s požadovaným časovým rozdělením a pak provést operaci nebo s tabulkou LEFT OUTER JOIN RIGHT OUTER JOIN datové sady.
Když jako příklad použijeme výše uvedená data, můžete pomocí následujícího dotazu vygenerovat souvislé seřazené sady časových razítek MachineTelemetry pro analýzu.
Poznámka
Následující dotaz vygeneruje chybějící řádky s hodnotami časového razítka null a hodnotami pro atributy.
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 OUTER JOIN MachineTelemetry b
on a.dt = b.[timestamp]
Výše uvedený dotaz vytvoří následující výstup obsahující všechna jednosekunová časová razítka v zadaném rozsahu.
Tady je sada výsledků dotazu
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
Zadávání chybějících hodnot
Předchozí dotaz vygeneroval chybějící časová razítka pro analýzu dat, ale nenahradil žádnou z chybějících hodnot (reprezentovaných jako null) pro hodnoty a voltage pressure . Ve službě Azure SQL Edge byla do T-SQL a funkcí přidána nová syntaxe, která poskytuje mechanismy pro nahánění chybějících hodnot na základě předchozích nebo následujících hodnot v datové LAST_VALUE() FIRST_VALUE() sadě.
Nová syntaxe přidá IGNORE NULLS do funkcí RESPECT NULLS a LAST_VALUE() FIRST_VALUE() klauzuli and. Následující dotaz na datovou sadu vypočítá chybějící hodnoty pomocí funkce last_value, kde se chybějící hodnoty nahradí poslední pozorovanou MachineTelemetry hodnotou v datové sadě.
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
Tady je sada výsledků dotazu
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
Následující dotaz imputuje chybějící hodnoty pomocí funkce LAST_VALUE() i FIRST_VALUE . Ve výstupním sloupci se chybějící hodnoty nahradí poslední pozorovanou hodnotou, zatímco ve výstupním sloupci se chybějící hodnoty nahradí další pozorovanou hodnotou v datové ImputedVoltage ImputedPressure sadě.
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 OUTER JOIN
MachineTelemetry b
on a.dt = b.[timestamp]) A
order by timestamp
Tady je sada výsledků dotazu
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
Poznámka
Výše uvedený dotaz používá funkci FIRST_VALUE() k nahrazení chybějících hodnot další pozorovanou hodnotou. Stejného výsledku lze dosáhnout pomocí LAST_VALUE() funkce s ORDER BY <ordering_column> DESC klauzulí .