[SQL 공부] 메타코드 강의를 통한 SQL 스킬 학습

HJ·2024년 1월 6일
sql메타코드엠
0
post-thumbnail

계기

최근 마인크래프트 프로젝트를 진행하면서 SQL을 사용할 일이 많아졌다. select는 물론이고 join , trigger , view 등 mysql 고급 함수, 기능들을 활용해 보고 있다. sql 관련 공부는 예전에 코딩 테스트 문제를 풀어보면 잠깐이랑, 최근 SQlD 자격증을 공부하면서 했던 것이다라 새로운 기능을 쓸 때마다 찾아보는데 시간이 오래 걸렸다. 따라서 이참에 sql 개념 정리랑 공부도 할 겸 강의를 찾아 공부하게 되었다.

강의 선정

강의를 선정하는 기준은 두 가지였다.

첫째로, 빠른 시간 안에 학습할 수 있는 강의
둘째로, 무료로 제공되는 강의

이 두 가지 선정 기준에 부합하는 강의를 찾다가 메타 코드를 선택하게 되었다. 이유는 최근에 메타 코드에서 무료로 제공되었던 빅 분기 실기 강의를 들었었는데 퀄리티가 너무 좋았으며, 실제로 시험에 너무나도 도움이 되었다. 메타 코드 사이트에 들어가 보니 빅 분기 말고도 다양한 강의들이 제공되어 있었고, 마침 나에게 필요한 SQL 강의가 있었다. 따라서 해당 강의를 듣고 공부하기로 결심했다.

강의 목차


강의 목차는 다음과 같이 되어있었으며 나는 기존에 알던 내용보다 새로운 내용을 배우고 싶었다. 따라서 window 함수로 시작하는 DAY4부터 끝까지 학습을 진행했으며 시간이 난다면 DAY1,2,3도 복습하고 포스팅할 예정이다.

학습

WINDOW 함수

window함수란 select 구문에서 사용되며 분석 구간을 변동시키는 함수이며 특정 기간,구간이라고 생각하면 된다. 구조는 아래와 같다.

partition by는 group by와 비슷하다고 생각하면 된다. 위 구조를 간단하게 해석하자면 column2별로 column1의 sum을 구하는데 column3순서별로 더한다. 여기서 order by , partition by는 생략이 가능하다. 구조로 보면 이해가 안가니 예제를 바로 보자 .다음은 각각 경우에수에 따라 어떻게 될지 예제를 보여준다.

order by , partition by 모두 사용


order by, partition by 모두 사용했을 때 total_amount는 CUSTOMERNUMBER 기준으로 그룹화 시키고 amount의 누적합을 구하는데 paymentdate를 기준으로 오름차순 정렬해서 구하게 된다.

order by 생략


order by를 생략했을때는 정렬의 순서가 사라지므로 partition by에 의해 그룹화된 그룹마다 누적합이 똑같아 진다.

order by , partition by 모두 생략

모두 생략한 경우에는 당연히 정렬,그룹기준이 모두 사라지므로 전체 합으로 채워지게 된다.

문제

문제 ) "orders" 테이블에서 각 고객별(customerNumber)로 주문 날짜(orderNumber)에 따라서 지금까지의 주문 횟수를 계산하세요.


SELECT o.customerNumber, o.orderNumber, o.orderDate,
       COUNT(o.orderNumber) OVER (PARTITION BY o.customerNumber ORDER BY o.orderDate) AS order_count_so_far
FROM orders o;

해당 문제는 간단하다.partition by를 활용해 customerNumber별로 그룹을 묶고 order by를 활용해 orderDate에 따라 정렬시키고 orderNumber의 수를 세주는 구문을 만들면 된다.

window LAG/LEAD

또 하나의 개념으로 LAG,LEAD함수가 있다. 간단하게 말하자면 LEAD다음 행 데이터를 가져오고 LAG는 이전 행 데이터를 가져온다.

order , partition 모두 사용

order만 사용

문제

문제) "orderdetails" 테이블에서 각 제품 코드별로 주문된 수량(ORDERNUMBER)을 기준으로 정렬했을 때, 주문 수량의 증분을 계산하시오..

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

위 문제는 LAG 함수를 사용하는 문제이다. 이전 값을 반환하는 LAG 함수를 사용해 현재 값에서 빼면 중분을 구할 수 있다. 또한 조건에 따라 각 제품 코드별(partition by 사용)로 주문된 수량을 기준으로(order by 사용) 구하게 된다.

window 순위 함수

순위함수는 3종류가 존재한다

Rank : 중복 없이 고유한 순위 부여(예)4,5,5,5,8)
Row_number : 중복된 숫자만큼 건너뜀(예)4,5,6,7,8)
Dense_rank : 중복된 숫자를 건너뛰지 않음 (예)4,5,5,5,6)

First_value() : 가장 첫번째 오는 row 조회
LAst_value() : 가장 마지막에 오는 row 조회

예) first_value(column) over (partition by ~ order by~)

문제

문제) "products" 테이블에서 각 제품 라인별로 가장 비싼 제품의 이름과 가장 싼 제품의 이름을 조회하세요.

SELECT productLine, productname, buyprice, 
       FIRST_VALUE(productName) OVER (PARTITION BY productLine ORDER BY buyPrice ASC) AS cheapest_product,
       FIRST_VALUE(productName) OVER (PARTITION BY productLine ORDER BY buyPrice DESC) AS most_expensive_product
FROM products;

제품 라인별로(PARTITION BY productLine 사용) 먼저 정렬(order by buyprice) 하고 첫번째 값을 뽑아내면 비싼 제품과 , 싼 제품을 뽑아낼 수 있다.

window Frame

윈도우 프레임은 데이터를 정렬하고 특정 범위의 행을 지정하는 방법을 정의한다.

Row : 행의 개수로 윈도우 프레임을 정의
Range : 정렬의 기준이 되는 행의 값을 기준으로 정의
Precending : 현재 행보다 전에 있는 행들을 의미
Following : 현재 행보다 다음에 있는 행들을 의미
Unbounded Preceding : 현재 파티션의 첫 번째 행부터 현재 행까지 범위를 의미(기준부터 위로 전부다)
Unbounded following : 현재 행부터 현재 파티션의 마지막 행까지의 범위를 의미(기준부터 아래로 전부다)
Current Row : 현재행


첫번쨰줄 -> 현재 , 전 , 다음 총 3개의 평균
두번쨰줄 -> 현재 , 다음 총 2개의 평균
세번쨰줄 -> 이전 , 현재 총 2개의 평균
네번쨰줄 -> 구간을 나누고 이전구간,현재구간,다음구간 3구간의 평균

서브 쿼리

서브쿼리(Subquery)는 SQL 문장 내에서 다른 SQL 문장을 포함하는 쿼리이다. 서브쿼리는 주로 다른 쿼리의 결과를 사용하여 더 복잡한 조건이나 연산을 수행하는 데 사용된다. 종류는 다음과 같다.

1. Scalar Subquery

  • 단일값을 반환
  • 일반적으로 select , where , having절에서 사용
  • 모든 서브쿼리는 반드시 괄호 ‘()’ 안에 포함

예) column1 평균 보다 큰 것들만 조회

	where column1 > (select avg(column1) from product);

2. Table subquery

  • 테이블처럼 사용할 수 있는 행과 열 반환
  • 일반적으로 from 절에서 사용
  • from 절에 사용되는 서브쿼리는 별칭 필수

예) 주문 개수가 5개 이상인 고객만 조회 -> 그룹으로 묶고 from절에 놓고 where 조건으로

문제

문제) products와 orderdetails테이블을 이용해 각 제품별로 총 주문금액을 계산하고, 그결과를 기반으로 상위 5개의 제품만 조회하세요.

# 방법1) 서브쿼리 활용
select productCode , productName , totalamount
from (select  p.productCode , p.productName , sum(o.quantityOrded * o.price) as totalamount
from product p 
Join orderdetails on p.productCode = od.productCode
group by p.productCode  , p.productName) as inlineview
order by totalamount DESC
limit 5;

우선 products와 orderdetails두개를 productCode기준으로 join 시키고 p.productCode , p.productName별로 group by를 한후 총 주문금액을 계산한다. 해당 결과 테이블을 inlineview로 이름짓고 만들고 from절에 넣고 정렬후 조회하면 원하는 결과를 얻을 수 있다.

# 방법2) 서브쿼리 이용x
SELECT p.productCode, p.productName, SUM(od.quantityOrdered * od.priceEach) AS totalAmount
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productCode, p.productName
ORDER BY totalamount DESC
LIMIT 5;

서브쿼리를 사용하지 않으면 위와 같이 풀 수 있다.

3. 상관 서브쿼리(correlated Subquery)

  • 메인 쿼리의 각 행을 참조하여 수행
  • 메인 쿼리의 각 행마다 한번씩 실행되며 , 메인 쿼리의 현재 행과 연관된 결과 반환
  • 조인이랑 비슷하다

문제

문제)각 고객별로 가장 최근의 주문일을 조회하세요

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

WITH AS 문

  • 쿼리의 구조를 단순화하고 복잡한 쿼리를 분해하는데유용
  • 코드의 가독성 향상
  • CTE는 쿼리의 시작 부분에 정의되며 이름을 가진 임시 결과 집합을 생성(가독성 향상)
  • CTE내에 정의된 쿼리를 메인 쿼리에서 여러번 재사용가능 vs 서브쿼리 : 서브쿼리는 메인 쿼리 내에서 일반적으로 한번만 사용

예 ) orders 테이블에서 가장 최근 주문을 찾는 쿼리를 작성

select customernumber , max(orderDate) as maxorderdate
from orders
group by customernumber)  
select o.ordernumber , o.orderdate , o.customernumber
from orders o
join latestorders lo on o.customernumber = lo.customernumber and o.orderdate = lo.maxdrderdate;
#ordernumber 까지 같이 보기위해 LatestOrders 과 기존 orders 테이블 조인

LatestOrders 테이블을 만들고 (customernumber별 가장 최근 주문) LatestOrders테이블을 원본 테이블인
orders테이블과 join해서 select문으로 조회를한다. 이렇게 하는 이유는 group by로 customernumber묶었기때문 계산된 maxorderdate는 나올 수 있지만 ordernumber와 orderdate는 한번에 나올 수 없기 떄문이다. 따라서 조건에 맞는 값만 가상테이블로 만들어서 원본과 inner join 하고 원하는 값들만 select로 조회한다.

문제

이제 적용하기 위해 문제를 적어풀어보자.

문제) products 테이블에서 각 제품 라인별로 평균 제품 가격을 계산하세요. 그리고 이 평균 가격보다 높은 가격을 가진 제품들만을 해당 제품 라인별로 조회하세요.

WITH AveragePrice AS (
    SELECT productLine, AVG(buyPrice) AS avgPrice
    FROM products
    GROUP BY productLine
)
SELECT p.productName, p.productLine, p.buyPrice, a.avgPrice
FROM products p
JOIN AveragePrice a ON p.productLine = a.productLine
WHERE p.buyPrice > a.avgPrice;

위 문제와 비슷하게 제품 라인별로 평균 제품 가격을 계산된 AveragePrice가상 테이블을 만들고 원본 products 테이블과 조인해 필요한 부분만 추출한다.

문제) 각 제품 라인별로 제품의 평균 가격과 전체 제품의 평균 가격을 비교하여 전체 평균 가격보다 높은 제품 라인만 조회하세요.

# 방법1) WITH AS 활용
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;

라인별 제품 평균 가격이 계산된 ProductLineAvgPrice 테이블 , 전체 제품의 평균 가격이 계산된 TotalAvgPrice을 만들고 조회한다. 여기서 join을 FROM ProductLineAvgPrice plap, TotalAvgPrice tap 이렇게 사용했는데 이유는 join기준이 될 공통된 column이 없는 경우이기 때문이다. 

# 방법2) 서브쿼리로 풀기
select pl.productline,avg(buyprice) as avgprice , (select avg(buyprice) from products) as totalprice
from procuts pl
group by pl.productline
Having avgPrice > (select avg(buyprice) from proeducts);

서브쿼리 Scalar Subquery로 풀면 더 간단하게 풀 수 있다.

문제) 각 직원별로 맡은 고객들의 평균 크레딧 한도를 계산하고, 크레딧 한도의 평균이 $100,000 이상인 직원만 조회하세요.

# 방법1) with as 활용
WITH EmployeeAvgCredit AS (
    SELECT salesRepEmployeeNumber, AVG(creditLimit) AS avgCredit
    FROM customers
    GROUP BY salesRepEmployeeNumber
)
SELECT e.employeeNumber, e.firstName, e.lastName, eac.avgCredit
FROM employees e
JOIN EmployeeAvgCredit eac ON e.employeeNumber = eac.salesRepEmployeeNumber
WHERE eac.avgCredit >= 100000;
# 방법2) subquery 활용
SELECT 
    e.employeeNumber, e.firstName, e.lastName, eac.avgCredit
FROM employees e
JOIN (
    SELECT salesRepEmployeeNumber, AVG(creditLimit) AS avgCredit
    FROM customers
    GROUP BY salesRepEmployeeNumber
) eac ON e.employeeNumber = eac.salesRepEmployeeNumber
WHERE eac.avgCredit >= 100000;

# 방법3) join활용
SELECT e.employeeNumber, e.firstname, e.lastname, AVG(c.creditLimit) AS avgcredit
FROM customers c
JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
GROUP BY e.employeeNumber
HAVING avgcredit > 100000;

세 방법 모두 위에서 했던 방식과 매우 유사하므로 자세한 설명은 생략하겠다. 쿼리가 얼마나 길어지는지에 따라 적절하게 with as 나 subquery를 활용하면 된다. 마지막 join 방법은 firstname, lastname이 숫자가 아닌 문자 형태이므로 집계 함수를 사용했어도 같이 활용 가능하다.

후기

메타 코드 강의에 매우 만족한다. 이 강의 덕분에 이론으로 공부만 했던 window 함수, 어려워서 활용하지 못했던 subquery , with as 개념을 잡을 수 있었다. 강의 구성 자체가 이론 -> 예제 -> 실습 -> 과제로 이루어져 있어서 배운 내용들을 바로 고민하면서 적용시킬 수 있고 문제 난이도도 딱 적당한 것 같다 좋다. 또한, 강사님에 딕션도 좋아 핵심 내용을 쉽게 습득할 수 있다. 시간이 된다면 아직 듣지 못한 강의들도 들으면서 개념을 더 정리해야겠다.


강의중 이해가 안가는 내용이 있다면 위 사진과 같이 질문 게시판을 통해서 쉽게 가능하다.

SQL을 공부하려고 하는데 기초가 필요한 사람에게 메타코드 강의를 추천한다. 물론 SQL 말고도 다양한 특강 , 강의 , 커뮤니티등이 형성 되어있으니 데이터 직무로 관심있는 사람들에게 매우 추천한다!!

메타코드 공식 사이트 : https://mcode.co.kr/

<SQL 강의 유튜브>

#메타코드 #메타코드M #SQL #데이터분석 #MYSQL #기초 #SQL공부 #데이터

profile
HJ
데이터 공부 기록
이전 포스트

프로젝트(minecraft서버 운영) (2)

다음 포스트

[SQL 공부] 메타코드 강의를 통한 SQL 스킬 학습(JOIN)

0개의 댓글

관련 채용 정보