[SQL] 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의 후기 #5 (Fin) - 서브쿼리 (SubQuery), With 구문

 

  드디어 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 의 마지막 시간이다. 이전글에서 공부한 SUM() OVER(), LEAD/LAG 등의 Window 함수는 처음이라 그런지 확실히 쉽지 않았다. 아마 이번에 배울 서브쿼리나 With 구문도 마찬가지일 것이다. 다만 분명한 건, 이 강의가 SQL을 배우는 데 큰 도움이 된다는 것이다. 또 누구나 처음엔 서툰 법이기에 꾸준히 노력하여 목표한 바를 이루도록 하자.

메타코드M

 

※ OT & 1과목: 2024.02.11 - [SQL] - [SQL] 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의 후기 #1 - SQL 설치, SELECT문 등

※ 2과목: 2024.02.12 - [SQL] - [SQL] 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의 후기 #2 - WHERE, ORDER BY, GROUP BY 등

※ 3과목: 2024.02.13 - [SQL] - [SQL] 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의 후기 #3 - Having, Join 등

※ 4과목: 2024.02.14 - [SQL] - [SQL] 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의 후기 #4 - Window 함수

 

제5과목

서브쿼리

  서브쿼리는 일종의 가상 테이블을 형성하여 원하는 데이터를 이끌어내기 위해 쓴다. 세분화된 카테고리에 들어가기 앞서 모든 서브쿼리는 반드시 괄호 안에 들어가야 한다는 점을 밝혀둔다. 그럼 스칼라 서브쿼리, 테이블 서브쿼리, 상관 서브쿼리를 알아보자.

  스칼라 서브쿼리는 단일값을 출력하고 대개 select, where, having 절에서 사용된다. 

SELECT productName, buyPrice
FROM products
WHERE buyPrice > 
(SELECT AVG(buyPrice) FROM products);


# 참고 코드
SELECT c.customerNumber, c.customername, o.orderdate
FROM customers c, orders o
WHERE c.customerNumber = o.customerNumber 
AND o.orderDate = 
	(SELECT MAX(orderdate) FROM orders WHERE customernumber = c.customerNumber);

SQL Scalar SubQuery

  상기 코드에서 스칼라 서브쿼리는 WHERE 절에서 products 테이블 전체의 평균 구입가라는 단일값을 출력하기 위해 쓰였다.

  참고 코드는 join을 쓰지 않고 서브쿼리로 두 테이블의 데이터를 사용할 수 있다는 것을 보여주기 위해 적어둔다.

  테이블 서브쿼리는 테이블처럼 활용할 수 있는 행과 열을 생성한다. 또 다음과 같은 특성을 지닌다.

• 보통 From 부분에서 사용된다.
• From 절에서 사용될 때, 별칭을 반드시 부여해야 한다.

SELECT productcode, productname, amount
FROM (
	SELECT p.productCode, p.productName, SUM(o.priceEach * o.quantityOrdered) AS amount  
	FROM products p
	JOIN orderdetails o ON p.productCode = o.productCode
	GROUP BY p.productCode) AS sq
ORDER BY amount DESC
LIMIT 5;	

# 다른 풀이
SELECT p.productCode, p.productName, SUM(o.priceEach * o.quantityOrdered) AS amount  
FROM products p
JOIN orderdetails o ON p.productCode = o.productCode
GROUP BY p.productCode
ORDER BY amount desc
LIMIT 5;

SQL Table SubQuery

  sq라는 별칭을 부여한 테이블 서브쿼리를 써서 필요한 데이터를 추출할 가상의 테이블을 만들었고 이렇게 도출한 데이터을 내림차순으로 5개만 조회하도록 했다. 다른 풀이는 서브쿼리를 쓰지 않고 바로 계산한 것이다.

  상관 서브쿼리는 일반적인 서브쿼리와는 다르게 메인쿼리의 각 행을 참조한다. 메인쿼리의 각 행마다 한 번씩 실행 (반복실행)되며 업데이트되고 메인쿼리의 현재 행에 따른 결과를 출력한다 (참조). 반복실행과 참조가 상관 서브쿼리의 특징이라 할 수 있다.

SELECT customername
FROM customers
WHERE customernumber IN (
	SELECT customernumber FROM orders 
	WHERE YEAR(orderdate) = 2003);

SQL Correlated SuqQuery

  Where 절에서 customernumber가 어디에 속해야 하는지 조건을 지정하는 데 사용되었다. 각기 다른 테이블에서 customernumber의 행을 확인해가며 데이터를 갱신하고 조건에 부합하는 데이터를 찾아낸다.

With 구문

  With문은 CTE (Common Table Expression)이라고도 불린다. 서브쿼리와 동일하게 SQL 쿼리의 구조를 단순하는 역할이나 CTE는 쿼리의 첫 부분에서 정의되고 반복 사용이 가능하다는 점에서 서브쿼리에 비해 가독성이 뛰어나다. 

  Group By 구문은 집계값을 나타낼 때 쓰이는데 With 구문과 연계하여 집계값 이외의 데이터를 덧붙이거나 심도있는 분석을 시도할 수 있다.

# 풀이1
WITH pdtable AS (
	SELECT productline, AVG(buyprice) AS avgpr
	FROM products
	group by productline),
total AS (
	SELECT AVG(buyprice) AS totavgpr
	FROM products)
SELECT p.productline, p.avgpr, t.totavgpr  
FROM pdtable p, total t
WHERE p.avgpr > t.totavgpr;

# 풀이2 - 서브쿼리
SELECT p.productLine, AVG(p.buyPrice) AS avgpr, (SELECT AVG(buyprice) FROM products) AS totavgpr
FROM products p 
GROUP BY p.productline
HAVING avgpr > totavgpr;

SQL With 구문

  풀이 1에서는 pdtable과 total이라는 2개의 테이블을 정의하여 전체 제품의 평균 가격보다 라인별 평균 가격이 높은 productline을 도출하였다. 이 코드는 공유하는 키가 없을 때, 두 테이블을 join하는 방법이기도 하다.

  풀이 2는 서브쿼리를 썼다. 스칼라 서브쿼리로 전체 제품의 평균 가격을 구했고 (단일값 도출), Group By로 얻은 라인별 평균값을 HAVING절에 있는 조건에 따라 조회하였다.

---

  이것으로 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 강의를 마치도록 하겠다. 실전에서는 교재처럼 힌트가 주어지지 않기에 원하는 결과를 이끌어내기 위해 더 실력을 갈고 닦아야 할 것이다. 하나의 문제에 다양한 풀이가 있듯이 유연한 발상으로 효율적인 코딩을 할 필요도 있다. 지금까지의 글이 SQLD를 준비하는 데도 도움이 되기를 바란다. 혹시 전체 코드가 필요한 독자가 있다면 다음 깃허브 링크에 접속하여 챙겨가면 된다.