Időhiányok kitöltése és hiányzó értékek imputálása
Fontos
Az Azure SQL Edge 2025. szeptember 30-án megszűnik. További információkért és a migrálási lehetőségekért tekintse meg a kivonásról szóló közleményt.
Feljegyzés
Az Azure SQL Edge már nem támogatja az ARM64 platformot.
Az idősoradatok kezelésekor gyakran előfordulhat, hogy az idősor adatai nem tartalmaznak értékeket az attribútumokhoz. Az is lehetséges, hogy az adatok természete vagy az adatgyűjtés megszakadása miatt az adathalmaz időbeli eltérései vannak.
Például az intelligens eszközök energiahasználati statisztikáinak gyűjtésekor, amikor az eszköz nem működik, a használati statisztikákban rések vannak. Hasonlóképpen, a gépi telemetriai adatgyűjtési forgatókönyvben lehetséges, hogy a különböző érzékelők úgy vannak konfigurálva, hogy különböző frekvenciákon bocsátanak ki adatokat, ami hiányzó értékeket eredményez az érzékelők számára. Ha például két érzékelő, a feszültség és a nyomás van konfigurálva 100 Hz- és 10-Hz-es frekvencián, a feszültségérzékelő másodpercenként egyszázadik másodpercenként bocsát ki adatokat, míg a nyomásérzékelő csak egy tized másodpercenként bocsát ki adatokat.
Az alábbi táblázat egy gépi telemetriai adatkészletet ismertet, amelyet egy másodperces időközzel gyűjtöttünk össze.
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
Az előző adathalmaznak két fontos jellemzője van.
- Az adathalmaz nem tartalmaz több időbélyeggel
2020-09-07 06:14:47.000,2020-09-07 06:14:48.000, ,2020-09-07 06:14:50.0002020-09-07 06:14:53.000és2020-09-07 06:14:55.000. Ezek az időbélyegek az adathalmazban lévő rések . - Hiányoznak a
nullfeszültség- és nyomásértékek értékei.
Rés kitöltése
A rések kitöltése olyan technika, amely segít egybefüggő, rendezett időbélyegek létrehozásában az idősoradatok elemzésének megkönnyítése érdekében. Az Azure SQL Edge-ben az idősor-adathalmaz hézagainak kitöltésének legegyszerűbb módja egy ideiglenes tábla definiálása a kívánt időeloszlással, majd egy LEFT OUTER JOIN vagy egy RIGHT OUTER JOIN művelet végrehajtása az adathalmaztáblán.
MachineTelemetry A korábban példaként megjelenített adatok alapján az alábbi lekérdezés használható egybefüggő, rendezett időbélyegek létrehozására az elemzéshez.
Feljegyzés
Az alábbi lekérdezés létrehozza a hiányzó sorokat az attribútumok időbélyegértékeivel és null értékeivel.
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];
A fenti lekérdezés a következő kimenetet állítja elő, amely a megadott tartomány összes egy másodperces időbélyegét tartalmazza.
Az eredményhalmaz a következő:
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
Hiányzó értékek impute
Az előző lekérdezés létrehozta az adatelemzéshez szükséges hiányzó időbélyegeket, de nem cserélte le a hiányzó értékeket (null értékként jelölve) voltage és pressure az olvasásokat. Az Azure SQL Edge-ben egy új szintaxis lett hozzáadva a T-SQL-hez LAST_VALUE() és FIRST_VALUE() a függvényekhez, amely mechanizmusokat biztosít a hiányzó értékek pótlására az adathalmaz előző vagy következő értékei alapján.
Az új szintaxis hozzáadja és RESPECT NULLS hozzáadja IGNORE NULLS a LAST_VALUE() FIRST_VALUE() függvényeket. Az adathalmaz következő MachineTelemetry lekérdezése kiszámítja a hiányzó értékeket a LAST_VALUE függvénnyel, ahol a hiányzó értékek helyébe az adathalmaz utolsó megfigyelt értéke kerül.
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;
Az eredményhalmaz a következő:
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
Az alábbi lekérdezés a hiányzó értékeket a függvény és a LAST_VALUE() függvény használatával is imputálta FIRST_VALUE . A kimeneti oszlopban ImputedVoltageaz utolsó megfigyelt érték helyettesíti a hiányzó értékeket, míg a kimeneti oszlopban ImputedPressure a hiányzó értékeket az adathalmaz következő megfigyelt értéke váltja fel.
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;
Az eredményhalmaz a következő:
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
Feljegyzés
A fenti lekérdezés a függvény használatával lecseréli a FIRST_VALUE() hiányzó értékeket a következő megfigyelt értékre. Ugyanez az eredmény a függvény záradékkal ORDER BY <ordering_column> DESC való használatával LAST_VALUE() érhető el.