[SQL튜닝] SUBQUERY와 함수의 활용

[SQL튜닝] SUBQUERY와 함수의 활용

---

주요내용

• SUBQUERY의 종류
• NESTED SUBQUERY
• CORRELATED SUBQUERY
• SCALAR SUBQUERY
• ROLLUP()&CUBE()
• GROUPING SETS()
• ANALYTIC FUNCTIONS

학습목표

• SUBQUERY와 함수에 대한 이해를 바탕으로 비효율적으로 수행되는 SQL문장을 진단할 수 있다.
• SUBQUERY와 함수를 활용하여 SQL 문장을 성능목표에 부합하도록 개선할 수 있다.
• SUBQUERY와 함수의 활용을 통해 개선된 SQL 문장이 성능목표에 부합하는지 테스트할 수 있다.

---

NESTED SUBQUERY

SELECT empno, ename
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

---

CORRELATED SUBQUERY

SELECT ename, empno
FROM emp
WHERE EXISTS(SELECT 'X' FROM dept WHERE dept.deptno = emp.deptno AND dept.dname = 'SALES');

---

SCALAR SUBQUERY

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dname
FROM emp e;

-- UDF로 변환
CREATE OR REPLACE FUNCTION f_dnm(a_dno IN dept.deptno%TYPE)
RETURN VARCHAR2
RESULT_CACHE
RELIES_ON(dept)
AS h_dnm dept.dname%type := NULL;
BEGIN
  SELECT dname INTO h_dnm
  FROM dept
  WHERE deptno = a_dno;

  RETURN h_dnm;
END;
/
SELECT ename, f_dnm(deptno) AS dname
FROM emp;

---

ROLLUP()&CUBE()

SELECT d.dname, e.job, COUNT(*) AS 'Empl Cnt', SUM(e.sal) AS 'Tot Sal'
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY d.dname, e.job
ORDER BY 1, 2;

SELECT d.dname, e.job, COUNT(*) AS 'Empl Cnt', SUM(e.sal) AS 'Tot Sal'
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY ROLLUP(d.dname, e.job)
ORDER BY 1, 2;

SELECT d.dname, e.job, COUNT(*) AS 'Empl Cnt', SUM(e.sal) AS 'Tot Sal'
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY CUBE(d.dname, e.job)
ORDER BY 1, 2;

---

GROUPING SETS()

SELECT d.dname, e.job, COUNT(*) AS 'Empl Cnt', SUM(e.sal) AS 'Tot Sal'
FROM dept d, emp e
WHERE d.deptno = e.deptno
GROUP BY GROUPING SETS((d.dname, e.job), (d.dname), ())
ORDER BY 1, 2;

---

ANALYTIC FUNCTIONS

• SYNTAX

  SELECT Analytic_Function(arguments) OVER ([Partition By 컬럼] [Order By절] [Windowing절])
  FROM 테이블명 ... WHERE ...;

• Argument : 함수에 따라 0-3개의 인자가 지정됨
• Partition By절 : 전체 집합을 기준에 의해 소그룹으로 나눔
• Order By절 : 어떤 항목에 대한 정렬 기준을 기술함
• Windowing절 : 함수에 의해서 제어하고자 하는 데이터 범위를 정의함

  /* Windowing절에 대한 이해(ROWS) */
  ROWS UNBOUNDED PRECEDING/
  ROWS CURRENT ROW/
  ROWS N PRECEDING (단, N>=0)
  ROWS BETWEEN UNBOUNDED PRECEDING/
             CURRENT ROW/
             N PRECEDING/
             N FOLLOWING
     AND UNBOUNDED FOLLOWING/
         CURRENT ROW/
         N PRECEDING/
         N FOLLOWING

  /* Windowing절에 대한 이해(RANGE) */
  RANGE UNBOUNDED PRECEDING/
  RANGE CURRENT ROW/
  RANGE N PRECEDING (단, N>=0)
  RANGE BETWEEN UNBOUNDED PRECEDING/
              CURRENT ROW/
              N PRECEDING/
              N FOLLOWING
     AND UNBOUNDED FOLLOWING/
         CURRENT ROW/
         N PRECEDING/
         N FOLLOWING

• 그룹 내 데이터 순위 관련
  • EMP 테이블에서 부서(DEPTNO) 별로 급여가 높은 사람 순으로 순위를 구하기 위한 SQL
  • ROW_NUMBER, RANK, DENSE_RANK 함수를 사용하여 세 함수의 차이점 비교

    SELECT deptno, ename, sal,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rno,
       RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rk,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS drk,
    FROM emp;

• 일반 그룹함수 관련
  • EMP 테이블에서 부서별로 각 사원을 기준으로 해서 급여에 대한 누계를 구하기 위한 SQL

    SELECT deptno, dname, sal,
       SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ASC ROWS UNBOUNDED PRECEDING) AS csum
    FROM emp;

• 그룹 내 비율 관련
  • EMP 테이블에서 업무별로 총급여를 기준으로 각 사원의 급여를 백분율(소수점으로)을 구하기 위한 SQL

    SELECT job, ename, sql,
       RATIO_TO_REPORT(sal) OVER (PARTITION BY job) AS rr
    FROM emp;