Window 함수를 다룬 메타코드M의 '데이터분석가 입문 필수 SQL 부트캠프' 4과목은 난이도가 상당하다. 다만 이 위기를 극복하면 SQL의 실력자가 될 수 있는 튼튼한 기초를 갖게 될 것으로 기대한다. 지난 강의의 HAVING, JOIN에 이어 유용한 SQL 코드를 익혀보도록 하자.
메타코드M
빅데이터 , AI 강의 플랫폼 & IT 현직자 모임 플랫폼ㅣ메타코드 커뮤니티 일원이 되시기 바랍니다.
mcode.co.kr
※ 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 등
Index
제4과목 - WINDOW 함수
Window는 창문을 뜻한다. 바깥 세상은 굉장히 넓지만 우리는 창문을 통해 극히 일부분을 볼 뿐이다. Window 함수는 창문처럼 특정 구간, 특정 기간을 보여줌으로써 보다 큰 세상을 이해할 수 있는 인사이트를 도출하는 역할을 맡고 있다.
누적합
- PARTITION BY: GROUP BY처럼 특정 카테고리로 묶어서 데이터를 출력. 단, GROUP BY와 달리 집계결과가 아닌 테이블을 그대로 보여줌. (같은 값이 여러 개 나오는 데 반해 GROUP BY는 컬럼 당 데이터값이 하나만 존재.)
- ORDER BY: 어떤 순서로 정렬하며 계산할지 정함.
누적합을 나타내는 4가지 경우를 다음 코드로 살펴보자.
1. SUM() OVER(PARTITION BY ~ ORDER BY ~)
SELECT customernumber, paymentdate, amount,
SUM(amount) OVER(PARTITION BY customernumber
ORDER BY paymentdate) AS total_amount
FROM payments;
Amount의 합계를 구하는데 customernumber별로 paymentdate의 흐름에 따라 출력한다. 103번 고객의 지불 시기에 따라 지불액과 누적 지불액을 구하고 있다.
2. SUM() OVER(PARTITION BY ~)
SELECT customernumber, paymentdate, amount,
SUM(amount) OVER(PARTITION BY customernumber) AS total_amount
FROM payments;
1번 케이스에서 ORDER BY 부분을 제거한 것이다. 그 결과, paymentdate가 1번처럼 정렬되지 않고 뒤죽박죽인 상태로카테고리별 누적지출액을 구한 것을 알 수 있다. 즉, 카테고리만 고려한 코드이다.
3. SUM() OVER(ORDER BY ~)
SELECT customernumber, paymentdate, amount,
SUM(amount) OVER(ORDER BY paymentdate) AS total_amount
FROM payments;
이번에는 PARTITION BY 부분을 삭제했다. 출력 기준으로 삼는 카테고리 (코드상 customernumber)가 사라졌기에 데이터 정렬 기준인 paymentdate에 따라 오름차순으로 누적액을 산출하며 데이터를 출력했다.
4. SUM() OVER()
SELECT customernumber, paymentdate, amount,
SUM(amount) OVER() AS total_amount
FROM payments;
그렇다면 OVER 괄호에 아무것도 넣지 않으면 어떻게 될까? 답은 한꺼번에 sum(amount)를 처리하여 누적지출액을 계산하는 것이다.
LEAD / LAG
- Lead: 다음 행의 데이터를 가져옴.
- Lag: 이전 행의 데이터를 가져옴.
Lead와 Lag도 4가지 경우로 나누어 알아보도록 하자.
1. OVER(PARTITION BY ~ ORDER BY ~)
SELECT o.orderNumber, o.customerNumber, o.orderDate,
LAG(o.orderDate) OVER(PARTITION BY o.customernumber ORDER BY o.orderDate) AS prev_date,
LEAD(o.orderDate) OVER(PARTITION BY o.customernumber ORDER BY o.orderDate) AS next_date
FROM orders o;
orderdate를 가져오는데 customernumber별로 이전일과 다음일을 나타내고 있다.
2. OVER(PARTITION BY ~)
SELECT o.orderNumber, o.customerNumber, o.orderDate,
LAG(o.orderDate) OVER(PARTITION BY o.customernumber) AS prev_date,
LEAD(o.orderDate) OVER(PARTITION BY o.customernumber) AS next_date
FROM orders o;
PARTITION BY에만 조건을 부여한 경우다. 결과 화면만 보면 1번 케이스와 동일하나 데이터의 특성에 따라 다를 수 있기에 주의해야 한다.
3. OVER(ORDER BY ~)
SELECT o.orderNumber, o.customerNumber, o.orderDate,
LAG(o.orderDate) OVER(ORDER BY o.orderDate) AS prev_date,
LEAD(o.orderDate) OVER(ORDER BY o.orderDate) AS next_date
FROM orders o;
ORDER BY만 남기면 customernumber를 무시하고 orderdate에 따라 이전일과 다음일을 가져온다는 것을 확인할 수 있다.
4. OVER()
SELECT o.orderNumber, o.customerNumber, o.orderDate,
LAG(o.orderDate) OVER() AS prev_date,
LEAD(o.orderDate) OVER() AS next_date
FROM orders o;
OVER에 아무 조건을 넣지 않았더니 3번과 같은 결과가 출력되었다.
순위 함수
- ROW_NUMBER: 공동 순위없이 고유 순위 지정.
- RANK: 중복값에 공동 순위를 부여하고, 이 경우 순위를 스킵함. (1, 1, 3, 4, 5)
- DENSE_RANK: RANK와 유사하나 공동 순위가 있는 경우, 순위를 건너뛰지 않음. (1, 1, 2, 3, 4)
SELECT c.customerName, c.creditLimit,
row_number() OVER(ORDER BY c.creditLimit desc) AS row_num,
RANK() OVER(ORDER BY c.creditLimit desc) AS rank_,
DENSE_RANK() OVER(ORDER BY c.creditLimit desc) AS dense_num
FROM customers c
ORDER BY c.creditLimit desc;
순위 함수 바로 옆의 괄호에는 아무것도 넣지 않는 것이 특이했다. 20번째 라인 부근에서 각 순위 함수의 차이점을 찾아볼 수 있다.
다음 함수를 써서 순위를 나타내는 것도 가능하다.
- First_Value(): 가장 처음 오는 row 출력
- Last_Value(): 가장 나중에 오는 row 출력
# 정답 1
SELECT p.productLine, p.productName, p.buyPrice,
FIRST_VALUE(p.productName) OVER(PARTITION BY p.productLine ORDER BY p.buyPrice) AS cheapest,
FIRST_VALUE(p.productName) OVER(PARTITION BY p.productLine ORDER BY p.buyPrice desc) AS expensive
FROM products p;
# 정답 2
SELECT p.productLine, p.productName, p.buyPrice,
FIRST_VALUE(p.productName) OVER(PARTITION BY p.productLine ORDER BY p.buyPrice) AS cheapest,
last_VALUE(p.productName) OVER(PARTITION BY p.productLine ORDER BY p.buyPrice ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED following) AS expensive
FROM products p
ORDER BY p.productLine, p.buyPrice DESC;
정답 1은 productname을 가져오는데 productline별로 buyprice에 따라 가장 싼 것과 비싼 것을 출력하도록 설정했다. 싼 것은 오름차순, 비싼 것은 내림차순으로 나타내고 처음 위치하는 값 (First_value)을 표시했다.
정답 2는 last_value를 사용했다. Unbounded Preceding과 Unbounded Following을 부여해 productline별 buyprice를 비교할 때 모든 buyprice를 체크하고 가장 나중에 오는 값을 취했다. buyprice가 오름차순으로 정렬되므로 마지막에 오는 값이 해당 productline에서 가장 비싼 값이다.
Window Frame
- ROW: 행의 개수로 윈도우 프레임 (비교 범위) 정의.
- RANGE: 같은 값을 가지는 모든 행.
- PRECEDING: 현재 행보다 전(위)에 있는 행.
- FOLLOWING: 현재 행보다 다음(아래)에 있는 행.
- Unbounded Preceding: 현 파티션 (범위)의 첫 행부터 현재 행까지.
- Unbounded Following: 현재 행부터 현 파티션 (범위)의 마지막 행까지.
- Current Row: 현재 행.
SELECT orderNumber, productCode, quantityOrdered,
# 1
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN 1
PRECEDING AND 1 FOLLOWING) AS moving_avg_quantity_1,
# 2
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN
CURRENT ROW AND 1 FOLLOWING) AS moving_avg_quantity_2,
# 3
AVG(quantityOrdered) OVER (ORDER BY orderNumber ROWS BETWEEN 1
PRECEDING AND CURRENT ROW) AS moving_avg_quantity_3,
# 4
AVG(quantityOrdered) OVER (ORDER BY orderNumber RANGE BETWEEN 1
PRECEDING AND 1 FOLLOWING) AS moving_avg_quantity_4
FROM orderdetails;
1. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Ordernumber를 기준으로 이전 1행과 다음 1행의 범위에서 평균을 구한다. (ex. 2번행: (30+50+22)/3 = 34)
2. ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
Ordernumber를 기준으로 현재 행과 다음 1행의 범위에서 평균을 구한다. (ex. 2번행: (50+22)/2 = 36)
3. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
생략.
4. RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
Ordernumber를 기준으로 같은 값을 지니는 행들 (ex. 주문번호 10,101 전체)과 이전 1개 구간 (10,100), 다음 1개 구간 (10,102)의 평균을 구한다. (ex. 5번행 (주문번호 10,101): {(30+50+22+49)+(25+26+45+46)+(39+41)}/10 = 37.3)
SQL Window함수 실전문제 풀이
-- 1
SELECT o.customernumber, o.ordernumber, o.orderdate,
COUNT(o.ordernumber) OVER(PARTITION BY o.customernumber ORDER BY o.orderdate) AS cnt_od
FROM orders o;
-- 2
SELECT o.orderNumber, o.productCode, o.quantityOrdered,
o.quantityOrdered - LAG(o.quantityOrdered) OVER(PARTITION BY o.productCode ORDER BY o.ordernumber) AS diff
FROM orderdetails o;
-- 3 (문제24 - 교재)
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
ORDER BY e.employeeNumber;
- HeidiSQL로 SQL 코드를 입력하는 경우, 테이블에 별칭을 부여한 상태로 '.'을 찍어 코딩하면 컬럼명 리스트가 나타나 코딩하기 쉽다.
- Lead, Lag 함수를 활용해 새로운 파생변수 (컬럼)를 만들 수 있다.
- 2개의 집계값을 괄호에 넣음으로써 표현할 수 있다.
※ 비고
여기서는 메타코드M '데이터분석가 입문 필수 SQL 부트캠프' 4과목 강의에 대한 의문 및 오류사항 등을 다룬다.
- '순위 함수'의 예제 코드를 그대로 입력하면 다른 결과가 나타나므로 order by에 내림차순 옵션을 추가해야 한다.
'순위 함수/Window Frame' 영상에서 교재 PPT 69페이지에 있는 문제24번에 대한 풀이가 없어 문의 중.(문제24 추가, 24.02.18)- '실습' 영상의 내용이 4과목의 주제와 무관한 join, group by 등을 다루고 있다.
