R, Python 분석과 프로그래밍의 친구 (by R Friend)

지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 JOIN 문을 사용하여 여러개의 테이블을 Key 값을 기준으로 왼쪽+오른쪽으로 연결하는 다양한 방법(INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN)을 소개하였습니다. (참고 ==> https://rfriend.tistory.com/657)

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 UNION, UNION ALL 함수를 사용해서 여러개의 테이블을 위+아래로 합치는 방법을 소개하겠습니다.  이전의 JOIN 이 Key값 기준 연결/매칭의 개념이었다면 이번 포스팅의 UNION, UNION ALL은 합집합(union of sets) 의 개념이라고 볼 수 있습니다. 

(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기

(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기

(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함. 

UNION 은 두 테이블의 중복값을 제거한 후에 두 테이블을 위+아래로 합친 결과를 반환하는 반면에, UNION ALL 은 중복값 여부를 확인하지 않고 두 테이블의 모든 값을 위+아래로 합친 결과를 반환합니다. 

UNION 의 경우 두 테이블의 값을 스캔 해서 두 테이블 간의 중복값 여부를 확인하는 중간 단계가 존재하기 때문에 두 테이블의 각 크기가 매우 큰 경우  UNION ALL 대비 상대적으로 연산 시간이 오래 걸립니다. 만약 위+아래로 합치려는 두 테이블의 값 간에 중복값이 없다거나 혹은 중복값 여부를 확인할 필요 없이 모두 합치기만 필요한 요건의 경우에는 UNION ALL 을 사용하는 것이 속도 면에서 유리합니다. 

간단한 예제 테이블들을 만들어서 예를 들어보겠습니다. 

-------------------------------------
-- UNION vs. UNION ALL
-------------------------------------

-- Sample Table 1
DROP TABLE IF EXISTS sample_1;
CREATE TABLE sample_1 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_1 VALUES (1, 11), (2, 12), (3, 13), (4, 14), (5, 15);
SELECT * FROM sample_1 ORDER BY x1;
--x1 x2
--1	11
--2	12
--3	13
--4	14
--5	15


-- Sample Table 2
DROP TABLE IF EXISTS sample_2;
CREATE TABLE sample_2 (x1 int, x2 int) 
DISTRIBUTED randomly;

INSERT INTO sample_2 VALUES (4, 14), (5, 15), (6, 16), (7, 17), (8, 18);
SELECT * FROM sample_2 ORDER BY x1;

--x1 x2
--4	14
--5	15
--6	16
--7	17
--8	18

(1) UNION : 중복값 제거 후 테이블을 위+아래로 합치기

SELECT column1, column2, ... FROM table1 

UNION

SELECT column1, column2, ... FROM table2

와 같은 형식의 구문으로 두 테이블에서 중복값을 제거한 후에 위+아래로 합칠 수 있습니다. 

--------------------
-- (1) UNION
--------------------
SELECT * FROM sample_1 
UNION 
SELECT * FROM sample_2
ORDER BY x1;

--x1 x2
--1	11
--2	12
--3	13
--4	14
--5	15
--6	16
--7	17
--8	18

(2) UNION ALL : 중복값 제거하지 않은 채 테이블을 위+아래로 합치기

SELECT column1, column2, ... FROM table1 

UNION ALL

SELECT column1, column2, ... FROM table2

와 같은 형식의 구문으로 두 테이블에서 중복값을 제거하지 않은 상태에서 (중복값 체크 없음) 위+아래로 합칠 수 있습니다. 

-------------------------
-- (2) UNION ALL 
-------------------------
SELECT * FROM sample_1 
UNION  ALL 
SELECT * FROM sample_2
ORDER BY x1;

--x1 x2
--1	11
--2	12
--3	13
--4	14
--4	14 -- 중복
--5	15
--5	15 -- 중복
--6	16
--7	17
--8	18

(3) 전제조건: 합치려는 테이블 칼럼의 데이터 유형(data type)이 서로 같아야 함. 

만약 UNION, UNION ALL 로 합치려는 두 테이블의 칼럼의 데이터 유형(data type)이 서로 같지 않다면, (아래의 예에서는 sample_1 테이블의 x2 칼럼은 integer, sample_3 테이블의 x2 칼럼은 text로서 서로 다름), "ERROR: UNION types integer and text cannot be matched" 라는 에러 메시지가 발생합니다. 

-- (3) The data types of all corresponding columns must be compatible.

-- Sample Table 3
DROP TABLE IF EXISTS sample_3;
CREATE TABLE sample_3 (x1 int, x2 text) 
DISTRIBUTED randomly;

INSERT INTO sample_3 VALUES (10, 'a'), (20, 'b'), (30, 'c'), (40, 'd'), (50, 'f');
SELECT * FROM sample_3 ORDER BY x1;

--x1		y
--10	a
--20	b
--30	c
--40	d
--50	f

-- ERROR
SELECT * FROM sample_1 
UNION
SELECT * FROM sample_3;

--SQL Error [42804]: ERROR: UNION types integer and text cannot be matched
--  Position: 38

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요. :-)