[SQLD] SQL 개발자 공부하기 Day 10 : 계층형 조회 / PIVOT / UNPIVOT / 정규표현식

목차

1. 계층형 조회 / PIVOT / UNPIVOT
2. 정규표현식

---

1. 계층형 조회 / PIVOT / UNPIVOT

 

 

계층형 조회 CONNECT BY

‣ Oracle 데이터베이스에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있다.

‣ 부장에서 차장, 차장에서 과장, 과장에서 대리, 대리에서 사원 순으로 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것이다.

 

 

(1) CONNECT BY 키워드

키워드	설명
LEVEL	‣ 검색 항목의 깊이를 의미한다. 계층구조에서 가장 상위 레빌이 1이 된다.
CONNECT_BY_ROOT	‣ 계층 구조에서 가장 최상위 값을 표시한다.
CONNECT_BY_ISLEAF	‣ 계층 구조에서 가장 최하위 값을 표시한다.
SYS_CONNECT_BY_PATH	‣ 계층 구조의 전체 전개 경로를 표시한다.
NOCYCLE	‣ 순환 구조가 발생지점까지만 전개된다.
CONNECT_BY_ISCYCLE	‣ 순환 구조 발생 지점을 표시한다.
START WITH 조건	‣ 계층 전개의 시작 위치를 지정하는 것이다.
PRIOR 자식 = 부모	‣ 부모에서 자식방향으로 검색을 수행하는 순방향 전개이다.
PRIOR 부모 = 자식	‣ 자식에서 부모방향으로 검색을 수행하는 역방향 전개이다.
NOCYCLE	‣ 데이터를 전개하면서 이미 조회된 데이터를 다시 조회되면 CYCLE이 형성된다. ‣ 이 때 NOCYCLE은 사이클이 발생되지 않게 한다.
Order sibling by 칼럼명	‣ 동일한 LEVEL인 형제노드 사이에서 정렬을 수행한다.

 

 

(2) CONNECT BY 예제

 

SELECT id, name, mentor_id, CONNECT_BY_ISLEAF
FROM students
START WITH mentor_id IS NULL
CONNECT BY PRIOR id = mentor_id;

 

 

SELECT id, name, mentor_id, CONNECT_BY_ISLEAF

‣ ID, NAME, MENTOR_ID, 그리고 현재 노드가 리프 노드인지 여부를 나타내는 값을 조회.

 

FROM students

‣ 'students' 테이블에서 데이터를 조회.

 

START WITH mentor_id IS NULL

‣ MENTOR_ID가 NULL인 레코드, 즉 최상위 노드부터 조회를 시작.

 

CONNECT BY PRIOR id = mentor_id

‣ 현재 로우의 'id' 값과 이전 로우의 'mentor_id' 값을 비교하여 연결되는 계층적 구조를 만듭니다.

 

 

 

PIVOT과 UNPIVOT

‣ 테이블에서 하나의 칼럼에 있는 행 값들을 펼쳐 각각을 하나의 칼럼으로 만들어 주는 것.

‣ 데이터를 행기반에서 열기반으로 바꾸는 것을 PIVOT (행→열)

‣ 데이터를 열기반에서 행기반으로 바꾸는 것을 UNPIVOT (열→행)

 

 

(1) 예제

 

반응형

SELECT * FROM( SELECT deptno, job, sal FROM EMP)
PIVOT(AVG(SAL) FOR job IN
('analyst' AS analyst, 'clerk' AS clerk, 'manager' AS manager, 'president' AS president, 'salesman' AS salesman)
);

 

 

‣ deptno에 대해, job별로 평균 sal을 계산한 후, 열로 나열한 피벗 테이블을 보여줍니다.

 

 

 

테이블 파티션 Table Partition

‣ 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다.

‣ 테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상된다.

‣ 파티션은 각각의 파티션 별로 독립적으로 관리될 수 있다. → 파티션 별로 백업하고 복구가 가능하면 파티션 전용 인덱스 생성도 가능하다.

‣ 파티션은 Oracle 데이터베이스의 논리적 관리 단위인 테이블 스페이스 간에 이동이 가능하다.

‣ 데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향상시킨다.

 

 

(1) Range Partition

‣ 테이블의 칼럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것이다.

 

 

‣ 행을 분리할 때 SAL 값의 범위를 기준으로 분리한다.

 

 

(2) List Partition

‣ 특정 값을 기준으로 분할하는 방법이다.

 

728x90

‣ 행을 분리할 때 DEPTNO 값을 기준으로 분리한다.

 

 

(3) Hash Partition

‣ 데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용해서 데이터를 분할한다.

‣ 결과적으로 데이터베이스 관리 시스템이 알아서 분할하고 관리하는 것이다.

 

 

(4) Composite Partition

‣ 여러 개의 파티션 기법을 조합해서 사용하는 것이다.

 

 

(5) 파티션 인덱스

‣ 여러 개의 파티션 기법을 조합해서 사용하는 것이다.

파티션 인덱스	설명
Global Index	‣ 여러 개의 파티션에서 하나의 인덱스를 사용한다.
Local Index	‣ 해당 파티션 별로 각자의 인덱스를 사용한다.
Prefixed Index	‣ 파티션 키와 인덱스 키가 동일하다.
Non Prefixed Index	‣ 파티션 키와 인덱스 키가 다르다.

 

 

 

---

2. 정규표현식 (Regular Expression)

 

정규표현식 Regular Expression

‣ 특정한 규칙을 가지고 있는 문자열 집합을 표현하기 위해서 사용되는 형식 언어이다.

‣ 데이터 중에서 전화번호, 주민등록번호 등 특정 규칙을 가지고 있는 데이터를 찾을 때 정규표현식을 사용한다.

 

 

(1) Oracle 정규표현식

구분	설명
REGEXP_LIKE	‣ LIKE문과 유사하고 정규표현식을 검색한다.
REGEXP_REPLACE	‣ 정규표현식을 검색한 후에 문자열을 변경한다.
REGEXP_INSTR	‣ 정규표현식을 검색하고 위치를 반환한다.
REGEXP_SUBSTR	‣ 정규표현식을 검색하고 문자열을 추출한다.
REGEXP_COUNT	‣ 정규표현식을 검색하고 발견된 횟수를 반환한다.

 

 

(2) 정규표현식을 사용하기 위한 메타문자

구분	설명
.	‣ 임의의 한문자이다.
?	‣ 앞 문자가 없거나 하나 있음을 의미한다.
+	‣ 앞 문자가 하나 이상 있음을 의미한다.
*	‣ 앞 문자가 0개 이상 있음을 의미한다.
{m}	‣ 선행 표현식이 정확히 m번 발생한다.
{m,}	‣ 선행 표현식이 최소 m번 이상 발생한다.
{m,n}	‣ 선행 표현식이 최소 m번 이상, 최대 n번 이하 발생한다.
[・・・]	‣ 괄호 안의 리스트에 있는 임의의 단일 문자와 일치한다.
|	‣ OR을 의미한다.
^	‣ 문자열 시작 부분과 일치한다.
[^]	‣ 해당 문자에 해당하지 않는 한 문자이다.
$	‣ 문자열의 끝 부분과 일치한다.
₩	‣ 표현식에서 후속 문자를 일반문자로 처리한다.
₩n	‣ 괄호 안에 그룹화 된 n번째(1-9) 선행 하위식과 일치한다.
₩d	‣ 숫자 문자이다.
[:class:]	‣ 지정된 POSIX 문자 클래스에 속한 임의의 문자와 일치한다. ‣ [:alpha:] = 알파벳 문자이다. ‣ [:digit:] = 숫자이다. ‣ [:lower:] = 소문자이다. ‣ [:upper:] = 대문자이다. ‣ [:alnum:] = 알파벳 및 숫자이다. ‣ [:space:] = 공백 문자이다. ‣ [:punct:] = 특수문자이다. ‣ [:cntrl:] = 컨트롤 문자이다. ‣ [:print:] = 출력 가능한 문자이다.
[^:class:]	‣ 괄호안의 리스트에 없는 임의의 단일 문자와 일치한다.