데이터베이스 Advanced SQL

3.1 (Advanced SQL) Roll-up and Cube

---

Cube

• 다차원 집계 계산
• 코드

  SELECT model, year, color, sum(amount)
  FROM sales
  GROUP BY CUBE(mode, year, color);

Rollup

• Cube의 부분집합
• 순서에 따라 계산
• 코드

  SELECT job, deptno, sum(sal)
  FROM emp
  GROUP BY ROLLUP(deptno,job);

  SELECT deptno, job sum(sal)
  FROM emp
  GROUP BY deptno, job
  UNION ALL
  SELECT deptno, NULL, sum(sal)
  FROM emp
  GROUP BY deptno
  UNION ALL
  SELECT NULL, NULL, sum(sal)
  FROM emp

Grouping Function

• AS

  • 칼럼이나 테이블에 간단한 이름을 부여해 가독성 높임

    SELECT deptno, job, sum(sal), grouping(job) as T1
    FROM emp
    GROUP BY cube(deptno, job);

• decode

  • 특정 조건에 따라 값을 변환할 때 사용

    SELECT decode(grouping(deptno),0,to_char(deptno), 'ALL') as deptno,
    decode(grouping(job),0,job, 'ALL') as job,
    sum(sal)
    FROM emp
    GROUP BY cube(deptno, job)

• grouping_id()

  • null이 실제 데이터인지, 집계 결과로 추가된 값인지 구분

  • 0 → 실제 값, 1 → 생략된 집계

    SELECT deptno,job, sum(sal), grouping_id(deptno, job) as GRP_ID
    FROM emp
    GROUP BY CUBE(deptno, job);

3.2 (Advanced SQL) Pivoting

---

Pivoting

SELECT *
FROM (SalesTable PIVOT (Sales for Month IN
(‘Jan’,’Feb’,’Mar’))

• 행을 열로 변환
• IN 다음에 있는 게 열 이름으로 들어감

Un-pivoting

SELECT *
FROM (SalesReport UNPIVOT (Sales for Month IN
(‘Jan’,’Feb’,’Mar’))

• 열을 행으로 변환
• IN 다음에 있는 게 행 이름으로 들어감

3.3 (Advanced SQL) Analytic Function

---

SELECT EMPNO, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) AS RANK FROM EMP

• 모든 직원의 급여를 내림차순으로 보여줌
• 쿼리 전체 구조
  • EMP 테이블에서데이터 가져올 거고
  • EMPNO, ENAME, SAL, RANK가 출력되는 칼럼임
  • 급여를 기준으로 내림차순

SELECT DEPTNO, EMPNO, ENAME, SAL,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
AS DEPT_RANK FROM EMP ORDER BY DEPTNO

• 각 부서 내에서 급여에 따른 순위 확인
• 쿼리 전체 구조
  • DEPTNO, EMPNO, ENAME, SAL, DEPT_RANK 출력되는 칼럼이고
  • 급여기준으로 내림차순 할 거고
  • 최종결과를 DEPTNO 기준으로 정렬해서 각 부서별로 데이터 묶는다

SELECT EMPNO, ENAME, SAL,
RANK() OVER(ORDER BY SAL DESC) AS RANK,
DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROW_NUMBER
FROM EMP;

• 동일한 급여를 가진 직원의 순위 차이를 RANK, DENSE_RANK, ROW_NUMBER로 비교
• 쿼리 전체 구조
  • RANK
    • 1, 2, 2, 4
  • DENSE_RANK
    • 1, 2, 2, 3
  • ROW_NUMBER
    • 1, 2, 3, 4

SELECT DEPTNO, EMPNO, ENAME, SAL,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS DEPT_RANK FROM EMP

• 각 부서별 직원의 급여 순위를 확인
• 쿼리 전체 구조
  • 최종결과 부서별로 안 되어있을 수 있음

SELECT DEPTNO, JOB, EMPNO, ENAME, SAL,
RANK() OVER(PARTITION BY DEPTNO, JOB ORDER BY SAL DESC) AS DEPT_RANK FROM EMP;

• 각 부서와 직업 그룹 내에서 급여 순위를 확인
• 쿼리 전체 구조
  • deptno랑 job이 같은 그룹 내에서 순위 확인

SELECT DEPTNO, EMPNO, ENAME, SAL,
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO) DEPTNO)) AS SAL
ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO) DEPTNO)) AS DIFF FROM EMP

• 부서별 평균 급여와 각 직원의 급여 차이를 볼 수 있음
• 쿼리 전체 구조
  • deptno별로 나누어 평균 급여 계산
  • round로 평균 급여 반올림하여 정수값
  • 각 직원의 급여가 평균 급여와 얼마나 차이나는지 계산
    • 양수면 직원이 높은 거임

SELECT EMPNO, ENAME, SAL,
NTILE(2) OVER(ORDER BY SAL DESC) AS NTILE2,
NTILE(4) OVER(ORDER BY SAL DESC) AS NTILE4
NTILE(10) OVER(ORDER BY SAL DESC) AS NTILE10 FROM EMP;

• 각 직원이 급여 기준으로 몇 번째 그룹에 속하는지를 보여줌
• 쿼리 전체 구조
  • NTILE(2)
    • 급여 내림차순으로 정리해서 그룹 2개로 나눔

SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER(ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ACC_SAL
FROM EMP

• 직원번호 순서로 각 행까지의 누적 급여를 보여줌
• 쿼리 전체 구조
  • UNBOUNDED PRECEDING: 첫 번째 행부터 시작합니다.
  • CURRENT ROW: 현재 행까지를 포함
  • 첫 번째부터 현재 행까지 누적 급여

SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER(ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_SAL
FROM EMP

• 각 직원의 급여를 주변 직원(1명 앞, 1명 뒤)의 급여 합계로 확인
• 쿼리 전체 구조
  • 직전 행 + 현재 행 + 다음 행 계산

SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER(ORDER BY EMPNO RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING) AS ACC_SAL
FROM EMP

• 재 행의 직원 번호(EMPNO) 기준으로 100 이전부터 200 이후의 값을 포함하여 급여 합계를 계산
• 쿼리 전체 구조
  • EMPNO 기준으로 100이전부터 200이후 까지 더하기

SELECT DEPTNO, EMPNO, ENAME, SAL,
SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY EMPNO ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS ACC_SAL
FROM EMP;

• 부서별로 데이터를 나누고(PARTITION BY DEPTNO), 각 부서에서 직원 번호 기준으로 이전 1행과 다음 1행을 포함한 급여 합계를 계산

SELECT EMPNO, ENAME, SAL,
LAG(SAL) OVER (ORDER BY EMPNO) AS LAG1,
LEAD(SAL) OVER (ORDER BY EMPNO) AS LEAD1,
LAG(SAL,3) OVER (ORDER BY EMPNO) AS LAG3,
LEAD(SAL,3) OVER (ORDER BY EMPNO) AS LEAD3
FROM EMP;

• LAG(SAL)은 이전 행의 값을 가져오며, LEAD(SAL)은 다음 행의 값을 가져옴
• LAG(SAL,3)와 LEAD(SAL,3)은 현재 행 기준으로 3행 전과 후의 값을 가져옴
• 쿼리 전체 구조
  • 이전이나 이후에 데이터 없으면 null

3.4 (Advanced SQL) Tree Traversal and Recursive CTE

---

SELECT empno, ename, job, mgr FROM emp
START WITH empno = 7698
CONNECT BY PRIOR mgr = empno;

• 7698번 직원과 그 하위 계층에 속한 직원들이 반환
• 쿼리 전체 구조
  • 7698인 직원 넘버를 가진 직원이랑
  • 7698넘버를 가진 매니저의 하위에 있는 직원들을 보기

SELECT empno, ename, job, mgr FROM emp
START WITH empno = 7698
CONNECT BY empno = PRIOR mgr;

• 7698번 직원이 관리하는 모든 상위 직원 계층을 반환

SELECT ename ||' reports to ' || PRIOR ename "Walk" FROM emp
START WITH ename = 'KING'
CONNECT BY PRIOR empno = mgr

• KING 직원으로부터 시작된 보고 체계를 출력
• 쿼리 전체 구조
  • 현재 직원이 상위 직원에게 보고한다

SELECT LPAD (' ', 3 * LEVEL-3)|| ename AS org_chart, LEVEL , empno , mgr , deptno FROM emp
START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr

• 직원들의 계층 구조를 들여쓰기 형태로 보여줌
• 쿼리 전체 구조
  • 매니저가 null인 (최상위 계층)부터 시작
  • 트리 레벨