ORDER BY 절

ORDER BY 절

ORDER BY 절은 쿼리 결과의 정렬 기준을 지정해요. 표현식 목록(예: ORDER BY visits, search_phrase), SELECT 절의 열을 가리키는 숫자 목록(예: ORDER BY 2, 1), 또는 SELECT 절의 모든 열을 의미하는 ALL(예: ORDER BY ALL)을 포함할 수 있어요. DESC 또는 ASC 수정자로 정렬 방향을 결정할 수 있어요.

출처: 문서

본문

ORDER BY 절에는 다음이 포함돼요.

• 표현식 목록, 예: ORDER BY visits, search_phrase,
• SELECT 절의 열을 가리키는 숫자 목록, 예: ORDER BY 2, 1, 또는
• SELECT 절의 모든 열을 의미하는 ALL, 예: ORDER BY ALL.

열 번호로 정렬하는 것을 비활성화하려면 enable_positional_arguments = 0 설정을 사용해요. ALL로 정렬하는 것을 비활성화하려면 enable_order_by_all = 0 설정을 사용해요. ORDER BY 절에는 정렬 방향을 결정하는 DESC(내림차순) 또는 ASC(오름차순) 수정자를 지정할 수 있어요. 명시적인 정렬 순서를 지정하지 않으면 기본적으로 ASC가 사용돼요. 정렬 방향은 목록 전체가 아니라 단일 표현식에 적용돼요. 예: ORDER BY Visits DESC, SearchPhrase. 또한 정렬은 대소문자를 구분해서 수행돼요. 정렬 표현식에 대해 동일한 값을 가진 행은 임의의 비결정적 순서로 반환돼요. SELECT 문에서 ORDER BY 절을 생략하면 행 순서 또한 임의적이고 비결정적이에요.

Sorting of Special Values (특수 값 정렬)

NaN과 NULL의 정렬 순서에는 두 가지 접근 방식이 있어요.

• 기본값 또는 NULLS LAST 수정자: 먼저 값들, 그다음 NaN, 그다음 NULL.
• NULLS FIRST 수정자: 먼저 NULL, 그다음 NaN, 그다음 다른 값들.

Example (예제)

테이블에 대해

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    2 │
│ 1 │  nan │
│ 2 │    2 │
│ 3 │    4 │
│ 5 │    6 │
│ 6 │  nan │
│ 7 │ ᴺᵁᴸᴸ │
│ 6 │    7 │
│ 8 │    9 │
└───┴──────┘

SELECT * FROM t_null_nan ORDER BY y NULLS FIRST 쿼리를 실행하면:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 7 │ ᴺᵁᴸᴸ │
│ 1 │  nan │
│ 6 │  nan │
│ 2 │    2 │
│ 2 │    2 │
│ 3 │    4 │
│ 5 │    6 │
│ 6 │    7 │
│ 8 │    9 │
└───┴──────┘

부동 소수점 숫자를 정렬할 때 NaN은 다른 값들과 분리돼요. 정렬 순서와 관계없이 NaN은 끝에 옵니다. 즉 오름차순 정렬에서는 모든 다른 숫자보다 큰 것처럼 배치되고, 내림차순 정렬에서는 나머지보다 작은 것처럼 배치돼요.

Collation Support (정렬 규칙 지원)

String 값을 정렬할 때 collation(비교 규칙)을 지정할 수 있어요. 예: ORDER BY SearchPhrase COLLATE 'tr' - 문자열이 UTF-8로 인코딩되어 있다고 가정하고 터키어 알파벳, 대소문자 무시, 오름차순으로 키워드를 정렬해요. COLLATE는 ORDER BY의 각 표현식에 대해 독립적으로 지정할 수도 있고 지정하지 않을 수도 있어요. ASC 또는 DESC가 지정되면 COLLATE는 그 뒤에 지정돼요. COLLATE를 사용하면 정렬은 항상 대소문자를 무시해요. Collate는 LowCardinality, Nullable, Array, Tuple에서 지원돼요. COLLATE를 사용한 정렬은 바이트 기준 일반 정렬보다 덜 효율적이므로, 적은 수의 행을 최종 정렬할 때만 COLLATE를 사용하는 것을 권장해요.

Collation Examples (정렬 규칙 예시)

String 값만 있는 예시:

입력 테이블:

┌─x─┬─s────┐
│ 1 │ bca  │
│ 2 │ ABC  │
│ 3 │ 123a │
│ 4 │ abc  │
│ 5 │ BCA  │
└───┴──────┘

Query (쿼리)

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

Response (응답)

┌─x─┬─s────┐
│ 3 │ 123a │
│ 4 │ abc  │
│ 2 │ ABC  │
│ 1 │ bca  │
│ 5 │ BCA  │
└───┴──────┘

Nullable 예시:

입력 테이블:

┌─x─┬─s────┐
│ 1 │ bca  │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ ABC  │
│ 4 │ 123a │
│ 5 │ abc  │
│ 6 │ ᴺᵁᴸᴸ │
│ 7 │ BCA  │
└───┴──────┘

Query (쿼리)

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

Response (응답)

┌─x─┬─s────┐
│ 4 │ 123a │
│ 5 │ abc  │
│ 3 │ ABC  │
│ 1 │ bca  │
│ 7 │ BCA  │
│ 6 │ ᴺᵁᴸᴸ │
│ 2 │ ᴺᵁᴸᴸ │
└───┴──────┘

Array 예시:

입력 테이블:

┌─x─┬─s─────────────┐
│ 1 │ ['Z']         │
│ 2 │ ['z']         │
│ 3 │ ['a']         │
│ 4 │ ['A']         │
│ 5 │ ['z','a']     │
│ 6 │ ['z','a','a'] │
│ 7 │ ['']          │
└───┴───────────────┘

Query (쿼리)

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

Response (응답)

┌─x─┬─s─────────────┐
│ 7 │ ['']          │
│ 3 │ ['a']         │
│ 4 │ ['A']         │
│ 2 │ ['z']         │
│ 5 │ ['z','a']     │
│ 6 │ ['z','a','a'] │
│ 1 │ ['Z']         │
└───┴───────────────┘

LowCardinality 문자열 예시:

입력 테이블:

┌─x─┬─s───┐
│ 1 │ Z   │
│ 2 │ z   │
│ 3 │ a   │
│ 4 │ A   │
│ 5 │ za  │
│ 6 │ zaa │
│ 7 │     │
└───┴─────┘

Query (쿼리)

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

Response (응답)

┌─x─┬─s───┐
│ 7 │     │
│ 3 │ a   │
│ 4 │ A   │
│ 2 │ z   │
│ 1 │ Z   │
│ 5 │ za  │
│ 6 │ zaa │
└───┴─────┘

Tuple 예시:

Response (응답)

┌─x─┬─s───────┐
│ 1 │ (1,'Z') │
│ 2 │ (1,'z') │
│ 3 │ (1,'a') │
│ 4 │ (2,'z') │
│ 5 │ (1,'A') │
│ 6 │ (2,'Z') │
│ 7 │ (2,'A') │
└───┴─────────┘

Query (쿼리)

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

Response (응답)

┌─x─┬─s───────┐
│ 3 │ (1,'a') │
│ 5 │ (1,'A') │
│ 2 │ (1,'z') │
│ 1 │ (1,'Z') │
│ 7 │ (2,'A') │
│ 4 │ (2,'z') │
│ 6 │ (2,'Z') │
└───┴─────────┘

Implementation Details (구현 세부 사항)

ORDER BY에 더해 충분히 작은 LIMIT을 지정하면 더 적은 RAM이 사용돼요. 그렇지 않으면 정렬에 사용되는 메모리 양은 데이터의 양에 비례해요. LIMIT ... AFTER ... UNTIL 범위는 데이터가 정렬될 때까지 범위 크기를 알 수 없으므로 이를 줄여주지 않아요. 분산 쿼리 처리에서 GROUP BY가 생략되면 정렬은 부분적으로 원격 서버에서 수행되고, 결과는 요청 서버에서 병합돼요. 즉 분산 정렬의 경우 정렬할 데이터의 양이 단일 서버의 메모리 양보다 클 수 있어요. RAM이 충분하지 않다면 외부 메모리에서 정렬을 수행할 수 있어요(디스크에 임시 파일 생성). 이를 위해 max_bytes_before_external_sort 설정을 사용해요. 이 값이 0(기본값)으로 설정되면 외부 정렬은 비활성화돼요. 활성화되면 정렬할 데이터 양이 지정된 바이트 수에 도달할 때 수집된 데이터가 정렬되어 임시 파일로 덤프돼요. 모든 데이터를 읽은 후 모든 정렬된 파일이 병합되고 결과가 출력돼요. 파일은 config의 /var/lib/clickhouse/tmp/ 디렉터리에 작성돼요(기본값이며, tmp_path 매개변수로 이 설정을 바꿀 수 있어요). 또한 쿼리가 메모리 한도를 초과할 때만 디스크에 스필링할 수도 있어요. 즉 max_bytes_ratio_before_external_sort=0.6은 쿼리가 메모리 한도의 60%에 도달할 때만 디스크로 스필링을 활성화해요(사용자/서버). 쿼리 실행은 max_bytes_before_external_sort보다 더 많은 메모리를 사용할 수 있어요. 그래서 이 설정은 max_memory_usage보다 훨씬 작은 값이어야 해요. 예를 들어 서버에 128GB RAM이 있고 단일 쿼리를 실행해야 한다면 max_memory_usage를 100GB로, max_bytes_before_external_sort를 80GB로 설정해요. 외부 정렬은 RAM에서 정렬하는 것보다 훨씬 덜 효율적으로 동작해요.

Optimization of Data Reading (데이터 읽기 최적화)

ORDER BY 표현식에 테이블 정렬 키와 일치하는 접두사가 있다면 optimize_read_in_order 설정으로 쿼리를 최적화할 수 있어요. optimize_read_in_order 설정이 활성화되면 ClickHouse 서버는 테이블 인덱스를 사용하고 ORDER BY 키 순서로 데이터를 읽어요. 이를 통해 LIMIT이 지정된 경우 모든 데이터를 읽지 않을 수 있어요. ALL이 없는 LIMIT ... AFTER ... UNTIL 범위도 마찬가지인데, 범위가 끝나면 읽기를 중단해요. 그래서 작은 limit이 있는 대용량 데이터의 쿼리는 더 빨리 처리돼요. 최적화는 ASC와 DESC 모두에서 동작하며 GROUP BY 절과는 함께 동작하지 않아요. FINAL 수정자를 사용하면 최적화는 정렬 키의 직접 순서로 동작하고, ReplacingMergeTree 테이블에서는 optimize_read_in_reverse_order_final 설정으로 제어되는 역순으로도 동작해요. optimize_read_in_order 설정이 비활성화되면 ClickHouse 서버는 SELECT 쿼리를 처리할 때 테이블 인덱스를 사용하지 않아요. ORDER BY 절, 큰 LIMIT, 그리고 찾고자 하는 데이터 이전에 엄청난 양의 레코드를 읽어야 하는 WHERE 조건이 있는 쿼리를 실행할 때는 optimize_read_in_order를 수동으로 비활성화하는 것을 고려해요. 최적화는 다음 테이블 엔진에서 지원돼요.

• MergeTree (materialized views 포함),
• Merge,
• Buffer

MaterializedView-engine 테이블에서 최적화는 SELECT ... FROM merge_tree_table ORDER BY pk 같은 뷰와 동작해요. 하지만 뷰 쿼리에 ORDER BY 절이 없다면 SELECT ... FROM view ORDER BY pk 같은 쿼리에서는 지원되지 않아요.

ORDER BY Expr WITH FILL Modifier (ORDER BY Expr WITH FILL 수정자)

이 수정자는 LIMIT … WITH TIES modifier 및 LIMIT … AFTER … UNTIL 범위 형식과도 결합할 수 있어요. WITH FILL 수정자는 선택적인 FROM expr, TO expr, STEP expr 매개변수와 함께 ORDER BY expr 뒤에 설정할 수 있어요. expr 열의 누락된 모든 값은 순차적으로 채워지고, 다른 열은 기본값으로 채워져요. 여러 열을 채우려면 ORDER BY 섹션에서 각 필드 이름 뒤에 선택적 매개변수와 함께 WITH FILL 수정자를 추가해요.

Query (쿼리)

ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr] [STALENESS const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr] [STALENESS numeric_expr]
[INTERPOLATE [(col [AS expr], ... colN [AS exprN])]]

WITH FILL은 Numeric(모든 종류의 float, decimal, int) 또는 Date/DateTime 타입의 필드에 적용할 수 있어요. String 필드에 적용하면 누락된 값은 빈 문자열로 채워져요. FROM const_expr가 정의되지 않으면 채우기 시퀀스는 ORDER BY의 최소 expr 필드 값을 사용해요. TO const_expr가 정의되지 않으면 채우기 시퀀스는 ORDER BY의 최대 expr 필드 값을 사용해요. STEP const_numeric_expr이 정의되면 const_numeric_expr은 숫자 타입에서는 그대로, Date 타입에서는 days, DateTime 타입에서는 seconds로 해석돼요. 또한 시간과 날짜 간격을 나타내는 INTERVAL 데이터 타입도 지원해요. STEP const_numeric_expr이 생략되면 채우기 시퀀스는 숫자 타입에 1.0, Date 타입에 1 day, DateTime 타입에 1 second를 사용해요. STALENESS const_numeric_expr이 정의되면 쿼리는 원래 데이터의 이전 행과의 차이가 const_numeric_expr을 초과할 때까지 행을 생성해요. INTERPOLATE는 ORDER BY WITH FILL에 참여하지 않는 열에 적용할 수 있어요. 이러한 열은 expr을 적용해 이전 필드 값을 기반으로 채워져요. expr이 없으면 이전 값을 반복해요. 목록을 생략하면 허용되는 모든 열이 포함돼요.

WITH FILL이 없는 쿼리 예시:

Query (쿼리)

SELECT n, source FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n;

Response (응답)

┌─n─┬─source───┐
│ 1 │ original │
│ 4 │ original │
│ 7 │ original │
└───┴──────────┘

WITH FILL 수정자를 적용한 같은 쿼리:

Query (쿼리)

SELECT n, source FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;

Response (응답)

┌───n─┬─source───┐
│   0 │          │
│ 0.5 │          │
│   1 │ original │
│ 1.5 │          │
│   2 │          │
│ 2.5 │          │
│   3 │          │
│ 3.5 │          │
│   4 │ original │
│ 4.5 │          │
│   5 │          │
│ 5.5 │          │
│   7 │ original │
└─────┴──────────┘

여러 필드가 있는 경우 ORDER BY field2 WITH FILL, field1 WITH FILL의 채우기 순서는 ORDER BY 절의 필드 순서를 따릅니다.

Example (예제):

Query (쿼리)

SELECT
    toDate((number * 10) * 86400) AS d1,
    toDate(number * 86400) AS d2,
    'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
    d2 WITH FILL,
    d1 WITH FILL STEP 5;

Response (응답)

┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-01 │ 1970-01-03 │          │
│ 1970-01-01 │ 1970-01-04 │          │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-01-01 │ 1970-01-06 │          │
│ 1970-01-01 │ 1970-01-07 │          │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘

d2 값에 반복 값이 없어서 d1 필드는 채워지지 않고 기본값을 사용해요. d1의 시퀀스를 제대로 계산할 수 없기 때문이에요. ORDER BY의 필드를 바꾼 다음 쿼리:

Query (쿼리)

SELECT
    toDate((number * 10) * 86400) AS d1,
    toDate(number * 86400) AS d2,
    'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
    d1 WITH FILL STEP 5,
    d2 WITH FILL;

Response (응답)

┌───d1───────┬───d2───────┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-16 │ 1970-01-01 │          │
│ 1970-01-21 │ 1970-01-01 │          │
│ 1970-01-26 │ 1970-01-01 │          │
│ 1970-01-31 │ 1970-01-01 │          │
│ 1970-02-05 │ 1970-01-01 │          │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-15 │ 1970-01-01 │          │
│ 1970-02-20 │ 1970-01-01 │          │
│ 1970-02-25 │ 1970-01-01 │          │
│ 1970-03-02 │ 1970-01-01 │          │
│ 1970-03-07 │ 1970-01-01 │          │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘

다음 쿼리는 d1 열에 채워지는 각 데이터에 대해 1일의 INTERVAL 데이터 타입을 사용해요.

Query (쿼리)

SELECT
    toDate((number * 10) * 86400) AS d1,
    toDate(number * 86400) AS d2,
    'original' AS source
FROM numbers(10)
WHERE (number % 3) = 1
ORDER BY
    d1 WITH FILL STEP INTERVAL 1 DAY,
    d2 WITH FILL;

Response (응답)

┌─────────d1─┬─────────d2─┬─source───┐
│ 1970-01-11 │ 1970-01-02 │ original │
│ 1970-01-12 │ 1970-01-01 │          │
│ 1970-01-13 │ 1970-01-01 │          │
│ 1970-01-14 │ 1970-01-01 │          │
│ 1970-01-15 │ 1970-01-01 │          │
│ 1970-01-16 │ 1970-01-01 │          │
│ 1970-01-17 │ 1970-01-01 │          │
│ 1970-01-18 │ 1970-01-01 │          │
│ 1970-01-19 │ 1970-01-01 │          │
│ 1970-01-20 │ 1970-01-01 │          │
│ 1970-01-21 │ 1970-01-01 │          │
│ 1970-01-22 │ 1970-01-01 │          │
│ 1970-01-23 │ 1970-01-01 │          │
│ 1970-01-24 │ 1970-01-01 │          │
│ 1970-01-25 │ 1970-01-01 │          │
│ 1970-01-26 │ 1970-01-01 │          │
│ 1970-01-27 │ 1970-01-01 │          │
│ 1970-01-28 │ 1970-01-01 │          │
│ 1970-01-29 │ 1970-01-01 │          │
│ 1970-01-30 │ 1970-01-01 │          │
│ 1970-01-31 │ 1970-01-01 │          │
│ 1970-02-01 │ 1970-01-01 │          │
│ 1970-02-02 │ 1970-01-01 │          │
│ 1970-02-03 │ 1970-01-01 │          │
│ 1970-02-04 │ 1970-01-01 │          │
│ 1970-02-05 │ 1970-01-01 │          │
│ 1970-02-06 │ 1970-01-01 │          │
│ 1970-02-07 │ 1970-01-01 │          │
│ 1970-02-08 │ 1970-01-01 │          │
│ 1970-02-09 │ 1970-01-01 │          │
│ 1970-02-10 │ 1970-01-05 │ original │
│ 1970-02-11 │ 1970-01-01 │          │
│ 1970-02-12 │ 1970-01-01 │          │
│ 1970-02-13 │ 1970-01-01 │          │
│ 1970-02-14 │ 1970-01-01 │          │
│ 1970-02-15 │ 1970-01-01 │          │
│ 1970-02-16 │ 1970-01-01 │          │
│ 1970-02-17 │ 1970-01-01 │          │
│ 1970-02-18 │ 1970-01-01 │          │
│ 1970-02-19 │ 1970-01-01 │          │
│ 1970-02-20 │ 1970-01-01 │          │
│ 1970-02-21 │ 1970-01-01 │          │
│ 1970-02-22 │ 1970-01-01 │          │
│ 1970-02-23 │ 1970-01-01 │          │
│ 1970-02-24 │ 1970-01-01 │          │
│ 1970-02-25 │ 1970-01-01 │          │
│ 1970-02-26 │ 1970-01-01 │          │
│ 1970-02-27 │ 1970-01-01 │          │
│ 1970-02-28 │ 1970-01-01 │          │
│ 1970-03-01 │ 1970-01-01 │          │
│ 1970-03-02 │ 1970-01-01 │          │
│ 1970-03-03 │ 1970-01-01 │          │
│ 1970-03-04 │ 1970-01-01 │          │
│ 1970-03-05 │ 1970-01-01 │          │
│ 1970-03-06 │ 1970-01-01 │          │
│ 1970-03-07 │ 1970-01-01 │          │
│ 1970-03-08 │ 1970-01-01 │          │
│ 1970-03-09 │ 1970-01-01 │          │
│ 1970-03-10 │ 1970-01-01 │          │
│ 1970-03-11 │ 1970-01-01 │          │
│ 1970-03-12 │ 1970-01-08 │ original │
└────────────┴────────────┴──────────┘

STALENESS가 없는 쿼리 예시:

Query (쿼리)

SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL;

Response (응답)

    ┌─key─┬─value─┬─source───┐
 1. │   0 │     0 │ original │
 2. │   1 │     0 │          │
 3. │   2 │     0 │          │
 4. │   3 │     0 │          │
 5. │   4 │     0 │          │
 6. │   5 │    25 │ original │
 7. │   6 │     0 │          │
 8. │   7 │     0 │          │
 9. │   8 │     0 │          │
10. │   9 │     0 │          │
11. │  10 │    50 │ original │
12. │  11 │     0 │          │
13. │  12 │     0 │          │
14. │  13 │     0 │          │
15. │  14 │     0 │          │
16. │  15 │    75 │ original │
    └─────┴───────┴──────────┘

STALENESS 3을 적용한 같은 쿼리:

Query (쿼리)

SELECT number AS key, 5 * number value, 'original' AS source
FROM numbers(16) WHERE key % 5 == 0
ORDER BY key WITH FILL STALENESS 3;

Response (응답)

    ┌─key─┬─value─┬─source───┐
 1. │   0 │     0 │ original │
 2. │   1 │     0 │          │
 3. │   2 │     0 │          │
 4. │   5 │    25 │ original │
 5. │   6 │     0 │          │
 6. │   7 │     0 │          │
 7. │  10 │    50 │ original │
 8. │  11 │     0 │          │
 9. │  12 │     0 │          │
10. │  15 │    75 │ original │
11. │  16 │     0 │          │
12. │  17 │     0 │          │
    └─────┴───────┴──────────┘

INTERPOLATE가 없는 쿼리 예시:

Query (쿼리)

SELECT n, source, inter FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5;

Response (응답)

┌───n─┬─source───┬─inter─┐
│   0 │          │     0 │
│ 0.5 │          │     0 │
│   1 │ original │     1 │
│ 1.5 │          │     0 │
│   2 │          │     0 │
│ 2.5 │          │     0 │
│   3 │          │     0 │
│ 3.5 │          │     0 │
│   4 │ original │     4 │
│ 4.5 │          │     0 │
│   5 │          │     0 │
│ 5.5 │          │     0 │
│   7 │ original │     7 │
└─────┴──────────┴───────┘

INTERPOLATE를 적용한 같은 쿼리:

Query (쿼리)

SELECT n, source, inter FROM (
   SELECT toFloat32(number % 10) AS n, 'original' AS source, number AS inter
   FROM numbers(10) WHERE number % 3 = 1
) ORDER BY n WITH FILL FROM 0 TO 5.51 STEP 0.5 INTERPOLATE (inter AS inter + 1);

Response (응답)

┌───n─┬─source───┬─inter─┐
│   0 │          │     0 │
│ 0.5 │          │     0 │
│   1 │ original │     1 │
│ 1.5 │          │     2 │
│   2 │          │     3 │
│ 2.5 │          │     4 │
│   3 │          │     5 │
│ 3.5 │          │     6 │
│   4 │ original │     4 │
│ 4.5 │          │     5 │
│   5 │          │     6 │
│ 5.5 │          │     7 │
│   7 │ original │     7 │
└─────┴──────────┴───────┘

Filling grouped by sorting prefix (정렬 접두사로 그룹 지어 채우기)

특정 열에서 같은 값을 가진 행들을 독립적으로 채우는 것이 유용할 수 있어요. 시계열(time series)에서 누락된 값을 채우는 것이 좋은 예시예요. 다음과 같은 시계열 테이블이 있다고 가정해 볼게요.

CREATE TABLE timeseries
(
    `sensor_id` UInt64,
    `timestamp` DateTime64(3, 'UTC'),
    `value` Float64
)
ENGINE = Memory;

SELECT * FROM timeseries;

┌─sensor_id─┬───────────────timestamp─┬─value─┐
│       234 │ 2021-12-01 00:00:03.000 │     3 │
│       432 │ 2021-12-01 00:00:01.000 │     1 │
│       234 │ 2021-12-01 00:00:07.000 │     7 │
│       432 │ 2021-12-01 00:00:05.000 │     5 │
└───────────┴─────────────────────────┴───────┘

각 센서의 누락된 값을 1초 간격으로 독립적으로 채우고 싶다고 가정해 볼게요. 이를 달성하는 방법은 sensor_id 열을 timestamp 열을 채우기 위한 정렬 접두사로 사용하는 것이에요.

SELECT *
FROM timeseries
ORDER BY
    sensor_id,
    timestamp WITH FILL
INTERPOLATE ( value AS 9999 )

┌─sensor_id─┬───────────────timestamp─┬─value─┐
│       234 │ 2021-12-01 00:00:03.000 │     3 │
│       234 │ 2021-12-01 00:00:04.000 │  9999 │
│       234 │ 2021-12-01 00:00:05.000 │  9999 │
│       234 │ 2021-12-01 00:00:06.000 │  9999 │
│       234 │ 2021-12-01 00:00:07.000 │     7 │
│       432 │ 2021-12-01 00:00:01.000 │     1 │
│       432 │ 2021-12-01 00:00:02.000 │  9999 │
│       432 │ 2021-12-01 00:00:03.000 │  9999 │
│       432 │ 2021-12-01 00:00:04.000 │  9999 │
│       432 │ 2021-12-01 00:00:05.000 │     5 │
└───────────┴─────────────────────────┴───────┘

여기서 value 열은 채워진 행을 더 눈에 띄게 만들기 위해 9999로 보간(interpolate)되었어요. 이 동작은 use_with_fill_by_sorting_prefix 설정으로 제어돼요 (기본적으로 활성화).

Related content (관련 콘텐츠)

• 블로그: Working with time series data in ClickHouse

더 알아보기 (Learn more)

• LIMIT
• GROUP BY
• Working with time series data in ClickHouse