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

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

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

이번 포스팅에서는 PostgreSQL, Greenplum Database에서 여러개의 테이블을 Key 값을 기준으로 JOIN 구문을 사용하여 연결하는 다양한 방법을 소개하겠습니다. 그리고 두 테이블 내 관측치 간의 모든 가능한 조합을 반환해주는 CROSS JOIN 에 대해서도 마지막에 소개하겠습니다. (DB 종류에 상관없이 join SQL query는 거의 비슷합니다.)

(1) INNER JOIN

(2) LEFT JOIN

(3) RIGHT JOIN

(4) FULL JOIN

(5) 3개 이상 복수개의 테이블을 JOIN 으로 연결하기

(6) CROSS JOIN

먼저 예제로 사용한 간단한 2개의 테이블을 만들어보겠습니다. 두 테이블을 연결할 수 있는 공통의 Key값으로서 'id'라는 이름의 칼럼을 두 테이블이 모두 가지고 있습니다. 

'tbl1' 과 'tbl2'는 Key 'id'를 기준으로 id = [2, 3, 4] 가 서로 동일하게 존재하며, 'tbl1'의 id = [1]은 'tbl1'에만 존재하고, 'tbl2'의 id = [5] 는 'tbl2'에만 존재합니다. 각 JOIN 방법 별로 결과가 어떻게 달라지는지 유심히 살펴보시기 바랍니다. 

-- Creating two sample tables

-- sample table 1
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
	id int
	, x text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

SELECT * FROM tbl1 ORDER BY id;
--id  x
--1	a
--2	b
--3	c
--4   d


-- sample table 2
DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
	id int
	, y text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'e'), (3, 'f'), (4, 'g'), (5, 'h');

SELECT * FROM tbl2 ORDER BY id;
--id  y
--2	e
--3	f
--4	g
--5	h

(1) INNER JOIN 

INNER JOIN 은 두 테이블의 Key 값을 기준으로 교집합에 해당하는 값들만 반환합니다. 두 테이블에서 Key 값이 겹치지 않는 값들은 제거되었습니다. 

--------------
-- INNER JOIN
--------------

SELECT a.id, a.x, b.y
FROM tbl1 AS a 
INNER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g

(2) LEFT OUTER JOIN

LEFT OUTER JOIN 은 왼쪽 테이블을 기준으로 Key값이 서로 같은 오른쪽 테이블의 값들을 왼쪽 테이블에 연결해줍니다. 아래의 예에서는 왼쪽의 'tbl1'의 값들은 100% 모두 있고, LEFT OUTER JOIN 으로 연결해준 오른쪽 'tbl2' 테이블의 경우 id = [5] 의 값이 제거된 채 id = [2, 3, 4] 에 해당하는 값들만 'tbl1'과 연결이 되었습니다. 그리고 왼쪽 'tbl1'에는 있지만 오른쪽 'tbl2'에는 없는 id = [1] 에 해당하는 값의 경우 y = [NULL] 값을 반환하였습니다. 

-------------------
-- LEFT OUTER JOIN
-------------------

SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g

(3) RIGHT OUTER JOIN

RIGHT OUTER JOIN 은 LEFT OUTER JOIN 과 정반대라고 생각하면 이해하기 쉽습니다. 이번에는 오른쪽 테이블을 기준으로 Key 값이 같은 왼쪽 테이블의 값을 오른쪽 테이블에 연결해줍니다. 

아래 RIGHT OUTER JOIN 예에서는 오른쪽 테이블은 'tbl2'는 100% 모두 있고, 왼쪽 테이블 'tbl1'의 경우 'tbl2'와 Key 값이 동일한 id = [2, 3, 4] 에 해당하는 값들만 'tbl2'에 연결이 되었습니다. 'tbl2'에만 존재하고 'tbl1'에는 없는 id = [5] 의 경우 'tbl1'의 'x' 칼럼 값은 [NULL] 값이 됩니다. 

--------------------
-- RIGHT OUTER JOIN
--------------------

SELECT a.id, x, y
FROM tbl1 AS a 
RIGHT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g
--5 [NULL]	h

(4) FULL JOIN

FULL JOIN은 양쪽 테이블 모두를 기준으로 Key 값이 같은 값들을 연결시켜 줍니다. 이때 한쪽 테이블에만 Key 값이 존재할 경우 다른쪽 테이블의 칼럼 값에는 [NULL] 값을 반환합니다. 

제일 위에 있는 도식화 그림을 참고하시면 위의 INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN에 대해서 좀더 이해하기 쉬울 거예요. 

---------------
-- FULL JOIN
---------------

SELECT a.id, x, y
FROM tbl1 AS a 
FULL JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g
--5 [NULL] h

(5) 3개 이상의 복수개의 테이블을 JOIN 으로 연결하기

위의 (1)~(4) 는 2개의 테이블을 Key 값을 기준으로 JOIN 문으로 연결한 것이었습니다. 만약 3개 이상의 복수개의 테이블을 JOIN 으로 연결하고자 한다면 아래의 예처럼 JOIN 문과 연결 Key 값을 ON 으로 이어서 써주면 됩니다.

아래의 예는 'tbl1' 테이블을 기준으로 'tbl2', 'tbl3'를 'id' Key를 기준으로 LEFT OUTER JOIN 한 것입니다. 

--------------------------------------------------------
-- LEFT OUTER JOIN with Multiple Tables
--------------------------------------------------------

-- creating the 3rd table
DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3 (
	id int
	, z text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'i'), (4, 'j'), (6, 'k'), (8, 'l');
SELECT * FROM tbl3 ORDER BY id;
--id  z
--2	i
--4	j
--6	k
--7	l


-- LEFT OUTER JOIN with 3 tables
SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
	ON a.id = b.id
LEFT OUTER JOIN tbl3 AS c	
	ON a.id = c.id
ORDER BY a.id;
--id x y z
--1	a	[NULL] [NULL]
--2	b	e i
--3	c	f [NULL]
--4	d	g j

(6) CROSS JOIN

위의 (1)~(5)까지의 JOIN은 두 테이블에 동일하게 존재하는 Key값을 기준으로 두 테이블을 연결하여 주었다면, 이제 CROSS JOIN 은 두 테이블의 모든 값들 간의 조합을 반환하며, 이때 Key 값은 필요없습니다. 가령 왼쪽 테이블에 m 개의 행이 있고, 오른쪽 테이블에 n 개의 행이 있다면 두 테이블의 CROSS JOIN은 m * n 개의 조합(combination)을 반환합니다. 

실수로 행의 개수가 엄청나게 많은 두 테이블을 CROSS JOIN 하게 될 경우 시간도 오래 걸리고 자칫 memory full 나서 DB가 다운되는 경우도 있습니다.  따라서 CROSS JOIN 을 할 때는 지금 하려는 작업이 CROSS JOIN 요건이 맞는 것인지 꼭 한번 더 확인이 필요하며, 소요 시간이나 메모리가 여력이 되는지에 대해서도 먼저 가늠해볼 필요가 있습니다. 

----------------
-- CROSS JOIN
----------------

SELECT a.id AS id_a, a.x, b.id AS id_b, b.y
FROM tbl1 AS a 
CROSS JOIN tbl2 AS b
ORDER BY a.id, b.id;
--id_a x id_b y
--1	a	2	e
--1	a	3	f
--1	a	4	g
--1	a	5	h
--2	b	2	e
--2	b	3	f
--2	b	4	g
--2	b	5	h
--3	c	2	e
--3	c	3	f
--3	c	4	g
--3	c	5	h
--4	d	2	e
--4	d	3	f
--4	d	4	g
--4	d	5	h

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

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

이번 포스팅에서는 PostgreSQL, Greenplum Database의 문자열 데이터형 (Character Types)의 종류와, 생성, 운영자, 함수 등에 대해서 알아보겠습니다. 

먼저, PostgreSQL 의 문자열형 종류는 아래처럼 

 - (1) 고정 길이 n 바이트 이내의 문자열 character(n), char(n)

 - (2) 가변 길이 n 바이트 이내의 문자열 varchar(n)

 - (3) 제한 없는 가변 길이 문자열 text, varchar

의 3가지 종류가 있습니다. 

PostgreSQL은 이들 3가지 문자열 종류별로 성능 차이는 없습니다.

위에서 소개한 3개의 문자열형 종류인 CHAR(2), VARCHAR(10), TEXT 를 포함하고 있는 예제 테이블을 만들어보겠습니다. 괄호안의 숫자는 Bytes 길이를 의미합니다. 

아래의 두가지 경우는 CHAR(2), VARCHAR(10) 처럼 바이트 길이 제약이 있는데 이를 만족하지 않는 경우 SQL ERROR가 발생한 예입니다. 

(1-1) col_1 의 바이트 길이가 CHAR(2) 와 정확하게 맞지 않아서 에러가 나는 경우

(1-2) col_2 의 바이트 길이가 VARCHAR(10) 보다 길어서 에러가 나는 경우

(1-3) col_1, col_2, col_3 가 모두 문자열형 조건을 만족하여 정상 입력된 경우

(2-1) 문자열 합치기 (String Concatenation)

• 문자열 합치기: '||' 연산자
• 문자열 합치기: CONCAT() 함수
• 구분자를 포함하여 문자열 합치기: CONCAT_WS() 함수

(2-1-1) 문자열 합치기 연산자: '||'

지난번 포스팅에서는 PostgreSQL의 4가지 유형의 연산자로서 산술 연산자, 비교 연산자, 논리 연산자, 비트 연산자에 대해서 소개하였는데요, 문자열에 특화된 연산자 중에서 두 문자열을 합칠 때 사용하는 '||' 연산자를 소개하겠습니다. 

string || string

string || non-string or non-string || string

(2-1-2) 두 문자열 합치기 함수: CONCAT()

위의 (2-1-1)과 동일한 결과를 concat() 함수를 사용하여 얻을 수 있습니다. 

문자열을 합치는데 사용하는 '||' 연산자와 CONCAT() 함수 사이에는 NULL 값을 처리하는데 있어 차이가 존재합니다. '||' 연산자는 NULL 값이 있으면 NULL 값을 반환하는 반면에, CONCAT() 함수는 NULL 값을 무시하고 나머지 값을 반환합니다. 

(2-1-3) 구분자를 포함하여 문자열을 합치기 (CONCAT With Separator): CONCAT_WS()

합치려는 문자열의 개수가 여러개이고 구분자를 포함시키고 싶을 경우, CATCAT_WS() 함수는 구분자를 한번만 써줘도 되므로, 구분자를 매번 써줘어야 하는 CATCAT() 함수보다 편리합니다. 

(2-2) 문자열 길이 (String Length) 

• 문자열 내 비트의 길이: BIT_LENGTH(string)
• 문자열 내 바이트의 길이: OCTET_LENGTH(string)
• 문자열 내 문자 길이: LENGTH(string)

문자열 내 바이트의 길이를 재는 OCTET_LENGTH()와 문자 길이를 재는 LENGTH() 함수가 알파벳과 숫자에서는 차이가 없는데요, 한글처럼 문자열 내 바이트와 문자 길이가 다른 경우도 있습니다. 따라서 한글의 바이트를 재고 싶다면 LENGTH()가 아니라 OCTET_LENGTH() 함수를 사용해야 겠습니다. 

(2-3) 문자열 대/소문자 변환 (String Case Conversion)

• 문자열 내 문자를 소문자로 바꾸기: LOWER(string)
• 문자열 내 문자를 대문자로 바꾸기: UPPER(string)
• 문자열 내 첫번째 문자를 대문자로, 나머지 문자는 소문자로 바꾸기: INITCAP(string)

(2-4) 문자열 겹쳐쓰기, 바꾸기, 뒤집기, 반복하기

• 문자열 겹쳐쓰기: OVERLAY(string PLACING string FROM int [FOR int])
• 문자열 바꾸기: REPLACE(string, FROM, TO)

• 문자열 순서 뒤집기: REVERSE(string)
• 문자열 n번 반복하기: REPEAT(string, int)

(2-5) 문자열의 위치 인덱스: POSITION(substring IN string)

POSITION() 함수이 첫번째 인자로 받는 substring 은 대/소문자를 구분합니다. 

(2-6) 문자열의 부분 문자열을 잘라오기: SUBSTR(string [from int] [for int])

(2-7) 문자열을 구분자(Delimeter)를 기준으로 분할 후 일부분 가져오기

: SPLIT_PART(string, delimiter, part_position)

(2-8) 문자열 내 특정 하위 문자열을 잘라내기: TRIM()

• 문자열 내 특정 문자로 시작하는 부분 잘라내기: TRIM(leadnig characters from string)
• 문자열 내 특정 문자로 끝나는 부분 잘라내기: TRIM(trailing characters from string)
• 문자열 내 특정 문자로 시작하거나 끝나는 부분 잘라내기: TRIM(both characters from string)
• 문자열의 왼쪽 시작부분에 특정 문자가 있으면 잘라내기: LTRIM(string, characters)
• 문자열의 오른쪽 끝부분에 특정 문자가 있으면 잘라내기: RTRIM(string, characters)

TRIM()과 LTRIM(), RTRIM() 간에 인자의 위치가 조금 다른 것에 주의하세요. 

(2-9) 문자열의 처음 n개 문자 가져오기

• 문자열의 왼쪽으로(시작) 부터 처음 n개 문자 가져오기: LEFT(string, int n)
• 문자열의 오른쪽으로(끝) 부터 처음 n개 문자 가져오기: RIGHT(string, int n)

(2-10) 문자열의 길이가 n개보다 모자라는 개수만큼 채우기(Padding)

• 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 왼쪽부터 채우기
  : LPAD(string, int n, character)
• 문자열의 길이가 n 개보다 모자라는 개수만큼 character로 오른쪽부터 채우기
  : RPAD(string, int n, character)

문자열형의 패턴 매칭(pattern matching)까지 다루기에는 포스팅이 너무 길어지므로 다음번에 별도로 소개하겠습니다. 

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)

이번 포스팅에서는 Greenplum DB에서 PL/R을 실행했을 때 "Error on receive from seg0 slice1: server closed the connection unexpectedly" 에러가 발생했을 경우 확인해보고 수정하는 예를 소개하겠습니다. 

먼저, PL/R의 input으로 사용할 array 가 들어있는 간단한 예제 테이블을 만들어보겠습니다. 

예제로 사용한 PL/R 함수는 국소 회귀모형(Local Regression)을 R의 LOESS() 함수를 적합해서 평활(smoothing)한 결과값을 array형태로 반환하는 것입니다. 그런데 returns numeric[] 으로 하는 바람에 서버가 비정상적으로 끊기고 리커버리 되는 상황이 발생하였습니다. 

이번에는 위의 (1)번에서 정의했던 PL/R 함수를 삭제(drop)하고 y 에 해당하는 인자의 데이터 유형을 기존numeric[] 에서 float8[] 으로 새로 변경하고, return 받는 데이터 유형도 기존의  returns numeric[] 에서 returns float8[] 로 변경하여 정의한 후에 PL/R 함수를 실행해보겠습니다. PL/R이 Greenplum에서 정상적으로 잘 수행되네요. 

PL/R 수행 후의 결과를 return 받을 데이터 유형 (data type)을 잘 확인하고 맞게 설정을 해주어야 합니다. 

본 포스팅의 주제는 아닙니다만, 위의 PL/R에서 반환받는 값이 array 형태이다보니 조회해서 보기에, 또 이를 가지고 연산을 하거나 다른 테이블과 조인을 하기에 불편한감이 있습니다. 이에 unnest() 함수를 사용해서 array를 풀어서 long format으로 조회하는 예제도 추가로 아래에 예를 들어보았습니다. 

위의 (2)번에서 unnest() 를 소개한 김에 옆으로 조금만 더 세어보자면요, 

위의 (2)번에서는 returns float8[]        --> PL/R 수행 시 array 를 반환했던 반면에, 

이번 (3)번에서는 returns setof float8   --> PL/R 수행 시 rows 를 반환하도록 하는 경우입니다. 

'returns setof' 처럼 'setof'가 추가된게 다르구요, 'float8[]' array가 아니라 'float8' 형태인게 다릅니다. 

이렇게 개별 float8 값을 row로 반환하므로 위의 (2)번처럼 unnest() 함수를 써서 array를 long format으로 풀어줄 필요가 없이도 (2)번의 unnest() 를 쓴 것과 동일한 결과를 얻었습니다.  

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요.