[SQL] 메타코드 강의 후기_SQL - SELECT 관련 함수 / 서브쿼리 / CTE / 강의 후기

안녕하세요.

빅데이터, AI 강의 플랫폼 메타코드M에서 제공하는

< 데이터분석가 입문 필수 SQL 부트캠프> 를 수강하였습니다.

데이터분석가 입문 필수 SQL 부트캠프 (이미지 클릭 시 강의 링크로 이동합니다.)

 

SQL은 파이썬, R과 더불어 데이터 분석에서 가장 많이 사용하는 언어로,

데이터 분석가로서 데이터를 능숙하게 추출하고 가공할 수 있으려면,

SQL 역량은 선택이 아닌 필수 입니다.

 

이 강의는 데이터분석가 입문을 위한 SQL 입문 강의로, 

기초 SQL 구문부터 심화 구문까지 쉽게 이해할 수 있도록

이론 설명과 예제 풀이를 반복 설명하여

데이터 분석가의 기초를 다질 수 있는 강의입니다.

 

이 포스트는 지난 번 1 ~ 3과목 정리 포스트에 이어서

4 ~ 5과목의 주요 내용 정리입니다.

학습한 자세한 강의 목차는 다음과 같으며,

주요 내용은 SELECT 관련 함수(Window, LEAD/LAG, RANK, Window Frame),

서브쿼리, CTE입니다.

강의 목차

 

 

 

---

SELECT 관련 함수(Window, LEAD/LAG, RANK, Window Frame)

1. Window 함수

• 역할 : SELECT구문에서 사용되며 분석 구간을 변동 시킴
• 종류
  • SUM, AVG, MIN, MAX, COUNT
  • ROW_NUMBER, RANK, DENSE_RANK,LAG,LEAD, FIRST_VALUE, LAST_VALUE
  • OVER(PARTITION BY ~ ORDER BY ~)
• GROUP BY와의 차이점 
  • GROUP BY 는 집계 결과로 조회가 되는 반면,
  • PARTITION BY 는 본래의 TALBE 그대로 출력
• 예시 ) 누적 합

1. over partition by order by

SELECT customerNumber, paymentDate, amount,
SUM(amount) OVER(PARTITION BY customerNumber
ORDER BY paymentDate) AS total_amount
FROM payments ;

 

2. over partition by

SELECT customerNumber, paymentDate, amount,
SUM(amount) OVER(PARTITION BY customerNumber) AS total_amount
FROM payments ;

 

3. over order by

SELECT customerNumber, paymentDate, amount,
SUM(amount) OVER(ORDER BY paymentDate) AS total_amount
FROM payments ;

 

4. over partition by 생략 order by 생략

SELECT customerNumber, paymentDate, amount,
SUM(amount) OVER(ㅁ) AS total_amount
FROM payments ;

 

---

2. LEAD / LAG 함수

• LEAD : 다음 행 데이터 (아래)
• LAG : 이전 행 데이터 (위)
• 예제 ) 각 제품 코드별로 주문된 수량(ORDERNUMBER)을 기준으로 정렬했을 때, 주문 수량의 증분을 계산

SELECT orderNumber, productCode, quantityOrdered,
	quantityOrdered-LAG(quantityOrdered) 
	OVER (PARTITION BY productCode 
				ORDER BY orderNumber) AS quantity_difference
FROM orderdetails ;

 

 

---

3. 순위 함수 / Window Frame

순위 함수 ( ROW_NUMBER, RANK, DENSE_RANK )

• ROW_NUMBER : 중복 없이 고유한 순위 부여
• RANK : 중복 값에 같은 순위 부여, 중복된 숫자만큼 건너뜀(1,1,1,4,5,6)
• DENSE_RANK : RANK와 유사하지만 중복된 숫자를 건너뛰지 않음(1,1,1,2,3,4)

순위 함수 ( First_value() / Last_value() )

• First_value() : 가장 첫 번째 오는 row 조회
• Last_value() : 가장 마지막에 오는 row 조회
• 위 두 함수는 order by의 활용에 따라 결과가 달라짐

WINDOW FRAME(윈도우 프레임)

• ROW : 행의 개수로 윈도우 프레임을 정의
• RANGE : 정렬의 기준이 되는 행의 값을 기준으로 정의
• PRECEDING : 현재 행보다 전에 있는 행들을 의미
• FOLLOWING : 현재 행보다 다음에 있는 행들을 의미
• UNBOUNDED PRECEDING : 현재 파티션의 첫 번째 행부터 현재 행까지의 범위
• UNBOUNDED FOLLOWING : 현재 행부터 현재 파티션의 마지막 행까지의 범위
• CURRENT ROW :현재 행
• 예제 ) 직원 별 담당하는 고객 수 계산 및 각 직원 별 담당 고객 수의 누적 합계

SELECT employeeNumber, firstName, lastName, 
	COUNT(c.customerNumber)  AS customerCount,
	SUM(COUNT(c.customerNumber)) OVER (ORDER BY e.employeeNumber 
	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulativeCustomerCount
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber 
GROUP BY e.employeeNumber;

 

 

---

서브쿼리

1. Scalar Subquery

• 단일 값을 반환하며, 반적으로 select, where, having 절에서 사용
• 모든 서브쿼리는 반드시 괄호 ‘()’ 안에 포함
• 예제 ) 평균 가격(buyprice)보다 비싼 상품을 조회

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

 

---

2. Table Subquery

• 테이블처럼 사용할 수 있는 행과 열을 반환(테이블 반환)
• 일반적으로 from 절에서 사용
• From 절에 사용되는 서브쿼리는 별칭 필수
• 예제 ) 각 제품 별 총 주문 금액(quantityOrdered * priceEach)을 계산하고, 그 결과를 기반으로 상위 5개의 제품만 조회

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

---

3. 상관 서브쿼리

• 일반 서브쿼리와는 달리 메인 쿼리의 각 행을 참조하여 수행
• 메인 쿼리의 각 행마다 한 번씩 실행되며, 메인 쿼리의 현재 행과 연관된 결과를 반환
• 특징
  1. 반복 실행 : 상관 서브쿼리는 메인 쿼리의 각 행에 대해 별도로 실행된다. 따라서 메인 쿼리에 100개의 행이 있다면 상관 서브쿼리도 100번 실행된다.
  2. 참조 : 상관 서브쿼리는 메인 쿼리의 열을 참조할 수 있다. 참조로 서브쿼리는 메인 쿼리의 현재 행에 따라 다른 값을 반환 가능하다.
• 예제 ) 각 고객별로 가장 최근의 주문일을 조회

SELECT 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);

---

Common Table Expression (CTE)

• subquery와 비슷함
• SQL 쿼리의 구조를 단순화하고 복잡한 쿼리를 분해하는 데 유용
• 스크립트 맨 먼저 선언을 해준 다음, 메인 쿼리에서 사용
• With 문 vs subquery (정의 및 가독성)
  1. CTE (WITH 문)
     • 쿼리의 시작 부분에 정의
     • 이름을 가진 임시 결과 집합 생성
     • 효과 : 코드의 가독성 향상 및 CTE 내에서 정의된 쿼리를 메인 쿼리에서 재 사용
  2. 서브쿼리 
     • 메인 쿼리 내에서 일반적으로 한 번만 사용
• 예제 ) 각 제품 라인 별로 제품의 평균 가격과 전체 제품의 평균 가격을 비교하여 전체 평균 가격보다 높은 제품 라인만 조회

• WITH 문(CTE) 활용

WITH ProductLineAvgPrice AS (
	SELECT productLine, AVG(buyPrice) AS avgPrice
	FROM products
	GROUP BY productLine
),
TotalAvgPrice AS (
	SELECT AVG(buyPrice) AS totalAvgPrice
	FROM products
)
SELECT plap.productLine, plap.avgPrice, tap.totalAvgPrice
FROM ProductLineAvgPrice plap, TotalAvgPrice tap
WHERE plap.avgPrice > tap.totalAvgPrice ;

 

• 서브쿼리 활용

SELECT pl.productLine, AVG(buyPrice) AS avgPrice,
		(SELECT AVG(buyPrice) FROM products) AS totalAvgPrice
FROM products pl
GROUP BY pl.productLine
HAVING avgPrice > (SELECT AVG(buyPrice) FROM products);

 

 

---

마치며 ) 강의 후기

• 장점
  • 간단한 예시 외에도 다양한 실습 문제를 다뤄 SQL 문법을 체득할 수 있어 좋았습니다.
  • 강의가 일목요연하며, 입문자가 수강하거나 복습하기에 이해하기 어렵지 않습니다.

실습 예제 예시 - GROUP BY

 

• 아쉬운 점
  • 강의자료 중 예제파일이나 실습파일들이 과목별로 정리가 안 되어 있는 것이 있습니다.
  • 강의에 사용되는 실습파일의 이름 등이 강의 페이지나 PPT 등에 명시되거나 강의 목차별로 통일성 있게 정리되었으면 좋을 것 같습니다.