[Programming][MySQL] 서브쿼리 (feat. KOSPI50 종목)

 

• 본 페이지에서 사용하는 테이블에 대한 설명의 아래 JOIN 활용하기 링크에서 확인:)

 

 

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

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

 

서브 쿼리는 쿼리 문안에 다른 쿼리들이 포함되어 있는 것이다.

 

• SELECT 안의 서브 쿼리

SELECT ticker, stock, close, fluctuation, volume,
	(SELECT AVG(volume) FROM price) AS average_volume
FROM price;

SELECT 서브쿼리

 

하루 동안의 평균 거래량을 알고 싶을 때 평균을 조회하고 값을 추가해 주는 것이 아니라, SELECT 문 안에 서브 쿼리를 삽입하여 한번에 출력되도록 만들 수 있다. price열에서 가지고 오고 싶은 열(Column)들을 작성하고, SELECT AVG(volume) FROM price로 거래량의 평균을 만드는 쿼리를 출력하고 싶은 열로 취급하여 작성했다. 그리고, average_volume으로 alias를 만들었다. 그 결과, 그림에서 볼 수 있는 것과 같이 volume 열(Column) 옆에 평균 거래량이 출력되는 것을 확인할 수 있다.

 

Error Code: 1241 Operand should contain 1 column(s)

서브 쿼리를 만들 때, 출력하는 열(Column)을 하나만 지정해야 한다.  SELECT AVG(volume), AVG(close) FROM price
 이런 식으로 두 개 이상의 열을 지정하면 오류가 발생한다. 

 

• WHERE 안의 서브 쿼리

SELECT ticker, stock, close, fluctuation, volume
FROM price
WHERE volume >= (SELECT AVG(volume) FROM price); #AVG(volumn)=849824

WHERE 절 안에 조건으로 서브 쿼리를 넣어 원하는 조건을 바로 출력할 수 있다. 전체 거래량의 평균보다 거래량이 많았던 종목을 보고 싶다면 SELECT AVG(volume) FROM price를 WHERE 절에 추가하여 평균 거래량을 만들어 주고 이 보다 큰 값들을 가지고 오는 조건으로 만들어주면 된다. 평균 거래량이 849,824 이므로 평균 거래량보다 큰 거래량들이 잘 출력되었다.

 

• IN / ANY(SOME) / ALL / EXIST

WHERE절에서 포함하고 있는 조건들을 다양하게 출력할 수 있다. 

 

SELECT *
FROM price
WHERE ticker IN 
(SELECT sell FROM information WHERE gender = 'f');

IN 서브쿼리

 

조건절(WHERE) 뒤에 서브 쿼리가 여러 개의 행을 산출하는 추출 문이라면 IN을 사용해서 일치하는 값들을 가지고 오도록 한다. 기존의 IN과 같은 기능을 하며, '='을 사용하면 서브 쿼리에 여러 값이 있어서 오류가 나기 때문에 여러 개의 행을 가진 서브 쿼리라면 IN을 사용한다.

 

 

연습용 테이블

위의 테이블은 ANY와 ALL 함수를 사용하기 위해서 Price 테이블에서 삼성그룹의 종가(Close)를 가지고 온 것이다.

 

SELECT *
FROM price
WHERE close > ANY 
(SELECT close FROM price WHERE stock LIKE '%삼성%');

 

ANY 서브 쿼리

 

ANY는 조건 중 하나라도 일치하는 값을 가지고 오는 함수이다. 따라서, 삼성그룹의 가격들 중 하나라도 큰 값이 있으면 값을 산출하게 된다. 종가(Close)가 가장 작은 삼성 생명(75,500) 보다 큰 값부터, 종가(Close)가 가장 큰 삼성 바이오로직스(894,000) 보다 큰 값 모두를 산출하라는 의미이다. 결국 조건의 최솟값보다 큰 값들은 모두 가지고 오기 때문에 결과창과 같이 75,500원 보다 종가(Close)가 작은 모든 값을 다 산출하게 된다. 

 

ANY대신 SOME을 사용할 수도 있는데 결과적으로 같은 값이 나온다.

 

SELECT *
FROM price
WHERE close > ALL 
(SELECT close FROM price WHERE stock LIKE '%삼성%');

ALL 서브 쿼리

 

반면에, ANY 대신 ALL을 사용하면 모두 일치하는 조건을 가지고 오는 의미이기 때문에 종가(Close)가 가장 작은 삼성 생명(75,500) 보다 큰 값보다 커야 되고, 동시에 종가(Close)가 가장 큰 삼성 바이오로직스(894,000) 보다도 큰 값을 산출해야 한다. 따라서, 최댓값보다 큰 값을 가지고 오는 의미를 가진다. ANY의 결과와는 다르게 종가(Close)가 894,000원보다 큰 LG 생활건강(1,480,000)만 산출하고 있다.

 

# EXISTS
SELECT *
FROM price
WHERE EXISTS 
(SELECT * FROM information WHERE information.buy = price.ticker);

# NOT EXISTS
SELECT *
FROM price
WHERE NOT EXISTS 
(SELECT * FROM information WHERE information.buy = price.ticker);

왼쪽: EXISTS / 오른쪽: NOT EXIST

 

EXISTS는 서브 쿼리의 조건에 존재하는 값들을 산출하는 함수이다. information 테이블에서 주식을 매수(buy)한 사람의 종목의 정보를 알아보고 싶을 때, 서브 쿼리 문으로 주식을 매수한 종목 번호와 price 테이블의 종목 번호(ticker)가 일치하는 사람을 가지고 온다. 그러면, 왼쪽과 같이 매수를 진행한 종목만 산출되게 된다. 

 

NOT EXISTS는 이와 반대로 존재하지 않는 값만 가지고 오는 것이므로, 오른쪽 결과와 같이 매수하지 않은 종목들만 나오게 된다. 

 

• FROM 안의 서브 쿼리(Derived Table)

여러 행과 여러 열이 포함된 서브 쿼리를 만드는 경우 FROM절 안에 서브 쿼리를 Derived Table로 만들어줄 수 있다.

 

SELECT CONCAT(SUBSTR(stock, 1, 2), '그룹') AS name , 
	AVG(age) AS avg_age, COUNT(*) AS group_count
FROM analysis AS a INNER JOIN information AS i
ON a.ticker = i.sell
WHERE SUBSTR(a.stock, 1, 2) IN ( '삼성', '현대', 'SK', 'LG')
GROUP BY SUBSTR(a.stock, 1, 2) 
;

 

 

먼저, 우리나라의 그룹 기업들 매수한 사람들의 정보를 분석하기 위해서 analysis 테이블과 information 테이블을 INNER JOIN 하고, [삼성, 현대, SK, LG]가 포함되는 주식 종목을 매수한 사람들의 정보만 가지고 왔다. 또한, 그룹들 별로 GROUP BY를 하고, '그룹'을 붙여 이름 지었다. 기업 그룹 별로 나이와 총기업을 매수한 수를 추가적으로 산출되도록 작성했다.

 

이 정보가 지금은 4건밖에 없기 때문에 계산하기 쉽지만, 데이터가 많을 경우를 대비해서 FROM절에 Derived Table로 만들어 기업들 간의 평균과 매수한 기업수의 평균을 해보도록 하자. 

 

SELECT AVG(avg_age), AVG(group_count)	
FROM (
	SELECT CONCAT(SUBSTR(stock, 1, 2), '그룹') AS name , 
		AVG(age) AS avg_age, COUNT(*) AS group_count
	FROM analysis AS a INNER JOIN information AS i
	ON a.ticker = i.sell
	WHERE SUBSTR(a.stock, 1, 2) IN ( '삼성', '현대', 'SK', 'LG')
	GROUP BY SUBSTR(a.stock, 1, 2) 
)   AS group_info 
;

Derived Table

 

Derived Table를 만들 때는 반드시 Derived Table에 alias를 붙여줘야 한다. 

 

결과를 분석해 보면, 우리나라 대기업을 그룹끼리 묶었을 때 4대 그룹들을 매수한 사람들의 나이는 36세 정도로 젊은 편이고, (데이터가 작지만) 20명의 매수자 데이터 중 2명 정도가 매수한 것으로 보아 10% 정도가 매수했다고 생각해 볼 수 있다.