Mengisi kesenjangan waktu dan menyiratkan nilai yang hilang

  • Artikel

Penting

Azure SQL Edge tidak lagi mendukung platform ARM64.

Saat berhadapan dengan data rangkaian waktu, sangat mungkin data rangkaian waktu telah kehilangan nilai untuk atribut. Ada juga kemungkinan bahwa, karena sifat data, atau karena gangguan dalam pengumpulan data, ada kesenjangan dalam himpunan data.

Misalnya, saat mengumpulkan statistik penggunaan energi untuk perangkat pintar, setiap kali perangkat tidak beroperasi, ada celah dalam statistik penggunaan. Demikian pula, dalam skenario pengumpulan data telemetri mesin, ada kemungkinan bahwa sensor yang berbeda dikonfigurasi untuk memancarkan data pada frekuensi yang berbeda, menghasilkan nilai yang hilang untuk sensor. Misalnya, jika ada dua sensor, tegangan dan tekanan, dikonfigurasi masing-masing pada frekuensi 100 Hz dan 10-Hz, sensor tegangan memancarkan data setiap seratus detik, sementara sensor tekanan hanya memancarkan data setiap sepuluh detik.

Tabel berikut menjelaskan himpunan data telemetri mesin, yang dikumpulkan pada interval satu detik.

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

Ada dua karakteristik penting dari himpunan data sebelumnya.

  • Himpunan data tidak berisi titik data apa pun yang terkait dengan beberapa stempel waktu 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.000, dan 2020-09-07 06:14:55.000. Tanda waktu ini adalah celah dalam himpunan data.
  • Ada nilai yang hilang, diwakili sebagai null, untuk pembacaan tegangan dan tekanan.

Pengisian kesenjangan

Pengisian kesenjangan adalah teknik yang membantu membuat kumpulan tanda waktu yang berdekatan dan berurutan untuk memudahkan analisis data rangkaian waktu. Pada Azure SQL Edge, cara termudah untuk mengisi kesenjangan dalam himpunan data seri waktu adalah dengan menentukan tabel sementara dengan distribusi waktu yang diinginkan lalu melakukan operasi LEFT OUTER JOIN atau RIGHT OUTER JOIN pada tabel himpunan data.

Mengambil data yang MachineTelemetry diwakili sebelumnya sebagai contoh, kueri berikut dapat digunakan untuk menghasilkan kumpulan tanda waktu yang berdekatan dan diurutkan untuk analisis.

Catatan

Kueri berikut menghasilkan baris yang hilang, dengan nilai dan null nilai tanda waktu untuk atribut.

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

Kueri di atas menghasilkan output berikut yang berisi tanda waktu satu detik penuh dalam rentang yang ditentukan.

Berikut set hasilnya:

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 nilai yang hilang

Kueri sebelumnya menghasilkan tanda waktu yang hilang untuk analisis data, namun tidak menggantikan salah satu nilai yang hilang (direpresentasikan sebagai null) untuk voltage pembacaan dan pressure . Di Azure SQL Edge, sintaks baru ditambahkan ke fungsi T-SQL LAST_VALUE() dan FIRST_VALUE(), yang menyediakan mekanisme untuk mengimputasi nilai yang hilang, berdasarkan nilai sebelumnya atau setelahnya dalam himpunan data.

Sintaks baru menambahkan klausul IGNORE NULLS dan RESPECT NULLS ke fungsi LAST_VALUE() dan FIRST_VALUE(). Kueri berikut pada himpunan MachineTelemetry data menghitung nilai yang hilang menggunakan fungsi LAST_VALUE, di mana nilai yang hilang diganti dengan nilai terakhir yang diamati dalam himpunan data.

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;

Berikut set hasilnya:

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

Kueri berikut ini mengimputasi nilai yang hilang menggunakan fungsi LAST_VALUE() dan FIRST_VALUE. Untuk kolom ImputedVoltageoutput , nilai terakhir yang diamati menggantikan nilai yang hilang, sementara untuk kolom ImputedPressure output, nilai yang hilang digantikan oleh nilai yang diamati berikutnya dalam himpunan data.

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;

Berikut set hasilnya:

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

Catatan

Kueri di atas menggunakan fungsi FIRST_VALUE() untuk mengganti nilai yang hilang dengan nilai berikutnya yang diamati. Hasil yang sama dapat dicapai dengan menggunakan fungsi LAST_VALUE() dengan klausul ORDER BY <ordering_column> DESC.

Langkah berikutnya