SQL/SQL 개념 정리

[SQL] WINDOW 함수

yeah-12 2024. 8. 19. 00:48

윈도우 함수 = 순위 함수 = 분석 함수

 

  • SQL은 컬럼과 컬럼간의 연산, 비교, 집계에 특화되어 있는 언어
  • 윈도우 함수는 복잡한 서브쿼리 없이 간단히 행과 행간의 데이터에 대해 결과물을 만들 수 있음
  • 함수(컬럼) OVER( PARTITION BY 컬럼 ORDER BY 컬럼)
    • 함수 : 기존 함수 또는 윈도우 함수용 함수들
    • OVER : 필수 구문, OVER 내부에 파티션과 오더 절이 들어감 - 옵션

기본 구조

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

 

 

ARGUMENTS

함수에 따라 0~N 개의 인수가 지정될 수 있다.

윈도우 함수로 인해 결과 건수가 줄어들지는 않는다.

 

PARTITION BY

전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

 

WINDOW 함수의 PARTITION 구문과 GROUP BY 구문은 둘 다 파티션을 분할한다는 의미에서는 유사하나,

GROUP BY는 분할 및 집약까지 수행하지만 PARTITION은 분할까지만 수행

 

ORDER BY

어떤 항목에 대해 순위를 지정할지 기술

 

WINDOWING

함수의 대상이 되는 행 기준의 범위를 지정할 수 있습니다.

SQL Server에서는 지원하지 않는다.

  • 사용할 수 있는 요소
  •  ROWS : 윈도우의 크기를 물리적 단위로 출력된 결과 행의 개수를 지정
  •  RANGE : 논리적인 값에 의한 범위를 지정

 

  •  PRECEDING : 이전 범위 의미
  • UNBOUNDED PRECEDING : 첫 번째 행부터 윈도우가 시작됨을 지정

 

  • FOLLOWING 이전 범위를 의미
  • UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 행임을 의미

 

  • BETWEEN 시작 AND 끝: 윈도우의 시작과 끝 위치를 지정
  • CURRENT ROW : 윈도우의 시작 위치가 데이터가 출력된 현재 행임을 의미

 

기본 정의

기존 관계형 데이터 베이스는 칼럼 간의 연산, 비교, 연결이나 집합에 대한 집계는 비교적 쉽게 할 수 있었다.

하지만 행과 행간의 관계를 정의하거나, 행 간 비교를 하거나 하나의 SQL 문으로 처리하기는 매우 힘든 일.

이렇게 복잡한 SQL 문을 작성해야 하던 것을 부분적으로나마 쉽게 정의하기 위해 만들어진 함수가 바로 윈도우 함수.

 

윈도우 함수를 사용해서 순위나 합계, 평균, 행 위치 등을 조작할 수 있다.

또한, 윈도우 함수는 다른 함수와 달리 GROUP BY 구문과 병행하여 중첩(NEST) 사용할 수 없지만, 서브쿼리에서는 사용할 수 있다.

 

참조 - 데이터 웨어하우스(Data Warehouse)

데이터 웨어하우스란 사용자의 의사결정에 도움을 주기 위하여 기간 시스템의 데이터베이스에 축적된 데이터를 공통의 형식으로 변환해서 관리하는 데이터베이스를 의미합니다.

 

순위함수

RANK
누적순위
1,2,2,4
- 특정항목 및 파티션에 대해서 순위를 계산합니다. - 동일한 순위는 동일한 값이 부여됩니다.
DENSE_RANK
순차순위
1,2,2,3
- 동일한 순위를 하나의 건수로 계산합니다.
ROW_NUMBER
번호매기기
1,2,3,4
- 동일한 순위에 대해서 고유의 순위를 부여됩니다.

순서 함수 종류

FIRST_VALUE - 파티션에서 가장 처음에 나오는 값을 구할 수 있습니다.
- MIN 함수를 사용해서 같은 결과를 구할 수 있습니다.
LAST_VALUE - 파티션에서 가장 나중에 나오는 값을 구할 수 있습니다.
LAG - 이전의 행을 가지고 올 수 있습니다. (바로 이전, 특정 위치)
LEAD - 이후의 행을 가지고 올 수 있습니다. (바로 이후, 특정 위치) 
LAG(칼럼명[, 가져오고 싶은 위치, default])  -- 기본 default는 NULL

LAG(TEAM, 5, 0)    -- 5행 앞의 TEAM을 가져오고 가져올 값이 없으면 0으로 처리
LAG(SALARY)        -- 바로 직전 행의 SALARY 값
SELECT 
    ENAME, 
    HIREDATE, 
    LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) "NEXTHIRED"
FROM EMP;

비율 함수 종류

RATIO_TO_REPORT - 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 조회합니다.
PERCENT_RANK - 파티션에서 제일 먼저 나온 것을 0으로, 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회합니다.
CUME_DIST - 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회합니다.
- 누적 분포상에 위치를 0~1사이의 값을 갖습니다. 
 NTILE - 파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회합니다 
SELECT 
    ENAME, 
    SAL, 
    ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R_RESULT
FROM EMP
WHERE JOB = 'SALESMAN';