시간 간격 채우기 및 누락된 값 대치
Important
Azure SQL Edge는 더 이상 ARM64 플랫폼을 지원하지 않습니다.
시계열 데이터를 처리할 때 시계열 데이터에 누락된 특성 값이 있을 수 있습니다. 또한 데이터의 특성이나 데이터 수집에서 중단이 발생하여 데이터 세트에 시간 간격이 있을 수 있습니다.
예를 들어 스마트 디바이스에 대한 에너지 사용 통계를 수집하는 경우 디바이스가 작동하지 않을 때마다 사용 통계에 간격이 나타납니다. 마찬가지로, 컴퓨터 원격 분석 데이터 수집 시나리오에서 여러 센서가 데이터를 다른 빈도로 내보내도록 구성된 경우 센서에 누락된 값이 생성될 수 있습니다. 예를 들어 전압과 압력이 각각 100Hz와 10Hz 빈도로 구성된 두 개의 센서가 있는 경우 전압 센서는 1/100초 마다 데이터를 내보내고 압력 센서는 1/10초 간격으로 데이터를 내보냅니다.
다음 표는 1초 간격으로 수집된 컴퓨터 원격 분석 데이터 세트를 나타냅니다.
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.000,2020-09-07 06:14:50.000,2020-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];
위의 쿼리를 사용하면 지정된 범위의 모든 1초 타임스탬프를 포함하는 다음과 같은 출력을 생성합니다.
결과 세트:
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 값의 누락된 값(null로 표시됨)을 대체하지는 못합니다. Azure SQL Edge에서 T-SQL LAST_VALUE() 및 FIRST_VALUE() 기능에 대해 새로운 구문이 추가되었습니다. 이 구문은 데이터 세트 앞의 값이나 다음 값을 기준으로 누락된 값을 대체하는 메커니즘을 제공합니다.
새로운 구문은 LAST_VALUE() 및 FIRST_VALUE() 함수에 IGNORE NULLS 및 RESPECT NULLS 절을 추가합니다. 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() 함수를 사용하여 누락된 값을 다음 관찰된 값으로 바꿉니다. ORDER BY <ordering_column> DESC 절을 사용하는 LAST_VALUE() 기능을 사용해도 동일한 결과를 얻을 수 있습니다.