우리 팀은 아직까지 기획자가 직접 데이터를 추출할 수 있는 시스템이 없었다. 지금까지는 개발 담당자에게 직접 요청을 해서 데이터를 전달받았다. 그러다보니 데이터를 ‘한번에, 잘’ 요청해야 했고… (개발자 두 번 일 시키고 싶지 않으므로) 여러 각도에서 돌려보는 건 어려웠다.
이번에 SQL로 직접 데이터를 뽑아볼 수 있는 프로세스를 만드는 프로젝트를 진행하면서, SQL을 다시 열심히 공부해봐야겠다는 생각이 들었다. 그래서 Kaggle을 들으면서 나름대로 정리해본 노트를 이곳에 공개하려고 한다. 나와 같은 기획자에게 조금이나마 도움이 되었으면 하고…
Practice : 배달 서비스의 데이터를 뽑아보자
보다 재밌게 공부하기 위해 배달 서비스의 데이터를 예시로 가져왔다. (실제로 배달 서비스에 이런 데이터가 있는지는 모름)
이제 우리는 이 테이블을 가지고 SQL의 여러 문법을 요모조모 뜯어볼 것이다.
Select A From B: “B 테이블에서 A 컬럼 뽑아줘”
가장 기본이 되는 문법이다. SQL을 통한 데이터 추출에서 Select와 From은 빠져서는 안되는 중요한 놈이다! 각각의 뜻은 아래와 같다.
- Select: 데이터를 뽑아보고 싶은 컬럼을 규정하는 문법이다.
- From: 해당 컬럼을 어떤 테이블에서 가져와야 하는지 규정하는 문법이다.
만약 위의 테이블에서, 이름을 뽑아보고 싶다면, 아래처럼 작성할 수 있다.
추출 결과: 우영우, 태수미, 동그라미, 최수연
Where A: “A 조건을 만족할 때“
위의 Select와 함께 Where 문법을 사용하면, 그 중에서도 특정 조건을 만족하는 데이터만 가져올 수 있다.
해석: ‘회원별 최근 주문 음식 카테고리’ 테이블에서 ‘이름’을 가져오되, 최근 주문한 음식 카테고리가 ‘일식’인 경우에만 추출해!
추출 결과: 우영우, 태수미
Count(A): “A 컬럼이 몇 행인지 세줘”
Count()는 괄호 안에 컬럼 이름을 넣으면, 해당 컬럼의 숫자를 세준다. Count는 컬럼 내에 해당하는 데이터의 숫자만 반환하기 때문에, 데이터가 몇개이든지간에 한 개의 값만 반환한다. 이런 함수를 ‘집계 함수’ 라고 한다. 집계 함수에는 Count 외에도 SUM, AVG, MIN, MAX 등이 있다.
해석: ‘회원별 최근 주문 음식 카테고리’ 테이블에서 ‘이름’에 해당하는 데이터의 갯수를 세봐. 단, 최근 주문한 음식 카테고리가 ‘일식’인 경우에만 추출해!
추출 결과: 2
GROUP BY A: “A를 기준으로 그룹핑해줘”
count()와 같은 집계 함수를 사용할 때, Group by A를 사용하면 A 열 기준으로 값이 동일한 행을 하나의 그룹으로 처리한다.
예를 들면 최근 주문한 음식 카테고리 별로 몇명이 주문했는지를 데이터로 보고 싶다고 해보자.
해석: ‘회원 별 최근 주문 음식 카테고리’ 테이블에서 최근 주문문한 음식 카테고리 별로, 몇 명이 주문했는지 추출해줘.
추출 결과:
GROUP BY A Having B: “A를 기준으로 그룹핑하고, B 기준을 충족하는 것만 보여줘”
Having은 Group by와 함께 사용하는 문법인데, 특정 기준을 충족하지 않는 그룹은 추출하지 않는다. 예를 들어서 주문한 회원이 2개 이상인 경우에만 데이터를 추출하고 싶다고 가정해보자.
해석: ‘회원 별 최근 주문 음식 카테고리’ 테이블에서 최근 주문한 음식 카테고리별로, 몇 명이 주문했는지 추출해줘. 단, 집계했을 때 회원 ID의 수가 2개 이상인 경우에만 보여줘!
추출 결과:
ORDER BY A: “다 뽑았으면 A 기준으로 정렬해줘”
보통 가장 마지막에 위치하는 문법으로, 추출한 데이터를 어떤 기준으로 정렬할지를 규정하는 문법이다. 디폴트는 오름차순(123, abc, 가나다)이며, 내림차순으로 정렬하고 싶다면 뒤에 DESC를 붙이면 된다.
해석: ‘회원 별 최근 주문 음식 카테고리’에서 ID, 이름, 최근 주문한 음식 카테고리 데이터를 가져와줘. 단, 최근 주문한 음식 카테고리 기준으로 내림차순으로 정렬해줘!
추출 결과:
Extract(A from Date) As B : “날짜 데이터에서 A값을 추출하고, B라고 명명해”
특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터 원하는 날짜 영역을 추출하여 출력한다. A에는 year, month, day, week, hour, minute, second 등의 변수가 올 수 있다.
위의 테이블에는 날짜 데이터가 없지만, 만약 있다고 가정하고 SQL문을 작성하면 아래와 같다.
해석: ‘회원별 최근 주문 음식 카테고리’ 테이블에서 1)이름 2)Date 데이터에서 날짜를 추출해
추출 결과 (예시):
AS A: “컬럼의 이름은 A로 정하겠어“
select한 컬럼 바로 옆에 붙여서, 컬럼의 이름을 수정할 수 있다. 앞서 Group By 함수를 보면, count(ID)에 대한 컬럼의 이름을 정의하지 않았기 때문에 ‘f0_’로 정해진 것을 볼 수 있다. 이때 뒤에 AS를 붙이면 컬럼의 이름을 넣을 수 있다.
해석: ‘회원 별 최근 주문 음식 카테고리’ 테이블에서 최근 주문한 음식 카테고리별로, 몇 명이 주문했는지 추출해줘. 이때 Count(회원 ID)의 컬럼 이름은 ‘ID 수’로 정할게!
추출 결과:
WITH A AS (B) C“ B 쿼리를 이용해 A라는 테이블을 만들고, C 쿼리를 실행해”
데이터 추출을 할 때, 보다 가독성있게 코드를 짤 수 있도록 쿼리 내 임시 테이블을 만드는 문법이다. 예를 들어 최근 주문한 회원이 2명 이상인 카테고리 이름만 뽑고 싶을 수 있다. 임시 테이블을 만들지 않고도 충분히 데이터를 뽑을 수 있지만, 다른 사람이 보기에는 어떤 데이터를 뽑고 싶어서 이렇게 쿼리를 짰는지 이해하기 어려울 수 있다. 예시로 든 데이터는 아주 간단한 테이블이지만, 실전에서는 테이블에 엄청나게 많은 열이 있기 마련이다.
아래의 예시로 확인해보자.
해석: ‘회원별 최근 주문 음식 카테고리’ 테이블에서 최근 주문한 음식 카테고리 별 회원 ID 수를 ‘인기메뉴’ 테이블로 정의할게 (이때 count(회원 ID)는 ‘ID 수’로 정의할게) 단, 회원 ID 수가 2명 이상인 카테고리만 기재해줘.
이 테이블에서, ‘최근 주문한 음식 카테고리’만 뽑아줘.
추출결과:
Limit N: “추출 결과는 N개로 제한해줘“
limit을 이용하면, 추출 결과의 갯수를 제한할 수 있다. 예를 들어 위의 테이블에서 회원 ID 순으로 3개의 데이터만 보고싶다고 가정하자.
Having과 Where의 차이?
Group by 구문을 쓸 때에는 having을 이용해 추출 조건을 걸 수 있다. 그런데 우리는 Where도 조건을 걸 때 쓰는 문법이라고 배웠다! 둘은 같은 기능을 하는걸까? Group by를 쓸 때 where을 쓰면 안 되는건가?
둘의 특징을 다시 정리하면 이렇다.
- Where: 항상 from 뒤에 위치하고 조건에는 다양한 비교 연산자들이 사용되어 구체적인 조건을 줄 수 있다.
- Having: 항상 group by뒤에 위치하고 where 조건절과 마찬가지로 조건에는 다양한 비교연산자들이 사용되어 구체적인 조건을 줄 수 있다.
둘의 차이점은, where은 모든 데이터에 대해 조건을 걸고, 걸러진 데이터를 보여준다. 반면에 Having은, group by 된 이후 특정한 필드로 그룹화 되어진 새로운 테이블에 조건을 줄 수 있다.
Group by 뒤에 where을 쓸 때와 having을 쓸 때 차이점을 말로 표현하면 이렇다
- Group by A where B: B 조건에 맞는 데이터를 A 조건으로 그룹핑해
- Group by A having C: A 조건으로 그룹핑한 뒤, C 조건에 맞는 데이터만 보여줘.
- Group by A having C where B: B 조건에 맞는 데이터를 A 조건으로 그룹핑한 뒤, C 조건에 맞는 데이터만 보여줘.
쪼렙 서비스 기획자님이 브런치에 게재한 글을 편집한 뒤 모비인사이드에서 한 번 더 소개합니다.
