Fylla tidsluckor och imputera saknade värden
Viktigt!
Azure SQL Edge stöder inte längre ARM64-plattformen.
När du hanterar tidsseriedata är det ofta möjligt att tidsseriedata saknar värden för attributen. Det är också möjligt att det på grund av datatypen eller på grund av avbrott i datainsamlingen finns tidsluckor i datamängden.
När du till exempel samlar in energianvändningsstatistik för en smart enhet finns det luckor i användningsstatistiken när enheten inte används. I ett scenario med datortelemetridatainsamling är det också möjligt att de olika sensorerna är konfigurerade för att generera data med olika frekvenser, vilket resulterar i saknade värden för sensorerna. Om det till exempel finns två sensorer, spänning och tryck, konfigurerade med 100 Hz respektive 10 Hz frekvens, genererar spänningssensorn data var hundradels sekund, medan trycksensorn endast genererar data var tionde sekund.
I följande tabell beskrivs en datauppsättning för datortelemetri som samlades in med ett intervall på en sekund.
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
Det finns två viktiga egenskaper för den föregående datamängden.
- Datamängden innehåller inga datapunkter relaterade till flera tidsstämplar
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.000och2020-09-07 06:14:55.000. Dessa tidsstämplar är luckor i datamängden. - Det saknas värden, som representeras som
null, för spännings- och tryckavläsningarna.
Gapfyllning
Gapfyllning är en teknik som hjälper till att skapa sammanhängande, ordnade uppsättning tidsstämplar för att underlätta analysen av tidsseriedata. I Azure SQL Edge är det enklaste sättet att fylla luckor i tidsseriedatamängden att definiera en tillfällig tabell med önskad tidsfördelning och sedan utföra en LEFT OUTER JOIN eller en RIGHT OUTER JOIN åtgärd i datamängdstabellen.
Med data MachineTelemetry som tidigare representerats som ett exempel kan följande fråga användas för att generera sammanhängande, ordnade uppsättningar tidsstämplar för analys.
Kommentar
Följande fråga genererar de saknade raderna med tidsstämpelvärdena och null värdena för attributen.
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];
Ovanstående fråga genererar följande utdata som innehåller alla ensekunders tidsstämplar i det angivna intervallet.
Här är resultatuppsättningen:
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
Impute saknade värden
Föregående fråga genererade saknade tidsstämplar för dataanalys, men den ersatte inte något av de saknade värdena (representeras som null) för voltage och pressure läsningar. I Azure SQL Edge har en ny syntax lagts till i T-SQL LAST_VALUE() och FIRST_VALUE() funktioner, som tillhandahåller mekanismer för att imputera saknade värden baserat på föregående eller följande värden i datauppsättningen.
Den nya syntaxen lägger till IGNORE NULLS och RESPECT NULLS sats till LAST_VALUE() funktionerna och FIRST_VALUE() . En följande fråga i datauppsättningen MachineTelemetry beräknar saknade värden med hjälp av funktionen LAST_VALUE, där saknade värden ersätts med det senast observerade värdet i datauppsättningen.
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;
Här är resultatuppsättningen:
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
Följande fråga imputerar de saknade värdena med hjälp av både LAST_VALUE() och funktionen FIRST_VALUE . För utdatakolumnen ImputedVoltageersätter det senast observerade värdet de saknade värdena, medan de saknade värdena för utdatakolumnen ImputedPressure ersätts med nästa observerade värde i datauppsättningen.
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;
Här är resultatuppsättningen:
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
Kommentar
Ovanstående fråga använder FIRST_VALUE() funktionen för att ersätta saknade värden med nästa observerade värde. Samma resultat kan uppnås med hjälp LAST_VALUE() av funktionen med en ORDER BY <ordering_column> DESC sats.