신기한 연구소

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

-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개 행까지의 합만 표현합니다.

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

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

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

이번 포스팅은 그룹핑에서 유용하게 사용할 수 있는 함수들을 알아봅니다.

기본적으로 GROUP BY를 사용해서 데이터를 그룹핑하는 방법은 기존 포스팅에서

같이 알아봤습니다.

그 그룹핑된 결과를 그룹별로 소계, 총계를 구할 수 있는 함수가 있는데요.

바로 ROLLUP과 CUBE입니다.

ROLLUP 함수

ROLLUP 함수는 조건 컬럼의 중간 소계와 총합계를 만들어주는 함수입니다.

이렇게 멋진 함수가 있었다니..ㅎㅎㅎ

보통 이런 쿼리를 이용해서 그룹핑합니다.

하지만 동호회를 그룹핑해서 JOB의 소계를 구하고

전체 합계를 구하려면 별도로 쿼리를 작성해서 UNION 같은

연산자를 사용하거나 서브 쿼리를 사용하는 방법을 사용할 수 있지만

그렇게 만들면 점점 복잡해지고 쿼리양도 많아집니다.

ROLLUP 이미지

이런 경우 ROLLUP을 사용해서 편리하게 소계와 총계를 구할 수 있습니다.

GROUP BY 다음에 ROLLUP을 추가하고 소계와 총계를 원하는

컬럼을 구성하면 됩니다.

GROUP BY에서 컬럼의 구성에 따라 소계와 총계의 개념이 달라집니다.

컬럼이 많아지면 소계도 단계별로 생성됩니다.

CUBE 함수

CUBE 함수는 그룹핑한 컬럼에 대해 소계를 만드는데

설정한 컬럼에 대해 각각 소계를 보여주고 총계를 보여줍니다.

JOB에 대한 소계도 잘 표출하고 있습니다.

GROUPING SETS 함수

CLNAME 과 JOB에 대해 각각 소계를 구할 수도 있습니다.

GROUPING SETS에 CLNAME 과 JOB 컬럼을 사용하면

CLNAME에 해당하는 모든 취미의 합과

JOB에 해당하는 모든 취미에 대한 합을 보여줍니다.

집계 데이터를 가지고 원하는 컬럼에 대해 소계를 구하는데 제격이네요.

GROUPING 함수

지금까지 3가지 그룹 관련 함수를 확인했습니다.

해당 쿼리를 보면 SELECT문에 GROUING 함수를 볼 수 있습니다.

GROUPING 함수는 GROUP BY에 설정된 컬럼을 ARGUMENT로 넣은 경우

합계를 나타내는 행이 나오는 경우 결과로 1을 보여줍니다.

일반 합계는 0 해당 컬럼에 소계(총계)가 나오는 경우 1을 리턴합니다. 

이제 이 4가지 그룹 함수를 가지고

개발하면서 멋진 그룹 쿼리를 만들어 볼 수 있겠네요.

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

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