[CRM 마케터의 SQL] 랜덤 샘플링 하기 (+해시 샘플링 쿼리로 구현하기)

Data/SQL

[CRM 마케터의 SQL] 랜덤 샘플링 하기 (+해시 샘플링 쿼리로 구현하기)

J._.haza 2025. 1. 14. 01:17

취준 시절 SQL을 공부하면서 가장 궁금했던 것 중 하나는 "CRM 마케터" 직무에서 자주 쓰는 SQL은 무엇인지 궁금했던 것 같다. 작년 입사 후 1년간 신입 CRM 마케터로 일하면서 자주 사용했던 SQL 쿼리들을 [CRM 마케터의 SQL] 시리즈로 연재해볼까 한다.

*이 시리즈는 필자가 실무에서 자주 사용하는 SQL 쿼리들을 ChatGPT 데이터로 임의로 구현한 내용입니다. 기업마다 사내 실험/분석 환경 및 직무 R&R에 따라 본 내용을 마케터가 수작업으로 하지 않아도 될 수 있습니다. (하지만 저는 최첨단 수동화 기법을 사용합니다...)

직무 특성상 CRM 실험, 프로모션 분석 등등의 목적으로 랜덤 샘플링을 하는 일이 자주 있다.

랜덤이 다 같은 랜덤이지 뭐가 다르냐 싶긴 하지만....조금 더 잘 섞기 위한 노력과 스킬이 필요할 때가 있다. 실험 대조군의 특성이 비교가능하도록 잘 나눠져야 한다거나, 실험 대조군 나누고 추출 할 때 마다 유저가 달라지면 안된다든가 등등

암튼 주로 사용하는 두 가지 방법을 정리해봤다.

1. 간편하게 두 그룹으로 나누는 방법 : ORDER BY RANDOM() LIMIT N 사용 (난수 생성 후 정렬)

진짜 유저 그룹을 후딱 나눠야 한다거나

~~귀찮거나~~

할 때 사용하는 방법이다.
사용하기 편리하고 아주 기초적이라서 SQL 기초만 안다면 누구든 써먹을 수 있다.

select user_id
from user_tb
order by random() asc
limit 100 -- 원하는 규모의 모수를 입력

하지만 정말 큰 단점은 추출 할 때 마다 시드값이 고정되어 있지 않아서 추출 할 때마다 유저가 계속 바뀐다는 점이다.

따라서 유저 리스트를 따로 임시테이블이나 csv 파일로 저장해놔야하는 번거로움이 있다.

그리고 동일한 조건 모수를 추출하고 랜덤으로 A B 그룹을 분기하고 싶을 때는 저 방법을 쓰기가 조금 까다로워진다. (쿼리를 두 번 쓰든...먼저 뽑은 유저를 저장해놓고 where문에서 제외를 하든...암튼 귀찮다)

2. 해시함수를 사용하는 방법 (Hash Sampling)

두 번째 방법은 해시 샘플링이다.

요즘은 위 단점을 보완한 해시샘플링을 더 자주 쓴다. salt 값을 잘 활용하면 시드값이 고정 되는게 큰 장점이다.

salt는 암호쪽 분야에서 많이 사용되는 개념인 듯 한데, 간단히 이해 한 바로는 원본 데이터를 복원하기 어렵도록 암호화 전 추가하는 값을 의미한다.

왜 쓸까? 예를 들어 xxhash64(user_id) % 2 = 0 조건으로 유저를 추출한다고 하면 항상 같은 유저가 추출될 것이다. 이 때 저 조건은 놔두고 실험 번호와 추출 일자를 salt 값으로 사용하면 더욱 고도화된 샘플링이 가능하다.

일단 코드로 구현하면 아래와 같다.

-- 단순히 특정 테이블에서 10%만 랜덤 추출하고 싶을 때 
SELECT user_id
FROM user_tb
WHERE xxhash64(user_id) % 10 = 0 -- 해시함수는 여러가지가 있는데 알아서 잘 쓰면 된다

-- salt 값을 사용하는 경우
-- 유저 아이디, 실험 일자, 실험 번호를 concat 한 뒤, 이를 해시함수로 암호화하여 정렬
select *
from (
    SELECT 
        user_id
        , row_number() over(order by xxhash64(concat(user_id,'2025-01-14','Exp 1'))) as rn 
    FROM user_tb
)
WHERE rn <= 100 -- 원하는 모수 규모를 입력

해시 함수는 여러가지가 있다고 한다

출처. Chat GPT. 사실 개발자는 아니라서 크게 관심은 없고 중요하지는 않다

만약 두 그룹 이상으로 나누고 싶다면 어떻게 해야할까? 그 때는 아래와 같이 그룹을 쉽게 분기할 수 있다.

-- 두 그룹으로 분기한다면
select 
    user_id
    , case 
        when rn % 2 = 0 then 'A'
        else 'B'
     end as group_name
from (
    SELECT 
        user_id
        , row_number() over(order by xxhash64(concat(user_id,'2025-01-14','Exp 1'))) as rn 
    FROM user_tb
)
WHERE rn <= 100 -- 원하는 모수 규모를 입력

두 그룹이든 세 그룹이든 방법은 나머지 값을 이용해서 원하는 반큼 그룹을 나눌 수 있다.

그렇다면 난수 생성방식이랑 해시샘플링 방식은 무슨 차이가 있고 어떨 때 해시샘플링을 사용할까?

해시 샘플링은 난수 생성 방식과 다르게 유저 단위로 맵핑 되는거라서 같은 salt와 해시 함수를 사용하면 항상 같은 유저가 같은 그룹에 할당된다. 즉 유저를 나눠놓고 유저 리스트만 저장하면 되지, 유저가 어느 그룹에 속했는지 메모해 둘 필요가 없다는 것이다. 심지어 DB 액세스 조차 필요 없다. 왜냐면 salt 값이 같다면 항상 같은 유저가 추출되기 때문이다.

사실 앱푸시 소재 테스트나 프로모션 테스트 등 사전에 실험/대조군을 나눠 놓을 수 있는 테스트라면 위에서 소개한 난수 생성 방식도 나쁘지 않다.

하지만 실시간 스트림으로 a/b 테스트를 해야하는 상황이라면 그 모든 유저가 어느 그룹에 들어갔는지 기록하고, 그 유저가 재접속했을 때 A/B 어느 유저였는지 DB를 스캔한다거나...하면 해시 샘플링 방식을 쓸 수 밖에 없을 듯 하다. (하지만 난 엔지니어가 아니지)

개인적으로 템플릿화해놓고 추출하기에는 해시 샘플링 방식이 편리하여 요즘은 위의 방식을 자주 사용한다.

끝!

참고
https://brunch.co.kr/@springboot/283
https://zzsza.github.io/data/2019/02/23/ab-test-sampling-with-hash-function/