SQLD 2-2 - SQL 활용

SQL 활용 1 - 계층형 질의

계층형 데이터는 데이터 간에 상하위 관계를 가진 데이터를 이야기 한다.

 

루트 노드 : 출발점이 되는 노드를 이야기 한다.

리프 노드 : 하위가 없는 노드를 이야기 한다.

루트 ~ 리프 중간이 브랜치 노드

 

 

EMP 내에서도 계층형 데이터를 가지고도록 설계된 모델이다.

 

이렇게 같은 테이블 내에서도 본인을 참조하고 있는 칼럼이 존재한다.

 

동일 테이블 조인 == 셀프 조인

 

계층형 쿼리 == 셀프 조인을 반복하는걸 자동으로 해준다.

 

START WITH절에는 루트 노드를 생성하기 위한 조건을 기술

CONNECT BY 절에는 루트 노드의 하위 노드를 생성하기 위한 조건을 기술

쿼리끼리 차자가는 조건을 CONNECT BY에 적어준다.

 

START WITH는 WHERE 절 처럼 생각하면 된다.

즉 여기에서는 MGR이 비어 있는 것을 찾겠다는 의미이다.

 

그리고 찾아낸 내용을 가지고 기존 테이블에서 CONNECT WITH로 연결해준다.

 

그렇게 LV2에서 찾은 것들을 기준으로 다시 또 찾는다

 

LV3에서 찾은걸 가지고 또 찾는다.

 

이걸 합치면 계층형 쿼리가 나온다.

 

이걸 그냥 하면 안되고 PRIOR이 붙어 있기 때문에 가능한 것이다.

 

계층형 질의에서 순방향 전개는 부모에서 시작해서 점차 자식으로 가는 방법이고

 

역방향 전개는 그 반대로 찾아가는 방식이다.

 

데이터를 걸러내는 조건을 CONNECT BY를 적어주는 것이다.

과정 속에서 BLAKE와 관련된 데이터는 모두 걸러지게 되는 방법이다.

이렇게하면 전체 테이블을 만든 다음에 WHERE 절이 적용되게 된다.

 

ANSI 표준으로 CTE 방식이 있는데 SQL Server에서는 CTE를 재귀 호출함으로써 계층 구조를 전개할 수 있다.

 

SQL 활용 2 - 그룹 함수

GROUP BY절의 기능을 조금 더 확장한다는 개념이다.

 

GROUP BY 절은 집계 그룹 별 집계 칼럼들을 생성하는 규칙이 존재한다.

GROUPING SETS가 (a.deptno, a.job)과 a.job 의 두 가지로 그룹핑 되어 있는 것을 볼 수 있다. 그럼 얘네 별로 각각 그룹핑한 결과가 나온다.

 

그래서 파란부분과 노란 부분 두 가지가 나오고 이 둘을 합친형태의 테이블이 반환되는 것이다.

 

ROLLUP은 집계 칼럼들을 줄여가면서 집계 그룹을 생성해준다.

 

a.deptno, a.job -> a.deptno -> () 순으로 가게 된다.

 

서로 바뀌는 것들을 알아두어야 시험보기 용이해진다.

 

2^n 개 만큼 그룹을 만들어서 반환해준다.

 

3. 윈도우 함수

대상이 되는 행의 범위 == 윈도우

각 행 별로 윈도우가 정의된다.

 

OVER 키워드가 있다면 분석함수 없다면 집계함수이다.

윈도우 함수는 GROUP BY와 유사한 부분이 있으니 일단 잠깐만 보고 지나가자

집계값과 개별값을 함께 볼 수 있다는 것이 GROUP BY와는 차이점이다. 분석함수라고도 부르고 윈도우 함수라고도 부른다.

진짜로 합친 것은 아니고 결과값만 옆에 나온 형태가 된다.

그래서 GROUP BY 절과는 다르게 어떤 값들이 합치어진 것인지 알 수 있다.

 

ORDER BY를 쓰면 누적집계도 가능해진다.

 

실행 대상이 되는 행의 범위(윈도우)를 세밀하게 지정한다.

 

윈도윙 절을 쓰면 보다 세밀하게 분석이 가능해진다.

 

그냥 order by 해놓은 결과와 큰 차이는 없어보인다.

 

이런식으로 현재 위치에서 마지막 행까지의 합을 더한 결과물을 얻어낼 수도 있다.

 

 

위와같이 위아래 1행까지의 합을 구해낼 수 있다.

 

지금까지는 ROWS 였고 이번에는 RANGE를 사용해보자

 

RANGE로 UNBOUNDED PRECEDING AND CURRENT ROW를 해줄 경우. 현재 3000이라는 행과 800까지의 값의 범위를 설정하고 윈도우를 모두 탐색해서 SUM 결과물을 구한 결과가 위에서의 10875이다.

 

UNBOUNDED FOLLOWING은 윈도우의 끝을 값의 마지막 범위로 가정하고 계산한다.

 

윈도윙 절에서도 RANGE를 생략해주면 default로 RANGE 함수가 끝과 끝 사이를 계산해준다.