3-2. SQL 활용

박영빈·2023년 9월 2일

SQLD 이기적

SQL Developer

목록 보기

5/5

조인(JOIN)

EQUI(등가) JOIN

조인은 여러 개의 릴레이션을 사용하여 새로운 릴레이션을 만드는 과정이다.
기본적으로 교집합을 만드는 것이며, 두 테이블 간 일치하는 것을 조인한다.

SELECT * FROM EMP, DEPT
	WHERE EMP.DEPTNO = DEPT.DEPTNO;
# 이처럼 동일한 컬럼을 기준으로 합친다.

SELECT * FROM EMP, DEPT
	WHERE EMP.DEPTNO = DEPT.DEPTNO
	AND EMP.ENAME LIKE '임%'
	ORDER BY ENAME;
# 추가적인 조건을 걸수도 있다 -> 임~ 인 튜플만 출력됨

INNER JOIN
- EQUI JOIN과 마찬가지로 ISO 표준 SQL이다.
- INNER JOIN은 ON문을 사용하여 테이블을 연결한다.
- 그냥 EQUI JOIN과 동일하다고 보면 됨
```
SELECT * FROM EMP INNER JOIN DEPT
	ON EMP.DEPTNO = DEPT.DEPTNO
	AND EMP.ENAME LIKE '임%'
	ORDER BY ENAME;
```

INTERSECT 연산

두 개의 테이블에서 교집합을 조회한다.

SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
# EMP와 DEPT의 DEPTNO에서 동일한 값을 조회

Non-EQUI(비등가) JOIN

두 테이블 간 조인하는 경우 “=”를 사용하지 않고 “>”, “<”, “≥”, “≤”등을 사용한다.
즉, 정확하게 일치하지 않는 것을 JOIN 한다.

OUTER JOIN

두 테이블 간 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함하여 조회한다.
왼쪽 테이블에만 있는 행도 포함하면 LEFT OUTER JOIN, 오른쪽 테이블의 행만 포함하면 RIGHT OUTER JOIN, 양쪽 모두 포함하면 FULL OUTER JOIN이라고한다.
Oracle에서는 “(+)” 기호를 통해 사용 가능하다.

CROSS JOIN

조인 조건 없이 두 테이블을 하나로 조인한다.
조건이 없으므로 카테시안 곱이 발생한다.

SELECT * FROM EMP CROSS JOIN DEPT;

UNION을 사용한 합집합

UNION 연산은 두 테이블을 하나로 합치는 연산이다.
주의사항은 두 테이블의 칼럼 수, 칼럼의 데이터 형식이 모두 동일해야 한다.
UNION 연산은 두 테이블을 하나로 합치면서 중복 된 데이터를 제거하며 정렬을 발생시킨다.
UNION ALL 연산은 동일하지만 중복을 제거하거나 정렬을 발생시키지 않는다.

SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM EMP;

SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM EMP;

# 위는 20, 30, 40 아래는 20, 30, 30, 20, 40

차집합을 만드는 MINUS

MINUS 연산은 두 테이블에서 차집합을 조회한다.
앞의 SELECT문에는 있고 뒤의 SELECT문에는 없는 집합을 조회한다.

SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;
# 40

계층형 조회(CONNECT BY)

이는 Oracle에서 지원하며, 계층형으로 데이터를 조회할 수 있다.
CONNECT BY는 트리 형태의 구조로 질의를 수행하는 것으로, START WITH는 시작조건, CONNECT BY PRIOR는 조인 조건이다. ROOT 노드로부터 하위 노드의 질의를 실행한다.
계층형 조회에서 MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있다
CONNECT BY 키워드
- LEVEL : 검색 항목의 깊이, 가장 상위 레벨이 1이 된다.
- CONNECT_BY_ROOT : 가장 최상위 값을 표시
- CONNECT_BY_ISLEAF : 가장 최하위 값을 표시
- SYS_CONNECT_BY_PATH : 전체 전개 경로를 표시
- NOCYCLE : 순환 구조 발생지점까지만 전개
- CONNECT_BY_ISCYCLE : 순환 구조 발생 지점을 표시
계층형 조회
- START WITH 조건 : 계층 전개의 시작 위치 지정
- PRIOR 자식 = 부모 : 부모에서 자식방향으로 검색을 수행하는 순방향 전개
- PRIOR 부모 = 자식 : 자식에서 부모방향으로 검색을 수행하는 역방향 전개
- NOCYCLE : 데이터를 전개할 때 이미 조회한 데이터를 다시 조회하면 CYCLE이 형성되는데, 이 사이클이 발생하지 않게 한다.
- Order sibling by 칼럼명 : 동일한 LEVEL인 형제노드 사이에서 정렬을 수행

서브쿼리(Subquery)

서브쿼리는 SELECT문 안에 SELECT문을 사용하는 SQL문
FROM문에 SELECT문을 쓰면 인라인 뷰(Inline View), SELECT문에 쓰면 스칼라 서브쿼리(Scalar Subquery)라고 한다.

SELECT * FROM EMP
	WHERE DEPTNO = (SELECT DEPTNO FROM DEPT
										WHERE DEPTNO = 10);
# WHERE 안의 SELECT문을 서브쿼리라고 하며 밖의 SELECT문은 메인쿼리라고 한다.

단일 행 서브쿼리와 다중 행 서브쿼리

반환하는 행의 개수에 따라 단일 행 서브쿼리, 멀티 행 서브쿼리로 분류한다.
단일 행 서브쿼리는 단 하나의 행만 반환하고 =, <, >, ≤, ≥, <>를 사용한다.
다중 행 서브쿼리는 여러 행을 반환하며 IN, ANY, ALL, EXISTS를 사용한다.

다중 행 서브쿼리

IN : 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나만 동일하면 참
ALL : 메인쿼리와 서브쿼리의 결과가 모두 동일해야 참
ANY : 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나 이상 동일하면 참
EXISTS : 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참

SELECT ENAME, DNAME, SAL FROM EMP, DEPT
	WHERE EMP.DEPTNO-DEPT.DEPTNO AND EMP.EMPNO
	IN (SELECT EMPNO FROM EMP WHERE SAL > 2000);
# EMP에서 급여가 2000이 넘는 사원을 조회하고 메인쿼리의 사원번호와 비교하여 같은것만 조회

SELECT * FROM EMP
	WHERE DEPTNO <= ALL (20, 30);
# DEPTNO가 20,30 보다 작거나 같으면 조회

SELECT ENAME, DNAME, SAL FROM EMP, DEPT
	WHERE EMP.DEPTNO=DEPT.DEPTNO
	AND EXISTS (SELECT 1 FROM EMP WHERE SAL > 2000);
# 급여가 2000보다 큰 직원이 있으면 AND 뒷 내용이 TRUE

스칼라 서브쿼리

반드시 한 행과 한 칼럼만 반환하는 서브쿼리

SELECT ENAME, SAL, (SELECT AVG(SAL) FROM EMP) AS "평균급여" FROM EMP
	WHERE EMPNO = 1000;
# 괄호 안 SELECT문은 한 행 및 한 칼럼만 조회 됨

연관 서브쿼리

서브쿼리 내에서 메인쿼리 내의 칼럼을 사용하는 것을 의미한다.

그룹함수

ROLLUP

GROUP BY의 칼럼에 대해 Subtotal을 만들어 준다.
ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라 결과가 달라진다.

SELECT DECODE (DEPTNO, NULL, '전체합계', DEPTNO), SUM(SAL) FROM EMP
	GROUP BY ROLLUP(DEPTNO);
# GROUP BY로 부서별 급여합계를 구하고 ROLLUP을 통해 부서별 전체 합계를 추가로 계산한다.
# DECODE문을 통해 DEPTNO가 NULL이면 전체합계를 출력한다.

GROUPING 함수

GROUPING은 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분해준다.
소계, 합계 등이 계산되면 GROUPING은 1을 반환하고 그렇지 않다면 0을 반환한다.

SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL) FROM EMP
	GROUP BY ROLLUP(DEPTNO, JOB);
# 소계와 합계로 계산된 데이터는 GROUPING 출력이 1로 나온다.

SELECT DEPTNO, DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, 
			JOB, DECODE(GROUPING(JOB), 1, '전체합계') T_DEPT), SUM(SAL) FROM EMP
	GROUP BY ROLLUP(DEPTNO, JOB);
# 이를 이용해 DECODE문에 활용 가능하다.

GROUPING SETS 함수

GROUP BY에 나오는 칼럼의 순서와 상관없이 다양한 소계를 만들 수 있다. 개별적으로 모두 처리함

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP
	GROUP BY GROUPING SETS(DEPTNO, JOB);

CUBE 함수

CUBE함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다.
예를 들어 부서와 직업을 제시하면 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체합계가 모두 조회된다. 즉, 조합 가능한 모든 조합이 조회된다.

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP
	GROUP BY CUBE(DEPTNO, JOB);
# 전체합계, 직업별 합계, 부서별 합계, 부서별 직업별 합계 모두 조회

윈도우 함수

행과 행간의 관계를 정의하기 위해 제공되는 함수
순위, 합계, 평균, 행 위치 등을 조작 가능

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER(PARTITION BY 칼럼 ORDER BY WINDOWING절) FROM 테이블명;

윈도우 함수 구조
- ARGUMENTS : 인수, 함수에 따라 개수가 다름
- PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눔
- ORDER BY : 정렬
- WINDOWING : 행 기준의 범위를 정한다. ROWS는 물리적 결과의 행의 수, RANGE는 논리적인 값에 의한 범위이다.
WINDOWING
- ROWS : 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
- RANGE : 논리적인 주소에 의해 행 집합을 지정
- BETWEEN~AND : 윈도우 시작과 끝을 지정
- UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫번째 행임을 의미
- UNBOUNDED FOLLOWING : 윈도우 시작 위치가 마지막 행임을 의미
- CURRENT ROW : 윈도우 시작 위치가 현재 행임을 의미

SELECT EMPNO, ENAME, SAL
			SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND
																				UNBOUNDED FOLLOWING) TOTSAL FROM EMP;
# PRECEDING은 첫번째 행, FOLLOWING은 마지막 행이므로 모든 합계를 계산

SELECT EMPNO, ENAME, SAL
			SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND
																				CURRENT ROW) TOTSAL FROM EMP;
# PRECEDING은 첫번째 행, FOLLOWING은 현재 행이므로 계속해서 누적합계가 계산

SELECT EMPNO, ENAME, SAL
			SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN CURRENT ROW AND
																				UNBOUNDED FOLLOWING) TOTSAL FROM EMP;
# 따라서 이처럼 적으면 현재행부터 마지막까지의 합계를 계산

순위함수

특정 항목과 파티션에 대해 순위를 계산할 수 있는 함수를 제공
- RANK : 특정항목 및 파티션에 대해 순위를 계산, 동일한 순위는 동일한 값 부여
- DENSE_RANK : 동일한 순위를 하나의 건수로 계산
- ROW_NUMBER : 동일한 순위에 대해서 고유의 순위를 부여
```
SELECT ENAME, SAL,
			RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
			RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP
# ALL_RANK는 SAL로 등수를 계산하고 내림차순으로 조회
# JOB_RANK는 JOB으로 파티션을 만들고 JOB별 순위를 조회
```

집계함수

SUM : 파티션 별로 합계를 계산
AVG : 파티션 별로 평균을 계산
COUNT : 파티션 별로 행 수를 계산
MAX, MIN : 최댓값과 최솟값을 계산

SELECT ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) SUM_MGR FROM EMP;
# 같은 관리자에 파티션을 만들고 합계를 계산

행 순서 관련 함수

상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력하거나 특정 위치의 행을 출력 가능하다.
FIRST_VALUE : 파티션에서 가장 처음에 나오는 값, MIN 함수로 같은 결과를 구할 수 있음
LAST_VALUE : 파티션에서 가장 나중에 나오는 값, MAX 함수로 같은 결과를 구할 수 있음
LAG : 이전 행을 가지고 온다.
LEAD : 윈도우에서 특정 위치의 행을 가지고 온다. 기본값은 1이다.

SELECT DEPTNO, ENAME, SAL,
		FIRST VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC 
				ROWS UNBOUNDED PRECEDING) AS DEPT_A FROM EMP;
# 파티션에서 조회된 행 중 첫 번째 값을 가지고 온다.
# SAL 내림차순으로 조회하였으므로 의미상 가장 급여가 많은 사원이 조회된다.

SELECT DEPTNO, ENAME, SAL,
		LAST VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC 
			ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWTING) AS DEPT_A FROM EMP;
# 파티션에서 조회된 행 중 마지막 값을 가지고 온다.
# 현재 행에서부터 마지막 행까지의 파티션에 대해 조회하므로 어쨌든 마지막 행

SELECT DEPTNO, ENAME, SAL,
		LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL FROM EMP;
# 이전 값을 가져온다.
# SAL 칼럼을 한칸씩 아래로 땡겨서 PRE_SAL에 출력한다고 보면 됨

SELECT DEPTNO, ENAME, SAL,
		LEAD(SAL,2) OVER(ORDER BY SAL DESC) AS PRE_SAL FROM EMP;
# 지정된 위치의 값을 가져온다.
# 여기서는 SAL 칼럼의 2번째 값을 가져온다.

비율 관련 함수

누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회 가능하다.
CUME_DIST : 파티션 전체에서 현재 행보다 작거나 같은 건수에 대해 누적 백분율을 조회
PERCENT_RANK : 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나은 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
NTILE : 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회
RATIO_TO_REPORT : 파티션 내에 SUM에 대한 행 별 칼럼값의 백분율을 소수점까지 조회

SELECT DEPTNO, ENMAE, SAL,
			PERCENT RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PERCENT_SAL FROM EMP;
# 같은 부서에서 자신의 급여 퍼센트를 구한다

SELECT DEPTNO, ENMAE, SAL,
			NTILE(4) OVER(ORDER BY SAL DESC) AS N_TILE FROM EMP;
# 급여가 높은 순으로 4개로 나누어 분류한다.

테이블 파티션

대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다.
테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상된다.
파티션은 각각 독립적으로 관리될 수 있다. 파티션 별 백업과 복구가 가능하면 파티션 전용 인덱스 생성도 가능하다.
파티션은 Oracle 데이터베이스의 논리적 관리 단위인 테이블 스페이스 간에 이동이 가능함
데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향상시킨다.

Range Partition

테이블의 칼럼 중 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장
SAL 칼럼이 2000~4000은 datafile1.dbf에 저장하고 5000~7000은 datafile2.dbf에 저장한다.

List Partition

특정 값을 기준으로 분할하는 방법이다.
DEPTNO 칼럼의 값이 10이면 datafile1.dbf에, 20이면 datafile2.dbf에 저장한다.

Hash Partition

데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용하여 분할한다.
결과적으로 시스템이 알아서 분할하고 관리한다.

Partition Index

파티션 인덱스는 4가지 유형의 인덱스를 제공한다.
- Global Index : 여러 개의 파티션에서 하나의 인덱스를 사용한다.
- Local Index : 해당 파티션 별로 각자의 인덱스를 사용한다.
- Prefixed Index : 파티션 키와 인덱스 키가 동일하다.
- Non Prefixed Index : 파티션 키와 인덱스 키가 다름
Oracle은 Global Non-Prefixed를 지원하지 않는다.

박영빈

안녕하세요<br>반가워요<br>안녕히가세요

이전 포스트

3-2. SQL 활용

SQL Developer

조인(JOIN)

EQUI(등가) JOIN

Non-EQUI(비등가) JOIN

OUTER JOIN

CROSS JOIN

UNION을 사용한 합집합

차집합을 만드는 MINUS

계층형 조회(CONNECT BY)

서브쿼리(Subquery)

단일 행 서브쿼리와 다중 행 서브쿼리

다중 행 서브쿼리

스칼라 서브쿼리

연관 서브쿼리

그룹함수

ROLLUP

GROUPING 함수

GROUPING SETS 함수

CUBE 함수

윈도우 함수

순위함수

집계함수

행 순서 관련 함수

비율 관련 함수

테이블 파티션

Range Partition

List Partition

Hash Partition

Partition Index

3-3. SQL 최적화의 원리

0개의 댓글