파라메트릭 집계 함수
파라메트릭 집계 함수 (Parametric aggregate functions)
일부 집계 함수는 인자(argument) 컬럼(압축에 사용)뿐 아니라 파라미터(parameter) 집합, 즉 초기화를 위한 상수들을 받을 수 있어요. 문법은 괄호 한 쌍이 아니라 두 쌍이에요. 첫 번째는 파라미터용이고, 두 번째는 인자용이에요.
출처: 문서
본문
일부 집계 함수는 인자 컬럼뿐 아니라 파라미터 집합, 즉 초기화를 위한 상수들을 받을 수 있어요. 문법은 괄호 한 쌍이 아니라 두 쌍이에요. 첫 번째는 파라미터용이고, 두 번째는 인자용이에요.
histogram
적응형 히스토그램(adaptiive histogram)을 계산해요. 정확한 결과를 보장하지는 않아요.
histogram(number_of_bins)(values)
이 함수는 스트리밍 병렬 결정 트리 알고리즘(A Streaming Parallel Decision Tree Algorithm) 을 사용해요. 새로운 데이터가 함수에 들어올 때마다 히스토그램 빈(bin)의 경계가 조정돼요. 일반적으로 빈의 폭은 같지 않아요.
인자 (Arguments)
values— 입력 값을 결과로 내는 표현식이에요.
파라미터 (Parameters)
number_of_bins— 히스토그램의 빈 수의 상한이에요. 함수가 빈 수를 자동으로 계산해요. 지정된 빈 수에 도달하려 시도하지만, 실패하면 더 적은 빈을 사용해요.
반환 값 (Returned values)
다음 형식의 튜플 배열이에요:
[(lower_1, upper_1, height_1), ... (lower_N, upper_N, height_N)]
lower— 빈의 하한이에요.upper— 빈의 상한이에요.height— 계산된 빈의 높이예요.
예시 (Example)
SELECT histogram(5)(number + 1)
FROM (
SELECT *
FROM system.numbers
LIMIT 20
)
┌─histogram(5)(plus(number, 1))───────────────────────────────────────────┐
│ [(1,4.5,4),(4.5,8.5,4),(8.5,12.75,4.125),(12.75,17,4.625),(17,20,3.25)] │
└─────────────────────────────────────────────────────────────────────────┘
bar 함수로 히스토그램을 시각화할 수 있어요. 예를 들어:
WITH histogram(5)(rand() % 100) AS hist
SELECT
arrayJoin(hist).3 AS height,
bar(height, 0, 6, 5) AS bar
FROM
(
SELECT *
FROM system.numbers
LIMIT 20
)
┌─height─┬─bar───┐
│ 2.125 │ █▋ │
│ 3.25 │ ██▌ │
│ 5.625 │ ████▏ │
│ 5.625 │ ████▏ │
│ 3.375 │ ██▌ │
└────────┴───────┘
이 경우 히스토그램 빈 경계를 알 수 없다는 점을 기억해야 해요.
sequenceMatch
수열이 패턴과 일치하는 이벤트 체인을 포함하는지 확인해요.
구문 (Syntax)
sequenceMatch(pattern)(timestamp, cond1, cond2, ...)
같은 초에 발생한 이벤트들은 정의되지 않은 순서로 수열에 놓일 수 있어 결과에 영향을 줄 수 있어요.
인자 (Arguments)
timestamp— 시간 데이터를 포함하는 것으로 간주되는 컬럼이에요. 일반적인 데이터 타입은Date와DateTime이에요. 지원되는UInt데이터 타입도 사용할 수 있어요.cond1, cond2— 이벤트 체인을 설명하는 조건들이에요. 데이터 타입:UInt8. 최대 32개의 조건 인자를 전달할 수 있어요. 함수는 이 조건들에 설명된 이벤트만 고려해요. 조건에 설명되지 않은 데이터가 수열에 있으면 함수는 그것들을 건너뛰어요.
파라미터 (Parameters)
pattern— 패턴 문자열이에요. 패턴 문법(Pattern syntax)을 참고하세요.
반환 값 (Returned values)
- 패턴이 일치하면
1. - 패턴이 일치하지 않으면
0.
타입: UInt8.
패턴 문법 (Pattern syntax)
(?N)— 위치 N의 조건 인자와 일치해요. 조건은[1, 32]범위에서 번호가 매겨져요. 예를 들어(?1)은cond1파라미터에 전달된 인자와 일치해요..*— 임의 개수의 이벤트와 일치해요. 패턴의 이 요소를 일치시키는 데 조건 인자가 필요하지 않아요.(?t operator value)— 두 이벤트를 구분해야 하는 시간(초)을 설정해요. 예를 들어 패턴(?1)(?t>1800)(?2)는 서로 1800초 이상 떨어져 발생한 이벤트와 일치해요. 이 이벤트들 사이에 임의 개수의 어떤 이벤트든 놓일 수 있어요.>=,>,<,<=,==연산자를 사용할 수 있어요.
예시 (Examples)
t 테이블의 데이터를 생각해 보세요:
┌─time─┬─number─┐
│ 1 │ 1 │
│ 2 │ 3 │
│ 3 │ 2 │
└──────┴────────┘
쿼리를 실행해 보세요:
SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2) FROM t
┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2))─┐
│ 1 │
└───────────────────────────────────────────────────────────────────────┘
함수는 number 2가 number 1 뒤에 오는 이벤트 체인을 찾았어요. 그 사이의 number 3은 이벤트로 설명되지 않았기 때문에 건너뛰었어요. 이 숫자를 예시에서 주어진 이벤트 체인을 찾을 때 고려하고 싶다면, 그 숫자에 대한 조건을 만들어야 해요.
SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 3) FROM t
┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 3))─┐
│ 0 │
└──────────────────────────────────────────────────────────────────────────────────────────┘
이 경우 함수는 패턴과 일치하는 이벤트 체인을 찾을 수 없었어요. number 3에 대한 이벤트가 1과 2 사이에서 발생했기 때문이에요. 같은 경우에 number 4에 대한 조건을 확인했다면 수열은 패턴과 일치했을 거예요.
SELECT sequenceMatch('(?1)(?2)')(time, number = 1, number = 2, number = 4) FROM t
┌─sequenceMatch('(?1)(?2)')(time, equals(number, 1), equals(number, 2), equals(number, 4))─┐
│ 1 │
└──────────────────────────────────────────────────────────────────────────────────────────┘
참고 (See Also)
- sequenceCount
sequenceCount
패턴과 일치한 이벤트 체인의 개수를 세요. 이 함수는 겹치지 않는(non-overlapping) 이벤트 체인을 검색해요. 현재 체인이 일치하면 다음 체인을 검색하기 시작해요.
같은 초에 발생한 이벤트들은 정의되지 않은 순서로 수열에 놓일 수 있어 결과에 영향을 줄 수 있어요.
구문 (Syntax)
sequenceCount(pattern)(timestamp, cond1, cond2, ...)
인자 (Arguments)
timestamp— 시간 데이터를 포함하는 것으로 간주되는 컬럼이에요. 일반적인 데이터 타입은Date와DateTime이에요. 지원되는UInt데이터 타입도 사용할 수 있어요.cond1, cond2— 이벤트 체인을 설명하는 조건들이에요. 데이터 타입:UInt8. 최대 32개의 조건 인자를 전달할 수 있어요. 함수는 이 조건들에 설명된 이벤트만 고려해요. 조건에 설명되지 않은 데이터가 수열에 있으면 함수는 그것들을 건너뛰어요.
파라미터 (Parameters)
pattern— 패턴 문자열이에요. 패턴 문법(Pattern syntax)을 참고하세요.
반환 값 (Returned values)
일치한 겹치지 않는 이벤트 체인의 개수예요.
타입: UInt64.
예시 (Example)
t 테이블의 데이터를 생각해 보세요:
┌─time─┬─number─┐
│ 1 │ 1 │
│ 2 │ 3 │
│ 3 │ 2 │
│ 4 │ 1 │
│ 5 │ 3 │
│ 6 │ 2 │
└──────┴────────┘
number 1 뒤에 number 2가, 그 사이에 임의 개수의 다른 숫자와 함께 몇 번이나 오는지 세어 보세요:
SELECT sequenceCount('(?1).*(?2)')(time, number = 1, number = 2) FROM t
┌─sequenceCount('(?1).*(?2)')(time, equals(number, 1), equals(number, 2))─┐
│ 2 │
└─────────────────────────────────────────────────────────────────────────┘
sequenceMatchEvents
패턴과 일치한 가장 긴 이벤트 체인의 이벤트 타임스탬프를 반환해요.
같은 초에 발생한 이벤트들은 정의되지 않은 순서로 수열에 놓일 수 있어 결과에 영향을 줄 수 있어요.
구문 (Syntax)
sequenceMatchEvents(pattern)(timestamp, cond1, cond2, ...)
인자 (Arguments)
timestamp— 시간 데이터를 포함하는 것으로 간주되는 컬럼이에요. 일반적인 데이터 타입은Date와DateTime이에요. 지원되는UInt데이터 타입도 사용할 수 있어요.cond1, cond2— 이벤트 체인을 설명하는 조건들이에요. 데이터 타입:UInt8. 최대 32개의 조건 인자를 전달할 수 있어요. 함수는 이 조건들에 설명된 이벤트만 고려해요. 조건에 설명되지 않은 데이터가 수열에 있으면 함수는 그것들을 건너뛰어요.
파라미터 (Parameters)
pattern— 패턴 문자열이에요. 패턴 문법(Pattern syntax)을 참고하세요.
반환 값 (Returned values)
이벤트 체인에서 일치한 조건 인자 (?N)에 대한 타임스탬프의 배열이에요. 배열의 위치는 패턴에서 조건 인자의 위치와 일치해요.
타입: Array.
예시 (Example)
t 테이블의 데이터를 생각해 보세요:
┌─time─┬─number─┐
│ 1 │ 1 │
│ 2 │ 3 │
│ 3 │ 2 │
│ 4 │ 1 │
│ 5 │ 3 │
│ 6 │ 2 │
└──────┴────────┘
가장 긴 체인에 대한 이벤트의 타임스탬프를 반환해 보세요:
SELECT sequenceMatchEvents('(?1).*(?2).*(?1)(?3)')(time, number = 1, number = 2, number = 4) FROM t
┌─sequenceMatchEvents('(?1).*(?2).*(?1)(?3)')(time, equals(number, 1), equals(number, 2), equals(number, 4))─┐
│ [1,3,4] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
참고 (See Also)
- sequenceMatch
windowFunnel
슬라이딩 시간 창(sliding time window)에서 이벤트 체인을 검색하고 체인에서 발생한 이벤트의 최대 개수를 계산해요. 이 함수는 다음 알고리즘에 따라 동작해요.
- 함수는 체인의 첫 번째 조건을 트리거하는 데이터를 찾고 이벤트 카운터를 1로 설정해요. 이 시점이 슬라이딩 창이 시작되는 순간이에요.
- 체인의 이벤트가 창 내에서 순차적으로 발생하면 카운터가 증가해요. 이벤트의 순서가 끊기면 카운터는 증가하지 않아요.
- 데이터에 다양한 완료 지점의 여러 이벤트 체인이 있으면 함수는 가장 긴 체인의 크기만 출력해요.
구문 (Syntax)
windowFunnel(window, [mode, [mode, ... ]])(timestamp, cond1, cond2, ..., condN)
인자 (Arguments)
timestamp— 타임스탬프를 포함하는 컬럼의 이름이에요. 지원되는 데이터 타입:Date,DateTime및 기타 부호 없는 정수 타입(timestamp가UInt64를 지원하더라도 그 값은Int64최댓값인2^63 - 1을 초과할 수 없다는 점에 유의하세요).cond— 이벤트 체인을 설명하는 조건 또는 데이터예요.UInt8.
파라미터 (Parameters)
window— 슬라이딩 창의 길이로, 첫 번째 조건과 마지막 조건 사이의 시간 간격이에요.window의 단위는 타임스탬프 자체에 따라 달라져요.cond1의 타임스탬프 <=cond2의 타임스탬프 <= ... <=condN의 타임스탬프 <=cond1의 타임스탬프 +window라는 표현식으로 결정돼요.mode— 선택 인자예요. 하나 이상의 모드를 설정할 수 있어요.'strict_deduplication'— 이벤트 수열에 대해 같은 조건이 성립하면, 그런 반복 이벤트가 추가 처리를 중단시켜요. 참고: 같은 이벤트에 여러 조건이 성립하면 예상치 못하게 동작할 수 있어요.'strict_order'— 다른 이벤트의 개입을 허용하지 않아요. 예를 들어A->B->D->C의 경우D에서A->B->C찾기를 멈추고 최대 이벤트 수준은 2가 돼요.'strict_increase'— 조건을 엄격하게 증가하는 타임스탬프를 가진 이벤트에만 적용해요.'strict_once'— 이벤트가 조건을 여러 번 충족하더라도 체인에서 각 이벤트를 한 번만 집계해요.'allow_reentry'— 엄격한 순서를 위반하는 이벤트를 무시해요. 예를 들어A->A->B->C의 경우 중복된A를 무시하고A->B->C를 찾으며 최대 이벤트 수준은 3이 돼요.
반환 값 (Returned value)
슬라이딩 시간 창 내에서 체인에서 연속적으로 트리거된 조건의 최대 개수예요. 선택(selection)의 모든 체인이 분석돼요.
타입: Integer.
예시 (Example)
사용자가 온라인 상점에서 휴대폰을 선택하고 두 번 구매하기에 정해진 기간이 충분한지 확인해 보세요. 다음 이벤트 체인을 설정해요:
- 사용자가 상점의 계정에 로그인했어요 (
eventID = 1003). - 사용자가 휴대폰을 검색했어요 (
eventID = 1007, product = 'phone'). - 사용자가 주문을 했어요 (
eventID = 1009). - 사용자가 다시 주문을 했어요 (
eventID = 1010).
입력 테이블:
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-28 │ 1 │ 2019-01-29 10:00:00 │ 1003 │ phone │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-31 │ 1 │ 2019-01-31 09:00:00 │ 1007 │ phone │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-01-30 │ 1 │ 2019-01-30 08:00:00 │ 1009 │ phone │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
┌─event_date─┬─user_id─┬───────────timestamp─┬─eventID─┬─product─┐
│ 2019-02-01 │ 1 │ 2019-02-01 08:00:00 │ 1010 │ phone │
└────────────┴─────────┴─────────────────────┴─────────┴─────────┘
2019년 1월~2월 기간 동안 user_id 사용자가 체인에서 얼마나 멀리 진행할 수 있었는지 알아보세요.
쿼리 (Query)
SELECT
level,
count() AS c
FROM
(
SELECT
user_id,
windowFunnel(6048000000000000)(timestamp, eventID = 1003, eventID = 1009, eventID = 1007, eventID = 1010) AS level
FROM trend
WHERE (event_date >= '2019-01-01') AND (event_date <= '2019-02-02')
GROUP BY user_id
)
GROUP BY level
ORDER BY level ASC;
결과 (Response)
┌─level─┬─c─┐
│ 4 │ 1 │
└───────┴───┘
allow_reentry 모드 예시
이 예시는 allow_reentry 모드가 사용자 재진입(reentry) 패턴에서 어떻게 동작하는지 보여줘요.
-- 샘플 데이터: 사용자가 체크아웃 -> 상품 상세 -> 다시 체크아웃 -> 결제를 방문
-- allow_reentry 없음: 레벨 2(상품 상세 페이지)에서 멈춤
-- allow_reentry 포함: 레벨 4(결제 완료)에 도달
SELECT
level,
count() AS users
FROM
(
SELECT
user_id,
windowFunnel(3600, 'strict_order', 'allow_reentry')(
timestamp,
action = 'begin_checkout', -- 1단계: 체크아웃 시작
action = 'view_product_detail', -- 2단계: 상품 상세 보기
action = 'begin_checkout', -- 3단계: 다시 체크아웃 시작 (재진입)
action = 'complete_payment' -- 4단계: 결제 완료
) AS level
FROM user_events
WHERE event_date = today()
GROUP BY user_id
)
GROUP BY level
ORDER BY level ASC;
retention
이 함수는 이벤트에 대해 특정 조건이 충족되었는지를 나타내는 UInt8 타입의 조건 1~32개를 인자로 받아요. 어떤 조건이든 (WHERE에서처럼) 인자로 지정할 수 있어요. 첫 번째를 제외한 조건들은 쌍으로 적용돼요. 두 번째의 결과는 첫 번째와 두 번째가 모두 참이면 참이고, 세 번째의 결과는 첫 번째와 세 번째가 모두 참이면 참이 되는 식이에요.
구문 (Syntax)
retention(cond1, cond2, ..., cond32);
인자 (Arguments)
cond—UInt8결과(1 또는 0)를 반환하는 표현식이에요.
반환 값 (Returned value)
1 또는 0의 배열이에요.
1— 이벤트에 대해 조건이 충족되었어요.0— 이벤트에 대해 조건이 충족되지 않았어요.
타입: UInt8.
예시 (Example)
사이트 트래픽을 결정하는 retention 함수 계산 예를 생각해 볼게요.
- 예시를 보여줄 테이블을 만들어요.
CREATE TABLE retention_test(date Date, uid Int32) ENGINE = Memory;
INSERT INTO retention_test SELECT '2020-01-01', number FROM numbers(5);
INSERT INTO retention_test SELECT '2020-01-02', number FROM numbers(10);
INSERT INTO retention_test SELECT '2020-01-03', number FROM numbers(15);
입력 테이블:
SELECT * FROM retention_test
┌───────date─┬─uid─┐
│ 2020-01-01 │ 0 │
│ 2020-01-01 │ 1 │
│ 2020-01-01 │ 2 │
│ 2020-01-01 │ 3 │
│ 2020-01-01 │ 4 │
└────────────┴─────┘
┌───────date─┬─uid─┐
│ 2020-01-02 │ 0 │
│ 2020-01-02 │ 1 │
│ 2020-01-02 │ 2 │
│ 2020-01-02 │ 3 │
│ 2020-01-02 │ 4 │
│ 2020-01-02 │ 5 │
│ 2020-01-02 │ 6 │
│ 2020-01-02 │ 7 │
│ 2020-01-02 │ 8 │
│ 2020-01-02 │ 9 │
└────────────┴─────┘
┌───────date─┬─uid─┐
│ 2020-01-03 │ 0 │
│ 2020-01-03 │ 1 │
│ 2020-01-03 │ 2 │
│ 2020-01-03 │ 3 │
│ 2020-01-03 │ 4 │
│ 2020-01-03 │ 5 │
│ 2020-01-03 │ 6 │
│ 2020-01-03 │ 7 │
│ 2020-01-03 │ 8 │
│ 2020-01-03 │ 9 │
│ 2020-01-03 │ 10 │
│ 2020-01-03 │ 11 │
│ 2020-01-03 │ 12 │
│ 2020-01-03 │ 13 │
│ 2020-01-03 │ 14 │
└────────────┴─────┘
- retention 함수를 사용해 고유 ID
uid로 사용자를 그룹화해요.
SELECT
uid,
retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
ORDER BY uid ASC
┌─uid─┬─r───────┐
│ 0 │ [1,1,1] │
│ 1 │ [1,1,1] │
│ 2 │ [1,1,1] │
│ 3 │ [1,1,1] │
│ 4 │ [1,1,1] │
│ 5 │ [0,0,0] │
│ 6 │ [0,0,0] │
│ 7 │ [0,0,0] │
│ 8 │ [0,0,0] │
│ 9 │ [0,0,0] │
│ 10 │ [0,0,0] │
│ 11 │ [0,0,0] │
│ 12 │ [0,0,0] │
│ 13 │ [0,0,0] │
│ 14 │ [0,0,0] │
└─────┴─────────┘
- 일별 사이트 방문 총 횟수를 계산해요.
SELECT
sum(r[1]) AS r1,
sum(r[2]) AS r2,
sum(r[3]) AS r3
FROM
(
SELECT
uid,
retention(date = '2020-01-01', date = '2020-01-02', date = '2020-01-03') AS r
FROM retention_test
WHERE date IN ('2020-01-01', '2020-01-02', '2020-01-03')
GROUP BY uid
)
┌─r1─┬─r2─┬─r3─┐
│ 5 │ 5 │ 5 │
└────┴────┴────┘
여기서:
r1— 2020-01-01 동안 사이트를 방문한 고유 방문자 수 (cond1조건)예요.r2— 2020-01-01과 2020-01-02 사이의 특정 기간 동안 사이트를 방문한 고유 방문자 수 (cond1과cond2조건)예요.r3— 2020-01-01과 2020-01-03의 특정 기간 동안 사이트를 방문한 고유 방문자 수 (cond1과cond3조건)예요.
uniqUpTo(N)(x)
지정된 한도 N까지 인자의 서로 다른 값의 개수를 계산해요. 서로 다른 인자 값의 개수가 N보다 크면 이 함수는 N + 1을 반환하고, 그렇지 않으면 정확한 값을 계산해요. 작은 N, 최대 10까지 사용하는 것을 권장해요. N의 최댓값은 100이에요. 집계 함수의 상태에 대해 이 함수는 1 + N * 값 하나의 크기(바이트)에 해당하는 메모리를 사용해요. 문자열을 다룰 때 이 함수는 8바이트의 비암호화(non-cryptographic) 해시를 저장하며, 문자열에 대한 계산은 근사됩니다.
예를 들어 사용자가 웹사이트에서 한 모든 검색 쿼리를 기록하는 테이블이 있다고 해 볼게요. 테이블의 각 행은 단일 검색 쿼리를 나타내며, 사용자 ID, 검색 쿼리, 쿼리 타임스탬프 컬럼이 있어요. uniqUpTo를 사용해 적어도 5명의 고유 사용자를 생성한 키워드만 보여주는 보고서를 만들 수 있어요.
SELECT SearchPhrase
FROM SearchLog
GROUP BY SearchPhrase
HAVING uniqUpTo(4)(UserID) >= 5
uniqUpTo(4)(UserID)는 각 SearchPhrase에 대한 고유 UserID 값의 개수를 계산하지만 최대 4개의 고유 값까지만 세요. SearchPhrase에 4개가 넘는 고유 UserID 값이 있으면 함수는 5(4 + 1)를 반환해요. HAVING 절은 고유 UserID 값의 개수가 5보다 작은 SearchPhrase 값을 걸러내요. 이렇게 하면 적어도 5명의 고유 사용자가 사용한 검색 키워드 목록을 얻을 수 있어요.
sumMapFiltered
이 함수는 sumMap과 동일하게 동작하지만, 필터링할 키 배열을 파라미터로도 받아요. 키의 카디널리티가 높을 때 특히 유용할 수 있어요.
구문 (Syntax)
sumMapFiltered(keys_to_keep)(keys, values)
파라미터 (Parameters)
keys_to_keep: 필터링할 키 배열이에요.keys: 키 배열이에요.values: 값 배열이에요.
반환 값 (Returned Value)
두 배열의 튜플을 반환해요: 정렬된 순서의 키와 해당 키에 대해 합산된 값.
예시 (Example)
CREATE TABLE sum_map
(
`date` Date,
`timeslot` DateTime,
`statusMap` Nested(status UInt16, requests UInt64)
)
ENGINE = Log
INSERT INTO sum_map VALUES
('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10]);
SELECT sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests) FROM sum_map;
┌─sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests)─┐
1. │ ([1,4,8],[10,20,10]) │
└─────────────────────────────────────────────────────────────────┘
sumMapFilteredWithOverflow
이 함수는 sumMap과 동일하게 동작하지만, 필터링할 키 배열을 파라미터로도 받아요. 키의 카디널리티가 높을 때 특히 유용할 수 있어요. sumMapFiltered 함수와 다른 점은 오버플로우(overflow)가 있는 합산을 한다는 것이에요. 즉 합산에 대해 인자 데이터 타입과 같은 데이터 타입을 반환해요.
구문 (Syntax)
sumMapFilteredWithOverflow(keys_to_keep)(keys, values)
파라미터 (Parameters)
keys_to_keep: 필터링할 키 배열이에요.keys: 키 배열이에요.values: 값 배열이에요.
반환 값 (Returned Value)
두 배열의 튜플을 반환해요: 정렬된 순서의 키와 해당 키에 대해 합산된 값.
예시 (Example)
이 예시에서는 sum_map 테이블을 만들고 일부 데이터를 삽입한 다음 sumMapFilteredWithOverflow와 sumMapFiltered, 그리고 toTypeName 함수를 사용해 결과를 비교해요. 생성된 테이블에서 requests 타입이 UInt8이었는데, sumMapFiltered는 오버플로우를 피하기 위해 합산 값의 타입을 UInt64로 승격시킨 반면, sumMapFilteredWithOverflow는 결과를 저장하기에 충분히 크지 않은 UInt8 타입을 유지했어요. 즉 오버플로우가 발생했어요.
쿼리 (Query)
CREATE TABLE sum_map
(
`date` Date,
`timeslot` DateTime,
`statusMap` Nested(status UInt8, requests UInt8)
)
ENGINE = Log
INSERT INTO sum_map VALUES
('2000-01-01', '2000-01-01 00:00:00', [1, 2, 3], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:00:00', [3, 4, 5], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:01:00', [4, 5, 6], [10, 10, 10]),
('2000-01-01', '2000-01-01 00:01:00', [6, 7, 8], [10, 10, 10]);
SELECT sumMapFilteredWithOverflow([1, 4, 8])(statusMap.status, statusMap.requests) as summap_overflow, toTypeName(summap_overflow) FROM sum_map;
SELECT sumMapFiltered([1, 4, 8])(statusMap.status, statusMap.requests) as summap, toTypeName(summap) FROM sum_map;
결과 (Response)
┌─sum──────────────────┬─toTypeName(sum)───────────────────┐
1. │ ([1,4,8],[10,20,10]) │ Tuple(Array(UInt8), Array(UInt8)) │
└──────────────────────┴───────────────────────────────────┘
┌─summap───────────────┬─toTypeName(summap)─────────────────┐
1. │ ([1,4,8],[10,20,10]) │ Tuple(Array(UInt8), Array(UInt64)) │
└──────────────────────┴────────────────────────────────────┘
sequenceNextNode
이벤트 체인과 일치한 다음 이벤트의 값을 반환해요. 실험적 함수로, 활성화하려면 SET allow_experimental_funnel_functions = 1을 설정해야 해요.
구문 (Syntax)
sequenceNextNode(direction, base)(timestamp, event_column, base_condition, event1, event2, event3, ...)
파라미터 (Parameters)
direction— 방향 탐색에 사용돼요.forward— 앞으로 이동해요.backward— 뒤로 이동해요.
base— 기준점(base point)을 설정하는 데 사용돼요.head— 기준점을 첫 번째 이벤트로 설정해요.tail— 기준점을 마지막 이벤트로 설정해요.first_match— 기준점을 첫 번째 일치된event1로 설정해요.last_match— 기준점을 마지막 일치된event1로 설정해요.
인자 (Arguments)
timestamp— 타임스탬프를 포함하는 컬럼의 이름이에요. 지원되는 데이터 타입:Date,DateTime및 기타 부호 없는 정수 타입.event_column— 반환할 다음 이벤트의 값을 포함하는 컬럼의 이름이에요. 지원되는 데이터 타입:String및Nullable(String).base_condition— 기준점이 충족해야 하는 조건이에요.event1, event2, …— 이벤트 체인을 설명하는 조건들이에요.UInt8.
반환 값 (Returned values)
event_column[next_index]— 패턴이 일치하고 다음 값이 존재하는 경우.NULL— 패턴이 일치하지 않거나 다음 값이 존재하지 않는 경우.
타입: Nullable(String).
예시 (Example)
이벤트가 A->B->C->D->E이고 B->C 다음에 오는 이벤트가 D임을 알고 싶을 때 사용할 수 있어요. A->B 다음에 오는 이벤트를 검색하는 쿼리:
CREATE TABLE test_flow (
dt DateTime,
id int,
page String)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(dt)
ORDER BY id;
INSERT INTO test_flow VALUES (1, 1, 'A') (2, 1, 'B') (3, 1, 'C') (4, 1, 'D') (5, 1, 'E');
SELECT id, sequenceNextNode('forward', 'head')(dt, page, page = 'A', page = 'A', page = 'B') as next_flow FROM test_flow GROUP BY id;
┌─id─┬─next_flow─┐
│ 1 │ C │
└────┴───────────┘
forward와 head 동작 (Behavior for forward and head)
ALTER TABLE test_flow DELETE WHERE 1 = 1 settings mutations_sync = 1;
INSERT INTO test_flow VALUES (1, 1, 'Home') (2, 1, 'Gift') (3, 1, 'Exit');
INSERT INTO test_flow VALUES (1, 2, 'Home') (2, 2, 'Home') (3, 2, 'Gift') (4, 2, 'Basket');
INSERT INTO test_flow VALUES (1, 3, 'Gift') (2, 3, 'Home') (3, 3, 'Gift') (4, 3, 'Basket');
SELECT id, sequenceNextNode('forward', 'head')(dt, page, page = 'Home', page = 'Home', page = 'Gift') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home // 기준점, Home과 일치
1970-01-01 09:00:02 1 Gift // Gift와 일치
1970-01-01 09:00:03 1 Exit // 결과
1970-01-01 09:00:01 2 Home // 기준점, Home과 일치
1970-01-01 09:00:02 2 Home // Gift와 불일치
1970-01-01 09:00:03 2 Gift
1970-01-01 09:00:04 2 Basket
1970-01-01 09:00:01 3 Gift // 기준점, Home과 불일치
1970-01-01 09:00:02 3 Home
1970-01-01 09:00:03 3 Gift
1970-01-01 09:00:04 3 Basket
backward와 tail 동작 (Behavior for backward and tail)
SELECT id, sequenceNextNode('backward', 'tail')(dt, page, page = 'Basket', page = 'Basket', page = 'Gift') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home
1970-01-01 09:00:02 1 Gift
1970-01-01 09:00:03 1 Exit // 기준점, Basket과 불일치
1970-01-01 09:00:01 2 Home
1970-01-01 09:00:02 2 Home // 결과
1970-01-01 09:00:03 2 Gift // Gift와 일치
1970-01-01 09:00:04 2 Basket // 기준점, Basket과 일치
1970-01-01 09:00:01 3 Gift
1970-01-01 09:00:02 3 Home // 결과
1970-01-01 09:00:03 3 Gift // 기준점, Gift와 일치
1970-01-01 09:00:04 3 Basket // 기준점, Basket과 일치
forward와 first_match 동작 (Behavior for forward and first_match)
SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, page = 'Gift', page = 'Gift') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home
1970-01-01 09:00:02 1 Gift // 기준점
1970-01-01 09:00:03 1 Exit // 결과
1970-01-01 09:00:01 2 Home
1970-01-01 09:00:02 2 Home
1970-01-01 09:00:03 2 Gift // 기준점
1970-01-01 09:00:04 2 Basket The result
1970-01-01 09:00:01 3 Gift // 기준점
1970-01-01 09:00:02 3 Home // 결과
1970-01-01 09:00:03 3 Gift
1970-01-01 09:00:04 3 Basket
SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, page = 'Gift', page = 'Gift', page = 'Home') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home
1970-01-01 09:00:02 1 Gift // 기준점
1970-01-01 09:00:03 1 Exit // Home과 불일치
1970-01-01 09:00:01 2 Home
1970-01-01 09:00:02 2 Home
1970-01-01 09:00:03 2 Gift // 기준점
1970-01-01 09:00:04 2 Basket // Home과 불일치
1970-01-01 09:00:01 3 Gift // 기준점
1970-01-01 09:00:02 3 Home // Home과 일치
1970-01-01 09:00:03 3 Gift // 결과
1970-01-01 09:00:04 3 Basket
backward와 last_match 동작 (Behavior for backward and last_match)
SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, page = 'Gift', page = 'Gift') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home // 결과
1970-01-01 09:00:02 1 Gift // 기준점
1970-01-01 09:00:03 1 Exit
1970-01-01 09:00:01 2 Home
1970-01-01 09:00:02 2 Home // 결과
1970-01-01 09:00:03 2 Gift // 기준점
1970-01-01 09:00:04 2 Basket
1970-01-01 09:00:01 3 Gift
1970-01-01 09:00:02 3 Home // 결과
1970-01-01 09:00:03 3 Gift // 기준점
1970-01-01 09:00:04 3 Basket
SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, page = 'Gift', page = 'Gift', page = 'Home') FROM test_flow GROUP BY id;
dt id page
1970-01-01 09:00:01 1 Home // Home과 일치, 결과는 null
1970-01-01 09:00:02 1 Gift // 기준점
1970-01-01 09:00:03 1 Exit
1970-01-01 09:00:01 2 Home // 결과
1970-01-01 09:00:02 2 Home // Home과 일치
1970-01-01 09:00:03 2 Gift // 기준점
1970-01-01 09:00:04 2 Basket
1970-01-01 09:00:01 3 Gift // 결과
1970-01-01 09:00:02 3 Home // Home과 일치
1970-01-01 09:00:03 3 Gift // 기준점
1970-01-01 09:00:04 3 Basket
base_condition 동작 (Behavior for base_condition)
CREATE TABLE test_flow_basecond
(
`dt` DateTime,
`id` int,
`page` String,
`ref` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(dt)
ORDER BY id;
INSERT INTO test_flow_basecond VALUES (1, 1, 'A', 'ref4') (2, 1, 'A', 'ref3') (3, 1, 'B', 'ref2') (4, 1, 'B', 'ref1');
SELECT id, sequenceNextNode('forward', 'head')(dt, page, ref = 'ref1', page = 'A') FROM test_flow_basecond GROUP BY id;
dt id page ref
1970-01-01 09:00:01 1 A ref4 // head의 ref 컬럼이 'ref1'과 일치하지 않으므로 head는 기준점이 될 수 없음
1970-01-01 09:00:02 1 A ref3
1970-01-01 09:00:03 1 B ref2
1970-01-01 09:00:04 1 B ref1
SELECT id, sequenceNextNode('backward', 'tail')(dt, page, ref = 'ref4', page = 'B') FROM test_flow_basecond GROUP BY id;
dt id page ref
1970-01-01 09:00:01 1 A ref4
1970-01-01 09:00:02 1 A ref3
1970-01-01 09:00:03 1 B ref2
1970-01-01 09:00:04 1 B ref1 // tail의 ref 컬럼이 'ref4'와 일치하지 않으므로 tail은 기준점이 될 수 없음
SELECT id, sequenceNextNode('forward', 'first_match')(dt, page, ref = 'ref3', page = 'A') FROM test_flow_basecond GROUP BY id;
dt id page ref
1970-01-01 09:00:01 1 A ref4 // ref 컬럼이 'ref3'과 일치하지 않으므로 이 행은 기준점이 될 수 없음
1970-01-01 09:00:02 1 A ref3 // 기준점
1970-01-01 09:00:03 1 B ref2 // 결과
1970-01-01 09:00:04 1 B ref1
SELECT id, sequenceNextNode('backward', 'last_match')(dt, page, ref = 'ref2', page = 'B') FROM test_flow_basecond GROUP BY id;
dt id page ref
1970-01-01 09:00:01 1 A ref4
1970-01-01 09:00:02 1 A ref3 // 결과
1970-01-01 09:00:03 1 B ref2 // 기준점
1970-01-01 09:00:04 1 B ref1 // ref 컬럼이 'ref2'와 일치하지 않으므로 이 행은 기준점이 될 수 없음