신기한 연구소

안녕하세요. 신기한 연구소입니다.

이번 포스팅은 윈도우함수(WINDOW FUNCTION) 중 순위함수에 대해 알아봅니다.

순위함수를 잘 사용한다면 다양한 그룹 데이터, 집계 데이터를 산출하는 경우

유용하게 사용할 수 있습니다.

그럼 윈도우함수 중 순위 함수는 어떤 것이 있는지 확인해보겠습니다.

먼저 RANK() 함수를 알아봅니다.

위 쿼리를 보면 RANK() 함수를 사용하는 방법을 알 수 있습니다.

RANK() 함수는 OVER 내 옵션에서 ORDER BY를 필수로 사용하면서 특정 항목에 대한 순위를

정할 수 있습니다.

PARTITION BY의 여부에 따라 전체 순위를 정할지 아니면 특정 컬럼마다 순위를 정할지

결정할 수 있습니다.

R_SAL을 보면 1부터 SAL의 높은 금액순으로 순위를 정하고 있습니다.

특징은 같은 급여에 대해 같은 순위를 설정했습니다. 9위를 보세요.

그리고 그 다음은 10위가 아닌 같은 순위의 개수만큼 건너뛰고 14위가 나왔습니다.

DENSE_RANK()도 순위를 정하는 함수입니다.

마찬가지로 OVER 내 옵션으로 ORDER BY를 사용해야 합니다.

DR_SAL을 보면 RANK()와 다른 점을 찾을 수 있습니다.

같은 급여에 대해 같은 순위를 정하는 건 같지만

그다음 순위를 정할 때 같은 순위의 개수만큼 건너뛰지 않고 바로 다음 순위인

10위를 보여줍니다.

마지막 ROW_NUMBER()를 보면 이 또한 순위를 정하는 함수입니다.

사용법은 기존 RANK(), DENSE_RANK()와 같으며 결과는 RN_SAL을 보면

같은 급여와 상관없이 그냥 쭉~ 1위부터 순서를 정했습니다.

전체 데이터에 대한 순위를 정하는 방법에 대해 알아봤습니다.

이제 PARTITION BY 컬럼을 사용해서 소그룹 순위를 정해보겠습니다.

위 쿼리를 보면 2가지 다른 점이 있습니다.

먼저 PARTITION BY JOB이라는 옵션을 추가했습니다.

그리고 ORDER BY절에 JOB이 추가되었습니다.

JOB별로 정렬하고 JOB별로 순위를 정한다고 보면 됩니다.

RANK()를 보면 JOB별로 SAL 순위를 정했습니다.

마찬가지로 중복된 SAL에 대해서는 공동 순위를 정했습니다.

그리고 중복 순위 다음은 중복 개수만큼 건너뛰고 7위가 나왔습니다.

DENSE_RANK()도 같은 개념으로 보이는데

공동 순위 다음의 순위에 대한 개념만 다릅니다.

중복 개수가 있어도 바로 다음 순위 4위를 보여줍니다.

마지막 ROW_NUMBER()도 JOB별로 순위를 정했는데

SAL 금액이 같아도 공동 순위 없이 순위를 정하고 있습니다.

오라클 윈도우함수에서 순위 함수에 대해 알아봤는데요.

이제는 RANK(), DENSE_RANK, ROW_NUMBER() 함수가 나와도

문제없이 사용할 수 있다고 생각됩니다.

윈도우함수는 SELECT문에서만 사용할 수 있다는 것은 잊지 마세요.

잘못된 내용이나 수정이 필요하면 댓글 주세요.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.

안녕하세요. 신기한 연구소입니다.

-WINDOW FUNCTION-

 오라클의 고급 함수인 윈도우함수(분석함수)에 대해 알아봅니다. 

프로젝트에 투입 후 SQL문을 보면 SELECT문이 정말 어지러울 정도로 복잡하게

구성되어 있는 경우가 종종 있습니다.

화면단에 원하는 데이터를 구성하기 위해 다양한 기술/기법들을 사용하기 때문인데요.

SQL에서 기본 데이터만 추출하고 프로그램단에서 가공하는 방법도 있지만

가급적 SQL에서 최적의 데이터를 구성하고 프로그램단에서는 큰 가공 없이 바로

화면에 보여준다면 관리 측면에서도 더 효율적이라 할 수 있습니다.

윈도우 함수는 SELECT절에서만 사용 가능하고

복잡하게 구성해야 할 SQL문을 좀 더 편하고 가독성이 좋게

작업할 수 있도록 도와줍니다.

잘 이해하고 사용한다면 큰 도움이 될 거예요.

기본 형식은 다음과 같습니다.

윈도우함수명(인수들) OVER ([PARTITION BY 컬럼] [ORDER BY 절 ASC|DESC] [윈도우절])

뭔가 복잡해 보이는데 하나씩 살펴보겠습니다.

윈도우함수명은 RANK(), MAX(), FIRST_VALUE() 등이 있습니다. 메인 기능입니다.

인수는 윈도우함수를 사용할 때 설정이 필요한 경우 옵션으로 사용합니다.

OVER는 윈도우함수 바로 뒤에 사용하고 옵션들을 그 뒤로 나열합니다. 필수 키워드입니다.

PARTITION BY 컬럼은 선택 옵션입니다.

전체 조회된 행에 대해 소그룹을 지정한 컬럼으로 만들 수 있습니다.

ORDER BY 컬럼은 해당 컬럼에 대해서 정렬을 합니다. 선택 옵션입니다.

위 쿼리를 보겠습니다.

먼저 윈도우 함수는 MAX()를 사용했습니다.

입력 변수는 MAX값을 구할 컬럼을 SAL로 지정했습니다.

윈도우 함수인 MAX(SAL)을 지정 후 OVER 뒤에 옵션을 지정합니다.

JOB별로 최고 SAL을 구하기 위해 PARTITION BY SAL을 사용했습니다.

JOB_MAX는 JOB별 합계를 보여줍니다.

PARTITION BY를 빼면 전체 결과값에서 최고 SAL을 구하게 됩니다.

그러면 SAL 최고값 5000으로 일괄적으로 조회가 됩니다.

마지막으로

윈도우절은 ROWS|RANGE 둘 중 하나를 선택하고 BETWEEN도 선택적으로 사용해서

결과에 대한 범위를 지정할 수 있습니다.

윈도우절 기본 형식은 다음과 같습니다.

BETWEEN이 있다면

ROWS|RANGE

BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | 상수값 PRECEDING 또는 FOLLOWING

    AND UNBOUNDED FOLLOWING| CURRENT ROW | 상수값 PRECEDING 또는 FOLLOWING

BETWEEN이 없다면

ROWS|RANGE UNBOUNDED PRECEDING | CURRENT ROW | 상수값 PRECEDING

ROWS와 RANGE는 둘 중 하나를 선택하는데

ROWS는 조회된 행이 기준이기에 값의 동일 여부를 떠나 하나하나 연산을 해주는 것이고

RANGE는 조회된 행의 값이 기준이기에 같다면 묶어서 합산 후 연산을 하는 것입니다.

도대체 무슨 말일까요?

위 쿼리의 결과를 보면서 확인하겠습니다.

ROWS로 하면 SALESMAN의 급여 순서대로 합계를 만들어 쭉 보여줍니다.

하지만 RANGE를 보면 3번째 행부터 7번째 행까지 급여가 1250으로 같습니다.

같은 급여가 나오면 합산해서 같은 급여자들에게 그 합산한 9350을 보여줍니다.

BETWEEN을 사용해서 시작과 끝의 범위를 정할 수 있습니다.

UNBOUNDED PRECEDING는 시작 범위를 정하는 데 사용합니다.

무제한으로 앞부분을 뜻하는데 가장 앞을 의미합니다. 시작부겠네요.

CURRENT ROW는 현재 행을 의미합니다.

10건이 조회되었다면 1건씩 10번 행이 추가되는데 그때그때 CURRENT ROW가 됩니다.

10건 중 3번째 행을 처리하는 중이라면 CURRENT ROW는 3번째 행을 말합니다.

상수값 PRECEDING/FOLLOWING은 고정된 값을 넣고 뒤에 옵션을 선택하게 됩니다.

ROWS 상수값 PRECEDING/FOLLOWING를 사용하면 행 기준으로 상수값 만큼

앞의 행 갯수 뒤의 행 갯수만큼 범위를 정하는 것이고

RANGE 상수값 PRECEDING/FOLLOWING를 사용하면 값 기준이기에 

행과 관계없이 값의 범위를 상수값만큼 정하게 됩니다.

예를 들어 ROWS 10 PRECEDING을 사용하면 현재 행에서 10행 앞쪽의 범위를 잡는 것이고

RANGE 10 PRECEDING을 사용하면 현재 행의 컬럼값이 100의 값을 갖는다면

 -10만큼 작은 값 즉 90부터 범위를 정하는 것이 됩니다. 이해되시죠?

글로 설명하기 앞서 쿼리를 보겠습니다.

S_PRE 별칭에 대한 쿼리를 보면 4 PRECEDING를 사용했습니다.

4번째 행까지는 앞의 4행이 존재하지 않기 때문에 NULL이 나왔습니다.

그리고 5번째 행이 되면 4번 앞선 행이 드디어 존재합니다.

바로 첫 번째 행이 5번째 행에서 4번 앞선 행이거든요

거기부터 더하기 시작한 것이 4 PRECEDING입니다.

4 FOLLOWING는 반대로 첫번째 행일 때 뒤로 4개까지 합산한 값부터 첫 번째에 표시합니다.

그래서 첫번째 행이 되어도 뒤로 4번째 행이 존재하므로

UNBOUNDED PRECEDING AND 4 FOLLOWING으로 처음부터 4번째 행까지의 합을 나타냅니다.

위 쿼리를 확인하면

BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

이 부분은 가장 앞부분부터 가장 끝부분까지의 범위를 표현합니다.

그래서 총합을 각 행마다 보여줍니다.

UP_UF값이 9350으로 총합을 쭉~ 보여줍니다.

BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

이 부분은 현재행부터 가장 끝부분까지의 합을 행마다 보여줍니다.

그래서 앞의 SAL 값이 하나씩 빠지면서 합이 표출됩니다.

CR_UF를 보시면 값이 바로 앞의 행 SAL 수치만큼 줄어드는 것을 볼 수 있습니다.

BETWEEN이 없는 경우는 다음과 같이 이해하면 됩니다.

UNBOUNDED PRECEDING은 처음부터 하나씩 SUM 하라는 의미입니다.

CURRENT ROW는 현재행부터 현재행까지이므로 현재행의 값만 보여줍니다.

2 PRECEDING은 현재행에서 앞선 2개 행까지의 합만 표현합니다.

위 쿼리 결과를 보면 이해가 잘 될 겁니다.

아래 하트(공감) 버튼을 눌러서 더 다양한 글을 쓸 수 있게 응원 부탁드립니다. 감사합니다.