[SQL] data.world tutorial SQL INNER JOIN 예제 풀이

손이 움직이기 전에 머리를 써야 한다. 쿼리를 작성하기 전에 확인해야 될 절차를 나누어서 서술해보았다. 여러 테이블을 JOIN 해서 쿼리할 수 있다면 쿼리 입문 딱지는 떼도 좋을 것. JOIN 에 익숙해지기 전에 좋은 쿼리 습관을 들이는 것은 매우 중요하다. 

이 글은 data.world 의 INNER JOIN 튜토리얼 예시에 대한 풀이이다. [링크]

The SELECT and FROM Clauses

 

데이터셋 확인 및  쿼리 질의 모두 아래 링크에서 확인할 수 있다. [링크]

SQL CRM Example Data - dataset by siyeh

 

직접 쿼리를 실행시켜 보기를 바라며 풀이 시작

---

1. 요구사항을 검토한다. 

 

we want to see
a. the managers’ names as well as the sales agents’ names
b. by account for deals
c. which had been won

a. managers' name 과 sales agents' names 추출하고 싶다.
b. deals 를 위한account 단위로
c. 성사된 거래에 한해서

 

 

2. Dataset 을 확인한다. 

우리가 원하는 정보는 어디서 얻을 수 있을까? 추출대상인 dataset 테이블을 확인하고, 어떻게 데이터가 쌓여있는 지 검토한다.
테이블은 특정 정보에 대한 추상적인 모델링을 담고 있다. 

sales_teams (35)
sales_agent 와 manager 의 관계를 담고 있는 테이블이다. 

sales_agent: 유니크한 값은 35개이고, empty 값은 없다.
manager: 유니크한 값은 6개이고, empty 값은 없다.

sales_teams 테이블은 총 35 건이 있는데, sales_agent 는 35개, manager 는 6개 이다.
테이블 규모와 sales_agent 수가 일치하는 것으로 보아, 테이블 내에서 sales_agent 값은 유니크 하다.
따라서 sales_agent 와 manager 의 조합도 유니크 하다. 

와중에 status 필드에 2개의 유니크한 값 Current 와 Former 이 있다. 
혹시 sales_agent 가 manager 를 Current manager, Former manager 을 가지고 있는지 확인해본다. 없다. 
(물론 테이블 규모와 sales_agent 수가 일치하는 것을 보아 그럴 리는 없지만 한번 더 확인한다.)

sales_pipelines (8,800)
deal 에 대한 정보: account, sales_agent, deal_stage 데이터를 담고 있는 테이블이다. 

account: 유니크한 값은 85개이고, empty 값이 있다!  
sales_agent: 유니크한 값은 30개이고, empty 값은 없다.
deal_stage: 유니크한 값은 3개이고, Won / Lost / In Progress 로 구성되어 있다. empty 값은 없다.

sales_pipelines 테이블은 총 8800 건이 있는데 account 는 85개, sales_agent 는 30개밖에 없다. 
즉, 이 테이블 내에는 동일한 accouts 의 행이 많으며, 동일한 sales_agent 의 행도 많다.
따라서 account 와 sales_agent 의 유니크 조합을 알려면 DISTINCT 키워드가 필요하다! 

 

결론

Dataset 대상 테이블: sales_teams, sales_pipeline
JOIN 기준 컬럼: sales_agent  (sales_temas.sales_agent, sales_pipeline.sales_agent)
Dataset Conditions: sales_pipelines 내에서 deal_stage 가 "Won" 건

그리고 account 와 sales_agent 의 유니크 조합을 알려면 DISTINCT 키워드가 필요하다! 

 

 

3. 여러 테이블을 JOIN 한다. JOIN 방식 검토

데이터셋 대상 테이블을 어떻게 JOIN 할 지 검토한다.

양 테이블의 특정 컬럼을 기준으로 연결이 필요하면 INNER JOIN (default)
참고. JOIN 유형에 대해서는 그 다음글에 별도로 서술할 예정이다.

이 상황에서는 INNER JOIN 을 활용해야한다.

 

결론

JOIN 유형: INNER JOIN 
JOIN 연결할 컬럼: sales_agent

INNER JOIN 이라면 다양한 방식으로 쿼리를 추출할 수 있다. 우선 추출대상을 지정하기 전에 데이터셋을 지정하는 FROM JOIN WHERE 구문부터 작성해보자!

 

a. JOIN 안 쓰고 WHERE 조건으로 연결 & AS (Alias) 활용

 

JOIN 으로 연결하지 않고, 두개의 타이블을 나열하면 CROSS JOIN 이 된다. 즉 sales_teams 의 모든 row 와 sales_pipeline 의 모든 row 의 경우의 수를 모두 조합한 데이터가 나온다. (35*8800) 이 중, 특정 필드값이 같은 row 만 WHERE 조건문으로 한정지어준 것이다.

일반적으로 데이터 규모가 클 때에는 모든 경우의 수를 곱한 후에 데이터를 가져오는 방식은 쿼리 효율성이 떨어지므로 지양한다. 

FROM sales_teams AS teams, sales_pipeline AS pipeline WHERE teams.sales_agent = pipeline.sales_agent AND pipeline.deal_stage = "Won"

 

b. JOIN 과 USING

데이터셋 대상 테이블의 기준 컬럼이 컬럼명이 같을 때에는 USING 키워드를 활용할 수 있다.
이 때 () 로 감싸는 것은 필수이고, 여러 컬럼을 사용할 수 있다. 

FROM sales_teams AS teams  JOIN sales_pipeline AS pipeline USING (sales_agent) WHERE pipeline.deal_stage = "Won"

 

c. JOIN 과 ON 

데이터셋 대상 테이블의 기준 컬럼이 컬럼명이 다를 때에는 연결을 위한 컬럼을 양 테이블 모두 지정해주어야 한다. 
연결할 테이블 마다 ON 키워드가 필요하다. 즉, 여러개의 테이블을 연결하면 기준테이블을 제외한 나머지 테이블 갯수만큼 ON 키워드가 필요하다. 

여러 제약조건 없이 사용할 수 있어, 가장 기본적인 INNER JOIN 질의방식이다. 

FROM sales_teams AS teams  JOIN sales_pipeline AS pipeline  ON teams.sales_agent = pipeline.sales_agent WHERE pipeline.deal_stage = "Won"

 

4. 추출 대상 컬럼을 지정한다.

데이터셋을 확인하고 연결했으니, 최종적으로 추출대상 필드를 지정하면 된다. 요구받은 템플릿은 managers' name, sales agents' name, 그리고 account 이다. (account 기준으로 두 대상을 추출해달라고 요청했으니, 암묵적으로 기준값인 account 는 포함된다.) 

이들은 SELECT 를 써야 할까? SELECT DISTINCT 를 써야할까?  각 테이블에 데이터가 어떻게 쌓였는지를 다시 확인해본다.

sales_teams 테이블은 총 35 건이 있는데, sales_agent 는 35개, manager 는 6개 이다.
테이블 규모와 sales_agent 수가 일치하는 것으로 보아, 테이블 내에서 sales_agent 값은 유니크 하다. 
따라서 sales_agent 와 manager 의 조합도 유니크 하다. 

sales_pipelines 테이블은 총 8800 건이 있는데 account 는 85개, sales_agent 는 30개밖에 없다. 
즉, 이 테이블 내에는 동일한 accouts 의 행이 많으며, 동일한 sales_agent 의 행도 많다.
따라서 account 와 sales_agent 의 유니크 조합을 알려면 DISTINCT 키워드가 필요하다! 

결론

두 테이블 증 DISTINCT 조합이 필요한 건이 하나라도 있으면 SELECT DISTINCT 로 추출해야한다.

SELECT DISTINCT manager, sales_agent, account

 

5. (선택) 최종 조회결과의 컬럼명과 데이터타입을 확인한다.

만약 사용자가 manager, agent, account 로 컬럼명을 요구했다면, AS (alias) 를 통해 수정해줄 수 있다.
또한 각 컬럼명의 데이터타입을 데이터셋 원본 테이블의 데이터타입과 다르게 요구했을 경우 반영해줄 수 있다.
(이번에는 모두 STRING 이라서 그럴 니즈는 없어보이지만.)

SELECT DISTINCT manager, sales_agnet AS agent, account

 

보통 컬럼명에 대한 정보가 많아질 경우 줄바꿔서 가독성을 높일 수 있다. (이 경우에는 필요없어 보이지만.)

SELECT DISTINCT manager , sales_agnet AS agent , account

 

 

최종쿼리

최종 쿼리는 다음과 같다. 쿼리의 가독성을 높이기 위해 추출대상 컬럼과 데이터셋 상세 사이에 줄바꿈을 하였다. 
(외부에 데이터 결과와 함께 제공해야하는 경우도 있다. 이 때 다른 사람이 쿼리의 요구사항 및 구조를 파악할 수 있도록 줄바꿈을 해주자.)

SELECT DISTINCT manager , sales_agnet AS agent , account FROM sales_teams AS teams  JOIN sales_pipeline AS pipeline  ON teams.sales_agent = pipeline.sales_agent WHERE pipeline.deal_stage = "Won"

 

---

포스팅하면서 알고 있던 JOIN 문도 다시 체계적으로 학습할 수 있었다. ㅇ__ㅇb