SQL 컬럼 가공하기
feat. 컬럼, 원하는 형식로 바꿔주기
안녕하세요 브래드입니다.
오늘은 SQL 컬럼을 우리가 원하는 형식으로 바꿔 데이터를 직접 구해보겠습니다.
숫자를 문자열로, 문자열 칼럼에서 일부 잘라내기, 원하는 칼럼 추가 및 TOP3 카테고리 구하기 등을 진행해보겠습니다.
숫자를 문자열로 바꿔주기
dt 데이터dt(날짜 데이터) 데이터는 현재 볼 수 있듯이, 숫자 데이터로 지정되어 있음을 확인할 수 있습니다.
결과창에서 dt의 왼쪽 부분에 있는 123 표시로도 알 수 있고, 데이터 값들이 오른쪽으로 정렬된 것으로도 해당 데이터들이 숫자 데이터임을 인지할 수 있습니다.
cast 사용cast를 사용하여 다음 예시와 같이 숫자 데이터를 문자 데이터로 변환할 수 있습니다.
숫자 데이터 dt 옆에 나타난 CAST를 진행한 dt에서는 데이터 값들이 문자로서 왼쪽 정렬된 것을 볼 수 있습니다.
즉, CAST(변환하고자 하는 컬럼 as 변환하고자 하는 데이터 유형)을 입력하여 우리가 원하는 데이터 형식으로 바꿔줄 수 있습니다.
문자열 컬럼에서 일부만 잘라내기
SUBSTRING 사용문자열 컬럼에서 일부만을 잘라내려고 한다면 SUBSTRING을 사용해줄 수 있습니다.
SUBSTRING은 엑셀에서의 MID함수와 동일하게 생각해줄 수 있습니다. 괄호 안에 해당 문자열을 입력해준 후 어디 위치에서 해당되는 문자열부터 어느 문자열까지 잘라낼 것인지를 각각 숫자를 통해 입력해줍니다.
위의 예시에서는 DT에서 첫 번째 문자열부터 4번째 문자열까지를 가져온다는 식으로서, 2021을 반환했습니다.
SUBSTRING 사용 2또한, 다섯 번째 자리부터 두 번째 문자열까지 가져와달라는 식을 추가로 작성하여 DT에서 월을 따로 잘라낼 수 있습니다.
YYY-MM--DD 형식 만들기날짜를 2022-11-02와 같은 형식으로 만들어주기 위해선 || 을 활용해주어야 합니다.
엑셀이나 다른 툴에서는 이어주는 기능이 '+' 혹은 '&' 기호 등으로 이루어지지만, SQL에서는 엔터키 위에 있는 기호인 '||'를 사용해주어야 합니다.
따라서 위의 예시 식처럼, || '-' || 를 입력하여 SUBSTRING을 이어 줄 수 있습니다. ||(이어주는 기호) 사이에 원하는 문자를 작은 괄호를 추가하여 입력해 줍니다.
원하는 칼럼 추가하기
CASE WHEN 사용내가 원하는 칼럼을 추가하려면 CASE WHEN을 사용해줄 수 있습니다. 즉, 흔히들 알고 있는 IF 절로 이해할 수 있습니다.
위의 예시에서는 원래 데이터에 F로 있는 항목을 '여성'으로, M으로 있는 항목은 '남성'으로 바꿔주기 위하여 CASE WHEN을 사용한 모습입니다.
CASE WHEN을 사용할 때는 CASE는 맨 앞부분에 한 번만 사용해주고 그 이후에는 WHEN만 사용해줍니다. 그리고 마지막 부분에는 반드시 END로 끝마쳐야 합니다.
CASE WHEN 사용하여 연령대를 나누어주는 칼럼 만들기CASE WHEN을 사용하여 연령대별로 나누어주는 칼럼을 만들 수도 있습니다.
20~24, 25~29, 30~34, 35~39, 40~44로 기존에 나누어져 있는 칼럼들을 20대, 30대 등으로 묶어 보여주고 한다면 CASE WHEN을 사용하여 다음과 같이 나타낼 수 있습니다.
특히, CASE WHEN와 THEN을 사용해주는 방식과 CASE WHEN과 THEN 및 IN으로 묶어 사용해주는 방식도 존재하므로 상황에 맞게 활용해줄 수 있습니다. 추가적으로 IN뿐만 아니라 BETWEEN 등의 기호도 사용 가능합니다.
TOP3 카테고리와 그 외 상품의 매출액 비교하기
TOP3 카테고리 구하기먼저 TOP3 카테고리는 다음가 같은 식을 입력하여 구해줍니다.
Online_order 테이블로부터 item 테이블 및 category 테이블을 조인해주고, cate1 및 매출 합계를 불러와줍니다. 이후 group by 및 order by를 사용하여 다음과 같이 정렬해줄 수 있습니다.
TOP3 및 나머지로 나누기CASE WHEN과 IN 그리고 THEN 및 ELSE를 사용하여 다음과 같이 나타낼 수 있습니다.
CASE WHEN 카테고리 IN ( TOP 3 항목들 각각 입력) THEN 'TOP3'를 입력한 후 나머지는 ELSE 'OTHERS'로 입력하고 END를 쳐서 마무리해줍니다.
추가로 SELECT 앞에 있던 c.cate1은 지워주고, SUM(GMV)는 END 후 쉼표를 입력하고 SUM(GMV)를 입력해줍니다.
특정 키워드가 담긴 상품과 나머지 상품의 매출 비교
특정 키워드가 담긴 상품 매출 및 상품 개수 구하기특정 키워드가 담긴 상품과 나머지 상품의 매출 비교를 위해서 CASE WHEN을 사용해줄 수 있습니다.
특히, LIKE 및 '%' 기호를 사용하여 특정 키워드가 담긴 상품만을 묶어 추출할 수 있으며, 나머지는 ELSE를 사용하여 표시해주고 끝부분에는 반드시 END로 마무리해줍니다.
추가적으로 THEN 이후, 그리고 ELSE 뒤에는 같은 형식의 데이터로 입력해주어야 올바른 값이 도출됩니다.
지금까지 SQL 칼럼을 직접 가공해보는 시간을 가져보았습니다.
특정 형식에 얽매이지 않고 다양하게 필요한 데이터 컬럼 형식으로 바꾸어보는 연습을 함께 해보아요.
브래드였습니다. 감사합니다.
