Zaman boşluklarını doldurma ve eksik değerleri belirtme
Önemli
Azure SQL Edge artık ARM64 platformunu desteklememektedir.
Zaman serisi verileriyle ilgilenirken, zaman serisi verilerinde öznitelikler için eksik değerler olması genellikle mümkündür. Ayrıca, verilerin doğası gereği veya veri toplamadaki kesintiler nedeniyle veri kümesinde zaman boşlukları olabilir.
Örneğin, bir akıllı cihaz için enerji kullanım istatistikleri toplandığında, cihaz çalışmadığında kullanım istatistiklerinde boşluklar vardır. Benzer şekilde, bir makine telemetrisi veri toplama senaryosunda, farklı algılayıcıların farklı frekanslarda veri yayacak şekilde yapılandırılması mümkündür ve bu da algılayıcılar için eksik değerlere neden olur. Örneğin, voltaj ve basınç olmak üzere sırasıyla 100 Hz ve 10-Hz frekansında yapılandırılan iki sensör varsa, voltaj sensörü saniyenin yüzde biri kadar veri yayarken, basınç sensörü saniyenin yalnızca onda biri için veri yayar.
Aşağıdaki tabloda, bir saniyelik aralıklarla toplanan bir makine telemetri veri kümesi açıklanmaktadır.
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
Önceki veri kümesinin iki önemli özelliği vardır.
- Veri kümesi, birkaç zaman damgası
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.000ve2020-09-07 06:14:55.000ile ilgili herhangi bir veri noktası içermez. Bu zaman damgaları veri kümesindeki boşluklardır. - Voltaj ve basınç okumaları için olarak
nulltemsil edilen eksik değerler vardır.
Boşluk doldurma
Boşluk doldurma, zaman serisi verilerinin analizini kolaylaştırmak için bitişik, sıralı zaman damgaları oluşturmaya yardımcı olan bir tekniktir. Azure SQL Edge'de zaman serisi veri kümesindeki boşlukları doldurmanın en kolay yolu, istenen zaman dağılımına sahip geçici bir tablo tanımlamak ve ardından veri kümesi tablosunda bir LEFT OUTER JOINRIGHT OUTER JOIN veya işlemi yapmaktır.
MachineTelemetry Daha önce örnek olarak gösterilen veriler alınarak, analiz için bitişik, sıralı zaman damgaları oluşturmak için aşağıdaki sorgu kullanılabilir.
Dekont
Aşağıdaki sorgu, özniteliklerin zaman damgası değerleri ve null değerleriyle birlikte eksik satırları oluşturur.
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];
Yukarıdaki sorgu, belirtilen aralıktaki tüm bir saniyelik zaman damgalarını içeren aşağıdaki çıkışı oluşturur.
Sonuç kümesi aşağıdadır:
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
Eksik değerleri açma/kapatma
Önceki sorgu, veri analizi için eksik zaman damgalarını oluşturdu, ancak ve pressure okumaları için voltage eksik değerlerden hiçbirinin (null olarak temsil edildi) yerini almadı. Azure SQL Edge'de, T-SQL LAST_VALUE() ve işlevlerine yeni bir söz dizimi eklenmiştir. FIRST_VALUE() Bu söz dizimi, veri kümesindeki önceki veya aşağıdaki değerleri temel alarak eksik değerleri açma mekanizmaları sağlar.
Yeni söz dizimi ve işlevlerine ve RESPECT NULLSFIRST_VALUE() yan tümcesi LAST_VALUE() eklerIGNORE NULLS. Veri kümesinde MachineTelemetry aşağıdaki sorgu, eksik değerlerin veri kümesindeki son gözlemlenen değerle değiştirildiği LAST_VALUE işlevini kullanarak hesaplar.
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;
Sonuç kümesi aşağıdadır:
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
Aşağıdaki sorgu hem hem de LAST_VALUE() işlevini kullanarak eksik değerleri açar FIRST_VALUE . Çıkış sütunu ImputedVoltageiçin son gözlemlenen değer eksik değerlerin yerini alırken, çıkış sütununda ImputedPressure eksik değerler veri kümesinde bir sonraki gözlemlenen değerle değiştirilir.
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;
Sonuç kümesi aşağıdadır:
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
Dekont
Yukarıdaki sorgu, eksik değerleri bir sonraki gözlemlenen değerle değiştirmek için işlevini kullanır FIRST_VALUE() . Aynı sonuç, işlevi yan ORDER BY <ordering_column> DESC tümcesi LAST_VALUE() ile kullanılarak elde edilebilir.