[clickhouse] neighbor 활용하여 이전행/다음행 가져오기

이탈 분석을 하다보면, 사용자들이 이탈 직전에 어떤 액션을 했는지, 또는 어떤 퍼널에서 이탈했는지 확인이 필요하다.

다만, 하나의 테이블에 유저의 모든 액션이 순서대로 집계된 경우 특정 사용자의 직전 액션만 모으는 것이 난해하다.

table명: user_action

이렇게 저장되는 경우, 우리가 뽑아내고 싶은 내용은 kazuha는 1달무료이용까지만 이용하고 회원탈퇴를 했다는 점, 그리고 sakura는 정기결제1개월까지 이용하고 회원탈퇴를 했다는 점이다. 아마 아래와 같이 데이터를 뽑아야 할 것이다.

이런 경우, SQL에서는 이전의 행(Row)을 가져올 수 있는 lag, lead라는 함수를 제공한다. 그리고 우리처럼 Clickhouse를 사용하는 경우에는 neighbor을 활용할 수 있다. 우선 neighbor에 대한 기능을 소개해본다.

 

---

neighbor(column, offset)

• neighbor은 지정된 열의 현재 행 앞/뒤에 오는 지정된 오프셋만큼 행에 대한 액세스를 제공하는 윈도우 함수다.
• 얼마나 앞/뒤에 있는 항목을 가져올지 정하기 때문에 offset에는 int64형태의 정수 형태 수치가 입력된다.

위에 있는 데이터로 예를 들어보면 다음과 같다. 우선 몰 아이디와 생성시간 순으로 정렬한다.

SELECT IDX, MALL_ID, STATUS_NOW, CRT_TIME
FROM user_action
ORDER BY MALL_ID ASC, CRT_TIME ASC

이제 바로 옆에 이전 단계(STATUS_PREV)를 생성할 것이다.

• 우리는 1행 이전의 데이터를 불러와야 하기 때문에, 오프셋에 -1을 입력해준다.
• 만약에 2행 이후의 데이터를 불러온다면 오프셋에 2를 입력하면 된다.

SELECT 
	*, neighbor(STATUS_NOW, -1) AS STATUS_PREV -- 이전 상태
FROM (
    SELECT IDX, MALL_ID, STATUS_NOW, CRT_TIME
    FROM user_action
    ORDER BY MALL_ID ASC, CRT_TIME ASC
	) -- 몰아이디, 액션등록시간 순으로 정렬한 테이블

이렇게 작업한 다음 STATUS_NOW가 '회원탈퇴'인 것만 추출하면 회원탈퇴 이전의 유저 액션만 모아서 집계할 수 있다.

• NEIGHBOR 함수에 대한 설명은 아니지만, sakura의 회원가입완료 이전 단계로 '회원탈퇴'가 보인다. 저것은 이전 행에 있던 kazuha의 STATUS_NOW에 있는 '회원탈퇴'를 가져온 것이다. 이런 경우를 방지하고자 할 때는 아래와 같이 작업이 가능하다. 이전 행에 있던 아이디와 현재 행에 나오는 아이디가 다른 경우에는 값을 가져오지 않게 하는 것이다.

SELECT 
	*,
	(CASE WHEN MALL_ID = neighbor(MALL_ID, -1) THEN neighbor(STATUS_NOW, -1) ELSE NULL END) as STATUS_PREV
FROM (
    SELECT IDX, MALL_ID, STATUS_NOW, CRT_TIME
    FROM user_action
    ORDER BY MALL_ID ASC, CRT_TIME ASC
	) -- 몰아이디, 액션등록시간 순으로 정렬한 테이블

 

 

---

오늘 순간적으로 추위먹었는지 미친짓을 했다. 저런 테이블에서 몰 아이디별, 액션 시간 오름차순 기준으로 RANK를 활용해서 순서 칼럼을 생성하고, WITH를 활용해서 Subquery를 생성했다. 이후 Subquery의 칼럼을 그대로 활용한 테이블과 RANK에 +1을 한 칼럼을 하나 생성해서 이전 단계가 나타나도록 JOIN했다. 너무 느려서 계속 고민하다가 이동평균선 때 사용하던 neighbor가 떠올랐다. 생각난 김에 저녁먹고 쿼리문 변경한 이후 이 기록을 남기게 되었다.

 

(스스로에게 하는 말) 나 정도 수준에서 집계하는 이슈면 분명 효율적인 방법이 있다!!