[SQL 성능 분석하기] SUBQUERY와 함수의 활용

SUBQUERY와 함수의 활용

 

1. SUBQUERY의 종류

FROM 절: InlineVIEW

WHERE절: Correlated Subquery / Nested Subquery

 

>SELECT 옆, WHERE절 ORDER BY절에 위치한 서브쿼리를 Scalar Subquery라 부른다.

>GROUP BY 절을 제외하고는 어디든 서브쿼리가 위치할 수 있다.

 

2. NESTED SUBQUERY

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

> SUBQUERY가 가장 먼저 행해진다.

 

 

3. CORRELATED SUBQUERY

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

> 메인에서 데이터를 읽어와서 서브쿼리에 값을 주어야해서 메인쿼리가 먼저 행해진다.

 

4. SCALAR SUBQUERY

1건의 데이터만을 리턴한다.(2건일 시 에러)

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

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

> 함수를 만들어 사용하는 것과 거의 실행원리가 같다.

 

5. ROLLUP() & CUBE()

 

GROUP BY YEAR, REGION

YEAR	REGION
	CENTRAL	EAST	WEST
2020	100	200	200
2021			200

GROUP BY ROLLUP(YEAR, REGION)

YEAR	REGION			TOT
	CENTRAL	EAST	WEST
2020	100	200	200	500
2021			200	200
				700

GROUP BY CUBE(YEAR, REGION)

YEAR	REGION			TOT
	CENTRAL	EAST	WEST
2020	100	200	200	500
2021			200	200
TOT	100	200	400	700

 

6. GROOUPING 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),
                        (e.job), --추가 시, CUBE()와 같은 결과. 제외 시, ROLLUP()과 같은 결과
                        ())
ORDER BY 1,2;

 

7. ANALYTIC FUNCTIONS

SYNTAX

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

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

그룹 내 데이터 순위 관련

: EMP 테이블에서 부서(DEPTNO) 별로 급여가 높은 사람 순으로 순위를 구하기 위한 SQL

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;


DEPTNO	ENAME	SAL		RNO	RK	DRK
--------------------------------------------------------
10		KING	5000	1	1	1
10		CLARK	2450	2	2	2
10		MILLER	1300	3	3	3
--------------------------------------------------------
20		SCOTT	3000	1	1	1
20		FORD	3000	2	1	1
20		JONES	2975	3	3	2
20		ADAMS	1100	4	4	3
20		SMITH	800	5	5	4
--------------------------------------------------------
30		BLAKE	2850	1	1	1
30		ALLEN	1600	2	2	2
30		TURNER	1500	3	3	3
30		MARTIN	1250	4	4	4
30		WARD	1250	5	4	4
30		JAMES	950	6	6	5