LIMIT BY 절
LIMIT BY 절
LIMIT n BY expressions 절이 있는 쿼리는 expressions의 각 고유 값마다 처음 n개 행을 선택해요. LIMIT BY의 키에는 임의 개수의 expressions이 포함될 수 있어요. 각 그룹에서 상위 N개 결과를 얻는 데 유용한 절이에요.
출처: 문서
본문
LIMIT n BY expressions 절이 있는 쿼리는 expressions의 각 고유 값마다 처음 n개 행을 선택해요. LIMIT BY의 키는 임의 개수의 expressions을 포함할 수 있어요. ClickHouse는 다음 구문 변형을 지원해요.
LIMIT [offset_value, ]n BY expressionsLIMIT n OFFSET offset_value BY expressions
쿼리 처리 중에 ClickHouse는 정렬 키(sorting key) 순서로 정렬된 데이터를 선택해요. 정렬 키는 ORDER BY 절로 명시적으로 설정하거나 테이블 엔진의 속성으로 암시적으로 설정해요(행 순서는 ORDER BY를 사용할 때만 보장되며, 그렇지 않으면 멀티스레딩 때문에 행 블록이 정렬되지 않아요). 그런 다음 ClickHouse는 LIMIT n BY expressions를 적용하고 expressions의 각 고유 조합에 대해 처음 n개 행을 반환해요. OFFSET이 지정되면 expressions의 고유 조합에 속하는 각 데이터 블록에 대해, ClickHouse는 블록의 시작부터 offset_value만큼의 행을 건너뛰고 결과로 최대 n개 행을 반환해요. offset_value가 데이터 블록의 행 수보다 크면 ClickHouse는 블록에서 0개 행을 반환해요. LIMIT BY는 LIMIT과 관련이 없어요. 둘은 같은 쿼리에 모두 사용할 수 있어요. 여기에는 LIMIT ... AFTER ... UNTIL 범위(range) 형식도 포함되는데, 이 범위는 LIMIT BY가 유지한 행에 적용돼요. LIMIT BY 절에서 열 이름 대신 열 번호를 사용하려면 enable_positional_arguments 설정을 활성화해요.
Examples (예제)
샘플 테이블:
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
쿼리:
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id 쿼리는 같은 결과를 반환해요. 다음 쿼리는 각 domain, device_type 쌍에 대해 상위 5개 referrer를 반환하면서 총 최대 100개 행을 반환해요 (LIMIT n BY + LIMIT).
SELECT
domainWithoutWWW(URL) AS domain,
domainWithoutWWW(REFERRER_URL) AS referrer,
device_type,
count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100;
LIMIT BY는 음수 limit과 offset에서도 동작해요. negative LIMIT clause와 유사하게, LIMIT BY에 음수 값을 사용하면 각 그룹의 끝에서 행을 선택할 수 있어요.
SELECT * FROM limit_by ORDER BY id, val LIMIT -2 BY id;
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
각 id의 마지막 2개 행을 반환해요. id = 1에 대해서는 11과 12행을 얻고, id = 2의 경우 그룹에 행이 2개뿐이므로 두 행 모두 반환돼요.
SELECT * FROM limit_by ORDER BY id, val LIMIT -1 OFFSET -1 BY id;
┌─id─┬─val─┐
│ 1 │ 11 │
│ 2 │ 20 │
└────┴─────┘
각 id의 끝에서 두 번째 행을 반환해요. 뒤따르는 OFFSET -1이 그룹당 마지막 행을 버리고, 앞선 -1이 남은 것 중 마지막 행을 유지해요. 서로 다른 부호의 LIMIT와 OFFSET도 혼합할 수 있어요. 예를 들어 각 그룹의 첫 행을 버리고 남은 것의 마지막 2개를 유지하려면:
SELECT * FROM limit_by ORDER BY id, val LIMIT -2 OFFSET 1 BY id;
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
id = 1의 경우 첫 행(10)은 건너뛰고, 11, 12 중 마지막 2개가 모두 반환돼요. id = 2의 경우 첫 행(20)을 건너뛰어 21만 남아요.
LIMIT BY ALL
LIMIT BY ALL은 SELECT 된 표현식 중 집계 함수가 아닌 모든 표현식을 나열한 것과 동등해요. 예를 들어:
SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL;
는 다음과 같아요.
SELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3;
특수한 경우로, 집계 함수와 다른 필드를 모두 인자로 갖는 함수가 있다면 LIMIT BY 키는 그 함수에서 추출할 수 있는 최대 비집계 필드를 포함해요. 예를 들어:
SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL;
는 다음과 같아요.
SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2);
Examples (예제)
샘플 테이블:
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
쿼리:
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id 쿼리는 같은 결과를 반환해요. LIMIT BY ALL 사용:
SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL;
이것은 다음과 동등해요.
SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val;