① 샘플 데이터 생성
오라클 튜닝 서적을 보면 예제 데이터를 생성하는데 CONNECT BY 구문을 자주 사용하는 것을 볼 수 있다. 튜닝 효과를 보려면 테이블에 데이터가 아주 많이 담겨 있어야 하는데 다량의 데이터를 만들어 내는 작업이 그리 쉽지만은 않다. 하지만 계층형 쿼리와 오라클에서 제공하는 DBMS_RANDOM이란 패키지(난수를 생성하는 패키지로 패키지에 대해선 PL/SQL 부분에서 상세히 설명할 것이다)를 사용하면 몇 만 건의 데이터도 쉽게 생성할 수 있다. 예제 테이블을 만들어 데이터를 생성해 보자.
입력
CREATE TABLE ex7_1 AS SELECT ROWNUM seq, '2014' || LPAD(CEIL(ROWNUM/1000) , 2, '0' ) month, ROUND(DBMS_RANDOM.VALUE (100, 1000)) amt FROM DUAL CONNECT BY LEVEL <= 12000;결과
table EX7_1이(가) 생성되었습니다.입력
SELECT * FROM ex7_1;결과
SEQ MONTH AMT --- ------- ------------ 1 201401 748 2 201401 972 3 201401 627 4 201401 609 5 201401 211 ...입력
SELECT month, SUM(amt) FROM ex7_1 GROUP BY month ORDER BY month;결과
MONTH SUM(AMT) -------- ----------- 201401 538702 201402 535529 201403 550541 201404 550948 201405 555712 201406 555138 201407 549140 201408 540903 201409 561034 201410 556503 201411 559535 201412 552660 12개의 행이 선택됨.위 쿼리에서 사용된 주요 항목을 설명하면 다음과 같다.
CONNECT BY LEVEL <= 12000
“CONNECT BY LEVEL <= 숫자”를 사용하면 명시한 숫자만큼의 로우를 반환하는데, 내부적으로 보면 등비수열의 합만큼 로우를 생성한다. 예를 들어, DUAL 테이블은 기본 로우 개수는 1개인데 “SELECT … FROM DUAL CONNECT BY LEVEL <= 3”이라고 명시할 때, 이는 첫째 항(a = 1), 공비(r =1), 항의 수(n = 3)인 등비수열에 해당된다.
등비수열 합(S) 공식 • 공비 r = 1이면, S = a\*n • 공비 r ≠ 1이면, S = ![](//lh3.googleusercontent.com/-vFAheZdc5hs/VcbRQEzRTmI/AAAAAAAABFM/jPoFPVc7Yl8/s0/num_221.jpg)따라서 “LEVEL <=3”일 때는 총합이 3이 되어 3개의 로우가 생성 및 반환된다. 그리고 다음과 같이 서브 쿼리로 DUAL 테이블을 조회하는 쿼리를 UNION ALL로 연결하면, 맨 바깥에 있는 쿼리의 기본 로우 수는 1이 아닌 2가 된다.
입력
SELECT ROWNUM FROM ( SELECT 1 AS row_num FROM DUAL UNION ALL SELECT 1 AS row_num FROM DUAL ) CONNECT BY LEVEL <= 4;결과
ROWNUM ---------- 1 2 3 4 5 ... 30개의 행이 선택됨.따라서 이때는 a = 2, r = 2, n = 4가 되어, 등비수열 공식에 대입하면(r 값이 1이 아닐 때) 30이 나오고 쿼리 결과도 30개의 로우가 생성된다.
LPAD(CEIL(ROWNUM/1000) , 2, ‘0’ )
“CONNECT BY LEVEL <= 12000”로 인해 총 12,000개의 로우가 반환되며 ROWNUM도 1부터 12,000이고, 이 수를 1,000으로 나눈 뒤 CEIL 함수로 정수 형태로 변환해 두 자리 숫자 형태로 만들면 ROWNUM이 999까지는 01을, 1000부터 2000까지는 02, 이런 식으로 01~12까지 값을 반환한다.
DBMS_RANDOM.VALUE (100, 1000)
이 구문은 100에서 1000 사이의 난수를 발생시킨다.
따라서 month 컬럼 값으로 201401 ~ 201412, amt컬럼은 100에서 1000 사이 값으로 총 12,000건의 데이터가 ex7_1 테이블에 생성된 것이다.
신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.
SAMPLE은 테이블에서 지정한 비율 만큼의 레코드(행)를 무작위로 추출해 주며, SELECT - FROM 구문과 함께 사용합니다. 통계분석을 위해 무작위 추출을 할 때 유용하게 사용할 수 있습니다.
테이블A의 레코드 중 N%가량의 레코드를 무작위 추출하는 방법은 다음과 같습니다.
select * from 테이블A sample (비율N)
select * from 테이블A sample (비율N) seed ((시드값K)
* 만약 무작위 추출 결과를 고정하고 싶다면, 특정한 seed 값으로 임의의 숫자 K를 입력해 줍니다.
요소 | 의미 | 범위 |
비율N | 샘플링 비율 | 0.000001 ≤ N < 100 |
시드값K | 샘플링 방식 고정 : 동일한 레코드 추출 방식으로, 같은 샘플이 추출됨 | 0 ≤ K ≤ 4294967295 |
예제 ) SAMPLE을 사용한 무작위 추출
hr 연습계정의 employees 테이블(전체 레코드 107개)에서 약 10% 내외의 레코드를 샘플로 추출하기 위해 아래의 쿼리를 실행합니다.
select * from employees sample(10);
같은 쿼리를 실행할 때마다 추출되는 레코드의 종류와 수가 달랐습니다. "무작위 추출"이기 때문입니다.
* 만약, 무작위 추출되는 레코드를 고정하고 싶다면 SEED 값을 지정하여 줍니다.
같은 SEED 값을 갖는 경우에 랜덤 샘플링 결과는 고정됩니다.
select * from employees sample(10) seed (30);
#오라클SQL #SQL #sampling #샘플링 #무작위추출 #Oracle #오라클 #OracleSQLDeveloper #데이터베이스 #database #데이터분석 #빅데이터 #bigdata