[SQLD] 2과목 2장 SQL 활용 (3) 윈도우 함수, Top N 쿼리

4. 윈도우 함수

윈도우 함수의 개요

윈도우 함수 기초 개념

• 윈도우(WINDOW)는 '전체 데이터 중 특정 부분만 발췌해서 본다'라는 것에서 유래, 분석 함수라고도 불림
• 칼럼을 기준으로 행의 데이터를 필터링하거나 조건값을 반환하는 전통적 개념과 달리 전체 데이터의 행들을 파티션으로 나눠 영역을 설정하고, 그 영역 안에서 행과 행 간의 관계를 쉽게 정의하기 위해 만들어짐
• 관계형 데이터베이스 SQL의 단점(행과 행 간의 관계 정의 및 비교가 어려움)을 보완하기 위해 만들어짐

윈도우 함수 종류

• 순위 함수: ROW_NUMBER(), RANK(), DENSE_RANK()
• 집계 함수: SUM(), AVG(), COUNT(), MAX(), MIN(), (집계 함수 + OVER)
• 행 순서 관련 함수(오프셋 함수): LAG(n), LEAD(n), FIRST_VALUE, LAST_VALUE, NTH_VALUE(n)
• 비율 함수: CUME_DIST(), PERCENT_RANK(), NTILE(n), RATIO_TO_REPORT, 
• 분석 함수(SQLD 범위 아님): CORE, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REFILSXY

윈도우 함수 형식

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

• OVER 키워드 필수로 포함 (GROUP BY 없이도 집계가 가능)
• ROWS는 물리적인 결과 행 수, RANGE는 논리적 값에 의한 범위 => 둘 중 하나 선택해서 사용
• WINDOW_FUNCTION: 기존에 사용하던 함수 혹은 새롭게 WINDOW 함수용으로 추가된 함수
• ARGUMENTS(인수): 함수에 따라 0~N개의 인수 지정
• PARTITION BY 절: 전체 집합을 기준에 따라 소그룹으로 나눌 수 있음
• ORDER BY 절: 어떤 항목에 대해 순위를 지정하고자 할 때 ORDER BY 절 기술
• WINDOWING 절: 함수의 대상이 되는 행 기준의 범위(계산이 되는 범위)를 세밀하게 지정 (SQL Server에서는 지원 X)

 

순위 함수

ROW_NUMBER()

• SQL에서 각 행에 고유한 순위 번호를 할당하는 윈도우 함수
• 주로 데이터의 순서를 매기거나 특정 기준에 따라 행을 나열할 때 사용
• 형식 : ROW_NUMBER() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])
  • ROW_NUMBER() 뒤에 OVER 절이 오며 두 가지 옵션을 괄호로 묶어 사용
  • PARTITION BY [파티션 기준]: 행을 기준으로 구분해 나누는 옵션. 생략 시 전체 결과 집합에 대해 순위 매김.
  • ORDER BY [정렬 기준]: 순위를 매길 때 사용할 정렬 기준으로 반드시 지정해야 함. (지정하지 않을 시 에러)
• 동일한 순위를 지정하고 싶을 때: RANK(), DENSE_RANK()를 사용하거나, ORDER BY 절에 추가로 칼럼 지정

RANK()

• ROW_NUMBER() 함수처럼 순위를 매길 때 사용되지만, 동일한 값이 있을 때 동일한 순위를 부여하고 다음 순위를 건너뜀.
• 형식 : RANK() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])
  • RANK() 뒤에 OVER 절이 오며, 아래 두 가지 옵션을 괄호로 묶어 사용
  • PARTITION BY [파티션 기준], ORDER BY [정렬 기준] 옵션은 ROW_NUMBER 함수와 동일

DENSE_RANK()

• ROW_NUMBER(), RANK() 함수처럼 순위를 매길 때 사용되지만, 동일한 값이 있을 때 동일한 순위를 부여하고 다음 순위를 건너뛰지 않고 연속된 순위를 매김
• 형식 : DENSE_RANK() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

 

집계 함수

SUM()

• 지정된 칼럼의 숫자 값들을 계산
• NULL 값은 무시되므로, NULL을 0으로 처리하고 싶다면 COALESCE 혹은 NVL 함수 함께 사용

참고) COALESCE, NVL 차이점
- NVL은 오라클 전용, COALESCE는 SQL 표준
- NVL은 정확히 두 개의 매개변수만 사용하지만 COALESCE는 두 개 이상의 매개변수를 사용
- NVL은 두 번째 매개변수를 항상 평가, COALESCE는 NULL이 아닌 값을 찾으면 그 이후 매개변수는 평가하지 않음(Short-circuit evaluation)

AVG()

• 특정 칼럼의 평균 값을 계산
• NULL 값은 무시되므로, NULL을 0으로 처리하고 싶다면 COALESCE 혹은 NVL 함수 함께 사용

COUNT()

• 행의 개수를 계산하는 집계 함수, 특정 칼럼이나 전체 행의 개수를 세는 데 사용
• NULL 값은 무시되므로, NULL을 0으로 처리하고 싶다면 COALESCE 혹은 NVL 함수 함께 사용

MAX()

• 특정 칼럼의 가장 큰 값을 찾는 데 사용, 주로 수치 데이터, 날짜, 문자열 등 비교 가능한 값에 대해 적용
• NULL 값은 무시됨

MIN()

• 특정 칼럼의 가장 작은 값을 찾는 데 사용
• NULL 값은 무시됨

집계 함수 + OVER

• 집계 함수와 OVER 절을 함께 사용하면 집계 함수가 윈도우 함수로 작동하여 데이터의 특정 '윈도우' 또는 '프레임' 내에서 계산 수행
• OVER 절은 PARTITION BY와 ORDER BY를 포함할 수 있음 => 집계 함수의 적용 범위 정의

 

행 순서 관련 함수(오프셋 함수)

LAG(n)

• 데이터 집합 내에서 특정 행을 기준으로 n번째 이전 행의 값을 가져오는 데 사용
• 데이터 분석과 시계열 처리에서 유용 (ex. 주식 데이터에서 가격 변화량 예상, 월별 매출의 증감, 부서별 매출 순위 비교 및 분석 등)
• 형식 : LAG(칼럼명, [위치 n], [이전 행이 없을 때 반환할 값]) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

LEAD(n)

• LAG(n) 함수의 반대, 데이터 집합 내에서 특정 행을 기준으로 n번째 이후 행의 값을 가져오는 데 사용
• 형식 : LEAD(칼럼명, [위치 n], [이후 행이 없을 때 반환할 값]) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

FIRST_VALUE()

• 데이터 집합 또는 파티션 내에서 첫 번째 값을 반환
• 형식 : FIRST_VALUE(칼럼명) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준] [WINDOW FRAME])

LAST_VALUE()

• 데이터 집합 또는 파티션 내에서 마지막 값을 반환
• 형식 : LAST_VALUE(칼럼명) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준] [WINDOW FRAME])
• 기본적으로 현재 행까지만의 데이터를 고려하기 때문에, LAST_VALUE() 함수에서 프레임절을 통해 어디부터 어디까지의 값을 읽어야 하는지 명시해주어야 함
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (모든 ROW에 대해 파티션의 첫 번째 행부터 시작해서 파티션의 마지막 행까지)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (현재까지의 모든 행)
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING (현재 행과 그 전후의 행)

NTH_VALUE(n)

• 데이터 집합 또는 파티션 내에서 지정된 순서의 값을 반환
• 형식 : NTH_VALUE(칼럼명, n) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준] [WINDOW FRAME])

 

비율 함수

CUME_DIST()

• 누적 분포 함수로, 데이터 집합 내에서 특정 값 이하의 값들이 차지하는 비율을 계산해 0과 1 사이의 값을 반환 (특정 값의 누적 분포를 계산하는 데 사용)
• 값의 누적 비율이 아닌 행의 개수에 따른 누적 비율임에 주의
• 형식 : CUME_DIST() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

PERCENT_RANK()

• 데이터 집합 내에서 각 값의 백분위 순위를 계산하여 첫 번째 행의 백분위 순위는 0으로 시작해 0과 1 사이의 값을 반환
• PERCENT_RANK() = 순위 - 1 / 전체 행의 개수 - 1
• PERCENT_RANK()는 순위 기반으로 0부터 시작하고, CUME_DIST()는 누적 분포 기반으로 0보다 큰 값부터 시작한다는 점이 다름
• 형식 : PERCENT_RANK() OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

NTILE(n)

• 데이터 집합을 n 개의 동일한 크기의 그룹으로 나누고, 각 행에 그룹 번호를 부여
• 형식 : NTILE(n) OVER (PARTITION BY [파티션 기준] ORDER BY [정렬 기준])

---

5. Top N 쿼리

ROWNUM 슈도 칼럼

슈도 칼럼(Pseudo Column)

• 데이터베이스 시스템에서 제공하는 특별한 종류의 칼럼으로 실제로 존재하는 데이터가 아니라, 가상의 칼럼을 만들어 쿼리에서 일반 칼럼처럼 사용 가능
• 슈도 칼럼의 특징: 데이터베이스 시스템에 의해 자동 생성 및 관리, 읽기 전용, 쿼리 실행 시 동적으로 값 할당
• 오라클의 슈도 칼럼: ROWNUM(쿼리 결과의 각 행에 순차적 번호 부여), ROWID(각 행의 고유 식별자), LEVEL(계층적 쿼리에서 트리 레벨 표시), CURRVAL / NEXTVAL(시퀀스 관련 작업에 사용)
• SQL Server의 슈도 칼럼: @@ERROR, @@IDENTITY
• MySQL의 슈도 칼럼: FOUND_ROWS(), ROW_COUNT()

ROWNUM

• 결과 집합에서 각 행에 순차적으로 고유한 번호 할당 (첫 번째 행은 1, 두 번째 행은 2, ... )
• 쿼리 실행 순서에 따라 부여되므로 결과 집합의 행 순서가 중요한 경우 사용
• 특별한 형식 따로 없이 하나의 칼럼처럼 사용 (ex. SELECT ROWNUM, EMPNO, ENAME FROM EMP)

 

TOP 절

SQL Server에서 사용하는 TOP절

• 결과 집합의 상위 N개의 행을 반환하는 데 사용되는 절
• 결과 집합 크기 제한, 쿼리의 성능 최적화, 특정 수의 행만 선택할 때 유용
• SELECT 문과 함께 사용되어 결과 집합에서 상위 N개의 행 / 상위 N%의 행 선택, ORDER BY 절과 함께 사용하면 특정 기준에 따라 상위 N개의 행 선택 가능
• 예시 : SELECT TOP(3) EMPNO, ENAME, SAL  FROM EMP  WHERE DEPTNO = 20  ORDER BY SAL DESC;

오라클에서 사용하는 ROW LIMITING 절

• 결과 집합의 행 수를 제한하는 데 사용되는 절
• FETCH FIRST, OFFSET 절을 사용하여 결과 집합의 상위 N개의 행을 선택하거나 특정 위치에서부터 N개의 행 선택 가능
• FETCH FIRST 예시 : SELECT EMPNO, ENAME, SAL  FROM EMP  ORDER BY SAL DESC  FETCH FIRST 5 ROWS ONLY;
• OFFSET 예시 : SELECT EMPNO, ENAME, SAL  FROM EMP  ORDER BY SAL DESC  OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; (상위 5개 행 건너뛰고, 다음 5개 행 선택해서 반환)

 

---

참고

 

이지패스 2025 SQLD SQL 개발자

◎ 빠르고 효율적인 공부를 위한 SQLD 수험서 방대한 SQL 관련 이론을 SQLD 출제 경향에 맞춰 알기 쉽게 정리하여 시간에 쫓기는 수험생들이 최대한 공부 효율을 높일 수 있도록 구성했습니다. ◎ 초심자, 비전공자를 위해 이해하기 쉽게 풀어 쓴 SQLD 수험서 SQL을 처음 접하는 초심자나 비전공자가 이해하기 쉽도록 상세하게 설명했습니다. 아울러 출제 경향과 중요도를 안내하는 ‘TIP-BOX’, 풍부한 설명을 담은 ‘참고-BOX’를 활용하여 이해하기

저자

전용문

출판

위키북스

출판일

2025.02.04