오늘도 역시 메타코드의 SQL 부트캠프 강의 정리. 강의는 메타코드에서 구매 가능! https://mcode.co.kr/kor/
window 함수는 뭔가 복잡해 보이지만 특징이 명확해서 공부할 때 생각보다 어렵지 않았다. 제일 이해하기 어려운 부분은 rows와 range + lead lag 함수가 나오는 부분이다. 구조적으로 사고해야 하는데 나는 공간감각이 없어서 어려운 것 같기도...
🔥 WINDOW 함수
WINDOW 함수는 SELECT 절에서 사용되며, 특정 구간에서 집계를 할 때 사용한다.
WINDOW_FUNCTION(컬럼1) OVER
(PARTITION BY ~
ORDER BY ~ )
AS 별칭;
📌1) WINDOW_FUNCTION
첫 번째 줄, WINDOW_FUNCTION 부분에는 다양한 함수가 들어갈 수 있다.
| 집계 함수 | 의미 |
| SUM | 파티션 별로 합계 계산 |
| AVG | 파티션 별 평균 |
| MIN | 파티션 내 최소값 |
| MAX | 파티션 내 최대값 |
| COUNT | 파티션 내 행의 개수 |
| 순위 함수 | 의미 |
| RANK | 파티션 내 순위 계산, 동순위 o, 건너뛰기(1위, 2위, 2위, 4위) |
| DENSE_RANK | 순위 계산, 동순위 o, 건너뛰기 x (1위, 2위, 2위, 3위) |
| ROW_NUMBER | 동점이어도 동순위 x, 고유순번 부여 (1위, 2위, 3위, 4위) |
| 행 순서 관련 함수 | 의미 |
| FIRST_VALUE | 파티션에서 가장 처음에 나오는 값을 구함 |
| LAST_VALUE | 파티션에서 가장 나중에 나오는 값을 구함 |
| LAG | 이전 행을 가지고 옴 |
| LEAD | 기본 값은 1로, 다음 행을 가지고 옴 LEAD(컬럼, N): N개 뒤의 행을 가지고 옴 |
| 비율 함수 | 의미 |
| PERCENT_RANK | 파티션 내 처음 값을 0으로, 가장 나중 값을 1로 하여 행의 순서별 백분율 조회 |
| NTILE | 파티션 별로 N 등분한 결과를 조회함 예) 만약 총 8행을 NTILE(3) 한다면, 3행, 3행, 2행으로 3등분 |
자질구레하게 좀 더 있는데 자주 쓰이는 여기까지만 정리!
📌 2) PARTITION BY ~
파티션 바이는 GROUP BY와 유사하다. GROUP BY에서 그룹별로 집계를 하듯이 PARTITION BY는 파티션별로 WINDOW_FUNCTION이 기능한다. PARTITION BY는 생략 가능하다. PARTITION BY가 없으면 테이블 전체를 하나의 파티션으로 인식한다.
PARTITION BY와 GROUP BY의 차이점은, GROUP BY는 집계한 최종 값(예를 들면 합계 값 1개)만 조회가 되는 반면 PARTITION BY는 본래의 테이블 원본을 그대로 출력한다.
📌 3) ORDER BY ~
ORDER BY는 계산 순서를 정해주고, 생략 가능하다. 예를 들어 SUM() 이라는 WINDOW_FUNCTION을 쓰고, ORDER BY를 해주면 파티션 내 정렬 순서에 따라 누적합이 계산된다. 만약 ORDER BY 없이 SUM을 적용하면, 순서를 모르기 때문에 누적합을 계산할 수 없다.
아래에 4가지 예시가 있다.
위 예에서는 누적 합(SUM)을 계산하는데 PARTITION BY CUSTOMERNUMBER 하면 고객번호 별로 PARTITION이 결정된다. 즉, 고객번호 103번이 구매한 내역들 전부가 하나의 파티션, 112번이 구매한 내역 전부가 하나의 파티션 이런 식으로 나뉜다.
그리고 ORDER BY PAYMENTDATE로 지불일 순으로 정렬했기 때문에, TOTAL_AMOUNT에 첫 번째 지불 14,571원, 첫 지불과 두 번째 지불의 누적합 (14,571원 + 6,066원 =) 20,638원, 처음부터 3번째 지불까지 누적합 (14,571원 + 6,066원 + 1,676원 =) 22,341원이 출력된다.
위 예에서는 SUM을 계산하는데, ORDER BY를 지정하지 않았다. WINDOW 함수 내 ORDER BY는 계산 순서를 정해주므로, 순서를 지정하지 않으면 그냥 합계를 구해 버린다. (뭐 부터 계산하고 쌓아 나가야 하는지 컴퓨터는 모르니까!) 즉, 누적합은 구해지지 않는다.
세 번째는 PARTITION BY 없이 ORDER BY만 사용한 경우이다. 이때는 전체 데이터를 하나의 PARTITION으로 생각하고 정렬, 지불일(PAYMENT DATE) 기준으로 전체 누적합이 계산된다.
PARTITION BY도 생략 가능, ORDER BY도 생략 가능하므로 둘 다 생략하는 경우에는 PARTITION별이 아닌 전체 데이터의 단순 합계(SUM)가 계산된다.
문제 1. 'orderdetails' 테이블에서 각 주문별로 주문된 제품의 평균 수량("quantityOrdered")을 계산하세요. (정답 예시는 아래와 같음)
먼저, '각 주문 별로' ~ 어떤 걸 구하라고 했으니 GROUP BY 혹은 PARTITION BY 중 하나일 것이다. 정답 예시를 보면 주문별(orderNumber)로 하나의 집계 값만 있는 것이 아니라 동일한 주문 번호가 반복되고 있으므로 GROUP BY가 아니라 PARTITION BY임을 알 수 있다.
- PARTITION BY orerNumber ~
WINDOW_FUNCTION 위치에는 어떤 함수가 들어가야 할까? 평균 수량을 계산하라고 했으니까 'AVG'를 사용할 것 같다. orerdetails 테이블 내 주문 수량(quantityOrdered) 컬럼이 있으니 이를 평균하면 될 것 같다.
- WINDOW_FUNCTITION: AVG(quantityOrdered) OVER ~
정답 예시에서는 딱히 순서 정보가 없으니까 ORDER BY는 생략된 것 같다.
기본형은 WINDOW_FUNCTION(컬럼) OVER (PARTITION BY ~ ORDER BY ~) AS 별칭;
금방 만든 구문을 모두 합치면,
AVG(quantityOrdered) OVER (PARTITION BY orderNumber) AS avg_quantity_per_order
이렇게 된다. 나머지 SELECT ~ FROM 등 기본적인 구문을 채워주면 완성!
#문제: "orderdetails" 테이블에서 각 주문별로 주문된 제품의 평균 수량("quantityOrdered")을 계산하세요.
SELECT orderNumber, productCode,
AVG(quantityOrdered)
OVER(PARTITION BY orderNumber) AS average_quantity_per_order
FROM orderdetails;
글이 너무 길어져서 LEAD와 LAG는 다음 글에서 정리해야겠다. 그럼 이만... 화르륵🔥
