오늘도 메타코드의 SQL 부트캠프 강의 11번째 글, 강의는 메타코드 사이트(https://mcode.co.kr/)에서 구매할 수 있다.
3월 29일(금)에 사전 발표, 4월 5일(금)에 최종 합격자 발표가 났는데 84점으로 고득점 합격하였다. 지난번 시험에선 겨우 58점 받고, 진짜 바쁘게 풀어도 반도 못 푼 것 같은데 이번 시험에서는 일단, 헷갈리는 걸 다 틀려도 70점은 넘겠단 생각을 했다. 그리고 문제도 모르는 거 빼곤 시간 맞춰 다 풀 수 있었다. 부트캠프 강의 들으면서 직접 쳐본 게 많은 도움이 된 것 같다. 히히
🔥 WINDOW FRAME
이게 굉장히 어려운 부분인데, 일단 결과를 보기 전에 예상하기가 힘들고, 노랭이에서 문제 풀면 느낌이 또 다르다...ㅠ
window 함수를 다룰 때는 한 번에 코드를 적으려고 하기보다는 쪼개서 출력한 다음, 확인해 가면서 원하는 값을 구해 나가야 할 것 같다.
마음을 가다듬고....
WINDOW 함수의 기본 틀을 다시 살펴보자. WINDOW 프레임은 윈도우 함수가 적용되는 조건 같은 것이다.
WINDOW_FUNCTION(컬럼1) OVER
(PARTITION BY ~
ORDER BY ~ )
AS 별칭;
ORDER BY 기준컬럼 뒤에 ROWS가 올 수도 있고, RANGE가 올 수도 있다. ROWS는 '행', RANGE는 '범위'라는 뜻인데 이게 어떻게 적용되는지가 중요하다.
📌
위 예에서 첫 번째 AVG 구문을 살펴보자.
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN 1 PRECENDING AND 1 FOLLOWING) AS moving_avg_quantity_1
ROWS는 행을 의미한다고 했다. 별도의 파티션이 없으니, 전체 범위에서 '현재 행'을 기준으로 1개 위, 1개 아래 행의 평균을 계산한다.
quantityOrdered 컬럼의 1행이 30, 2행이 50일 경우, moving_avg_quantity_1의 1행은 현재 행(1행)을 기준으로 하나 전 행(0행? -1행?)의 quantityOrdered값, 현재 행의 quantityOrdered 값, 하나 다음 행(2행)의 quantityOrdered 값을 평균한 것이다. 즉, (30+50)/2 = 40.
2행은 quantityOrdered의 1, 2, 3행을 평균한 값이다. (30+50+22)/3 = 34. 이런식으로 전체 행에 대해 계산이 진행된다.
📌
두 번째 AVG 구문을 살펴보자.
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS moving_avg_quantity_2
현재행과 그다음행의 평균을 계산한다.
1행에 들어갈 값은, quantityOrdered의 1행과 2행 평균 (30+50)/2 = 40
2행에 들어갈 값은, quantityOrdered의 2행과 3행 평균 (50+22)/2 = 36 ... 이런 식의 계산이 마지막 행까지 진행된다.
마지막 행은 다음 값이 없으니 현재 행의 값이 그대로 출력될 것이다.
📌
세 번째 AVG 구문을 살펴보자.
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN 1 PRECENDING AND CURRENT ROW) AS moving_avg_quantity_3
이전행과 현재행의 평균을 계산한다.
1행에 들어갈 값은, quantityOrdered의 -1행과 1행의 평균, -1행은 존재하지 않으므로 1행의 값 30이 그대로 출력된다.
2행에 들어갈 값은,quantityOrdered의 1행과 2행 평균, (30+50)/2 = 40
3행에 들어갈 값은, quantityOrdered의 2행과 3행의 평균, (50+22)/2 = 36이다. 이렇게 마지막 행까지 진행!
📌
마지막, 네 번째 행의 AVG 구문은 조금 다르다. RANGE가 들어가 있다.
AVG(quantityOrdered) OVER (ORDER BY orderNumber RANGE BETWEEN 1 PRECENDING AND 1 FOLLOWING) AS moving_avg_quantity_4
아래 그림을 보자..(복잡하지만 형광펜으로 박스친 부분들만 보면 된다.)
현재 RANGE를 기준으로, 하나 전 RANGE로부터 하나 다음 RANGE까지의 평균이다.
orderNumber가 같은 행들을 하나의 RANGE라고 생각해 보자.
RANGE BETWEEN 1 PRECENDING AND 1 FOLLOWING은 이전 RANGE, 현재 RANGE, 다음 RANGE의 평균이다.
1행의 현재 RANGE는 주문번호가 10,100인 경우를 말한다(노란색 형광펜으로 표시한 부분).
현재 RANGE보다 하나 앞이 존재하지 않으므로, 현재 RANGE와 다음 RANGE의 평균을 계산한다(하늘색 형관펜 표시한 부분).
즉, quantityOrdered (30 + 50 + 22 + 49 + 25 + 26 + 45 + 46 ) / 8 = 36.625가 입력된다.
=> 1행부터 4행까지는 모두 동일한 RANGE에 속해 있으므로 같은 값이 출력된다.
5행은 orderNumber가 10,101로 달라져 두 번째 RANGE가 된다. orderNumber가 10,100인 범위, 10,101(현재)인 범위, 그리고 10,102(다음)인 범위의 평균을 하면,
( 30 + 50 + 22 + 49 + 25 + 26 + 45 + 46 + 39 + 41 ) / 10 = 37.3이 출력된다. orderNumber가 10,101인 5, 6, 7, 8행은 모두 같은 값이 출력됨
실습 문제를 풀어보면... 일단 직원별(group by? partition by?)로 담당 고객 수를 계산하고, 직원별 고객수의 누적 합계를 계산하라고 한다.
주어진 테이블을 먼저 살펴보자.
employees(직원) 테이블에는 직원번호, 이름, 성, extension, 이메일, 부서번호, 누구한테 보고하는지(직속상사), 직업타이틀 컬럼이 있다.
customers(고객) 테이블에는 고객번호, 고객 이름, 담당직원의 이름(contactLastName), 담당직원의 성(contactFirstName), 폰 번호, 주소1, 2, 도시, 등등 컬럼이 있다. 12번 컬럼인 salesRepEmployeeNumber가 담당직원번호다.
일단 계산하라는 게 담당 '고객 수', 누적합으로 구할 것도 직원별 '고객 수' 니까
SUM(고객수) OVER (~ 이렇게 시작하면 될 것 같다.
근데 '고객 수'라는 컬럼은 존재하지 않는다. 만약 SUM(customerNumber)를 하면 고객번호들이 합쳐져(고객번호 101, 102 의 합은 고객 수인 2가 아니라 203이라는 의미 없는 숫자가 됨) 이상한 숫자가 나올 것이다. 그러므로 먼저 직원별 담당 고객 수 집계를 해주어야 한다. 집계랑 관련된 것은 group by.
SELECT salesRepEmployeeNumber AS employeeNumber, COUNT(customerNumber) AS customerCount
FROM customers
GROUP BY salesRepEmployeeNumber;
이렇게만 해서 출력해보면 직원번호별 고객수가 집계된다.
이 직원별 고객수의 '누적합'을 구하라고 한다. 누적합이니까 순서가 중요할 것이다. 다시 결과 예시를 보면 직원번호 순으로 누적합이 계산되고 있으며, 결과가 15행이므로 담당 직원이 없는 고객은 제외되어야 한다(= 담당 직원이 있는 고객만 계산되어야 한다).
직원을 기준으로 담당 고객 수를 계산하기 위해 JOIN을 수행하고, employeeNumber 순으로 정렬한 뒤 누적합(SUM)을 계산해 보자.
1. 조인 수행(employees & customers)
SELECT e.employeeNumber, e.firstName, e.lastName, COUNT(c.customerNumber) AS customerCount
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.employeeNumber;
담당 직원이 존재하지 않는 행이 사라지고, 15행으로 줄어들었다.
2. order by: employee 순, 누적합 계산
SELECT e.employeeNumber, e.firstName, e.lastName, COUNT(c.customerNumber) AS customerCount,
SUM(count(c.customerNumber)) OVER (ORDER BY e.employeeNumber) AS cumulativeCustomerCount
FROM employees e
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.employeeNumber, e.firstName, e.lastName;
이렇게 하면 정답 예시와 동일한 테이블이 생성된다. 이렇게 WINDOW 함수 내용이 끝났다. 이다음엔 서브쿼리와 WITH문을 정리해야 하는데 난이도가 점점 높아진다. 힣..ㅠ 그래도 파이팅...
SQL은 도구다.
연습을 많이 하고 자유자재로 다룰 수 있도록 해서 내 아이디어를 실현 못하는 일이 없게 해야겠다. 🔥🔥🔥
