AB 테스트는 특정 집단을 두 부분(A 그룹과 B 그룹)으로 나누어 각 그룹에 다른 버전의 제품, 웹페이지, 광고 등을 적용해보고 어느 버전이 더 좋은 성과를 내는지 비교 분석하는 실험적 방법입니다. 이런 테스트는 제품 개선, 사용자 경험 최적화, 마케팅 전략 개발 등 다양한 분야에서 유용하게 활용됩니다.
AB 테스트를 위해서는 특정 집단(사용자 또는 회원 등…)을 두 그룹으로 나눌 수 있어야 합니다. SQL을 활용해 데이터를 나누기 위해서는 다음 두 기술을 사용합니다.
•
RAND(): 랜덤하게 회원을 나누기 위해 사용합니다.
•
ROW_NUMBER 또는 NTILE 분석함수
◦
분석함수를 사용해 회원 별로 순번을 구하고, 발생된 순번에 따라 회원을 나눕니다.
가장 먼저, Member(회원) 테이블의 회원 데이터를 두 집단으로 나누려고 합니다. Member 테이블에 현재 Active한 데이터는 9,799 건이 있습니다.
SELECT COUNT(*) 회원수
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV';
회원수
------
9799
SQL
복사
9,799를 2로 나누면 4899.5가 됩니다. 한 건의 데이터를 그룹별로 0.5씩 나눌 수는 없으므로 A 그룹에 4900 건을, B 그룹에는 4899 건이 속하도록 나누어 봅니다. 특정 기준 없이 임의대로 데이터를 이처럼 나누는 것은 매우 간단합니다. 아래와 같이 ROW_NUMBER 분석함수에 ORDER BY로 RAND 함수를 사용해 회원 별로 임의의 번호를 부여하도록 하고, 임의의 번호에 따라 A 그룹과 B 그룹으로 나누어 버리면 됩니다.
WITH w1 as(
SELECT T1.MemberId
,ROW_NUMBER() OVER(ORDER BY RAND()) 임의번호
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV'
)
SELECT T2.Gr ,COUNT(*) 그룹회원수
FROM (
SELECT T1.*
,CASE WHEN T1.임의번호 <= 4900 THEN 'A' ELSE 'B' END Gr
FROM w1 T1
) T2
GROUP BY T2.Gr;
Gr 그룹회원수
-- ----------
A 4900
B 4899
SQL
복사
위 SQL에서 A그룹, B그룹을 구분하기 위해 CASE 절에 4900 이라는 조건 값을 고정해 사용했습니다. 이러한 방법 외에도 홀수, 짝수를 사용해 두 개의 그룹을 만들 수도 있습니다. 아래 SQL에서는 CASE 절에 MOD(나머지) 함수를 사용해 나머지가 1이면(홀수) A 그룹으로, 나머지가 0이면(짝수) B 그룹으로 처리하고 있습니다.
WITH w1 as(
SELECT T1.MemberId
,ROW_NUMBER() OVER(ORDER BY RAND()) 임의번호
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV'
)
SELECT T2.Gr ,COUNT(*) 그룹회원수
FROM (
SELECT T1.*
,CASE WHEN MOD(T1.임의번호,2) = 1 THEN 'A' ELSE 'B' END Gr
FROM w1 T1
) T2
GROUP BY T2.Gr
ORDER BY T2.Gr;
Gr 그룹회원수
-- ----------
A 4900
B 4899
SQL
복사
더 쉬운 방법은 NTILE 분석함수를 사용하는 것입니다. 아래와 같습니다.
WITH w1 as(
SELECT T1.MemberId
,NTILE(2) OVER(ORDER BY RAND()) 그룹번호
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV'
)
SELECT T2.Gr ,COUNT(*) 그룹회원수
FROM (
SELECT T1.*
,CASE WHEN 그룹번호 = 1 THEN 'A' ELSE 'B' END Gr
FROM w1 T1
) T2
GROUP BY T2.Gr
ORDER BY T2.Gr;
SQL
복사
위와 같이 회원을 구분 했을 때, 문제점은 랜덤(RAND)에 따라서 회원이 나누어지기 때문에 불규칙적으로 회원이 나누어질 수 있다는 점입니다. 예를 들어 A 등급에 PLAT(플래티넘) 등급의 회원이 모두 있거나, B 등급에는 PLAT 등급의 회원이 없을 수도 있는 것이죠. 아래 SQL은 그룹에 따라 회원 등급에 따른 회원수를 조회하는 SQL입니다. (SQL에서 RAND 함수를 사용하기 때문에, 각자 다른 결과가 출력될 것입니다.)
WITH w1 as(
SELECT T1.MemberId
,NTILE(2) OVER(ORDER BY RAND()) 그룹번호
,T1.MemberGd -- > MemberGd 추가
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV'
)
SELECT T2.Gr ,COUNT(*) Total
,SUM(CASE WHEN T2.MemberGd = 'PLAT' THEN 1 END) Platinum
,SUM(CASE WHEN T2.MemberGd = 'GOLD' THEN 1 END) Gold
,SUM(CASE WHEN T2.MemberGd = 'SILV' THEN 1 END) Silver
FROM (
SELECT T1.*
,CASE WHEN 그룹번호 = 1 THEN 'A' ELSE 'B' END Gr
FROM w1 T1
) T2
GROUP BY T2.Gr
ORDER BY T2.Gr;
Gr Total Platinum Gold Silver
-- ----- -------- ---- ------
A 4900 579 1557 2764
B 4899 565 1589 2745
SQL
복사
그룹에 따라 전체 회원수는 균일하게 나누어졌지만, 플래티넘과 실버 등급은 A 그룹에 좀 더 많고, 골드 등급은 B 그룹에 더 많이 분포하는 것을 알 수 있습니다. 등급별로 회원 수를 균일하게 그룹을 나누고 싶다면, NTILE에 PARTITION BY를 사용하면 됩니다. 아래와 같이 SQL을 실행해보면, 회원등급별로도 동일하게 A와 B그룹에 동일하게 분포된 것을 알 수 있습니다.
WITH w1 as(
SELECT T1.MemberId
,NTILE(2) OVER(PARTITION BY T1.MemberGd ORDER BY RAND()) 그룹번호 -- > 추가
,T1.MemberGd -- > MemberGd 추가
FROM startdb.Member t1
WHERE T1.MemberSt = 'ACTV'
)
SELECT T2.Gr ,COUNT(*) Total
,SUM(CASE WHEN T2.MemberGd = 'PLAT' THEN 1 END) Platinum
,SUM(CASE WHEN T2.MemberGd = 'GOLD' THEN 1 END) Gold
,SUM(CASE WHEN T2.MemberGd = 'SILV' THEN 1 END) Silver
FROM (
SELECT T1.*
,CASE WHEN 그룹번호 = 1 THEN 'A' ELSE 'B' END Gr
FROM w1 T1
) T2
GROUP BY T2.Gr
ORDER BY T2.Gr;
Gr Total Platinum Gold Silver
-- ----- -------- ---- ------
A 4900 572 1573 2755
B 4899 572 1573 2754
SQL
복사
여기까지 SQL을 이해했다면, 조금 더 복잡한 로직을 적용해 그룹을 나누는 것을 시도해 볼 수 있습니다.
•
가입년월별로 균일하게 나누기
•
전체 중에 100명의 회원에 대해서만 A, B 그룹 만들기
◦
각 그룹에 50명씩
◦
50명의 샘플도 회원 등급을 균일하게 나누기
•
두 개 그룹이 아닌 그 이상 갯수의 그룹으로 나누어 보기
•
다른 테이블과 조인해 샘플 대상 확정하기
◦
예를 들어 최근 로그인이 많은 회원, 최근 주문이 많은 회원만 대상으로 선별하기
각자 시스템에 맞게 다양한 방법을 시도해보기 바랍니다.