시계열 갭 채우기
시계열 갭 채우기 (Time series filling gaps)
시간 데이터의 갭을 WITH FILL 절로 채우는 방법을 살펴봐요.
출처: 문서
본문
시계열 데이터로 작업할 때 누락된 데이터나 비활동으로 인해 데이터에 갭이 있을 수 있어요. 보통 쿼리할 때 그런 갭이 존재하지 않기를 원해요. 이 경우 WITH FILL 절이 유용할 수 있어요. 이 가이드는 WITH FILL을 사용해 시계열 데이터의 갭을 채우는 방법을 다뤄요.
설정 (Setup)
GenAI 이미지 서비스가 생성한 이미지에 대한 메타데이터를 저장하는 다음 테이블이 있다고 상상해요:
CREATE TABLE images
(
`id` String,
`timestamp` DateTime64(3),
`height` Int64,
`width` Int64,
`size` Int64
)
ENGINE = MergeTree
ORDER BY (size, height, width);
일부 레코드를 가져와 볼게요:
INSERT INTO images VALUES (1088619203512250448, '2023-03-24 00:24:03.684', 1536, 1536, 2207289);
INSERT INTO images VALUES (1088619204040736859, '2023-03-24 00:24:03.810', 1024, 1024, 1928974);
INSERT INTO images VALUES (1088619204749561989, '2023-03-24 00:24:03.979', 1024, 1024, 1275619);
INSERT INTO images VALUES (1088619206431477862, '2023-03-24 00:24:04.380', 2048, 2048, 5985703);
INSERT INTO images VALUES (1088619206905434213, '2023-03-24 00:24:04.493', 1024, 1024, 1558455);
INSERT INTO images VALUES (1088619208524431510, '2023-03-24 00:24:04.879', 1024, 1024, 1494869);
INSERT INTO images VALUES (1088619208425437515, '2023-03-24 00:24:05.160', 1024, 1024, 1538451);
버킷별 조회
2023년 3월 24일 00:24:03과 00:24:04 사이에 생성된 이미지를 살펴볼게요. 그 시점에 대한 매개변수를 만들어 볼게요:
SET param_start = '2023-03-24 00:24:03',
param_end = '2023-03-24 00:24:04';
다음으로 데이터를 100ms 버킷으로 그룹화하고 해당 버킷에 생성된 이미지 수를 반환하는 쿼리를 작성할게요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
결과 집합은 이미지가 생성된 버킷만 포함하지만, 시계열 분석에서는 항목이 없는 경우에도 각 100ms 버킷을 반환하고 싶을 수 있어요.
WITH FILL
WITH FILL 절을 사용해 이 갭들을 채울 수 있어요. 또한 채울 갭의 크기인 STEP을 지정할 거예요. 이는 DateTime 타입의 경우 기본적으로 1초이지만, 100ms 길이의 갭을 채우고 싶으므로 단계 값으로 100ms 간격을 사용할게요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
갭이 count 컬럼에서 0 값으로 채워진 것을 볼 수 있어요.
WITH FILL…FROM
하지만 시간 범위의 시작 부분에는 여전히 갭이 있어요. FROM을 지정해 고칠 수 있어요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘
결과에서 00:24:03.000부터 00:24:03.500까지의 버킷이 모두 이제 나타나는 것을 볼 수 있어요.
WITH FILL…TO
하지만 시간 범위 끝에서 여전히 일부 버킷이 빠져 있어요. TO 값을 제공해 채울 수 있어요. TO는 포함하지 않으므로 끝 시간에 약간을 더해 포함되도록 할게요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
│ 2023-03-24 00:24:04.900 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │
└─────────────────────────┴───────┘
이제 갭이 모두 채워졌고 00:24:03.000부터 00:24:05.000까지 100ms마다 항목이 있어요.
누적 수 (Cumulative count)
이제 버킷 전체에 걸쳐 생성된 이미지 수의 누적 수를 유지하고 싶다고 가정해요. 아래처럼 cumulative 컬럼을 추가해 이를 할 수 있어요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │ 0 │
└─────────────────────────┴───────┴────────────┘
cumulative 컬럼의 값이 원하는 대로 동작하지 않아요.
WITH FILL…INTERPOLATE
count 컬럼에 0이 있는 행은 cumulative에도 0이 있는데, 우리는 cumulative 컬럼의 이전 값을 사용하길 원해요. 아래처럼 INTERPOLATE 절로 이를 할 수 있어요:
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │
└─────────────────────────┴───────┴────────────┘
훨씬 좋아 보여요. 이제 마무리로 bar 함수를 사용해 막대 차트를 추가하고, 새 컬럼을 INTERPOLATE 절에 추가하는 것을 잊지 않을게요.
SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative,
bar(cumulative, 0, 10, 10) AS barChart
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative, barChart);
┌──────────────────bucket─┬─count─┬─cumulative─┬─barChart─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │ █ │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │ █ │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │ ██ │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │ ███ │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │ ████ │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │ █████ │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │ ██████ │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │ ██████ │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │ ██████ │
└─────────────────────────┴───────┴────────────┴──────────┘