[SQLD] 윈도우 함수

윈도우 함수

윈도우 함수(Window Function)는 행과 행간의 관계를 쉽게 정의 하기 위한 함수라고 한다. 관계를 정의한다는 의미는 행간의 연산이나 비교를 할 수 있다는 얘기이다. 서브쿼리를 여러번 이용하면 만들 수 있지만 윈도우 함수를 통해 쉽게 처리할 수 있게 된 것이다. 

윈도우 함수는 주로 분석 함수나 순위 함수, 집계 함수라고도 부르는데 이는 `Group By`와 비슷한 점이 있어서 그런 듯하다. 다만 Groupy by와 차이가 있다면, Group By는 집계된 결과만 보여주지만 윈도우 함수는 행의 수는 그대로 두면서 집계된 행을 추가하는 식이다.(집약의 과정 유무의 차이) 

 

윈도우 함수의 또다른 특징

• 중첩으로 사용할 수 없으나 서브 쿼리로 사용이 가능하다.
• `OVER` 문구가 필수로 포함된다.

윈도우 함수 종류

윈도우 함수는 다음과 같이 작성한다. `WINDOW_FUNC(인수)`는 윈도우 함수를 나타내며 함수에 따라 여러개의 인수가 지정될 수 있다.  `OVER` 내부에는 PARTITION BY는 전체 집합을 소그룹으로 나눌 수 있으며(나누는 기준) ORDER BY는 정렬 순서(순위 지정), WINDOWING 절은 행 기준 범위를 지정할 수 있다. 그렇다면 윈도우  함수에는 어떤 것들이 있을까?

 

• 순위 : RANK, DENSE, ROW_NUMBER
• 집계 : SUM, AVG, MAX, MIN, COUNT
• 행 순서 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

---

순위 함수

각 행의 순위을 구하려면 어떻게 하면 좋을까? 순위 함수를 이용하면 해당 행과 행간의 순위를 구할 수 있다. 

예전에는 `SELECT`절에 서브 쿼리를 사용하여 순위를 만들었지만, 순위 윈도우 함수를 사용하면 쉽게 구현할 수 있다.

그럼 어떤게 더 좋은가 인데.. 서브 쿼리를 사용하면 동일한 테이블을 2번 스캔하기 때문에 윈도우 함수가 테이블 스캔 측면에서 좋은 방식이라고 볼 수 있다. 하지만 단점은 윈도우 함수를 사용하면 기본적으로 정렬 과정이 포함되기 때문에 성능은 저하된다.  서브쿼리로 행의 수를 줄인 다음, 윈도우 함수와 비교하며 사용해보는 것을 권한다.

• RANK : 동일한 순위는 비워놓고 순위를 부여한다. 1등 1등 3등 4등
• DENSE_RANK: 동일한 순위는 공동 순위로 부여한다(비워두지 않는다).1등 2등 2등 3등
• ROW_NUMBER : 동일한 값이라도 고유 순위를 부여하는 함수이다. 1등 2등 3등 4등

SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM(
    SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES, 
           ROW_NUMBER() OVER(PARTITION BY FOOD_TYPE ORDER BY FAVORITES DESC) AS TYPE_FAV_RANK
    FROM REST_INFO) AS INLINEVIEW
WHERE TYPE_FAV_RANK = 1
ORDER BY FOOD_TYPE DESC

 

해당 코드는 프로그래머스에는 예시용으로 사용해보았습니다.

 

그룹 내 집계 함수

파티션 별 윈도우의 집계 값을 구할 수 있다. SQL Server에서는 OVER 내부에 ORDER BY는 지원하지 않는다.

충분히 인지하고 있으니 코드 예시만 넣고 넘어가려고 한다.

SELECT SUM(SALES_AMOUNT) OVER(PARTITION BY PRODUCT_ID) SALES, PRODUCT_ID -- 누적합
FROM ONLINE_SALE
ORDER BY SALES DESC

 

해당 코드는 프로그래머스에는 예시용으로 사용해보았습니다.

 

중앙 값 구하는 함수

SELECT x
FROM MyTable
ORDER BY x
LIMIT 
OFFSET (SELECT COUNT(*)
FROM MyTable) / 2

행 순서 함수

• FIRST_VALUE : 파티션 별 윈도우에서 가장 먼저 나온 값(첫번째 행, 공동 순위 인정 X)을 구한다. MIN을 활용해도 같은 결과가 나온다. 
  • ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내 첫번째 행까지의 범위를 지정한다.
• LAST_VALUE : 파티션 별 윈도우에서 가장 나중에 나온 값을 구한다. MAX를 활용해도 같은 결과가 나온다.
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 현재 행을 포함해서 파티션 내 마지막 행 까지의 범위를 지정한다.
• LAG : 현재 읽혀진 데이터의 이전 값을 알아내는 함수이다. 3개의 인수를 가질 수 있는데 다음과 같다.
  • LAG({입력 컬럼}, {몇번째 앞의 행을 가져올지 수}, {첫번째의 경우 가져올 데이터가 없을 때 변경할 값})
• LEAD : 현재 읽혀진 데이터의 이후 값을 알아내는 함수이다. 3개의 인수를 가질 수 있는데 다음과 같다.
  • LEAD({입력 컬럼}, {몇번째 뒤의 행을 가져올지 수}, {첫번째의 경우 가져올 데이터가 없을 때 변경할 값})

SELECT FIRST_VALUE(REST_ID) OVER(PARTITION BY FOOD_TYPE ), FOOD_TYPE
FROM REST_INFO

SELECT  ENAME, HIREDATE, SAL,
        LAG(SAL) OVER (ORDER BY HIREDATE) PREV_SAL
FROM    EMP
WHERE   JOB = 'SALESMAN' ;

 

해당 코드는 프로그래머스에는 예시용으로 사용해보았습니다.

 

비율 함수

이번엔 그룹 내 해당 행이 얼마나 차지하는지 비율을 확인할 때 사용하는 함수이다. 

• CUME_DIST : 전체 건수에 현재 행보다 작거나 같은 건수의 백분율을 구한다. 누적 백분율
• PERCENT_RANK : 행의 순서 별 백분율로 가장 먼저 나오는 값을 0으로 둔다. 순서별  백분율
• RATIO_TO_REPORT : 파티션 내 전체 컬럼 값에 대해 백분율을 구한다. 파이차트(Pie Chart)로 생각하면 됨
• NTILE : 파티션 별 전체 건수를 N등분한 결과를 구한다. 인수로 등분의 수를 넣는다. 4의 경우, 그룹 4개로 나뉨

SELECT ENAME, SAL,
  ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
  FROM EMP
 WHERE JOB = 'SALESMAN';

 

---

☕️ 포스팅이 도움이 되었던 자료

• [SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자 - 소라고동_↗
• [SQLD]2과목 SQL 기본 및 활용 - 윈도우 함수 zinu.log↗
• GROUP BY vs. PARTITION BY: 유사점과 차이점 - 0ngoingjoy↗

오늘도 저의 포스트를 읽어주셔서 감사합니다.

설명이 부족하거나 이해하기 어렵거나 잘못된 부분이 있으면 부담없이 댓글로 남겨주시면 감사하겠습니다.