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

게시일 : 2019년 04월 08일    
# MySQL # split_part # PostgreSQL # split_part # 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']]

테이블 생성 쿼리

-- mysql
CREATE TABLE emails (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    address VARCHAR(255) NOT NULL)

-- postgresql
CREATE TABLE emails (id SERIAL PRIMARY KEY, address VARCHAR NOT NULL);'
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
0 jangseoyeong@jusighoesa.kr jusighoesa.kr
1 yeongceolgang@ihwang.net ihwang.net
2 yeongsun69@gimo.com gimo.com
3 seobyeongceol@dreamwiz.com dreamwiz.com
4 ugim@gmail.com gmail.com

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

MySQL

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

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

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

참고 : PostgreSQL에서는 regexp_split_to_array라는 MySQL의 regexp_substr과 같은 용도의 함수가 있다.

SELECT REGEXP_SUBSTR(address, '[^@]+', 1, 2) as domain FROM expert.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

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 :