[Programming][MySQL] JOIN 활용하기(feat. KOSPI50 종목)

 

• 데이터 설명

JOIN 함수를 사용하기 위해 price, analysis, information 세 테이블을 임의로 만들었다. 

 

price 테이블

 

INVESTING.COM에서 출력한 KOSPI50 종목(2021.07.26 기준)의 가격 데이터로 id, 종목번호(ticker), 종목 이름(stock), 종가(close), 고가(high), 저가(low), 변동(fluctuation), 거래량(volume)으로 구성된 열(Column)과 50개의 행(Row)으로 되어있다. 

 

price 데이터 셋

 

analysis 테이블

 

INVESTING.COM에서 출력한 KOSPI50 종목(2021.07.26 기준)의 기술 분석 데이터로 id, 종목번호(ticker), 종목 이름(stock), 시간당(time), 일 간(day), 주 간(week), 월 간(month) 열로 구성되어있다. 마찬가지로, 50개의 행(Row)으로 되어있다. 

 

analysis 데이터 셋

 

information 테이블

 

매수하고 매도한 개인들의 정보를 나타내는 테이블로 임의로 만들었다. id, 매수 종목(buy), 매도 종목(sell), 성별(gender), 나이(age), 거래 증권사(firm)로 구성되어 있고, 20명으로 설정했기 때문에 20개의 행(Row)으로 되어있다.

 

information 데이터 셋

 

• LEFT OUTER JOIN

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm
FROM price AS p LEFT OUTER JOIN information AS i
ON p.ticker = i.buy;

LEFT OUTER JOIN

 

LEFT OUTER JOIN은 왼쪽에 작성한 테이블이 기준이 되어 두 테이블을 붙이는 것이다. 위의 쿼리에서 price 테이블이 기준이 되었기 때문에 오른쪽 테이블의 20명이 산 주식이 없다면 NULL 값으로 나온다. 

 

→ 주식의 종가(close)가 있는 price의 테이블과 성별(gender), 나이(age), 거래 증권사(firm)를 JOIN 해서 코스피 50 기업들을 어떤 사람들이 매수했는지 파악하는 테이블을 만들었다.

 

JOIN을 할 때는 선택하는 열(Column) 앞에 어디에 속한 테이블인지 구분해서 작성해야 된다. 쿼리에서도 alias로 간편하게 작성할 수 있도록 이름을 지어주고, 해당 별칭으로 열(Column)마다 붙여주었다.

두 테이블을 JOIN 할 때, JOIN의 기준이 되는 열은 ON 구문으로 연결한다.

 

• RIGHT OUTER JOIN

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm
FROM price AS p RIGHT OUTER JOIN information AS i
ON p.ticker = i.buy;

RIGHT OUTER JOIN

 

같은 쿼리문에서 JOIN의 기준만 RIGHT OUTER JOIN으로 변경했다. 오른쪽 information 테이블이 기준이 되었고,  코스피 50 종목 안에서 매수한 사람들의 정보이기 때문에 왼쪽 테이블 price의 ticker열에 모두 속한다. 따라서, NULL 값이 발생하지 않고 ticker, stock, close의 정보만 information 테이블에 JOIN 된 것을 확인할 수 있다. 

 

• INNER JOIN

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm
FROM price AS p INNER JOIN information AS i
ON p.ticker = i.buy;

INNER JOIN

 

INNER JOIN은 두 테이블 모두에 존재하는 값만을 JOIN 하는 것이다. 예제로 사용하고 있는 테이블에서는 information의 ticker 값이 price에 속해있는 값들이기 때문에(price에는 모두 존재) RIGHT OUTER JOIN 한 결과와 동일하게 나온다. 

 

information 테이블의 buy 열(Column)은 price 테이블 ticker 열(Column)의 외래 키(Foreign Key) 역할을 한다.  외래 키는 테이블의 필드(attribute) 중 다른 테이블의 행(row)을 식별할 수 있는 키를 의미한다.(참고: 위키피디아 https://ko.wikipedia.org/wiki/%EC%99%B8%EB%9E%98_%ED%82%A4)

따라서, information 테이블(자식 테이블)의 key가 price 테이블(부모 테이블)의 key에 속해있기 때문에 RIGHT OUTER JOIN과 INNER JOIN이 같은 결과를 산출하는 것이다.

 

• 세 테이블 JOIN 하기

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm, 
    a.day, a.month
FROM price AS p RIGHT OUTER JOIN information AS i
    ON p.ticker = i.buy
    LEFT OUTER JOIN analysis AS a
    ON i.buy = a.ticker;

세 테이블 JOIN

 

information의 사람들이 매수한 주식들의 종가(close)와 일(day), 월(month) 별 분석의견을 알아보고자 price, information, analysis 세 테이블을 JOIN 했다. 먼저 RIGHT OUTER JOIN으로 사람들이 매수한 종목을 기준으로 종가(close) 데이터를 가지고 왔고, JOIN 된 테이블을 기준으로 analysis 테이블을 다시 한번 LEFT OUTER JOIN 했다. 그 결과, 세 테이블이 잘 연결되어 나오는 것을 볼 수 있다. 

 

이 정보를 기준으로, analysis 투자의견을 매수자들이 얼마나 반영했는지 보기 위해서 일(day) 별로 '매수'가 들어간 사람들만 출력해보자. 

 

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm, 
    a.day, a.month
FROM price AS p RIGHT OUTER JOIN information AS i
	ON p.ticker = i.buy
    LEFT OUTER JOIN analysis AS a
    ON i.buy = a.ticker
 WHERE a.day LIKE ('%매수');

 

WHERE 조건문에 '매수'로 끝나는 글자만 출력하는 것으로 설정했더니 절반 정도 되는 인원이 매수하여 일별 투자의견을 반영한 것으로 출력되었다. 

 

SELECT p.ticker, p.stock, p.close,
    i.gender, i.age, i.firm, 
    a.day, a.month
FROM price AS p RIGHT OUTER JOIN information AS i
	ON p.ticker = i.buy
    LEFT OUTER JOIN analysis AS a
    ON i.buy = a.ticker
 WHERE a.day LIKE ('%매수')
 AND gender = 'm'
 ORDER BY i.age DESC;

 

조금 더 다양하게 데이터를 가공하고 싶다면, 위와 같이 WHERE 조건문을 추가하거나 GROUP BY, ORDER BY 와 같이 조건들을 사용할 수 있다. 위의 쿼리에서는 매수자가 남자인 사람만 출력했고, 이를 나이 순으로 내림차순 정렬했다.