반응형
안녕하세요.
빅데이터, AI 강의 플랫폼 메타코드M에서 제공하는
< 데이터분석가 입문 필수 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. 상관 서브쿼리
- 일반 서브쿼리와는 달리 메인 쿼리의 각 행을 참조하여 수행
- 메인 쿼리의 각 행마다 한 번씩 실행되며, 메인 쿼리의 현재 행과 연관된 결과를 반환
- 특징
- 반복 실행 : 상관 서브쿼리는 메인 쿼리의 각 행에 대해 별도로 실행된다. 따라서 메인 쿼리에 100개의 행이 있다면 상관 서브쿼리도 100번 실행된다.
- 참조 : 상관 서브쿼리는 메인 쿼리의 열을 참조할 수 있다. 참조로 서브쿼리는 메인 쿼리의 현재 행에 따라 다른 값을 반환 가능하다.
- 예제 ) 각 고객별로 가장 최근의 주문일을 조회
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 (정의 및 가독성)
- CTE (WITH 문)
- 쿼리의 시작 부분에 정의
- 이름을 가진 임시 결과 집합 생성
- 효과 : 코드의 가독성 향상 및 CTE 내에서 정의된 쿼리를 메인 쿼리에서 재 사용
- 서브쿼리
- 메인 쿼리 내에서 일반적으로 한 번만 사용
- CTE (WITH 문)
- 예제 ) 각 제품 라인 별로 제품의 평균 가격과 전체 제품의 평균 가격을 비교하여 전체 평균 가격보다 높은 제품 라인만 조회
- 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 문법을 체득할 수 있어 좋았습니다.
- 강의가 일목요연하며, 입문자가 수강하거나 복습하기에 이해하기 어렵지 않습니다.
- 아쉬운 점
- 강의자료 중 예제파일이나 실습파일들이 과목별로 정리가 안 되어 있는 것이 있습니다.
- 강의에 사용되는 실습파일의 이름 등이 강의 페이지나 PPT 등에 명시되거나 강의 목차별로 통일성 있게 정리되었으면 좋을 것 같습니다.
반응형
LIST
'메타코드M 서포터즈 4기 > 데이터분석가 입문 필수 SQL 부트캠프' 카테고리의 다른 글
[SQL] 메타코드 강의 후기_SQL - 강의 소개 / Database / GROUP BY / JOIN (0) | 2024.04.13 |
---|