Wypełnianie luk czasu i przypisywanie brakujących wartości

Artykuł
10/17/2024

Ważne

Usługa Azure SQL Edge zostanie wycofana 30 września 2025 r. Aby uzyskać więcej informacji i opcji migracji, zobacz powiadomienie o wycofaniu.

Uwaga

Usługa Azure SQL Edge nie obsługuje już platformy ARM64.

Podczas pracy z danymi szeregów czasowych często jest możliwe, że dane szeregów czasowych mają brakujące wartości atrybutów. Istnieje również możliwość, że ze względu na charakter danych lub z powodu przerw w zbieraniu danych istnieją luki czasowe w zestawie danych.

Na przykład podczas zbierania statystyk użycia energii dla urządzenia inteligentnego zawsze, gdy urządzenie nie działa, występują luki w statystykach użycia. Podobnie w scenariuszu zbierania danych telemetrycznych maszyny możliwe jest, że różne czujniki są skonfigurowane do emitowania danych o różnych częstotliwościach, co powoduje brak wartości czujników. Na przykład, jeśli istnieją dwa czujniki, napięcie i ciśnienie, skonfigurowane odpowiednio z częstotliwością 100 Hz i 10-Hz, czujnik napięcia emituje dane co setną część sekundy, podczas gdy czujnik ciśnienia emituje tylko dane co jedną dziesiątą sekundy.

W poniższej tabeli opisano zestaw danych telemetrycznych maszyny, który został zebrany w jednym interwale sekundy.

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

Istnieją dwie ważne cechy powyższego zestawu danych.

Zestaw danych nie zawiera żadnych punktów danych związanych z kilkoma znacznikami 2020-09-07 06:14:47.000czasu, , 2020-09-07 06:14:48.000, 2020-09-07 06:14:50.0002020-09-07 06:14:53.000i 2020-09-07 06:14:55.000. Te znaczniki czasu to luki w zestawie danych.
Brak wartości reprezentowanych jako null, dla odczytów napięcia i ciśnienia.

Wypełnienie luki

Wypełnianie luk to technika, która ułatwia tworzenie ciągłych, uporządkowanych zestawów sygnatur czasowych w celu ułatwienia analizy danych szeregów czasowych. W usłudze Azure SQL Edge najprostszym sposobem wypełnienia luk w zestawie danych szeregów czasowych jest zdefiniowanie tabeli tymczasowej z odpowiednim rozkładem czasu, a następnie wykonywanie LEFT OUTER JOIN operacji lub RIGHT OUTER JOIN w tabeli zestawów danych.

MachineTelemetry Biorąc dane reprezentowane wcześniej jako przykład, następujące zapytanie może służyć do generowania ciągłych, uporządkowanych zestawów sygnatur czasowych na potrzeby analizy.

Uwaga

Poniższe zapytanie generuje brakujące wiersze z wartościami znacznika czasu i null wartościami atrybutów.

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];

Powyższe zapytanie generuje następujące dane wyjściowe zawierające wszystkie jednorazowe znaczniki czasu w określonym zakresie.

Oto zestaw wyników:

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

Uzupełnianie brakujących wartości

Poprzednie zapytanie wygenerowało brakujące znaczniki czasu na potrzeby analizy danych, jednak nie zamieniło żadnych brakujących wartości (reprezentowanych jako null) i voltage pressure odczytów. W usłudze Azure SQL Edge dodano nową składnię do języka T-SQL LAST_VALUE() i FIRST_VALUE() funkcji, które zapewniają mechanizmy imputowania brakujących wartości na podstawie powyższych lub następujących wartości w zestawie danych.

Nowa składnia dodaje IGNORE NULLS klauzulę LAST_VALUE() i RESPECT NULLS do funkcji i FIRST_VALUE() . Następujące zapytanie w MachineTelemetry zestawie danych oblicza brakujące wartości przy użyciu funkcji LAST_VALUE, gdzie brakujące wartości są zastępowane ostatnią obserwowaną wartością w zestawie danych.

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;

Oto zestaw wyników:

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

Poniższe zapytanie imputuje brakujące wartości przy użyciu funkcji LAST_VALUE() i FIRST_VALUE . W kolumnie wyjściowej ostatnia obserwowana wartość zastępuje brakujące wartości, podczas gdy dla kolumny ImputedVoltageImputedPressure wyjściowej brakujące wartości są zastępowane przez następną obserwowaną wartość w zestawie danych.

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;

Oto zestaw wyników:

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

Uwaga

Powyższe zapytanie używa FIRST_VALUE() funkcji , aby zastąpić brakujące wartości następną obserwowaną wartością. Ten sam wynik można osiągnąć przy użyciu LAST_VALUE() funkcji z klauzulą ORDER BY <ordering_column> DESC .

Udostępnij za pośrednictwem

Wypełnianie luk czasu i przypisywanie brakujących wartości

Wypełnienie luki

Uzupełnianie brakujących wartości

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

Wypełnianie luk czasu i przypisywanie brakujących wartości

Wypełnienie luki

Uzupełnianie brakujących wartości

Powiązana zawartość

Opinia

Dodatkowe zasoby