[SQL 기초 - 19장] 오라클 SQL 계층형 쿼리(2), 윈도우 함수

안녕하세요!

오늘은 어제 배웠던 계층형 쿼리 연습과 윈도우 함수를 알아보려고 합니다!!​

시작하기 전에 지난시간 내용을 복습 하실 분들은 ↓↓↓↓↓↓↓↓↓↓↓↓​

오라클 계층형쿼리 알아보기​

계층형 쿼리 연습

제가 드린 h_sum 테이블 입니다.

위의 테이블을 지난 시간에 배웠던 계층형 쿼리를 통해서

​

위와 같은 형식을 만드는 연습을 해보겠습니다.

​

그럼 시작점을 먼저 찾아줘야 겠죠?

시작점은 가장 상위 노드인 지점을 작성해주면 되겠고,

연결 부분은 행과 행을 연결해줄 수 있는 컬럼들로 구성하면 되겠네요.

​

위의 쿼리처럼 작성하시면 됩니다.

SELECT 절은 지난 시간에 말씀드린대로

그냥 눈으로 보기 좋게 하기 위해 LPAD 를 사용 해준것입니다.​​

다음으로 가지치기 라는 개념에 대해서 설명 하려고 합니다.

 

제가 초반에 한 번 말씀 드렸던적이 있는데

SELECT 쿼리의 실행순서 기억 하시나요?

 

위와 같은 순서를 가진다고 말씀 드렸습니다.

​

그렇다면 계층쿼리의 실행순서는 어떻게 될까요?

위와 같이 된답니다.

​

이 부분을 잘 기억하시고 내용 봐주세요!

​

​

dept_h 테이블을 이용해서 만든 쿼리와 결과입니다.

위에서 봤던 쿼리와 달리 CONNECT BY 절에

AND 로 조건이 하나 더 붙었습니다.

​

위의 쿼리는 같은 테이블인데

AND 옆의 조건을 WHERE 절로 따로 줬습니다.

근데 결과가 좀 다르죠?

​

이것을 아까 제가 말했던 계층쿼리에서 순서와 연관지어 생각해 보세요.

​

그럼 정리하자면 계층쿼리에서 조회할 행의 조건을 기술할 수 있는 부분이 2곳 존재합니다.

1. CONNECT BY 는 다음 행으로 연결할지, 말지를 결정

2. WHERE 는 START WITH, CONNECT BY 에 의해 조회된 행을 대상으로 적용

이라고 할 수 있을 것 같고,

이것들이 계층쿼리의 실행순서에 따라 실행된다고 할 수 있습니다.​

계층쿼리에서 사용할 수 있는 특수함수

계층쿼리에서 사용할 수 있는 특수 함수가 있습니다.

예시와 함께 설명하도록 하겠습니다.

​

아까와 비슷한 계층쿼리 입니다.

그런데 SELECT 절에 새로운 행이 3개가 추가 되었죠?

​

CONNECT_BY_ROOT 는 최상위 행의 컬럼값을 반환해줍니다.

SYS_CONNECT_BY_PATH 는 계층의 순회경로를 표현해줍니다.

마지막으로 CONNECT_BY_ISLEAF 는 해당 행이 LEAF 노드인지(1) 아닌지(0)를 반환해줍니다.

(즉, 최 하단 노드인지 아닌지를 구별해서 알려줍니다.)

​

SYS_CONNECT_BY_PATH를 쓴 부분에서 제가 LTRIM 을 함께 썼는데요,

LTRIM을 쓰지 않았다면 PATH 컬럼에 데이터들에서

XX회사 왼쪽에도 모두 '-' 이 붙어서 나왔을 거에요.

그것을 없애고자 LTRIM 을 사용했답니다.​

윈도우 함수

마지막으로 윈도우 함수입니다.

​

윈도우 함수를 사용하면 행간 연산이 가능해집니다.

이는 일반적으로 풀리지 않는 쿼리를 간단하게 만들 수 있다는 말이죠.

(**하지만 모든 DBMS 가 동일한 윈도우 함수를 제공하지는 않습니다!!)

​

그럼 문법과 간략한 설명 함께 보겠습니다.

 

위처럼 윈도우 함수를 적어주고 옆에 OVER 가 들어갑니다.

그리고 PARTITION BY, ORDER BY, WINDOWING 키워드를 적어주는데

[] 는 제가 예전에 선택사항이라고 말했었습니다.

하나를 적을수도 있고, 여러개를 적을 수도 있습니다.

​

먼저 순위 관련 함수를 볼건데 이것도 예시와 함께 보겠습니다.

​

emp 테이블에서 부서별 급여 순위를 조회하는 쿼리와 결과입니다.

​

모두 같은 순위를 결정해주는 함수인데 조금씩 다르죠?

동일한 순위에 대해서 처리하는게 다릅니다.

​

1. RANK()

동일 값일 때는 순위 부여, 후순위는 중복자만큼 건너 띄고 부여합니다.

(ex : 1등이 2명이면 후순위는 3등)

​

2. DENSE_RANK()

동일 값일 때는 동일 순위 부여, 후순위는 이어서 부여합니다.

(ex : 1등이 2명이어도 후순위는 2등)

​

3. ROW_NUMBER()

중복되는 값이 없이 순위 부여 합니다.

(ROWNUM 과 유사)

​

내용보고 다시 한 번 조회된 결과를 보시면 이해가 빠르게 되실거에요!

 

​

집계 윈도우 함수

다음으로 집계 윈도우 함수 입니다.

이 부분은 여러분이 알고계시는 그룹함수에서 사용하는 녀석들과 같습니다.

문법만 윈도우 함수 형식으로 사용하는 것이죠.​

위와 같이 평균을 구할때는 AVG를 사용하죠?

그것을 윈도우 함수와 같이 사용하면 위처럼 작성할 수 있습니다.

PARTITION BY 는 GROUP BY 와 비슷한 기능을 한다고 생각하시면 됩니다.

​

그 외에 위처럼 MAX, MIN 그리고 SUM, COUNT도 사용할 수 있습니다.

사용방법도 위와 같으니 여러분들이 가지고 있는 테이블에서 한 번 사용해 보세요!​

오늘은 계층쿼리와 윈도우 함수에 대해서 알아봤습니다.

 

오늘을 마지막으로 사실 SQL 기초는 마무리 지으려고 합니다!!

정말 실무적으로 필요한 내용들만 압축해서 담다보니

이론적인 내용이 많이 없었던것 같기도 하네요..

 

이론적인 부분은 앞으로 하나씩 업로드를 하려고 합니다.

여기까지 모든 내용을 이해하고 오신분들은 기초가 잘 잡히셨을거에요!

 

지금까지 잘 봐주셔서 감사합니다!

다음에는 더 알찬 포스팅 가져올게요~~

​