SQL에서 데이터 분리해서 활용하기(split)

게시일 : 2019년 04월 08일     수정일 : 2019년 09월 07일    
# MySQL # SUBSTRING_INDEX # REGEXP_SUBSTR # PostgreSQL # split_part # regexp_split_to_array # Faker

SQL에서 데이터를 분리해서 활용하는 방법을 알아본다.

사용할 데이터는 python의 Faker라는 라이브러리를 통해 생성했다.

from faker import Faker
fake = Faker('ko_KR')
data = [ [fake.ascii_email()] for i in range(100)]
[['yeongho58@dreamwiz.com'],
 ['cha@hanmail.net'],
 ['ohyeonjeong@naver.com'],
 ['anjeongung@gmail.com'],
 ['jeonghyi56@ju.com']]


사용 데이터
address id
0 gweoncunja@ju.kr 1
1 bjeon@jeongim.net 2
2 rgang@nate.com 3
3 gimjia@obagjo.com 4
4 ngim@jusighoesa.kr 5
5 dohyeonyun@yuca.com 6
6 ui@daum.net 7
7 ni@yuhanhoesa.com 8
8 pgim@hanmail.net 9
9 juweonbag@ju.kr 10

PostgreSQL

PostgreSQL에는 split_part라는 함수가 존재한다. 특정 문자를 기준으로 데이터를 array처럼 분리한다.

-- domain은 2번째 요소를 취한다.
SELECT split_part(address, '@', 2) as domain FROM emails;
address => domain
jangseoyeong@jusighoesa.kr => jusighoesa.kr
yeongceolgang@ihwang.net => ihwang.net
yeongsun69@gimo.com => gimo.com
seobyeongceol@dreamwiz.com => dreamwiz.com
ugim@gmail.com => gmail.com

DB Fiddle - PostgreSQL에서 확인

WITH 구문 활용 예시

WITH domains AS ( 
    SELECT split_part(address, '@', 2) as domain FROM emails
)
SELECT domain, COUNT(domain) as cnt FROM domains GROUP BY domain;
cnt domain
0 2 jusighoesa.com
1 1 gimiryu.net
2 1 yu.org
3 1 gimi.net
4 1 gimimin.net

정규식을 활용하여 데이터를 분리하려면 regexp_split_to_array을 활용할 수 있다.

이 함수에서는 기준이 되는 특정문자를 정규식으로 표현할 수 있다. 결과가 array이기 때문에 다음처럼 활용해야 한다.

-- 정규식 사용 시 E를 쓰고 \\ 방식을 사용한다.
SELECT addr_arr[1] as index_1, addr_arr[2] as index_2, addr_arr[3] as index_3
FROM ( SELECT regexp_split_to_array(address, E'\\W') as addr_arr FROM emails ) tmp;
index_1 index_2 index_3
gweoncunja ju kr
bjeon jeongim net
rgang nate com

DB Fiddle - PostgreSQL에서 확인

MySQL

MySQL에는 SUBSTRING_INDEX라는 함수가 존재한다. 이 함수는 PostgreSQL의 함수와 달리 사용 시 주의가 필요하다.

SUBSTRING_INDEX 함수의 정의는 Return a substring of a string before a specified number of delimiter occurs 이다.

즉, 특정 문자가 나타나기 전까지 모든 문자들을 가져온다.

다음 예시를 보면 index가 2인 경우, @가 2번째 나타날 때까지 모든 문자들을 보여줘야 한다.

SUBSTRING_INDEX(address, ‘@’, 1) SUBSTRING_INDEX(address, ‘@’, 2) SUBSTRING_INDEX(address, ‘@’, -1)
gweoncunja gweoncunja@ju.kr ju.kr
bjeon bjeon@jeongim.net jeongim.net
rgang rgang@nate.com nate.com
-- 여기서는 INDEX로 -1을 사용함으로써 해결할 수 있다.
SELECT SUBSTRING_INDEX(address, '@', -1) as domain FROM emails;

DB Fiddle - MySQL 5에서 확인

MySQL 8

MySQL 8.0에서는 REGEXP_SUBSTR이라는 정규식을 활용한 함수를 사용하여 구할 수 있다.

이 함수는 기준 문자를 정규식으로 표현하는 방식이 아니라 정규식에 해당하는 문자열을 가져온다.

사용방법 : REGEXP_SUBSTR(컬럼, 패턴, pos, idx)

pos는 패턴을 검색하는 시작 위치를 의미하고 idx는 substring 결과에서 index를 의미한다.

결과는 split_특정 정규식을 포함한 문자열을 찾아서 return 한다.

SELECT REGEXP_SUBSTR(address, '[^@]+', 1, 2) as domain FROM emails;

’[^@]+’은 xxxx@yyyyy.com에서 [xxxx , yyyyy.com] 형태로 분리를 위한 정규식.

REGEXP_SUBSTR(address, '[^@]+',1,1) REGEXP_SUBSTR(address, '[^@]+',1,2)
0 gweoncunja ju.kr
1 bjeon jeongim.net
2 rgang nate.com
3 gimjia obagjo.com
4 ngim jusighoesa.kr

DB Fiddle - MySQL 8에서 확인

WITH 구문 활용 예시

문제점 : domains 테이블에서 domain 컬럼의 데이터 타입이 char(0)으로 설정되어 버린다.

CAST를 사용하여 타입을 다시 지정하여야 새로운 쿼리문이 제대로 실행된다.

WITH domains AS ( 
    SELECT CAST(REGEXP_SUBSTR(address, '[^@]+',1,2) AS char(100)) as domain FROM emails
)
SELECT domain, COUNT(domain) as cnt FROM domains GROUP BY domain;

References :