Fylla i tidsluckor och imputera saknade värden

  • Artikel
  • 4 minuter för att läsa

När du hanterar tidsseriedata är det ofta möjligt att tidsseriedata har saknade värden för attributen. Det är också möjligt att det finns tidsluckor i datamängden på grund av datamängdens natur eller på grund av avbrott i datainsamlingen.

När du till exempel samlar in energiförbrukningsstatistik för en smart enhet, kommer det att finnas luckor i användningsstatistiken när enheten inte fungerar. I ett scenario med insamling av datortelemetridata är det på samma sätt möjligt att de olika sensorerna är konfigurerade för att sända 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, sänder spänningssensorn data var hundradel av en sekund, medan trycksensorn bara sänder data var tionde sekund.

I följande tabell beskrivs en datauppsättning för datortelemetri, som samlades in med en sekunds intervall.

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 föregående datauppsättning.

  • Datauppsättningen innehåller inte några datapunkter relaterade till flera tidsstämplar 2020-09-07 06:14:47.000 , , , och 2020-09-07 06:14:48.000 2020-09-07 06:14:50.000 2020-09-07 06:14:53.000 2020-09-07 06:14:55.000 . Dessa tidsstämplar är luckor i datamängden.
  • Det finns saknade värden, som null representeras som , för spännings- och tryckavläsningarna.

Fyllning av luckor

Gapfyllning är en teknik som hjälper till att skapa sammanhängande, sorterade 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 göra en - eller -åtgärd i LEFT OUTER JOIN datamängdstabellen. RIGHT OUTER JOIN

Med de data som representeras ovan som exempel kan följande fråga användas för att generera sammanhängande, sorterade MachineTelemetry tidsstämplar för analys.

Anteckning

Frågan nedan genererar de rader som saknas, med tidsstämpelvärden och null värden 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 OUTER JOIN MachineTelemetry b
    on a.dt = b.[timestamp]

Ovanstående fråga genererar följande utdata som innehåller alla tidsstämplar på en sekund 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

Mata in saknade värden

Föregående fråga genererade saknade tidsstämplar för dataanalys, men den ersätter inte några av de saknade värdena (som representeras som null) voltage för och pressure läsningar. I Azure SQL Edge har en ny syntax lagts till i funktionerna T-SQL och , som tillhandahåller mekanismer för att imputera saknade värden baserat på föregående eller följande värden i LAST_VALUE() FIRST_VALUE() datauppsättningen.

Den nya syntaxen lägger IGNORE NULLS till RESPECT NULLS - och -satsen i LAST_VALUE() funktionerna och FIRST_VALUE() . En följande fråga på datauppsättningen beräknar saknade värden med hjälp last_value,där saknade värden ersätts med det senast observerade MachineTelemetry 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 imputes saknade värden med hjälp av LAST_VALUE() både - och FIRST_VALUE -funktionen. I utdatakolumnen ersätts saknade värden med det senast observerade värdet, medan de saknade värdena för utdatakolumnen ersätts av nästa observerade värde i ImputedVoltage ImputedPressure 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 OUTER 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

Anteckning

Ovanstående fråga använder funktionen FIRST_VALUE() för att ersätta saknade värden med nästa observerade värde. Samma resultat kan uppnås med hjälp av funktionen LAST_VALUE() med en ORDER BY <ordering_column> DESC -sats.

Nästa steg