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

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']]


사용 데이터

PostgreSQL

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

-- domain은 2번째 요소를 취한다.
SELECT split_part(address, '@', 2) as domain FROM emails;

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;

정규식을 활용하여 데이터를 분리하려면 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;

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번째 나타날 때까지 모든 문자들을 보여줘야 한다.

-- 여기서는 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] 형태로 분리를 위한 정규식.

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 :

• Python Faker 활용 소개

• MySQL SUBSTRING_INDEX() Function - w3schools

• regexp_split_to_array to Split String …