250926_SQL 데이터 가공하기

1. 문자 포맷이 다를 때 SQL로 가공하기

우리가 SQL을 사용할 때, query를 통해 나온 결과를 바로 사용하지 못하는 경우가 있다.

잘못된 값이 있다던가, column에 있는 값들의 일부 정보만 사용하고 싶다던가, 아니면 두 column의 값을 잘라붙여 새로운 이름을 쓰고 싶다던가.. 여러 가지 경우가 있을 수 있다.

이번에는 이럴 때 사용할 수 있는 REPLACE, SUBSTRING, CONCAT 함수에 대해 알아보고자 한다.

REPLACE

REPLACE는 특정 문자를 다른 문자로 바꿀 수 있는 기능을 제공한다.

REPLACE(바꿀 column, 현재 값, 바꿀 값) as column_name

예시로, 'address' column의 '서울특별시' 값을 모두 '부산광역시'로 바꾸고 싶다면,

REPLACE(address, '서울특별시', '부산광역시')

라고 입력해주면 된다.

SUBSTRING

SUBSTRING은 특정 문자만 골라서 조회할 수 있는 기능을 제공한다.

SUBSTRING(조회 column, 시작 위치, 글자 수)

시작 위치에서 원하는 글자 수까지의 문자를 substring해준다.

예를 들어 '1234567890'이라는 문자가 'number'라는 column에 있고, 우리가 5까지만 출력하고 싶을 때,

SUBSTRING(number, 1, 5)

라고 입력하면 '12345'가 출력된다.

만약 문자 끝까지 자르고 싶으면, 글자 수 항을 빼주면 된다.

CONCAT

CONCAT은 여러 column의 문자를 합쳐 새로운 형태로 만드는 기능을 제공한다.

CONCAT(값 1, 값 2, 값 3, ...)

예를 들어, 다음과 같은  table이 있다고 하자.

Address	Name
서울특별시	홍길동

이를 조합해 '[서울]홍길동'이라는 형태의 값으로 만들고 싶다면,

CONCAT('[', substr(Address, 1, 2), ']', Name)

라고 입력하면 원하는 값이 출력된다.

위와 같이 변경한 값은, group by나 order by에도 이용할 수 있다.

REPLACE, SUBSTRING, CONCAT을 통해 column 안의 값들을 필요한 대로, 혹은 내가 보기 편한 대로 바꿔보자.

 

2. 조건에 따라 다른 연산을 하는 방법

Python에 있는 if, elif, else처럼, SQL에도 내가 원하는 조건에 따라 연산을 적용할 수 있는 명령어가 있다.

거리에 따라 톨게이트 요금이 다르다던지, 사용한 금액에 따라 고객 등급이 바뀐다던지.. 이럴 때 쓸 수 있는 것이

IF와 CASE문이다.

IF

IF는 우리가 아는 그 IF와 같다. 조건을 만족했을 때는 A라는 값을, 그렇지 않을 때는 B라는 값을 적용하는 것이다.

IF(조건, 조건을 충족할 때의 값, 조건을 충족하지 않을 때의 값)

예를 들어, 홀수와 짝수를 구분하는 명령어를 작성한다고 하면,

IF(number%2==0, '짝수', '홀수')

2로 나눈 나머지가 0이면 짝수, 아니면 홀수라는 값을 가지게 된다.

CASE

CASE는 두 개 이상의 조건을 지정해야 할 경우 사용할 수 있다.

조건에 따라 어떤 값을 지정할 것인지 설정할 수 있다. (Python의 if, elif, else와 C++의 if, else if, else와 비슷하다)

CASE WHEN 조건(들) THEN 값
     WHEN 조건(들) THEN 값
     ELSE 값
     END "column name"

기본적으로는 이와 같은 형태이며, 조건은 and나 or, not을 통해 여러가지를 쓸 수 있다.

또한 ELSE에 해당하는 값이 없을 경우 (즉 WHEN 문에서 만든 조건들이 column 내의 모든 값을 cover할 경우) ELSE는 생략해도 된다.

예를 들어, 다음과 같은 차량 타입과 이동 거리가 있는 table에 대해,

SUV는 거리 * 1.2, Sedan은 거리 * 1.1의 세금을 부과하고, 추가로 거리가 1000이 넘으면 추가적으로 세금이 10% 늘어난다면,

Type	Distance
SUV	400
Sedan	1200
Sedan	700
SUV	1500

CASE WHEN Type='SUV THEN Distance*1.2*if(Distance>=1000, 1.1, 1)
     WHEN Type='Sedan' THEN Distance*1.1*if(Distance>=1000, 1.1, 1)
     END "tax"

이와 같은 형태로 작성할 수 있다. (IF문은 어디나 들어갈 수 있다.)

그리고 근본적으로 CASE문 역시 column을 생성하기 때문에, 뒤에 추가적인 column을 불러오려면 END 뒤의 column 이름 뒤에 comma(,)를 넣는 것을 잊지 말자.

IF와 CASE를 통해 적절한 조건을 바탕으로 Segmentation하는 연습을 해보자.

중요한 것은 좋은 조건을 잘 설정하는 것이다. (무작정 WHEN의 개수를 늘리는 것만이 정답은 아니다!)