[PostgreSQL] normal_rand로 정규분포 테스트 데이터를 뽑는다

설치·접속: PostgreSQL 설치와 접속

부제: 성능 테스트용 시드 데이터를 만드는데 random()으로 채우면 값이 균등하게 퍼져서 현실과 안 맞을 때

-- tablefunc의 normal_rand(개수, 평균, 표준편차): 정규분포 난수를 setof로
CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE scores AS
SELECT round(v)::int AS score
FROM normal_rand(10000, 60, 15) AS v;

-- 실제로 평균 60, 표준편차 15 근처로 나왔는지 실측
SELECT count(*), round(avg(score),2) AS avg, round(stddev(score),2) AS stddev,
       min(score), max(score)
FROM scores;

 count |  avg  | stddev | min | max 
-------+-------+--------+-----+-----
 10000 | 60.10 |  14.78 |   5 | 122
(1 row)

normal_rand(10000, 60, 15)는 평균 60, 표준편차 15인 가우시안 난수 1만 개를 행 집합으로 돌려준다. 실측 평균 60.10, 표준편차 14.78로 지정한 파라미터에 그대로 수렴한다. 값을 10 단위로 묶어 히스토그램을 그리면 가운데(50~70)가 두껍고 양끝이 얇은 종 모양이 나온다.

 band   | count |  bar
 20-30  |   150 | ###
 30-40  |   656 | ################
 40-50  |  1556 | ######################################
 50-60  |  2456 | #############################################################
 60-70  |  2553 | ###############################################################
 70-80  |  1651 | #########################################
 80-90  |   716 | #################
 90-100 |   196 | ####

코어의 random()은 01 사이 균등분포만 만든다. 같은 개수·같은 범위를 random()으로 채우면 모든 구간이 800개 언저리로 평평하다 — 나이, 시험 점수, 응답시간처럼 "가운데로 몰리는" 실제 데이터의 모양이 전혀 안 나온다. 성능 테스트에서 인덱스 선택도나 통계 추정을 현실적으로 보려면 값 분포부터 현실을 닮아야 한다. 함정은 정규분포라 이론상 꼬리가 무한하다는 점. 위 실측에서도 max가 122까지 튀었으니, 나이(1880)처럼 경계가 있는 컬럼은 greatest/least로 클램프해서 넣는다.

이렇게도 쓴다

균등분포 random()과 나란히 비교해 차이를 눈으로 본다. (조합: 코어 random)

-- 아래는 모든 구간이 800 언저리로 평평하게 나온다
CREATE TABLE unif AS
SELECT (random()*120)::int AS score FROM generate_series(1,10000);

 

generate_series로 나이 컬럼을 현실적으로 채우되 경계로 클램프한다. (조합: generate_series)

CREATE TABLE seed_users AS
SELECT g AS id, greatest(18, least(80, round(a)::int)) AS age
FROM normal_rand(50000, 42, 12) WITH ORDINALITY AS t(a, g);
-- 결과: count 50000 | min 18 | avg 42.1 | max 80

 

WITH ORDINALITY로 두 정규분포 컬럼(키·몸무게)을 행 번호로 나란히 붙인다.

SELECT round(h)::int AS height_cm, round(w::numeric,1) AS weight_kg
FROM normal_rand(3, 170, 7) WITH ORDINALITY a(h, i)
JOIN normal_rand(3, 68, 9) WITH ORDINALITY b(w, j) ON a.i = b.j;
-- 예: 166 | 67.4  /  165 | 74.0  /  170 | 65.5

 

width_bucket으로 분포를 버킷 집계해 종 모양을 확인한다. (조합: width_bucket)

SELECT width_bucket(score, 0, 120, 12) AS bucket, count(*)
FROM scores GROUP BY bucket ORDER BY bucket;