database - 윈도우 함수 (OLAP 함수)

윈도우 함수는 OLAP 함수라고도 한다.

OLAP란, OnLine Analytical Processing의 약자로, 데이터베이스를 사용한 실시간(온라인) 데이터 분석처리다.

윈도우 함수는 이 OLAP 용도로 사용하기 위해 표준 SQL에 추가된 기능이다.

MySQL은 아직도 윈도우 함수를 지원하고 있지 않다..

 

윈도우 함수로 사용할 수 있는 대표적 함수

(1) 집약함수(SUM, AVG, COUNT, MAX, MIN)를 윈도우 함수로 사용

(2) RANK, DENSE_RANK, ROW_NUMBER 등의 윈도우 전용 함수 

 

윈도우 함수 구문

<윈도우 함수> OVER ( PARTITION BY <열 리스트>

                                   ORDER BY <소트용 열 리스트> )

 

중요한 키워드는 PARTITION BY와 ORDER BY다.

이 두가지의 역할을 이해하는 것이 윈도우 함수를 이해하는 열쇠가 된다.

 

 ㅡ RANK 함수

-- 상품분류별로 판매단가가 낮은 순서대로 랭킹표를 만드는 쿼리

SELECT goods_name, goods_classify, sell_price,
	RANK() OVER (PARTITION BY goods_classify
    			ORDER BY sell_price) AS 랭킹
FROM Goods;

PARTITION BY는 순위를 정할 대상 범위를 지정한다. 여기서는 상품 분류마다 순위를 정하였다.

ORDER BY는 어떤 열의 순위를 정할지 지정한다. (ASC 생략)

 

PARTITION BY는 테이블을 가로 방향으로 자르고, ORDER BY가 세로 방향으로 순위를 정하는 역할을 하는 것이다.

 

또한, PARTITION BY를 통해 구분된 레코드 집합을 '윈도우'라고 한다.

여기서 윈도우는 창이 아닌 '범위'를 나타낸다. 이것이 윈도우 함수라는 이름의 유래이다.

 

윈도우 함수는 GROUP BY구의 분류 기능과, ORDER BY구의 순서 정렬 기능을 모두 가지고 있는 것이다.

단, PARTITION BY구에는 GROUP BY구가 가진 집약 기능이 없다.

그래서 RANK 함수를 사용한 결과는 전체 행이 출력된다.

 

ㅡ PARTITION BY를 지정하지 않아도 된다.

앞 쿼리에서 PARTITION BY 구를 제외하면, 

앞에서는 상품분류별로 랭킹을 정했지만 이번에는 상품 테이블 전체에서 랭킹이 정해진다.

이와 같이 PARTITION BY는 테이블을 복수의 부분으로 분류하여 윈도우 함수를 사용하고 싶은 경우의 옵션이다.

 

ㅡ 대표적인 윈도우 함수

RANK 함수 )

랭킹을 산출하되, 같은 순위의 레코드가 복수 존재하면 후순위를 건너뛴다.

 

DENSE_RANK 함수 )

랭킹을 산출하되, 같은 순위의 레코드가 복수 존재해도 후순위를 건너뛰지 않는다.

 

ROW_NUMBER 함수 )

순위에 상관없이 연속 번호를 부여한다. 레코드에 연속 번호를 부여하고 싶을 때 사용.

 

SELECT goods_name, goods_classify, sell_price,
	RANK() OVER (ORDER BY sell_price) AS ranking,
	DENSE_RANK() OVER (ORDER BY sell_price) AS dense_ranking,
	ROW_NUMBER() OVER (ORDER BY sell_price) AS row_num
FROM Goods;

윈도우 함수는 인수를 취하지 않기에 항상 괄호 안은 공란이다.

 

윈도우 함수는 SELECT 구에만 사용할 수 있다. (WHERE, GROUP BY에서 사용 불가)

이유는 DBMS 내부에서 WHERE구나 GROUP BY구에 의한 처리가 끝난 '결과'에 대해 윈도우 함수가 작동하도록 만들어졌기 때문이다.

이런 이유로 SELECT 구 이외에서의 사용은 의미가 없기 때문에 구문상에서도 사용할 수 없도록 제한하고 있다.