[SQL 이해하기] - 13. SQL: 윈도우 함수

 

윈도우 함수는 OVER 절과 함께 사용되며 OVER 절 안의 내용을 요약한다. 예를 통해 살펴보자. 아래 예는 주(state) 당 학생 수를 TOTAL 컬럼에 함께 표시하는 쿼리이다.

SELECT STUDENTID, STUDSTATE, COUNT(*) OVER (PARTITION BY STUDSTATE) AS TOTAL FROM STUDENTS;

 STUDENTID STUD      TOTAL
---------- ---- ----------
      1015 CA            2
      1003 CA            2
      1008 OR            4
      1006 OR            4
      1011 OR            4
      1009 OR            4
      1012 TX            3
      1016 TX            3
      1001 TX            3
      1017 WA            9
      1014 WA            9
      1013 WA            9
      1010 WA            9
      1007 WA            9
      1005 WA            9
      1004 WA            9
      1018 WA            9
      1002 WA            9

 

위 SQL을 보면 SELECT 절 안의 아래 표현이 윈도우 함수이다.

COUNT(*) OVER (PARTITION BY STUDSTATE) AS TOTAL

 

SELECT 절은 SQL의 가장 마지막(ORDER BY 절이 없는 경우)에 실행된다. 따라서 윈도우 함수와 OVER 절도 SQL의 가장 마지막에 실행된다. 위 윈도우 함수는 최종 출력 데이터를 STUDSTATE 컬럼으로 구분(PARTITION)하고 현재 구역의 학생 수 COUNT(*)를 출력한다. 예를 들어 고향이 텍사스(TX)인 학번 1012, 1016, 1001 학생의 TOTAL 컬럼에는 STUDSTATE가 TX인 학생들의 수가 표시된다.

 

윈도우 함수에는 아래와 같은 종류들이 있고 OVER 절과 함께 쓰인다.

순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	SUM, MAX, MIN, AVG, COUNT
행 지정 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

 

SELECT 절 안에 사용되는 윈도우 함수의 기본적인 문법은 아래와 같다.

SELECT 컬럼1, 컬럼2, 윈도우 함수 OVER ([PARTITION BY 기준 컬럼] [ORDER BY 기준 컬럼 ASC/DESC])
FROM 대상 테이블

 

예를 들어 과목(SUBJECT) 별 점수(SCORE)에 따른 학생들의 순위를 구한다면 아래와 같이 쿼리를 작성할 수 있다.

SELECT STUDENTID, SUBJECT, SCORE, RANK() OVER (PARTITION BY SUBJECT ORDER BY SCORE DESC)
FROM MIDTERMEXAMRESULT;

 

RANK() 함수는 같은 순위의 학생이 여럿인 경우 그 다음 순위는 학생 수 만큼 밀린 값을 출력하고(예, 1, 2, 2, 4, 5, 5, 7), DENSE_RANK() 함수는 같은 순위의 학생이 여럿인 경우에도 순위는 항상 밀집되어 있는 연속된 값을 유지한다(예, 1, 2, 2, 3, 4, 4, 5).

 

---

 

윈도우 함수의 PARTITION BY 조건 유/무에 따른 차이를 통해 윈도우 함수의 사용 방법과 그 결과를 정리해보자.

SELECT STUDENTID, STUDSTATE, STUDBIRTHDATE, 
ROW_NUMBER() OVER(PARTITION BY STUDSTATE ORDER BY STUDBIRTHDATE ASC) AS ROW_NUM 
FROM STUDENTS;

 STUDENTID STUD STUDBIRT    ROW_NUM
---------- ---- -------- ----------
      1003 CA   89/03/13          1
      1015 CA   92/05/01          2
      1008 OR   89/08/15          1
      1011 OR   91/04/18          2
      1009 OR   93/12/05          3
      1006 OR   95/07/11          4
      1001 TX   91/10/01          1
      1012 TX   94/01/11          2
      1016 TX   94/03/03          3
      1014 WA   88/10/25          1
      1005 WA   92/08/12          2
      1017 WA   94/03/13          3
      1004 WA   94/07/04          4
      1018 WA   95/04/24          5
      1013 WA   95/09/22          6
      1002 WA   95/11/02          7
      1010 WA   96/03/08          8
      1007 WA   96/08/29          9

 

쿼리를 분석해보면 각 학생의 정보를 출력하면서 학생의 고향 주(STUDSTATE)로 구분된 영역(PARTITION) 안에서 학생의 생일(STUDBIRTHDATE)로 올림차순(ASCENDING, 작은 값이 앞으로 오도록) 정렬하고 정렬된 데이터의 순번(ROW_NUMBER)를 출력하도록 하고 있다. 예를 들어 고향이 텍사스(TX)인 학번 1001, 1012, 1016 학생들을 주를 기준으로 구분하고 생일 순서로 정렬한 후 각각의 학생에게 이 구분된 영역 안에서 순번을 1, 2, 3과 같이 표시한다.

 

PARTITION 조건 없이 다시 같은 윈도우 함수를 사용해보자.

SELECT STUDENTID, STUDSTATE, STUDBIRTHDATE, 
ROW_NUMBER() OVER(ORDER BY STUDBIRTHDATE ASC) AS ROW_NUM 
FROM STUDENTS;

 STUDENTID STUD STUDBIRT    ROW_NUM
---------- ---- -------- ----------
      1014 WA   88/10/25          1
      1003 CA   89/03/13          2
      1008 OR   89/08/15          3
      1011 OR   91/04/18          4
      1001 TX   91/10/01          5
      1015 CA   92/05/01          6
      1005 WA   92/08/12          7
      1009 OR   93/12/05          8
      1012 TX   94/01/11          9
      1016 TX   94/03/03         10
      1017 WA   94/03/13         11
      1004 WA   94/07/04         12
      1018 WA   95/04/24         13
      1006 OR   95/07/11         14
      1013 WA   95/09/22         15
      1002 WA   95/11/02         16
      1010 WA   96/03/08         17
      1007 WA   96/08/29         18

 

'ROW_NUMBER() OVER(ORDER BY STUDBIRTHDATE ASC) AS ROW_NUM' 쿼리를 분석해보면 특정 값으로 출력 결과를 구분하지 않고 전체 결과를 생일(STUDBIRTHDATE)을 기준으로 정렬하고 정렬된 순서에 따라 순번(ROW_NUMBER)을 부여하고 있다.

 

---

 

 

윈도우 함수를 이용해서 집계할 대상 행을 RANGE/ROWS 조건을 이용해 좀 더 자세히 지정할 수 있다. 예를 들면 아래와 같은 방식이다. RANGE/ROWS 조건을 사용하기 위해서는 ORDER BY 절이 반드시 함께 사용되어야 한다. 그래야 정렬 기준 컬럼의 값을 기준으로 RANGE/ROWS 범위를 계산할 수 있기 때문이다.

SELECT STUDENTID, SCORE, 
SUM() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_SUM 
FROM EXAMRESULT;

→ 시험결과(EXAMRESULT) 테이블을 과목 별로 구분한 후(PARTITION BY SUBJECT) 
점수의 내림차순으로 정렬하고(ORDER BY SCORE DESC) 
같은 과목의 처음(RANGE BETWEEN UNBOUNDED PRECEDING) 부터 
현재 행(CURRENT ROW)까지의 합(=누적 합. CUMULATIVE SUM)을 구한다.

 

위 쿼리의 윈도우 함수 그리고 OVER 절을 보면 'SUM() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)'와 같이 SUM() 함수의 적용 대상 행을 좀 더 자세히 지정하였다.

 

관심의 대상이 되는 행을 지정하는 RANGE/ROWS 조건의 기본적인 문법은 아래와 같다.

RANGE	RANGE: 논리적 범위. ORDER BY 기준 컬럼의 값이 같으면 하나의 행으로 취급한다.
RANGE 대신 ROWS 사용	ROWS: 물리적 범위. 범위에 지정한 수 만큼의 행을 그대로 적용한다.
BETWEEN	시작 행 지정
UNBOUNDED	이전 끝 범위까지
UNBOUNDED 대신 숫자 n	이전 n 행까지
PRECEDING	현재 행의 이전 행
AND	관심 대상 종료 행 지정
CURRENT ROW	현재 행 까지
UNBOUNDED 또는 숫자	UNBOUNDED: 끝 범위 까지, 숫자: 이후 n 행까지
FOLLOWING	현재 행의 다음 행

 

예를 들어 현재 행을 기준으로 물리적으로 이전 3개 행 그리고 이후 3개 행의 합(SUM)을 구하고자 한다면 아래와 같이 RANGE/ROWS 조건을 지정할 수 있다.

SUM() OVER(PARTITION BY SUBJECT ORDER BY SCORE DESC ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

 

RANGE와 ROWS의 차이가 무엇인지 헷갈리기 쉬우므로 책(Jonh L. Viescas, 『SQL QUERIES FOR MERE MORTALS』, 4th Ed. Addison-Wesley, 2018. pp.811-814.)에서 예를 가져왔다.

SELECT C.CustCity, C.CustFirstName || ' ' || C.CustLastName AS Customer, COUNT(*) AS Preferences,
SUM(COUNT(*)) OVER (ORDER BY C.CustCity ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalUsingRows,
SUM(COUNT(*)) OVER (ORDER BY C.CustCity RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalUsingRange
FROM Customers AS C INNER JOIN Musical_Preferences AS MP ON MP.CustomerID = C.CustomerID
GROUP BY C.CustCity, C.CustFirstName, C.CustLastName;


CustCity	Customer		Preferences	Total UsingRows	Total UsingRange
--------	---------		-----------	---------------	----------------
Auburn		Elizabeth Hallmark	2		2		2
Bellevue	Estella Pundt		2		4		16
Bellevue	Joyce Bonnicksen	3		7		16
Bellevue	Liz Keyser		3		10		16
Bellevue	Mark Rosales		3		13		16
Bellevue	Sarah Thompson		3		16		16
Kirkland	Darren Gehring		2		18		23
Kirkland	Peter Brehm		2		20		23
Kirkland	Zachary Ehrlich		3		23		23
Redmond		Dean McCrae		2		25		27
Redmond		Kerry Patterson		2		27		27
Seattle		Carol Viescas		3		30		32
Seattle		Doris Hartwig		2		32		32
Tacoma		Deb Waldal		2		34		36
Tacoma		Matt Berg		2		36		36

 

위의 예를 보면 ROWS 조건이 이전 모든 행(UNBOUNDED PRECEDING)과 현재 행 사이 행들의 Preference 컬럼 값의 누적 합을 구하는 반면, RANGE 조건은 ORDER BY 절의 정렬 기준인 CustCity가 동일한 값을 같는 경우 이들을 하나의 행으로 취급한다. 예를 들어 RANGE 조건 아래에서는 CustCuty가 Bellevue로 같은 5명의 고객들은 하나의 논리적인 행으로 취급되고 Preference의 합이 14(2+3+3+3+3)로 같게 표현된다. 따라서 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW의 값으로 2+14 = 16이 표시되게 된다. 이와 같은 RANGE의 동작 방식을 CustCity가 Kirkland인 3명의 고객들에게도 적용해 보면서 RANGE와 ROWS의 차이점에 대해 정리해보기 바란다.

 

만약 윈도우 함수에서 RANGE/ROWS 조건이 생략되었다면 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 기본값(default)으로 적용된다는 점도 기억해 둘 만 하다.

---

 

윈도우 함수의 PARTITION BY 조건과 쿼리의 GROUP BY 조건을 헷갈리는 경우가 있다. 윈도우 함수는 SELECT 구문 안에 사용되는 출력 조건이다. 따라서 GROUP BY 조건이 적용된 출력 대상 데이터를 윈도우 함수가 다시 정리하여 표시하는 역할을 한다. 쿼리의 GROUP BY 조건은 출력할 데이터를 선택하는 역할을 한다. 윈도우 함수와 윈도우 함수에 쓰인 PARTITION BY 조건은 출력 대상 데이터를 요약하고 정리하는 역할을 한다. 따라서 이 둘은 서로 관련이 없다.

 

이어지는 포스팅에서는 윈도우 함수에서 사용되는 함수들의 동작에 대해 정리해보도록 하겠다.

 

*포스팅의 내용에는 제가 잘 못 정리한, 틀린 내용이 있을 수 있습니다. 내용이 틀린 경우 댓글 남겨주시면 검토하고 수정하겠습니다.

 

■