[SQL] 윈도우 함수 범위 지정을 이용하여 퍼널 분석 로직 짜보기 - feat.window frame

핵심 내용

SQL 윈도우 함수가 가진 여러 특성들을 이해해보고 간단한 퍼널 분석 로직을 짜봅시다.
1. partition by
2. order by 
3. window frame 범위 체계
4. 간단한 퍼널 분석 로직

 

윈도우 함수를 사용하는 이유

윈도우 함수를 이해하기 위해서는 GroupBy와 비교해보는 것이 좋습니다. 우선, Group By에 대해서 생각해봅시다. Group By를 하면 행 수가 줄어듭니다. 그룹의 수만큼요. 하지만, 윈도우 함수는 그렇지 않습니다. 그룹의 수가 그대로 보존되는데요. 이 지점이 윈도우 함수와 Group By의 가장 큰 차이라고 할 수 있습니다. 공통점은 둘다 집계 함수를 사용할 수 있다는 점입니다. 

 

그렇다면, 윈도우 함수는 언제 사용해야 할까? 당연히 원래 행을 보존해야 하는 상황에 사용하는 것이 좋습니다. 예를 들어, 그룹 간의 비율을 구하는 경우가 있을 수 있습니다.

select *, value/total as ratio_g1
from (
    select g1, value
        , sum(value) over() total
    from t1
    ) A

그룹 간의 비율을 구할 때 그룹별 합계(=행)를 보존하면서 전체 값을 모든 행에 남겨 sum_value/total로 비율을 구할 수 있습니다. 

 

윈도우 함수가 가진 무기들

우선, 윈도우 함수가 가진 강력한 무기는 "행을 보존"하는 것입니다. 다음으로, 이 무기를 더욱 돋보이게 하는 장치들을 몇 가지 소개하겠습니다. 

 

Partition By

윈도우 내에서도 그룹(=Partition)을 구분할 수 있습니다. 아래와 같이 over안에 partition by를 사용하여 한번 더 그룹을 지정할 수 있습니다. sum(sum(value)) over(partition by g1) 을 직역하면 g1으로 그룹을 나누어 합산하라는 의미죠. 즉, g1내에서 g2의 상대적인 비율이 됩니다. 예를 들면, 광역시가 g1/ 구가 g2라고 하면 ratio_g2는 각 광역시 내에서 구별 인구 비율을 나타낸다고 할 수 있습니다.

select *, sum_value/total as ratio_g2
from (
    select g1, g2, value
        , sum(value) over(partition by g1) total
    from t1
    ) A

g1	g2	value	total	ratio_g2
부산	A구	1000	3000	1000/3000
부산	B구	2000	3000	2000/3000
울산	C구	2000	5000	2000/5000
울산	D구	3000	5000	3000/5000

이렇게 partition by를 통해 나눠진 구역을 window frame이라고 합니다. 여기서는 부산과 울산이 되겠네요. 이렇게 윈도우 함수를 사용하면 원본 테이블의 행 숫자가 보존됩니다.

 

Order By

그룹 구분 시 순서를 부여할 수도 있습니다. 이렇게 하면 각 광역시 내에서 가장 인구수가 많은 구를 출력하게 됩니다. 왜냐하면, partition by g1으로 그룹을 나누고 value를 desc역순 해서 번호를 순서로 부여했기 때문에 max_row=1로 하면 역순의 첫 번째 즉, 가장 인구수가 많은 구가 선택 됩니다.

select *
from (
    select g1, g2, value
        , row_number() over(partition by g1 order by value desc) max_row 
    from t1
    ) A
where max_row = 1

g1	g2	value	max_row
부산	B구	2000	1
울산	D구	3000	1

 

다음으로, 프레임 내에서도 범위를 설정할 수 있습니다.

 

UNBOUNDED PRECEDING/CURRENT ROW/UNBOUNDED FOLLOWING

위 세가지를 적절히 조합해 윈도우 프레임 내에서도 선택 범위를 정할 수 있습니다. 우선, current row란 기준행 이라고 생각해주시면 될 것 같습니다. 그리고 엄밀히 따지자면, window function은 "행의 길이"를 보존하기 때문에 한줄 한줄 모든 행이 current row가 된다고 생각하시면 될 것 같네요. 만약에 판다스 데이터프레임이라고 하면 for _, row in df.iterrows()로 생각해볼 수 있겠네요.

 

대충 이런 데이터 프레임이 있다고 가정해보겠습니다. current row는 21년 1월 4일입니다.

1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

해석하자면, 이전에(PRECEDING) 제한(UNBOUNDED)이 없다는 뜻입니다. 이미지로 표현하자면 아래와 같이 되겠네요.

2. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

해석하자면, 다음에(FOLLWING) 제한(UNBOUNDED)이 없다는 뜻입니다. 이미지로 표현하자면 아래와 같이 되겠네요.

 

3. ROWS BETWEEN 1 PRECIDING AND 1 FOLLOWING

현재행을 기준으로 범위를 지정할 수도 있습니다. 한개 이전(PRECEDING) 그리고 한개 이후(FOLLOWING)

 

4. ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING

현재행 이전으로도 범위를 구할 수 있습니다. 

5. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

이렇게하면 전체 범위를 선택합니다. 굳이 따지자면 집계함수(col1) over()와 똑같다고 할 수 있습니다.

 

간단한 퍼널 분석 로직

위 개념을 이용해서 간단한 퍼널 분석 로직을 짜봅시다. 

https://www.webdew.com/blog/how-to-conduct-a-funnel-analysis

 

위 그림과 같이, 퍼널이란 점점 좁아지는 모양의 그래프 형태를 뜻하는데요. 이걸 SQL을 이용하여 스텝별로 하나씩 집계한다면 굉장히 번거로울 것 입니다. 왜냐하면, 아래와 같이 이전 정보와 다음 정보를 지속적으로 호출해야 하기 때문입니다. 

# 단계별 유저 통과수
funnel_user_cnt = [10000, 8000, 6000, 5000, 4000]
prob_list = []
for i in range(len(funnel_user_cnt)-1):
	prob = funnel_user_cnt[i+1]/funnel_user_cnt[i]
    prob_list.append(prob)

 

그래서, 오늘은 앞서 배운 window frame의 범위 개념을 이용해 간단한 퍼널 분석 로직을 짜보겠습니다. 아래와 같은 데이터가 있다고 가정해봅시다.

우선, 각 유저별로 마지막 도달 단계가 어디인지 구해야겠죠?

SELECT *
FROM (
    SELECT *
         , row_number() over(partition by 유저명 order by 시간 desc) orders
    FROM T2
    ) A
WHERE orders = 1

 

그런다음, 마지막 도달 단계별로 도달한 유저 숫자를 구해봅니다.

SELECT 도달 단계, COUNT(유저명) 유저수
FROM (
    SELECT *
    FROM (
        SELECT *
             , row_number() over(partition by 유저명 order by 시간 desc) orders
        FROM T2
        ) A
    WHERE orders = 1
    ) B
GROUP BY 도달 단계

도달 단계에 어떤 인과적인 순서가 있다고 가정한다면 2단계에 진입하기 위해서는 반드시 1단계에 진입해야 합니다. 즉, 2단계 까지 진입한 유저들은 1단계는 통과한 유저라고 볼 수 있습니다. 3단계 까지 통과한 유저들은 1단계, 2단계 모두 통과한 유저라고 할 수 있습니다. 나머지 숫자들도 마찬가지입니다. 그렇기 때문에 본인이 속한 행 부터 시작해서 마지막 행까지의 값을 모두 더해주면 해당 퍼널에 진입한 유저 모수가 됩니다.

 

CURRENT ROW BETWEEN UNBOUNDED FOLLOWING을 사용하면 아래 이미지대로 current row에서부터 마지막 행까지 퍼널 단계별 모수를 구할 수 있습니다. 

SELECT *
     , SUM(유저수) OVER(ROWS CURRENT ROW BETWEEN UNBOUNDED FOLLOWING) 단계별모수
FROM (
    SELECT 도달 단계, COUNT(유저명) 유저수
    FROM (
        SELECT *
        FROM (
            SELECT *
                 , row_number() over(partition by 유저명 order by 시간 desc) orders
            FROM T2
            ) A
        WHERE orders = 1
        ) B
    GROUP BY 도달 단계
    ) C

 

최종적으로는 아래와 같은 결과를 얻을 수 있습니다. 

SELECT *, 유저수/단계별모수 퍼널통과율
FROM (
    SELECT *
         , SUM(유저수) OVER(ROWS CURRENT ROW BETWEEN UNBOUNDED FOLLOWING) 단계별모수
    FROM (
        SELECT 도달 단계, COUNT(유저명) 유저수
        FROM (
            SELECT *
            FROM (
                SELECT *
                     , row_number() over(partition by 유저명 order by 시간 desc) orders
                FROM T2
                ) A
            WHERE orders = 1
            ) B
        GROUP BY 도달 단계
        ) C
    ) D

 

만약, 분모를 전체 유저수로 두고 살펴보고 싶다면? (=단계별 모수가 동일함) 아래와 같이 하면 됩니다.

SELECT *, 유저수/단계별모수 퍼널통과율(단계별), 유저수/전체유저수 최종단계유저비율
FROM (
    SELECT *
         , SUM(유저수) OVER(ROWS CURRENT ROW BETWEEN UNBOUNDED FOLLOWING) 단계별모수
         -- SUM(유저수) OVER(ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)과 동일 
         , SUM(유저수) OVER() 전체 유저수 
    FROM (
        SELECT 도달 단계, COUNT(유저명) 유저수
        FROM (
            SELECT *
            FROM (
                SELECT *
                     , row_number() over(partition by 유저명 order by 시간 desc) orders
                FROM T2
                ) A
            WHERE orders = 1
            ) B
        GROUP BY 도달 단계
        ) C
    ) D

이상입니다. 읽어주셔서 감사합니다.