[Database] 윈도우 함수 정리

윈도우 함수

• 서로 다른 행의 비교나 연산을 위해 만든 함수
• group by를 쓰지 않고 그룹 연산이 가능하다.
• lag, lead, sum, avg, min, max, count, rank

over 뒤의 순서를 잘 지켜줘야 한다.

 

기존의 문제

다음과 같이 다른 컬럼과 그룹함수를 같이 사용하는 것이 불가능 했었다.

이를 윈도우 함수로 해결할 수 있다.

 

sum, count, min, max등

over 절을 사용하여 윈도우 함수로 사용이 가능하다.

반드시 연산할 대상을 그룹함수의 입력값으로 전달해야한다.

 

SUM OVER()

전체 총 합, 그룹별 총 합 출력이 가능하다.

 

위의 문제점을 다음과 같이 해결할 수 있다.

 

누적합 같은 것을 사용하지 않고 전체 총합만 나타낼 것이기 때문에

sum(sal) over()로 윈도우 함수가 끝났다.

 

윈도우 함수에서 범위 설정 방식

1. ROWS, RANGE 차이

• ROWS: 값이 같더라도 각 행씩 연산
• RANGE: 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산 (DEFAULT)

2. BETWEEN A AND B

• 시작점 정의
  • CURRENT ROW: 현재행 부터
  • UNBOUNDED PRECEDING: 처음부터(DEFAULT)
  • N PRECEDEING: N 이전부터
• 마지막 시점 정의
  • CURRENT ROW: 현재행 까지(DEFAULT)
  • UNBOUNDED FOLLOWING: 마지막까지
  • N FOLLOWING: N 이후까지

현재 over 안에 sal로 정렬을 시킨 후 범위 값은 default로 설정된 상태다

 

우선 default인 range 때문에 1250이라는 sal이 하나의 range로 묶여서 동시 연산이 됐다.

 

따라서 sum sal에 5350으로 동시 연산이 된 것을 볼 수 있다.

 

이후 between a and b는 기본 값인 unbounded preceding에서 current row까지

 

즉, 처음부터 현재 행까지가 기본값으로 설정되어 값이 나오는 것을 확인할 수 있다.

 

누적합이라는 말의 의미가 처음부터 지금까지의 합이니 적절한 default값이라 볼 수 있다.

 

ROW로 바꿔보니 다음과 같이 각 행별로 연산이 수행되어 5350이 중복되지 않는 것을 확인할 수 있다.

 

이번 예제는 범위 값을 수정한 것이다.

 

UNBOUNDED PRECEDING이니 처음부터 1FOLLOWING 즉 하나 이후까지 합을 구하란 뜻이다.

 

result가 첫 행부터 1750으로 1 이후의 값까지 합쳐져서 나오는 것을 확인할 수 있다.

 

순위 관련 함수

1. RANK WITHIN GROUP

• 위 함수는 over이 없기 때문에 윈도우 함수는 아니지만 강사님이 그냥 넣으셨다고 한다 ,,
• 특정값에 대한 순위 확인
• 윈도우 함수가 아닌 일반 함수다.

rank 안에 궁금한 값을 집어넣고 (컬럼 아님) within group에서 order by를 통해 정렬을 해준다.

 

순위기 때문에 당연히 order by가 필요하고 그 order by를 within group에서 수행해주는 것이다.

 

sal을 기준으로 내림차순 정렬 후 3000의 등수를 확인하니 2가 나왔다.

 

2. RANK() OVER()

• 전체 중/ 특정 그룹 중 값의 순위를 확인한다.
• order by 절이 필수다.
• 순위를 구할 대상을 order by 절에 명시한다. (여러 개 나열 가능함)
• 그룹 내 순위 구할 시 partition by 절을 사용해야 한다.

위가 기본 문법이다. over이 들어갔으니 윈도우 함수다.

 

다음은 sal 로 내림차순 정렬 후 순위를 구하는 쿼리다

 

sal이 3000으로 같은 값이 나올 땐 동순위로 처리되는 것을 기억하자

 

그 다음 순위는 4등이 된다.

 

동순위가 몇 명이던 간에 다음 순위가 3으로 이어지는 함수는 dense rank다 (뒤에 나옴)

 

이번엔 부서별 급여 순위를 구해봤다.

 

partition by로 부서별로 짤라주고 sal을 기준으로 내림차순 정렬하여 rank()를 적용했다.

 

부서별로 나뉜 후 sal로 내림차순 정렬 되어 rank가 매겨진 것을 볼 수 있다.

 

3. DENSE_RANK

• 누적 순위
• 값이 같을 때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식

4. ROW_NUMBER

• 연속된 행 번호를 표시한다.
• 동일한 순위를 인정하지 않고 단순히 순서대로 (나열한 대로)의 순서 값을 리턴한다.

세 가지의 차이점을 확인하는 예제다

rank는 동순위를 인정하여 1 2 2 4순

 

dense_rank는 1 2 2 3순

 

row_number는 단순히 행의 숫자 1 2 3 4를 반환하는 것을 확인할 수 있다.

 

5. LAG, LEAD

• 행 순서대로 각각 이전 값(LAG), 이후 값(LEAD)를 가져오는 함수다.
• ORDER BY절은 필수다.

lag(컬럼 명, N) N은 몇 행 이전의 값을 가져올 것이냐 인데 default 값은 1이다.

 

over 안에 order by는 필수다.

 

예시를 보면 hire date로 정렬한 후 바로 이전 입사자와 급여를 비교하는 예시를 볼 수 잇따.

 

조인과 서브쿼리를 사용하지 않고 다른 행의 값을 동시에 보여주기 때문에 아주 간편한 함수다

 

6. FIRST_VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값을 출력한다.
• 순서와 범위 정의에 따라 다른 최솟값과 최댓값을 리턴할 수 있다.
• partition by, order by는 생략 가능하다.

위의 예시는 부서별로 파티션을 나눈 후 sal을 기준으로 정렬시켜

최솟값과 최댓값을 구하는 예제다.

 

물론 desc를 쓰지 않고 LAST_VALUE를 사용해서 max value를 뽑아낼 수도 있다.

 

하지만 last value를 사용할 때는 주의할 점이 있다. 

바로 between a and b가 기본 값으로 설정된다는 점인데 

 

기본 값은 현재행부터 자기 자신까지다,,,

 

즉 현재행부터 자기 자신까지의 값중 last value는? 자기 자신이 되는 것이니 항상 자신의 값만 뱉는 걸 볼 수 있다.

 

이를 방지하기 위해 UNBOUNDED PRECEDING AND UNBOUNDED FOLLWING으로 처음부터 끝까지로

범위 설정을 해줘야 한다.

 

7. NTILE

• 행의 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴된다.
• order by는 필수다.
• partition by를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리가 가능하다.
• 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리된다.
  • 14명 3개 그룹 분리 시 -> 5,5,4로 나뉜다.

NTILE()안에 원하는 그룹의 수를 적어준다.

 

partition by를 통해 부서 별로 그룹을 나눌 수도 있다.

 

emp는 총 14건이다. ntile(2)니까 두 그룹으로 나누는 것이고

연봉 오름차순이니 연봉 하위 50퍼와 상위 50퍼로 나뉠 것이다.

 

8. RATIO_TO_REPORT

• 각 값의 비율을 리턴한다(전체 비율 또는 특정 그룹 내 비율)
• order by는 사용 불가능 하다.

대상의 비율이 리턴된다.

 

9. CUME_DIST

• 각 행의 수에 대한 누적 비율을 반환한다.
• 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력한다.
• order by를 사용하여 누적 비율을 구하는 순서를 정할 수 있다.
• order by는 필수다.
• 값이 3개면 0.33부터 시작된다

10. PERCENT_RANK

• 분위수를 출력한다
• 전체 count 중 상대적 위치를 0~1 사이에서 출력한다.
• order by는 필수다.
• cume_dist와 거의 동일하다 볼 수 있다.

 

위의 예시를 보면 cume_dist와 percent_rank의 차이를 볼 수 있다.

 

 

참고 동영상 및 이미지 출처:https://www.youtube.com/watch?v=hLvv0GN0rT8&t=1168s