سد الفجوات الزمنية وإسناد القيم المفقودة
هام
لم يعد Azure SQL Edge يدعم النظام الأساسي ARM64.
عند التعامل مع بيانات السلاسل الزمنية، غالبا ما يكون من الممكن أن تحتوي بيانات السلاسل الزمنية على قيم مفقودة للسمات. من الممكن أيضا، بسبب طبيعة البيانات، أو بسبب الانقطاعات في جمع البيانات، وجود فجوات زمنية في مجموعة البيانات.
على سبيل المثال، عند جمع إحصائيات استخدام الطاقة لجهاز ذكي، كلما لم يكن الجهاز قيد التشغيل، توجد فجوات في إحصائيات الاستخدام. وبالمثل، في سيناريو جمع بيانات تتبع الاستخدام للجهاز، من الممكن تكوين أجهزة الاستشعار المختلفة لإصدار البيانات بترددات مختلفة، ما يؤدي إلى فقدان القيم لأجهزة الاستشعار. على سبيل المثال، إذا كان هناك جهازا استشعار، الجهد والضغط، تم تكوينهما بسرعة 100 هرتز وتردد 10 هرتز على التوالي، فإن أداة استشعار الجهد تنبعث منها البيانات كل 100 من الثانية، بينما تنبعث من أداة استشعار الضغط البيانات كل عشر ثانية فقط.
يصف الجدول التالي مجموعة بيانات تتبع الاستخدام للجهاز، والتي تم جمعها في فاصل زمني مدته ثانية واحدة.
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
هناك نوعان من الخصائص المهمة لمجموعة البيانات السابقة.
- لا تحتوي مجموعة البيانات على أي نقاط بيانات تتعلق بعدة طوابع زمنية
2020-09-07 06:14:47.000و2020-09-07 06:14:48.0002020-09-07 06:14:50.0002020-09-07 06:14:53.000.2020-09-07 06:14:55.000هذه الطوابع الزمنية هي فجوات في مجموعة البيانات. - هناك قيم مفقودة، ممثلة ك
null، لقراءات الجهد والضغط.
سد الفجوة
ملء الثغرات هي تقنية تساعد على إنشاء مجموعة متجاورة مرتبة من الطوابع الزمنية لتسهيل تحليل بيانات السلاسل الزمنية. في Azure SQL Edge، أسهل طريقة لملء الثغرات في مجموعة بيانات السلسلة الزمنية هي تحديد جدول مؤقت مع توزيع الوقت المطلوب ثم القيام بعملية LEFT OUTER JOIN أو RIGHT OUTER JOIN على جدول مجموعة البيانات.
MachineTelemetry مع أخذ البيانات الممثلة سابقا كمثال، يمكن استخدام الاستعلام التالي لإنشاء مجموعة متقاربة مرتبة من الطوابع الزمنية للتحليل.
إشعار
ينشئ الاستعلام التالي الصفوف المفقودة، مع قيم null الطابع الزمني والقيم للسمات.
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];
ينتج الاستعلام أعلاه الإخراج التالي الذي يحتوي على جميع الطوابع الزمنية الثانية في النطاق المحدد.
ترد فيما يلي مجموعة النتائج:
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
إسناد القيم المفقودة
أنشأ الاستعلام السابق الطوابع الزمنية المفقودة لتحليل البيانات، ولكنه لم يحل محل أي من القيم المفقودة (ممثلة كقيم خالية) للقراءات voltage و pressure . في Azure SQL Edge، تمت إضافة بناء جملة جديد إلى T-SQL LAST_VALUE() والوظائف FIRST_VALUE() ، والتي توفر آليات لحساب القيم المفقودة، استنادا إلى القيم السابقة أو التالية في مجموعة البيانات.
يضيف بناء الجملة IGNORE NULLS الجديد وعبارة RESPECT NULLSLAST_VALUE() إلى الدالتين و FIRST_VALUE() . يحسب الاستعلام التالي على MachineTelemetry مجموعة البيانات القيم المفقودة باستخدام الدالة LAST_VALUE، حيث يتم استبدال القيم المفقودة بآخر قيمة تمت ملاحظتها في مجموعة البيانات.
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;
ترد فيما يلي مجموعة النتائج:
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
يحسب الاستعلام التالي القيم المفقودة باستخدام كل من LAST_VALUE() الدالتين FIRST_VALUE و. بالنسبة لعمود ImputedVoltageالإخراج ، تحل آخر قيمة تمت ملاحظتها محل القيم المفقودة، بينما بالنسبة لعمود ImputedPressure الإخراج يتم استبدال القيم المفقودة بالقيمة التالية التي تمت ملاحظتها في مجموعة البيانات.
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;
ترد فيما يلي مجموعة النتائج:
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
إشعار
يستخدم الاستعلام أعلاه الدالة FIRST_VALUE() لاستبدال القيم المفقودة بالقيمة التالية التي تمت ملاحظتها. يمكن تحقيق نفس النتيجة باستخدام الدالة LAST_VALUE() مع عبارة ORDER BY <ordering_column> DESC .