Vyplňování časových mezer a impuování chybějících hodnot
Důležité
Azure SQL Edge bude vyřazeno 30. září 2025. Další informace a možnosti migrace najdete v oznámení o vyřazení.
Poznámka:
Azure SQL Edge už nepodporuje platformu ARM64.
Při práci s daty časových řad je často možné, že data časových řad pro atributy chybí hodnoty. Je také možné, že kvůli povaze dat nebo přerušení shromažďování dat v datové sadě dochází k časovým mezerám .
Například při shromažďování statistik využití energie pro inteligentní zařízení platí, že když zařízení není funkční, jsou 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 vygenerovaly data s různými frekvencemi, což vede k chybějícím hodnotám pro senzory. Pokud jsou například dva senzory, napětí a tlak nakonfigurované na frekvenci 100 Hz a 10-Hz, senzor napětí vysílá data každých stoth sekundy, zatímco senzor tlaku vysílá data pouze každých 1 desetinu sekundy.
Následující tabulka popisuje datovou sadu telemetrie počítače, která byla shromážděna v jednom sekundovém intervalu.
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
Existují dvě důležité vlastnosti předchozí datové sady.
- Datová sada neobsahuje žádné datové body související s několika časovými razítky
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.000a2020-09-07 06:14:55.000. Tato časová razítka jsou mezery v datové sadě. - Chybí hodnoty, které jsou reprezentovány jako
null, pro čtení napětí a tlaku.
Vyplňování mezer
Vyplňování mezer je technika, která pomáhá vytvářet souvislou uspořádanou sadu časových razítek, aby se usnadnila analýza dat časových řad. V Azure SQL Edge je nejjednodušší způsob, jak vyplnit mezery v datové sadě časových řad, definovat dočasnou tabulku s požadovanou časovou distribucí a pak provést LEFT OUTER JOIN operaci v RIGHT OUTER JOIN tabulce datové sady.
MachineTelemetry Když vezmeme data reprezentovaná dříve jako příklad, můžete použít následující dotaz ke generování souvislých seřazených sad časových razítek pro analýzu.
Poznámka:
Následující dotaz vygeneruje chybějící řádky s hodnotami časového razítka a null hodnotami atributů.
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];
Výše uvedený dotaz vytvoří následující výstup obsahující všechna časová razítka o jednu sekundu v zadaném rozsahu.
Tady je sada výsledků:
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
Imputovat chybějící hodnoty
Předchozí dotaz vygeneroval chybějící časové razítka pro analýzu dat, ale nenahradil žádné chybějící hodnoty (reprezentované jako null) pro voltage čtení a pressure čtení. V Azure SQL Edge byla do T-SQL LAST_VALUE() a FIRST_VALUE() funkcí přidána nová syntaxe, která poskytuje mechanismy pro impuování chybějících hodnot na základě předchozích nebo následujících hodnot v datové sadě.
Nová syntaxe přidá IGNORE NULLS do RESPECT NULLS funkcí a FIRST_VALUE() klauzuliLAST_VALUE(). Následující dotaz na MachineTelemetry datovou sadu vypočítá chybějící hodnoty pomocí funkce LAST_VALUE, kde se chybějící hodnoty nahradí poslední pozorovanou 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ů:
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 načte chybějící hodnoty pomocí LAST_VALUE() funkce i funkce FIRST_VALUE . U výstupního sloupce ImputedVoltagenahradí poslední pozorovaná hodnota chybějící hodnoty, zatímco ve výstupním sloupci ImputedPressure se chybějící hodnoty nahradí další pozorovanou hodnotou v datové 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 JOIN MachineTelemetry b
ON a.dt = b.[timestamp]
) A
ORDER BY timestamp;
Tady je sada výsledků:
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á FIRST_VALUE() funkci k nahrazení chybějících hodnot další pozorovanou hodnotou. Stejný výsledek lze dosáhnout pomocí LAST_VALUE() funkce s klauzulí ORDER BY <ordering_column> DESC .