그룹별로 랜덤 sampling 하기

예전에 단순히 랜덤 sampling 을 하는 SQL 을 공유한 적이 있다. 

https://walkingfox.tistory.com/149

Mysql 샘플링 (Sampling) : 랜덤 추출

 

이번에는 데이터가 그룹별로 있을 경우 그룹별로 랜덤하게 sampling 하는 방법에 대해서 적어보려 한다.

 

아래와 같은 tbl_user 테이블이 있다고 가정한다.

연령대와 유져id 로 구성되어 있는

age_group	user_id
10대	123
10대	234
20대	345
...	...
60대	876

 

아래의 코드로 연령대별로 분포를 살펴보자. 

select age_group 
	, count(user_id) as count_user
from tbl_user
group by 1

 

연령별 그룹마다 사용자 수가 다른 분포이다. 

age_group	count_user
10대	2,100
20대	3,500
30대	5,000
40대	4,000
50대	3,400
60대	900

 

방법1. 각 연령대별로 100 명씩 랜덤하게 추출하기 (그룹별로 같은 수를 랜덤하게 추출)

• rand() 함수를 그룹별로 순서를 지정하고, 그 값을 이용하여 그룹별로 같은 수만큼 sampling 한다.

with 
user_list as (
    select age_group
        , user_id
        , row_number() over(partition by age_group order by rand()) as rand_ord
    from tbl_user
    )
select age_group, uesr_id
from user_list
where rand_ord <= 100

 

 

방법2. 각 연령대별로 해당 비율과 같도록 10% 추출하기 (그룹별로 일정 비율을 랜덤하게 추출)

• 그룹별로 unique 하게 일정한 0과 1 사이의 랜덤숫자가 발생하므로 원래 그룹의 분포의 일정 비율을 sampling 할 수 있다.

with 
user_list as (
    select age_group
        , user_id
        , rand() as rand_num
    from tbl_user
    )
select age_group, uesr_id
from user_list
where rand_num <= 0.1