02. SQL활용 (2)

• Top N 쿼리
  
  • ROWNUM : 오라클DB의 SELET문 결과에 대해 논리적인 일련번호 부여 / 조회되는 행 수 제한시 많이 사용 / 화면에 데이터를 출력할 때 부여되는 논리적인 순번... 만약 페이지 단위 출력하고 싶으면 인라인 뷰를 사용해야 함
  • ROWID : 오라클DB 내에서 데이터를 구분할 수 있는 유일한 값 / SELECT문으로 확인 가능 / 데이터가 어떤 데이터 파일, 어느 블록에 저정되어 있는지 확인 가능 / 모든 테이블은 ROWID를 가지고 있음
    • 구조
      • 오브젝트 번호(1~6) : 오브젝트 별로 유일한 값을 가지고 있고, 해당 오브젝트가 속해 있는 값
      • 상대 파일 번호(7~9) : 테이블스페이스에 속해 있는 데이터 파일에 대한 상대 파일번호
      • 블록 번호(10~15) : 데이터 파일 내부에서 어느 블록에 데이터가 있는지 알려줌
      • 데이터 번호(16~18) : 데이터 블록에 데이터가 저장되어 있는 순서 의미

// ROWNUM
SELECT * FROM EMP WHERE ROWNUM <=1;		// 한 행 조회
// 별칭 사용
SELECT *
FROM (SELECT ROWNUM list, ENAME FROM EMP)	// 인라인뷰
WHERE list <= 5;		// 5건의 행 조회
// 특정 행만 조회
SELECT * FROM(SELECT ROWNUM list, ENAME, FROM EMP)
WHERE list BETWEEN 5 AND 10;
// MySQL에서
SELECT * FROM EMP LIMIT 10;		// 10명만 인출

// ROWID
SELECT rowid, winetypename FROM wintype;

• 계층형 조회(CONNECT BY) : 오라클DB에서 제공... 계층형으로 데이터 조회 가능 / 트리 형태
  • 키워드
    • LEVEL : 검색 항목 깊이(계층구조에서 가장 상위레벨이 1)
    • CONNECT_BY_ROOT : 계층 구조에서 가장 최상위 값 표시
    • CONNECT_BY_ISLEAF : 계층 구조에서 가장 최하위
    • SYS_CONNECT_BY_PATH : 계층 구조의 전체 전개 경로 표시
    • NOCYCLE : 순환구조가 발생지점까지만 전개
    • CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시
  • 계층형 조회
    • START WITH 조건 : 계층 전개 시작 위치 지정
    • PRIOR 자식 = 부모 : 부모에서 자식 방향으로 검색 수행하는 순방향 전개
    • PRIOR 부모 = 자식 : 역방향 전개
    • NOCYCLE : 데이터를 전개하면서 이미 조회된 데이터를 다시 조회하면 사이클이 형성됨... 이 사이클이 발생되지 않도록 함
    • Order siblings by 칼럼명 : 동일한 LEVEL인 형제노드 사이에서 정렬 수행

// 최대 계층값 출력
SELECT MAX(LEVEL)
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;	// MGR로부터 EMPNO를 검색하는 순방향 전개

• PIVOT/UNPIVOT
  
  • 피벗? 테이블에서 하나의 칼럼에 있는 행 값들을 펼쳐 각각을 하나의 칼럼으로 만들어 주는 것 / PIVOT : 행기반 → 열기반 / UNPIVOT : 열기반 → 행기반

피벗 예시

SELECT 상품명, [2023], [2024]
FROM (SELECT 상품명, 수량, 주문연도 FROM 주문) AS D
	PIVOT(SUM(수량) FROM 주문연도 IN([2023], [2024])) AS PVT;

• 테이블 파티션(Table Partition)
  • 파티션 기능) 대용량 테이블을 여러 개 데이터 파일에 분리해서 저장... 입력, 수정, 삭제, 조회 성능 향상 / 각각의 파티션 별로 독립적으로 관리 가능... 백업 복구 가능하면 파티션 전용 인덱스 생성도 가능 / 오라클DB의 논리적 관리 단위인 테이블 스페이스 간에 이동 가능 / 데이터 조회시 데이터의 범위를 줄여 성능 향상
  • Range Partition : 테이블의 칼럼 중, 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나눠 저정하는 것
  • List Partition : 특정 값을 기준으로 분할
  • Hash Partition : DBMS이 내부적으로 해시 함수를 사용해 데이터 분할... DBMS가 알아서 분할하고 관리함
  • Composite Partition : 여러 개 파티션 기법 조합해 사용
  • 파티션 인덱스) 오라클DB는 Global Non-Prefixed 지원 X
    • Global Index : 여러 개의 파티션에서 하나의 인덱스 사용
    • Local Index : 해당 파티션 별로 각자의 인덱스 사용
    • Prefixed Index : 파티션 키와 인덱스 키가 동일
    • Non Prefixed Index : 파티션 키와 인덱스 키가 다름
• 정규표현식(Regular Expression) : 특정 규칙을 가지고 있는 문자열 집합을 표현하기 위해서 사용되는 형식 언어
  • 오라클 정규표현식
    • REGEXP_LIKE : LIKE문과 유사... 정규표현식 검색
    • REGEXP_REPLACE : 정규표현식 검색 후 문자열 변경
    • REGEXP_INSTR : 정규표현식 검색 후 위치 반환
    • REGEXP_SUBSTR : 정규표현식 검색 후 문자열 추출
    • REGEXP_COUNT : 정규표현식 검색 후 발견된 횟수 반환
  • 정규표현식 사용을 위한 메타문자

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

// 숫자가 포함된 문자 조회
SELECT SAL FROM EMP
WHERE regexp_like(SAL, '[0-9]');
// 영문자가 있는 데이터 찾기
WHERE regexp_like(DATA, '[a-zA-Z]');
// 이메일 패턴 찾기
WHERE regexp_like(DATA, '@([[:alnum:]]+\.?){3, 4}');