SQLD/함수

[함수-다중행 함수]윈도우 함수

김귤🐵 2023. 8. 28. 18:30

윈도우 함수

1)윈도우 함수의 기본 구문

SELECT 윈도우 함수(인자) OVER([PARTITION BY 컬럼][ORDER BY절][WINDOWING 절])
FROM 테이블명

❗️윈도우 함수는 반드시 OVER 키워드와 함께 사용된다.

  • 인자 : 함수에 따라 0~N개의 인자를 지정 가능
  • PARTITION BY : 전체 집합을 소그룹으로 지정하여 나눌 수 있다.
  • ORDER BY절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY절을 기술한다.
  • WINDOWING절 : 집계하려는 데이터의 범위를 지정할 수 있다. 오라클에서만 사용 가능하다.

 

*WINDOWING절 

범위 의미
UNBOUNDED PRECEDING 위쪽 끝 행
UNBOUNDED FOLLOWING 아래쪽 끝 행
CURRENT ROW 현재 행
n PRECEDING 현재 행에서 위로 n만큼 이동
n FOLLOWING 현재 행에서 아래로 n만큼 이동
기준 의미
ROWS 행 자체가 기준이 된다.
RANGE 행이 가지고 있는 데이터 값이 기준이 된다.

*WINDOWING절 기본 구문

--처음부터 현재 행 까지
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

--현재 행부터 끝까지
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

 

 

2)윈도우 함수의 분류

윈도우 함수는 역할에 따라 크게 4가지로 나눌 수 있다.

분류 종류
순위 함수 RANK
DENSE_RANK
ROW_NUMBER
집계 함수 SUM
MAX
MIN
AVG
COUNT
행 순서 함수(오라클) FIRST_VALUE
LAST_VALUE
LEG
LEAD
비율 함수(오라클) PERCENT_RANK
RATIO_TO_PEPORT
CUME_DIST
NTILE

 

 

 

 

1️⃣순위 함수

  • 순위를 구하는데 사용하는 함수.
  • 세 개의 함수 모두 동일한 동작을 수행 하지만 공동 순위 처리 방식이 다르다.
순위 함수 설명
RANK 공동 순위를 개별적인 존재로 취급하고 존재하는 수 만큼 다음 순위를 건너뛴다.
  EX) 1위 (2위) (2위) 4위 5위
DENSE_RANK RANK와 동일한 동작을 하지만 공동 순위에 대한 처리 방식이 다르다.
공동 순위를 하나의 묶음으로 취급하고 다음 순위를 건너뛰지 않고 이어서 매긴다.
  EX) 1위 (2위 2위) 3위 4위 
ROW_NUMBER 공동 순위가 있더라도 각기 다른 유니크한 순위를 부여한다.
ORDER BY절을 통해서 동일한 값에 대한 순서를 관리할 수 있다.

예제

1.사원 테이블에서 급여가 가장 높은 순서를 구하기

SELECT E_NAME,RANK()OVER(ORDER BY SAL DESC)
FROM EMP;

2.PARTITION BY절을 사용하여 그룹 내 순위를 구할 수 있다.

SELECT E_NAME,RANK()OVER(PARTITION BY JOB ORDER BY SAL DESC)
FROM EMP;

 

 

2️⃣집계 함수

집계 함수 설명
SUM 파티션별 합을 구할 수 있다.
MAX 파티션별 최대값을 구할 수 있다.
MIN 파티션별 최소값을 구할 수 있다.
AVG 파티션별 평균을 구한다.
COUNT 파티션별 카운트

❗️일반 집계함수 와 윈도우함수의 집계함수

  • 일반 적인 집계함수는 전체 테이블의 데이터를 집계하여 하나의 결과를 반환한다.
  • 윈도우 함수의 경우 데이터를 행 단위로 분석을 하면서 각 행에 대한 개별적인 계산을 한다. 

예제

점수 테이블

1.일반적인 집계함수의 사용

SELECT SUM(SCORE) as TOTAL_SCORE
FROM REVIEW;

2.윈도우 함수의 사용

SELECT   
    user_name,
    movie,
    score,
    SUM(SCORE) OVER(partition by user_name)as TOTAL_SCORE
FROM REVIEW;

 

3.오라클에서는 ORDER BY절을 사용해서 누적값을 구할 수 있다.

SELECT 
    user_name,
    movie,
    score,
    SUM(SCORE) OVER(partition by user_name ORDER BY SCORE DESC)as TOTAL_SCORE
FROM REVIEW;

 

 

3️⃣행 순서 함수

오라클에서만 사용 가능하다. 

행 순서 함수 설명
FIRST_VALUE 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
MIN함수를 사용하여 같은 결과를 얻을 수 있다. 
동일한 값이 존재한다면 OVER안에 ORDER BY 정렬 조건을 추가한다.)
LAST_VALUE 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
MAX함수를 사용하여 같은 결과를 얻을 수 있다.
LAG 파티션별 이전 몇 번째 행의 값을 가져올 수 있다.
LAG(컬럼[,앞에서 가져올 행][,NULL일경우 지정할 값])
  -두 번째 인자 : 몇 번째 앞의 행을 가져올지 결정 Default값은 1이다.
  -세 번째 인자 : 파티션의 첫 번째 행은 앞에 값이 없기 때문에 NULL일 경우 들어올 값을 지정
LEAD 파티션별 이후 몇 번째 행의 값을 가져올 수 있다. 
LEAD(컬럼[,뒤에서 가져올 행][,NULL일경우 지정할 값])
  LAG와 마찬가지로 세 개의 인자를 받고 동일한 역할.

 

4️⃣비율 함수

오라클에서만 사용 가능

비율 함수 설명
RATIO_TO_REPORT 파티션별 합계에서 차지하는 비율을 구하는 함수
PERCENT_RANK 파티션별 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 한다.
값이 아닌 행의 순서별 백분율을 구한다.
CUME_DIST 파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 구한다.
NTILE 주어진 수 만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수