SQLD - SQL 활용 (PIVOT 절과 UNPIVOT 절, 정규 표현식)

데이터의 구조

1. LONG DATA (Tidy data)
   • 하나의 속성이 하나의 컬럼으로 정의되어 값들이 여러 행으로 쌓이는 구조
   • RDBMS의 테이블 설계 방식
   • 다른 테이블과의 조인 연산이 가능한 구조
2. WIDE DATA(Cross table)
   • 행과 컬럼에 유의미한 정보 전달을 목적으로 작성하는 교차표
   • 하나의 속성값이 여러 컬럼으로 분리되어 표현
   • RDBMS 에서 WIDE 형식으로 테이블 설계 시 값이 추가될 때마다 컬럼이 추가돼야 하므로 비효율적
   • 다른 테이블과의 조인 연산이 불가능
   • 주로 데이터를 요약할 목적으로 사용

 

데이터 구조 변경

1. PIVOT : LONG → WIDE
2. UNPIVOT : WIDE → LONG

 

PIVOT

• 교차표를 만드는 기능
• STACK 컬럼, UNSTACK 컬럼, VALUE 컬럼의 정의가 중요
• FROM 절에 STACK, UNSTACK, VALUE 컬럼명만 정의 필요 (필요 시 서브 쿼리 사용하여 필요 컬럼 제한)
• PIVOT 절에 UNSTACK, VALUE 컬럼명 저으이
• PIVOT 절 IN 연산자에 UNSTACK 컬럼 값을 정의
• FROM 절에 선언된 컬럼 중 PIVOT 절에서 선언한 VALUE 컬럼, UNSTACK 컬럼을 제외한 모든 컬럼은 STACK 컬럼이 됨

 

UNPIVOT

• WIDE 데이터를 LONG 데이터로 변경하는 문법
• STACK 컬럼 : 이미 UNSTACK 되어 있는 여러 컬럼을 하나의 컬럼으로 STACK 시 새로 만들 컬럼 이름(사용자 정의)
• VALUE 컬럼 : 교차표에서 셀 자리(VALUE) 값을 하나의 컬럼으로 표현하고자 할 때 새로 만들 컬럼명 (사용자 정의)
• 값 1, 값 2 : 실제 UNSTACK 되어 있는 컬럼 이름들

 

---

정규 표현식

• 문자열의 공통된 규칙을 보다 일반화하여 표현하는 방법
• 정규 표현식 사용 가능한 문자 함수 제공
• 정규 표현식 종류

 

REGEXP_REPLACE

• 정규식 표현을 사용한 문자열 치환 가능
• 문법 : (대상, 찾을 문자열, [바꿀 문자열], [검색 위치], [발견 횟수], [옵션])

1. 특징
   • 바꿀 문자열 생략 시 문자열 삭제
   • 검색 위치 생략시 1
   • 발견 횟수 생략 시 0(모든)
2. 옵션
   • - c : 대소를 구분하여 검색
   • - i : 대소를 구분하지 않고, 검색
   • - m : 패턴을 다중 라인으로 선언 가능

 

REGEXP_SUBSTR

• 정규식 표현식을 사용한 문자열 추출
• 옵션은 REGEXP_SUBSTR과 동일
• 문법 : (대상, 패턴, [검색 위치], [발견 횟수], [옵션], [추출 그룹])
• 특징 
  • 검색 위치 생략 시 1
  • 발견 횟수 생략 시 1
  • 추출 그룹은 서브 패턴을 추출 시 그 중 추출할 서브패턴 번호

 

REGEXP_INSTR

• 주어진 문자열에서 특정 패턴의 시작 위치를 반환
• 옵션은 REGEXP_SUBSTR과 동일
• 문법 : (원본, 찾을 문자열, [시작 위치], [발견 횟수], [옵션])
• 특징 
  • 시작 위치 생략 시 처음부터 확인 (기본값 : 1)
  • 발견 횟수 생략 시 처음 발견된 문자열 위치 리턴

 

REGEXP_LIKE

• 주어진 문자열에서 특정 패턴을 갖는 경우 반환(WHERE 절 사용만 가능)
• 옵션 REGEXP_REPLACE와 동일
• 문법 : (원본, 찾을 문자열, [옵션])

 

REGEXP_COUNT

• 주어진 문자열에서 특정 패턴의 횟수를 반환
• 옵션 REGEXP_REPLACE와 동일