Tijdshiaten invullen en ontbrekende waarden imputeren

  • Artikel
  • 5 minuten om te lezen

Bij het verwerken van tijdreeksgegevens is het vaak mogelijk dat de tijdreeksgegevens ontbrekende waarden voor de kenmerken bevatten. Het is ook mogelijk dat er, vanwege de aard van de gegevens of vanwege onderbrekingen in het verzamelen van gegevens, tijdhiaten in de gegevensset zijn.

Wanneer u bijvoorbeeld statistieken over energieverbruik verzamelt voor een slim apparaat, zijn er hiaten in de gebruiksstatistieken wanneer het apparaat niet operationeel is. Op dezelfde manier is het in een scenario voor het verzamelen van telemetriegegevens van een machine mogelijk dat de verschillende sensoren zijn geconfigureerd om gegevens met verschillende frequenties te zenden, wat resulteert in ontbrekende waarden voor de sensoren. Als er bijvoorbeeld twee sensoren, spanning en druk, respectievelijk zijn geconfigureerd op een frequentie van 100 Hz en 10 Hz, stuurt de spanningssensor elke honderdste van een seconde gegevens, terwijl de druksensor slechts om de 10e seconde van een seconde gegevens stuurt.

In de volgende tabel wordt een telemetrie-gegevensset van een machine beschreven, die is verzameld met een interval van één seconde.

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

Er zijn twee belangrijke kenmerken van de voorgaande gegevensset.

  • De gegevensset bevat geen gegevenspunten die betrekking hebben op verschillende tijdstempels, 2020-09-07 06:14:47.000 2020-09-07 06:14:48.000 , , en 2020-09-07 06:14:50.000 2020-09-07 06:14:53.000 2020-09-07 06:14:55.000 . Deze tijdstempels zijn hiaten in de gegevensset.
  • Er ontbreken waarden, weergegeven als null , voor de spannings- en drukmetingen.

Hiaat vullen

Het vullen van hiaat is een techniek waarmee u aaneengesloten, geordende reeks tijdstempels kunt maken om de analyse van tijdreeksgegevens te gemaken. In Azure SQL Edge kunt u hiaten in de tijdreeksset het eenvoudigst opvullen door een tijdelijke tabel te definiëren met de gewenste tijddistributie en vervolgens een bewerking of uit te voeren op de gegevenssettabel. LEFT OUTER JOIN RIGHT OUTER JOIN

Als we de bovenstaande gegevens als voorbeeld nemen, kan de volgende query worden gebruikt voor het genereren van aaneengesloten, geordende MachineTelemetry reeks tijdstempels voor analyse.

Notitie

Met de onderstaande query worden de ontbrekende rijen gegenereerd, met de tijdstempelwaarden en null -waarden voor de kenmerken.

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]

De bovenstaande query produceert de volgende uitvoer met alle tijdstempels van één seconde in het opgegeven bereik.

Dit is de resultatenset

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

Ontbrekende waarden invoeren

Met de voorgaande query zijn de ontbrekende tijdstempels gegenereerd voor gegevensanalyse, maar deze heeft geen van de ontbrekende waarden (weergegeven als null) voor de waarden en voltage pressure vervangen. In Azure SQL Edge is een nieuwe syntaxis toegevoegd aan de T-SQL en functies, die mechanismen bieden om ontbrekende waarden toe te passen op basis van de voorgaande of volgende waarden in de LAST_VALUE() FIRST_VALUE() gegevensset.

De nieuwe syntaxis voegt IGNORE NULLS de component en toe aan de functies en RESPECT NULLS LAST_VALUE() FIRST_VALUE() . Met een volgende query op de gegevensset worden de ontbrekende waarden berekend met behulp van de functie last_value, waarbij ontbrekende waarden worden vervangen door de laatst waargenomen MachineTelemetry waarde in de gegevensset.

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

Dit is de resultatenset

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

De volgende query bespreekt de ontbrekende waarden met behulp van zowel LAST_VALUE() de functie als de functie FIRST_VALUE . Voor worden de ontbrekende waarden in de uitvoerkolom vervangen door de laatst waargenomen waarde, terwijl voor de uitvoerkolom de ontbrekende waarden worden vervangen door de volgende waargenomen waarde ImputedVoltage ImputedPressure in de gegevensset.

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

Dit is de resultatenset

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

Notitie

De bovenstaande query gebruikt de FIRST_VALUE() functie om ontbrekende waarden te vervangen door de volgende waargenomen waarde. Hetzelfde resultaat kan worden bereikt met behulp van de LAST_VALUE() functie met een ORDER BY <ordering_column> DESC -component.

Volgende stappen