WINDOW_FUNNEL (Lakehouse v1)

ClickHouse의 windowFunnel과 비슷하게(두 함수는 같은 작성자가 만들었습니다), 슬라이딩 시간 윈도우에서 이벤트 체인을 검색하고 체인에서 달성한 최대 이벤트 수를 계산합니다.

이 함수는 다음 알고리즘에 따라 동작합니다.

함수는 체인의 첫 번째 조건을 충족하는 데이터를 검색하고 이벤트 카운터를 1로 설정합니다. 이때 슬라이딩 윈도우가 시작됩니다.
체인의 이벤트가 윈도우 안에서 순서대로 발생하면 카운터가 증가합니다. 이벤트 순서가 끊어지면 카운터는 증가하지 않습니다.
데이터에 완료 지점이 서로 다른 이벤트 체인이 여러 개 있으면, 함수는 가장 긴 체인의 크기만 출력합니다.

SQL 구문

WINDOW_FUNNEL( <window> )( <timestamp>, <cond1>, <cond2>, ..., <condN> )

인수

<timestamp> — 타임스탬프가 들어 있는 열의 이름입니다. 지원되는 데이터 형식은 정수 형식과 datetime 형식입니다.
<cond> — 이벤트 체인을 설명하는 조건 또는 데이터입니다. Boolean 데이터 형식이어야 합니다.

매개변수

<window> — 슬라이딩 윈도우의 길이로, 첫 번째 조건과 마지막 조건 사이의 시간 간격입니다. window의 단위는 timestamp 자체에 따라 달라집니다. timestamp of cond1 <= timestamp of cond2 <= ... <= timestamp of condN <= timestamp of cond1 + window 식을 사용하여 결정합니다.

반환 값

슬라이딩 시간 윈도우 내에서 체인의 조건이 연속으로 충족된 최대 횟수입니다. 선택 항목의 모든 체인을 분석합니다.

형식: UInt8.

예제

사용자가 온라인 스토어에서 휴대폰을 선택하고 두 번 구매하는 데 정해진 시간이 충분한지 확인합니다.

다음 이벤트 체인을 설정합니다.

사용자가 스토어에서 계정에 로그인했습니다(event_name = 'login').
사용자가 페이지에 접속했습니다(event_name = 'visit').
사용자가 장바구니에 담았습니다(event_name = 'cart').
사용자가 구매를 완료했습니다(event_name = 'purchase').

CREATE TABLE events(user_id BIGINT, event_name VARCHAR, event_timestamp TIMESTAMP);

INSERT INTO events VALUES(100123, 'login', '2022-05-14 10:01:00');
INSERT INTO events VALUES(100123, 'visit', '2022-05-14 10:02:00');
INSERT INTO events VALUES(100123, 'cart', '2022-05-14 10:04:00');
INSERT INTO events VALUES(100123, 'purchase', '2022-05-14 10:10:00');

INSERT INTO events VALUES(100125, 'login', '2022-05-15 11:00:00');
INSERT INTO events VALUES(100125, 'visit', '2022-05-15 11:01:00');
INSERT INTO events VALUES(100125, 'cart', '2022-05-15 11:02:00');

INSERT INTO events VALUES(100126, 'login', '2022-05-15 12:00:00');
INSERT INTO events VALUES(100126, 'visit', '2022-05-15 12:01:00');

입력 테이블:

┌─────────┬────────────┬────────────────────────────┐
│ user_id │ event_name │ event_timestamp            │
├─────────┼────────────┼────────────────────────────┤
│  100123 │ login      │ 2022-05-14 10:01:00.000000 │
│  100123 │ visit      │ 2022-05-14 10:02:00.000000 │
│  100123 │ cart       │ 2022-05-14 10:04:00.000000 │
│  100123 │ purchase   │ 2022-05-14 10:10:00.000000 │
│  100125 │ login      │ 2022-05-15 11:00:00.000000 │
│  100125 │ visit      │ 2022-05-15 11:01:00.000000 │
│  100125 │ cart       │ 2022-05-15 11:02:00.000000 │
│  100126 │ login      │ 2022-05-15 12:00:00.000000 │
│  100126 │ visit      │ 2022-05-15 12:01:00.000000 │
└─────────┴────────────┴────────────────────────────┘

1시간 윈도우가 슬라이딩될 때 사용자 user_id가 체인을 어디까지 진행할 수 있었는지 확인합니다.

쿼리:

SELECT
    level,
    count() AS count
FROM
(
    SELECT
        user_id,
        window_funnel(3600000000)(event_timestamp, event_name = 'login', event_name = 'visit', event_name = 'cart', event_name = 'purchase') AS level
    FROM events
    GROUP BY user_id
)
GROUP BY level ORDER BY level ASC;

결과:

┌───────┬───────┐
│ level │ count │
├───────┼───────┤
│     2 │     1 │
│     3 │     1 │
│     4 │     1 │
└───────┴───────┘

사용자 100126의 레벨은 2입니다(login -> visit).
사용자 100125의 레벨은 3입니다(login -> visit -> cart).
사용자 100123의 레벨은 4입니다(login -> visit -> cart -> purchase).