[oracle sql] 의사 컬럼, 분석 함수, 계층 쿼리

1. 의사컬럼

---

 물리적 테이블에는 존재하지 않지만, select 결과셋(select절에서 추가됨)에는 존재하는 컬럼

 

• rowid : 자바의 hashcode같은 메모리 주소, 각 행들의 키값, Index로 사용
• rownum : 결과셋의 번호를 순서대로 붙인 것

select rowid, rownum, empno, ename from emp;

 

의사컬럼이 추가되는 시점은 select절이다. order by 전에 추가된다.

select rowid, rownum, empno, ename from emp order by sal desc;

 

2. 분석 함수

---

일반테이블의 결과셋과 group by 의 결과셋을 같이 출력할때 사용

 

□ 윈도우 함수(오버 함수)를 사용하는 경우 (내부적으로 join 등이 실행된다)

• 형태 : 함수이름(매개값) over(옵션) : 옵션의 디폴트 기능은 group by()와 같다. 
  ※ 옵션 부분에 들어가는 partition by는 group by()와 기능은 같지만, 이름만 다름
• 분석함수는 서브쿼리이다 . 분석함수 결과셋을 구한 후 메인 결과셋을 inner join이 된 형태이다.
• join을 하는 경우와 분석함수 사용하는 경우 비교

-- inner join 사용시
select e.empno, e.ename, e.deptno, t.total
from emp e inner join (select empno, (select sum(sal) from emp) total from emp) t 
on e.empno = t.empno;  

-- sum..over 사용시
select empno, ename, sum(sal) over() from emp;

 

 

□ 분석함수() over 옵션

• 파티션옵션(partition by)
• 오더바이 옵션(order by)
• 윈도잉옵션 : 파티션옵션으로 쪼개진 계산셋을 쪼개는 것 

 

 □ 순위/비율 관련 함수

각 partition 내에서 order by 절에 의해 정렬된 순서를 기준으로 정렬하고 윈도잉 옵션을 사용하지 못한다.

• 파티션옵션 - 필수
• 오더바이 옵션 - 필수
• 윈도잉옵션- 사용 불가

partition by를 기재하지 않으면 partition by null이 디폴트 값으로 들어간다.

1. row_number : 동점, 다른 순위

select empno, ename, sal, row_number() over(partition by null order by sal desc) from emp;

  2. rank : 동점이라면 같은 순위, 다음 순위 숫자 건너뛰기

select empno, ename, sal, row_number() over(partition by null order by sal desc) from emp;

 

3. dense_rank : 동점이라면 같은 순위, 다음 순위 숫자 건너뛰지 않음

select empno, ename, sal, dense_rank() over(partition by null order by sal desc) from emp;

 

4. 파티션을 나누면 

select empno, ename, sal, gender, row_number() over(partition by gender order by sal desc) from emp;

 

 

 □ 비율 관련 함수

 

-- 순서를 기준으로 3그룹을 나누기

select empno, ename, sal, ntile(3) over(order by sal desc) from emp;

-- 나머지가 0으로 안떨어진다면 앞쪽에 더 추가된다.

select empno, ename, sal, ntile(3) over(order by sal desc) from emp;

 

---

□ sum over, avg over, max over, min over, count over...

 

-  파티션 옵션, 오더바이 옵션, 윈도잉 옵션 모두 사용

• 파티션옵션 - 사용
• 오더바이 옵션 - 사용
• 윈도잉옵션- 사용 (오더바이에 따라 디폴트 옵션이 달라짐)

 - 윈도잉 옵션

 

  ㄴ order by가 없는 경우 : 윈도잉절의 기본값은 ("파티션의 처음부터 끝까지가 계산 기준")이 된다.

     - 윈도잉 옵션 : rows between unbounded preceding and unbounding following

select empno, ename, gender, sum(sal) over(partition by gender) from emp;

 

  ㄴorder by가 있는 경우 : 윈도잉절의 기본값은("파티션의 처음부터 현재행까지가 계산 기준")이 된다.

     - 윈도잉 옵션 : rows between unbounded preceding and current row

select empno, ename, gender, sum(sal) over(partition by gender order by empno asc) from emp;

// =
select empno, ename, gender
    , sum(sal) over(partition by gender 
                    order by empno asc
                    rows between unbounded preceding and current row) -- 윈도잉 디폴트 옵션
from emp;

 

  ㄴ order by의 디폴트 옵션 변경 : order by가 있고, 윈도잉 절의 기본값을 ("파티션의 처음부터 끝까지 계산")

select empno, ename, gender
    , sum(sal) over(partition by gender 
                    order by empno asc
                    rows between unbounded preceding and unbounded following) 
from emp;

 

 

 

3. 계층 쿼리

---

• 문법 : start with(시작조건) + connect by(다음조건)
  
  • start with : 1행의 조건
  • connect by : 연결되는 다음 행의 조건 
    • prior 속성1 = 값2 : 다음 연결될 값의 속성1의 값이 값2인 것

select empno, mgr, ename, level
from emp 
start with mgr is null -- empno = 7839
connect by prior empno = mgr -- 이전 empno가 mgr과 같으면 연결

• 계층쿼리 전용 함수
  • level은 계층 단계를 보여주는 계층 쿼리 전용 의사컬럼이다.
  • connect_by_root(필드) : 1단계의 필드의 값
  • SYS_CONNECT_BY_PATH : 루트 노드 ~ 현재 노드까지의 경로 출력
  • CONNECT_BY_ISLEAF : 가장 하위 노드인 경우 '1' 을 반환 그 외는 '0'을 반환

※ lpad로 계층을 시각화 할 수 있음

select lpad(' ', (level-1)*3, ' ') || ename, empno, mgr, level, connect_by_root(job)
from emp
start with mgr is null          
connect by prior empno = mgr;

 

• order by를 같이 사용시 정렬된 계층 순서에 영향이 없도록 유의해야한다.
  • 일반 order by 사용금지
  • 계층 쿼리 전용 order by 절을 사용 : order siblings by

select empno, mgr, ename, level
from emp 
start with mgr is null 
connect by prior empno = mgr 
order siblings by empno desc;