시계열 데이터셋용 GapFill 함수

시계열 데이터셋용 GapFill 함수 (GapFill Function for Time-Series Dataset)

시계열 데이터에서 누락된 데이터 포인트를 채워 넣는 GapFill 함수를 다루는 페이지예요.

출처: GapFill Function

본문

{% hint style="info" %} GapFill 함수는 실험적이며 지원 범위, 검증, 오류 보고가 제한적입니다. {% endhint %}

{% hint style="info" %} GapFill 함수는 단일 스테이지 쿼리 엔진(v1)에서만 지원됩니다. {% endhint %}

많은 데이터셋은 본질적으로 시계열이며, 시간에 따른 엔티티 상태 변화를 추적합니다. 기록된 데이터 포인트의 정밀도가 희박하거나 IOT 환경에서 네트워크 및 기타 장치 문제로 이벤트가 유실될 수 있습니다. 그러나 시간에 따른 엔티티 상태 변화를 추적하는 분석 애플리케이션은 메트릭 간격보다 더 낮은 정밀도로 값을 조회해야 할 수 있습니다.

다음은 주차 공간의 주차장 상태를 추적하는 샘플 데이터셋입니다.

lotId	event_time	is_occupied
P1	2021-10-01 09:01:00.000	1
P2	2021-10-01 09:17:00.000	1
P1	2021-10-01 09:33:00.000	0
P1	2021-10-01 09:47:00.000	1
P3	2021-10-01 10:05:00.000	1
P2	2021-10-01 10:06:00.000	0
P2	2021-10-01 10:16:00.000	1
P2	2021-10-01 10:31:00.000	0
P3	2021-10-01 11:17:00.000	0
P1	2021-10-01 11:54:00.000	0

일정 기간 동안 점유된 주차장의 총수를 알아내고 싶습니다. 이는 주차 공간을 관리하는 회사에게 흔한 사용 사례입니다.

30분 시간 버킷을 예로 들어 보겠습니다:

timeBucket/lotId	P1	P2	P3
2021-10-01 09:00:00.000	1	1
2021-10-01 09:30:00.000	0,1
2021-10-01 10:00:00.000		0,1	1
2021-10-01 10:30:00.000		0
2021-10-01 11:00:00.000			0
2021-10-01 11:30:00.000	0

위 표를 보면 시간 버킷 안에 주차장에 대한 누락 데이터가 많다는 것을 알 수 있습니다. 시간 버킷당 점유된 주차장 수를 계산하려면 누락 데이터를 gap fill해야 합니다.

데이터를 gap fill하는 방법

데이터를 gap fill하는 방법은 두 가지입니다: FILL_PREVIOUS_VALUE와 FILL_DEFAULT_VALUE.

FILL_PREVIOUS_VALUE는 특정 엔티티(이 경우 주차장)에 대해 이전 값이 있으면 그 이전 값으로 누락 데이터를 채운다는 뜻입니다. 그렇지 않으면 기본값으로 채웁니다.

FILL_DEFAULT_VALUE는 누락 데이터를 기본값으로 채운다는 뜻입니다. 숫자 컬럼의 기본값은 0입니다. 부울 컬럼 타입의 기본값은 false입니다. TimeStamp의 경우 1970년 1월 1일 00:00:00 GMT입니다. STRING, JSON, BYTES의 경우 빈 문자열입니다. 배열 타입 컬럼의 경우 빈 배열입니다.

다음 쿼리를 사용해 시간 버킷당 총 점유 주차장 수를 계산하겠습니다.

집계/Gapfill/집계

쿼리 문법

SELECT time_col, SUM(status) AS occupied_slots_count
FROM (
    SELECT GAPFILL(time_col,'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','2021-10-01 09:00:00.000',
                   '2021-10-01 12:00:00.000','30:MINUTES', FILL(status, 'FILL_PREVIOUS_VALUE'),
                    TIMESERIESON(lotId)), lotId, status
    FROM (
        SELECT DATETIMECONVERT(event_time,'1:MILLISECONDS:EPOCH',
               '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','30:MINUTES') AS time_col,
               lotId, lastWithTime(is_occupied, event_time, 'INT') AS status
        FROM parking_data
        WHERE event_time >= 1633078800000 AND  event_time <= 1633089600000
        GROUP BY 1, 2
        ORDER BY 1
        LIMIT 100)
    LIMIT 100)
GROUP BY 1
LIMIT 100

위 예제에서 TIMESERIESON(column_name) 요소는 필수이며, column_name은 실제 테이블 컬럼을 가리켜야 합니다. 리터럴이나 표현식일 수 없습니다.

또한 가장 안쪽 쿼리에 GROUP BY 절이 있으면 (일반 쿼리와 달리) 집계 함수를 반드시 포함해야 합니다. 그렇지 않으면 Select and Gapfill should be in the same sql statement 오류가 반환됩니다.

워크플로

가장 중첩된 SQL이 원시 이벤트 테이블을 다음 테이블로 변환합니다.

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P1	2021-10-01 09:30:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0

두 번째로 중첩된 SQL이 반환된 데이터를 다음과 같이 gap fill합니다:

timeBucket/lotId	P1	P2	P3
2021-10-01 09:00:00.000	1	1	0
2021-10-01 09:30:00.000	1	1	0
2021-10-01 10:00:00.000	1	1	1
2021-10-01 10:30:00.000	1	0	1
2021-10-01 11:00:00.000	1	0	0
2021-10-01 11:30:00.000	0	0	0

가장 바깥쪽 쿼리가 gapfill된 데이터를 다음과 같이 집계합니다:

timeBucket	totalNumOfOccuppiedSlots
2021-10-01 09:00:00.000	2
2021-10-01 09:30:00.000	2
2021-10-01 10:00:00.000	3
2021-10-01 10:30:00.000	2
2021-10-01 11:00:00.000	1
2021-10-01 11:30:00.000	0

여기서 우리가 만든 가정 하나는 원시 데이터가 타임스탬프로 정렬되어 있다는 것입니다. Gapfill과 Post-Gapfill Aggregation은 데이터를 정렬하지 않습니다.

위 예제는 세 단계가 일어나는 사용 사례를 보여 줍니다:

원시 데이터가 집계됩니다.
집계된 데이터가 gapfill됩니다.
gapfill된 데이터가 집계됩니다.

지원하는 시나리오가 세 개 더 있습니다.

Select/Gapfill

30분 시간 버킷당 누락 데이터를 gapfill하려면 쿼리는 다음과 같습니다:

쿼리 문법

SELECT GAPFILL(DATETIMECONVERT(event_time,'1:MILLISECONDS:EPOCH',
               '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','30:MINUTES'),
               '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','2021-10-01 09:00:00.000',
               '2021-10-01 12:00:00.000','30:MINUTES', FILL(is_occupied, 'FILL_PREVIOUS_VALUE'),
               TIMESERIESON(lotId)) AS time_col, lotId, is_occupied
FROM parking_data
WHERE event_time >= 1633078800000 AND  event_time <= 1633089600000
ORDER BY 1
LIMIT 100

워크플로

먼저 원시 데이터가 다음과 같이 변환됩니다:

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P1	2021-10-01 09:30:00.000	0
P1	2021-10-01 09:30:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	0
P2	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0

그런 다음 다음과 같이 gapfill됩니다:

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P3	2021-10-01 09:00:00.000	0
P1	2021-10-01 09:30:00.000	0
P1	2021-10-01 09:30:00.000	1
P2	2021-10-01 09:30:00.000	1
P3	2021-10-01 09:30:00.000	0
P1	2021-10-01 10:00:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	0
P2	2021-10-01 10:00:00.000	1
P1	2021-10-01 10:30:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 10:30:00.000	1
P1	2021-10-01 11:00:00.000	1
P2	2021-10-01 11:00:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0
P2	2021-10-01 11:30:00.000	0
P3	2021-10-01 11:30:00.000	0

집계/Gapfill

쿼리 문법

SELECT GAPFILL(time_col,'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','2021-10-01 09:00:00.000',
               '2021-10-01 12:00:00.000','30:MINUTES', FILL(status, 'FILL_PREVIOUS_VALUE'),
               TIMESERIESON(lotId)), lotId, status
FROM (
    SELECT DATETIMECONVERT(event_time,'1:MILLISECONDS:EPOCH',
           '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','30:MINUTES') AS time_col,
           lotId, lastWithTime(is_occupied, event_time, 'INT') AS status
    FROM parking_data
    WHERE event_time >= 1633078800000 AND  event_time <= 1633089600000
    GROUP BY 1, 2
    ORDER BY 1
    LIMIT 100)
LIMIT 100

워크플로

중첩 SQL이 원시 이벤트 테이블을 다음 테이블로 변환합니다.

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P1	2021-10-01 09:30:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0

바깥쪽 SQL이 반환된 데이터를 다음과 같이 gap fill합니다:

timeBucket/lotId	P1	P2	P3
2021-10-01 09:00:00.000	1	1	0
2021-10-01 09:30:00.000	1	1	0
2021-10-01 10:00:00.000	1	1	1
2021-10-01 10:30:00.000	1	0	1
2021-10-01 11:00:00.000	1	0	0
2021-10-01 11:30:00.000	0	0	0

Gapfill/집계

쿼리 문법

SELECT time_col, SUM(is_occupied) AS occupied_slots_count
FROM (
    SELECT GAPFILL(DATETIMECONVERT(event_time,'1:MILLISECONDS:EPOCH',
           '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','30:MINUTES'),
           '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','2021-10-01 09:00:00.000',
           '2021-10-01 12:00:00.000','30:MINUTES', FILL(is_occupied, 'FILL_PREVIOUS_VALUE'),
           TIMESERIESON(lotId)) AS time_col, lotId, is_occupied
    FROM parking_data
    WHERE event_time >= 1633078800000 AND  event_time <= 1633089600000
    ORDER BY 1
    LIMIT 100)
GROUP BY 1
LIMIT 100

워크플로

먼저 원시 데이터가 다음과 같이 변환됩니다:

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P1	2021-10-01 09:30:00.000	0
P1	2021-10-01 09:30:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	0
P2	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0

변환된 데이터가 다음과 같이 gap fill됩니다:

lotId	event_time	is_occupied
P1	2021-10-01 09:00:00.000	1
P2	2021-10-01 09:00:00.000	1
P3	2021-10-01 09:00:00.000	0
P1	2021-10-01 09:30:00.000	0
P1	2021-10-01 09:30:00.000	1
P2	2021-10-01 09:30:00.000	1
P3	2021-10-01 09:30:00.000	0
P1	2021-10-01 10:00:00.000	1
P3	2021-10-01 10:00:00.000	1
P2	2021-10-01 10:00:00.000	0
P2	2021-10-01 10:00:00.000	1
P1	2021-10-01 10:30:00.000	1
P2	2021-10-01 10:30:00.000	0
P3	2021-10-01 10:30:00.000	1
P2	2021-10-01 10:30:00.000	0
P1	2021-10-01 11:00:00.000	1
P2	2021-10-01 11:00:00.000	0
P3	2021-10-01 11:00:00.000	0
P1	2021-10-01 11:30:00.000	0
P2	2021-10-01 11:30:00.000	0
P3	2021-10-01 11:30:00.000	0

집계가 다음 테이블을 생성합니다:

timeBucket	totalNumOfOccuppiedSlots
2021-10-01 09:00:00.000	2
2021-10-01 09:30:00.000	2
2021-10-01 10:00:00.000	3
2021-10-01 10:30:00.000	2
2021-10-01 11:00:00.000	1
2021-10-01 11:30:00.000	0

시계열 데이터셋용 GapFill 함수