[데이터베이스개론·SQL] 250121

윈도우 함수

윈도우 함수 옵션

윈도우 함수에서 특정 행(Row)이나 범위(Window)를 지정하는 추가 설정이다. 옵션을 활용해 데이터 처리 범위를 유연하게 조정 가능하다.

ROWS와 RANGE

• ROWS : 물리적 행(Row)을 기준으로 범위를 지정.
• RANGE : 논리적인 값(Value)을 기준으로 범위를 지정.
• 차이점
  • ROWS는 행의 순서.
  • RANGE는 값의 범위에 따라 결정.

ROWS와 RANGE 옵션의 예제는 다음과 같다.

--ROWS 옵션
--현재 행을 기준으로 이전 2개 행과 현재 행의 합계를 계산
SELECT EMPLOYEE_ID, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY ROWS 2 PRECEDING) AS SALARY_SUM
FROM EMPLOYEES;

--RANGE 옵션
--현재 행을 기준으로 급여가 현재 급여보다 작거나 같은 행의 합계를 계산
SELECT EMPLOYEE_ID, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY RANGE UNBOUNDED PRECEDING) AS SALARY_SUM
FROM EMPLOYEES;

주로 RANGE보다 ROWS를 더 많이 사용한다.

UNBOUNDED PRECEDING

윈도우의 시작 범위를 첫 번째 행으로 지정한다.

--현재 행까지의 누적 합계
SELECT EMPLOYEE_ID, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY ROWS UNBOUNDED PRECEDING) AS CUM_SUM
FROM EMPLOYEES;

CURRENT ROW

현재 행(Row)을 기준으로 윈도우를 설정한다.

--현재 행부터 이후의 값 합계
SELECT EMPLOYEE_ID, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FUTURE_SUM
FROM EMPLOYEES;

FOLLOWING

현재 행 이후의 특정 행 범위를 설정한다.

--현재 행 이후 두 개의 행까지 합계
SELECT EMPLOYEE_ID, SALARY,
  SUM(SALARY) OVER (ORDER BY SALARY ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS SUM_NEXT_2
FROM EMPLOYEES;

ORDER BY SALARY ROWS 2 PRECEDING는 정상적으로 작동하지만 ORDER BY SALARY ROWS 2 FOLLOWING은 작동하지 않는다. 이는 데이터를 처리할 때 이후 행이 부족한 경우 참조가 없기 때문에 오류를 발생시키기 때문이다.

RANGE UNBOUNDED PRECEDING과는 다르게 RANGE UNBOUNDED FOLLOWING은 작동하지 않는다. 이는 RANGE는 현재 값의 범위로만 정의 가능하며, 현재 값 이후는 동작하지 않기 때문이다.

FOLLOWING은 BETWEEN 뒤에 사용이 가능하다.

복합 옵션 예제

복합 옵션으로 윈도우 설정이 가능하다.

--부서별 급여의 누적 합계
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID
    ORDER BY SALARY ROWS UNBOUNDED PRECEDING) AS CUM_SALARY
FROM EMPLOYEES;

옵션 조합에 따른 의미

옵션 조합	설명
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRNET ROW	첫 번째 행부터 현재 행까지의 범위 설정 (누적 합계, 누적 평균 등에 사용)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	현재 행부터 마지막 행까지의 범위 설정
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	현재 행을 기준으로 이전 1행, 현재 행, 이후 1행을 포함한 범위 설정
ROWS UNBOUNDED PRECEDING	첫 번째 행부터 현재 행까지의 범위 설정 (누적 계산 시 간단히 사용)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	값의 범위로 첫 번째 값부터 현재 값까지 설정 (누적 계산)
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	값의 범위로 현재 값부터 마지막 값까지 설정
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING	현재 값 기준으로 $\pm 100$ 범위 내 값을 포함 (논리적 값 범위에 따라 설정)
ROWS CURRENT ROW	현재 행만 포함
RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING	첫 번째 행부터 현재 행의 이전 1행까지 포함
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW	현재 행 기준으로 이전 2행과 현재 행 포함

옵션 사용 시 유의사항

• ROWS와 RANGE는 용도에 따라 신중히 선택.
• 데이터셋이 클 경우 성능에 유의.
• UNBOUNDED PRECEDING, FOLLOWING 옵션의 범위를 명확히 이해.

윈도우 함수 예제

--ACCOUNTS 테이블에서 고객별 누적 잔액(BALANCE)을 계산하여 출력
SELECT CUSTOMER_ID, ACCOUNT_ID, BALANCE,
  SUM(BALANCE) OVER (PARTITION BY CUSTOMER_ID
    ORDER BY ACCOUNT_ID) AS CUM_BALANCE
FROM ACCOUNTS;

--LOANS 테이블에서 지점(BRANCH_ID)별 최고 대출 금액을 계산하여 출력
SELECT BRANCH_ID, LOAN_ID, AMOUNT,
  MAX(AMOUNT) OVER (PARTITION BY BRANCH_ID) AS MAX_LOAN_AMOUNT
FROM LOANS;

--LOANS 테이블에서 대출 상태(STATUS)별 건수를 계산하여 출력
SELECT STATUS, LOAN_ID,
  COUNT(LOAN_ID) OVER (PARTITION BY STATUS) AS STATUS_COUNT
FROM LOANS;

계층 쿼리

계층 쿼리

데이터가 부모-자식 관게로 구성된 계층적 구조를 조회하기 위한 쿼리이다. 트리 구조 데이터를 표현하고 탐색하는 데 사용한다.

계층 쿼리의 동작 원리는 다음과 같다.

• START WITH : 계층 구조의 시작점 지정.
• CONNECT BY : 부모-자식 관계를 정의.
• PRIOR : 계층의 방향성을 설정.
• ORDER SIBLINGS BY : 동일 레벨의 데이터 정렬.
• LEVEL : 계층 깊이를 나타내는 가상 컬럼.

ORDER BY를 사용하면 계층 구조가 무너져버린다.

계층 쿼리의 기본 문법은 다음과 같다.

SELECT 컬럼명, LEVEL
FROM 테이블명
START WITH 시작 조건
CONNECT BY PRIOR 자식컬럼 = 부모컬럼;

--계층 쿼리 예시
SELECT EMPLOYEE_ID, NAME, LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

LEVEL 활용 예제

--직원의 계층 깊이를 조회(순방향)
SELECT EMPLOYEE_ID, NAME, LEVEL
FROMO EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

--계층 시각화
SELECT EMPLOYEE_ID, LPAD(NAME, LEVEL * 9, ' ') AS NAME, LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

SYS_CONNECT_BY_PATH 활용 예제

--계층 구조의 경로를 조회
SELECT EMPLOYEE_ID, NAME,
  SYS_CONNECT_BY_PATH(NAME, ' -> ') AS PATH
--  LTRIM(SYS_CONNECT_BY_PATH(NAME, ' -> '), ' -> ') AS PATH
--  SUBSTR(SYS_CONNECT_BY_PATH(NAME, ' -> '), 5) AS PATH
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

계층 구조에서 특정 조건 필터링

--사번이 56번인 직원의 상사들만 계층 구조로 조회(역방향)
SELECT EMPLOYEE_ID, NAME, DEPARTMENT_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 56
CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID;

START WITH의 조건에 해당하는 데이터를 시작으로 하며, PRIOR이 지정된 컬럼에서 반대편 컬럼으로 이동하게 된다.

순방향 : PRIOR 자식 컬럼 = 부모 컬럼
역방향 : PRIOR 부모 컬럼 = 자식 컬럼

SIBLINGS 정렬

--계층 구조에서 동일한 부모를 가진 행들을 정렬
SELECT EMPLOYEE_ID, NAME, LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY NAME;

계층 쿼리 예제

--SHOPPING_CATEGORIES 테이블에서 전체 계층 구조를 조회
SELECT CATEGORY_ID, PARENT_CATEGORY_ID, CATEGORY_NAME, LEVEL
FROM SHOPPING_CATEGORIES
START WITH PARENT_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;

--SHOPPING_CATEGORIES 테이블에서 계층 내역을 출력
SELECT CATEGORY_ID, PARENT_CATEGORY_ID,
  SYS_CONNECT_BY_PATH(CATEGORY_NAME, ' > ') CATE,
  LEVEL
FROM SHOPPING_CATEGORIES
START WITH PARENT_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;

--'Electronics' 카테고리의 하위 모든 카테고리를 출력
SELECT CATEGORY_ID, PARENT_CATEGORY_ID,
  SYS_CONNECT_BY_PATH(CATEGORY_NAME, ' > ') CATE
FROM SHOPPING_CATEGORIES
START WITH CATEGORY_NAME = 'Electronics'
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;

--계층 구조에서 중분류(LEVEL = 2)만 조회
SELECT CATEGORY_ID, CATEGORY_NAME, LEVEL
FROM SHOPPING_CATEGORIES
WHERE LEVEL = 2
START WITH PARENT_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID;

SELECT CATEGORY_ID, CATEGORY_NAME, LEVEL
FROM SHOPPING_CATEGORIES
START WITH PARENT_CATEGORY_ID IS NULL
CONNECT BY PRIOR CATEGORY_ID = PARENT_CATEGORY_ID
GROUP BY CATEGORY_ID, CATEGORY_NAME, LEVEL
HAVING LEVEL = 2;

--하위 카테고리가 없는 최하위 카테고리들만 조회
SELECT CATEGORY_ID, CATEGORY_NAME
FROM SHOPPING_CATEGORIES
WHERE CATEGORY_ID NOT IN (
  --모든 부모 카테고리 ID
  SELECT DISTINCT PARENT_CATEGORY_ID
  FROM SHOPPING_CATEGORIES
  WHERE PARENT_CATEGORY_ID IS NOT NULL);
 
 --각 카테고리의 부모 카테고리 이름을 함께 조회
SELECT C1.CATEGORY_ID, C1.CATEGORY_NAME, C2.CATEGORY_NAME AS PARENT_CATEGORY_NAME
FROM SHOPPING_CATEGORIES C1
LEFT JOIN SHOPPING_CATEGORIES C2
  ON C1.PARENT_CATEGORY_ID = C2.CATEGORY_ID;

Oracle 예약어
다음과 같이 작성하면 코드는 동작하지 않는다.

SELECT LEVEL, EMPLOYEE_ID, NAME, SALARY
FROM (
  SELECT LEVEL, EMPLOYEE_ID, NAME, SALARY,
    RANK() OVER (PARTITION BY LEVEL ORDER BY SALARY DESC) rank
  FROM EMPLOYEES
  WHERE SALARY IS NOT NULL
  START WITH MANAGER_ID IS NULL
  CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
)
WHERE rank <= 3;

해당 이유는 Oracle에서 LEVEL을 예약어로 해석하려고 시도하기 때문이다. 예약어가 아닌 컬럼명으로 해석시키기 위해서는 쌍따옴표를 사용한다.

SELECT "LEVEL", EMPLOYEE_ID, NAME, SALARY
FROM (
  SELECT LEVEL, EMPLOYEE_ID, NAME, SALARY,
    RANK() OVER (PARTITION BY LEVEL ORDER BY SALARY DESC) rank
  FROM EMPLOYEES
  WHERE SALARY IS NOT NULL
  START WITH MANAGER_ID IS NULL
  CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
)
WHERE rank <= 3;