[SQL입문 부트캠프] 메타코드M 데이터분석가 입문 필수 SQL 부트캠프 강의 후기 - 제 4과목 WINDOW

안상훈·2024년 5월 1일

MetaCodeM SQL개발자 SQL시험일정 SQL시험접수 SQL자격검정실천문제 SQL자격증 metacode sql 메타코드 메타코드M

메타코드M

목록 보기

7/27

개요

본 강의는 메타코드M에서 진행되는 이상훈 강사님의 실무에 적합한 SQL역량을 키우기 위해 준비된 강의입니다.
강의 정보는 아래 이미지 링크를 클릭해 주세요

https://mcode.co.kr/video/list2?viewMode=view&idx=53

1. WINDOWS함수

이 WINDOW구문으로 알려진 함수는 실제 코드의 구성은 아래와 같이 사용된다.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] )
FROM 테이블명 ;

windows함수라고 표현되지만 실제로는 window함수명은 포함되지 않은 구문이며, over구문이 보인다면 window함수로 이해해야 하기에 사실상 over함수로 봐야 하지 않을까.. 하지만 아무튼 이름은 WINDOW함수이다.

전체적인 결과는 Group by함수와 유사하지만, Group by함수는 계산 결과를 요약해서 표현하는 느낌이 강하다면, WINDOW함수는 중간의 계산과정까지 모두 보여주는 느낌이 강하다 볼 수 있다.

WINDOW함수에서는 가장 중요한 부분이 ARGUMENT인데 여기에 적용 가능한 항목으로는
'SUM, AVG, MIN, MAX, COUNT, RANK'
LEAD, LAG'
등이 있다.

우선 첫번째로 WINDOW함수를 활용하여 ARGUMENT로 'SUM'을 적용했을 때의 코드 결과를 확인해보자.

우선 단순하게 GROUP BY을 적용한 결과를 본다면 customerNumber에 맞춰서 요약된 합산 결과를 보여주고 있다.

이 계산 결과를 늘어트려 표현하고 싶다면 WINDOW함수를 적용할 수 있다.

바로 위 사진처럼 GROUP BY을 적용한 결과는 customerNumber을 요약하여 합산한 결과를 보여주지만, WINDOW함수에서 Partition by를 적용한다면 GROUP BY를 적용하기 바로 직전의 합산결과를 각 셀 별로 보여주고 있다.

여기에 추가적으로 order by를 적용한다면 흔히 누적합 이라고 불리는 계산 결과를 WINDOW함수로 구현할 수 있다

따라서 group by함수로 요약된 결과값을 도출하기 전 중간의 계산과정이 보여지는 컬럼을 생성하고 싶다면
WINDOW함수를 적극 활용하는 것이 옳을 듯 하다.

이를 단적으로 보여주는 예가 WINDOW함수에서 Partition by, Order by적용하지 않은 결과라 볼 수 있다.

위 사진처럼 Partition by, Order by를 적용하지 않고 ARGUMENT만을 적용시킨다면 해당 ARGUMENT결과값으로 데이터를 다 채워버린 새로운 COL만 생성된다.

실습

해당 문제를 풀기 위해서는 아래의 3단계를 따라서 문제를 풀이하면 된다.

1) 우선 문제를 풀기 위한 의미있는 COL을 먼저 색인한다

2) 그 후 문제를 풀기위한 ARGUMENT가 평균이니 WINDOW함수에서 ARGUMENT -> AVG만 적용한 계산 COL을 생성한다.

3) 다음으로 문제 풀이를 위하여 Partition by 조건을 적용 -> 이를 통해 OrderNumber 항목별로 그룹화된 평균값을 도출 할 수 있다.

추가로 Order by를 적용한다면 그룹화된 누적평균을 도출해낸다

이 문제 또한 위에서 적용한 단계별 코드 구현을 통해 고객별, 주문날짜, 주문횟수에 따른 결과가 적용된 COL을 구할 수 있다.

계산에 필요한 COL을 먼저 색인한 후, 필요에 따라 정렬

주문횟수 라는 결과를 도출해야 하니 WINDOW함수의 ARGUMENT로 COUNT만을 우선 적용

문제 풀이를 위해Partition by , Order by를 적용하여 해답 도출, 이때 Order by가 적용되었으니 FROM구문 아래 적용한 컬럼 정렬 구문은 삭제해도 된다.

2. WINDOWS함수 - LAG, LEAD

WINDOW함수의 ARGUMENT로 적용 가능한 SUM, AVG, COUNT와 다르게
LEAD, LAG함수는 데이터셋의 ROW항목에서 이전값, 이후값을 그냥 복사해서 붙인 새로운 COL을 만드는 함수이다.
이 함수를 쓰는 이유는 다른 개발언어에서 +=, -=와 같이 자기 자신과 새로운 값을 연산 한 후 자기 자신에게 덮어씌우는 연산을 수행하는 과정을 도출하기 위해 만들어진 구문이라 볼 수 있다.

우선 위 문제에 관해서 LEAD, LAG를 적용한 WINDOW함수의 계산 과정을 살펴보도록 한다.

첫번째로 필요한 컬럼들만 먼저 색인한 뒤

LEAD, LAG 구문만 적용하고 WINDOW함수의 추가적인 조건은 부여하지 않은 상태를 보자

LAG 구문은 이전 행 데이터를 복사해서 가져오고
LEAD 구문은 이후 행 데이터를 복사해서 가져오는 방식이다.

여기에 Partition by를 적용하면 CustomerNumber그룹별로 나뉘어서 이전/이후 행 데이터를 복사해서 가져오게 되며,

Order by까지 적용한다면 OrderDate 컬럼에 맞춰서 정렬 후 정렬된 행에서 이전/이후 행 데이터를 복사해서 가져온다.

이것의 응용이 증분계산(+=, -=)이다.

실습

위 사진처럼 '증분'에 해당하는 계산을 WINDOW함수로 구현해 보도록 하겠다.

1) 일단 의미있는 컬럼들을 먼저 색인

2) ARGUMENT로 LAG적용

3) ARGUMENT로 LAG적용된 컬럼에 원본 COL을 빼는 연산을 적용하여 증분 칼럼으로 변환

4) productCode별로 그룹화를 해야하기에 Partition by조건을 적용

5) 마지막으로 OrderNumber별로 정렬을 수행한 증분값을 도출해 내기 위하여 Order by 조건을 적용한다.

증분 계산의 계산과정을 좀더 가독성 있게 보이고 싶다면 3)과정을 생략한 COL을 추가로 생성하여 최종 결과 COL왼편에 적용시키면 된다.

3. WINDOWS함수 - RANG, ROW_NUMBER, DENSE_RANK

다음으로 공부할 항목은 Window함수 구문에서 순위를 보여주는 조건이다
이 함수의 경우 OVER구문에 어떠한 방식으로 정렬할 지를 알려주는 Order by구문을 필수적으로 입력해 줘야 한다.

위 사진처럼 Order by 조건으로 정렬을 한 뒤 순위를 구해주는 ROW_NUMBER, RANK, DENSE_RANK 조건을 걸고 그 결과를 보여주고 있다.
ROW_NUMBER는 중복이 되더라도 고유한 순위를 매기며,
RANK는 중복이 된다면 중복된 항목에 관해서 같은 순위를 매기고,
Dense_RANK는 중복이 된 항목에 대해서 중복된 순위를 매기나, 내부에서는 고유한 순위를 매긴 뒤 중복된 항목이 넘어가면 내부에서 계산된 고유한 순위 뒷 순위값을 할당한다 보면 된다.

그 다음으로 순위를 매기는 함수인 First_Value()와 Last_Value()함수가 존재하는데