Hiaten invullen en ontbrekende waarden invoeren

  • Artikel

Belangrijk

Azure SQL Edge biedt geen ondersteuning meer voor het ARM64-platform.

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, tijdsverschillen zijn in de gegevensset.

Wanneer u bijvoorbeeld statistieken over energieverbruik voor een slim apparaat verzamelt, 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 verzenden, wat resulteert in ontbrekende waarden voor de sensoren. Als er bijvoorbeeld twee sensoren, spanning en druk zijn geconfigureerd op respectievelijk 100 Hz- en 10-Hz-frequentie, verzendt de spanningssensor elke honderdste van een seconde gegevens, terwijl de druksensor slechts om de tiende van een seconde gegevens verzendt.

In de volgende tabel wordt een gegevensset voor machinetelemetrie beschreven, die met een interval van één seconde is verzameld.

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 met betrekking tot verschillende tijdstempels2020-09-07 06:14:47.000, , 2020-09-07 06:14:50.0002020-09-07 06:14:48.000, 2020-09-07 06:14:53.000en 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.

Opening vullen

Openingen vullen is een techniek die helpt bij het maken van aaneengesloten, geordende set tijdstempels om de analyse van tijdreeksgegevens te vereenvoudigen. In Azure SQL Edge is de eenvoudigste manier om hiaten in de tijdreeksgegevensset op te vullen door een tijdelijke tabel te definiëren met de gewenste tijddistributie en vervolgens een LEFT OUTER JOIN of een RIGHT OUTER JOIN bewerking uit te voeren in de gegevenssettabel.

Als u de MachineTelemetry eerder weergegeven gegevens als voorbeeld gebruikt, kan de volgende query worden gebruikt voor het genereren van aaneengesloten, geordende set tijdstempels voor analyse.

Notitie

Met de volgende 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 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

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

De nieuwe syntaxis voegt en component toe IGNORE NULLS aan de LAST_VALUE() en FIRST_VALUE() functies.RESPECT NULLS Met een volgende query op de MachineTelemetry gegevensset worden de ontbrekende waarden berekend met behulp van de functie LAST_VALUE, waarbij ontbrekende waarden worden vervangen door de laatst waargenomen 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

Met de volgende query worden de ontbrekende waarden met behulp van zowel de LAST_VALUE() als de FIRST_VALUE functie opgenomen. Voor de uitvoerkolom vervangt de laatst waargenomen waarde de ontbrekende waarden, terwijl voor de uitvoerkolom ImputedVoltageImputedPressure de ontbrekende waarden worden vervangen door de volgende waargenomen waarde 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 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