📊 Oracle SQL 분석함수, 정규표현식, 테이블 관리 완전정리

1️⃣ 분석 함수 (Analytic Functions)

분석 함수는 GROUP BY처럼 행 수를 줄이지 않고, 전체 결과에서 각 행에 대해 집계값을 계산할 수 있어.
대량의 데이터에서 누적 합계, 부서별 순위, 구간별 평균 등을 쉽게 처리할 수 있음.

✅ 분석 함수 기본 문법

분석함수(인자) OVER (PARTITION BY ~ ORDER BY ~)

• PARTITION BY: 데이터를 그룹으로 나눔
• ORDER BY: 그룹 내에서 정렬

✅ 주요 분석 함수

함수	설명
FIRST_VALUE(수식)	정렬된 값 중 첫 번째 반환
LAST_VALUE(수식)	정렬된 값 중 마지막 반환
COUNT(수식)	조건을 만족하는 행의 수
SUM(수식)	조건을 만족하는 합계
ROW_NUMBER()	그룹별 순번 부여
RANK()	순위 반환 (동점 시 건너뜀)
DENSE_RANK()	순위 반환 (동점 시 다음 번호로 진행)

🧪 예시

-- 각 부서의 최고 급여 보기
SELECT last_name, department_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS high_sal_by_department
FROM employees1;

-- SA 부서 직원들의 월급과 최대/최소 급여
SELECT last_name, job_id, salary, 
FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS SAL_FIRST,
LAST_VALUE(salary) OVER () AS SAL_LAST
FROM employees1
WHERE job_id LIKE 'SA%';

---

2️⃣ 정규 표현식 (Regular Expression)

정규표현식은 특정 규칙을 만족하는 문자열을 찾거나 처리할 때 사용하는 기법이야. Oracle에서는 REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE 등을 사용함.

✅ 주요 패턴 기호

 

패턴	의미
^A	A로 시작하는 문자열
B$	B로 끝나는 문자열
[a-z]	소문자 하나
[^PS]	P나 S가 아닌 문자

✅ 자주 쓰는 함수

1. REGEXP_LIKE

특정 패턴을 포함한 행 찾기

-- 'S'로 시작하고 'P'를 포함하지 않는 제품명
SELECT product_name 
FROM product_information
WHERE REGEXP_LIKE(product_name, '^S[^P]');

2. REGEXP_SUBSTR

패턴에 맞는 문자열 일부 추출

-- 이메일에서 도메인 추출
SELECT cust_email, 
REGEXP_SUBSTR(cust_email, '@(\w+.+)') AS DOMAIN 
FROM customers
WHERE nls_territory = 'ITALY' AND ROWNUM <= 5;

3. REGEXP_REPLACE

패턴과 일치하는 부분을 바꿈

-- 전화번호 포맷 변경
SELECT phone_number, 
REGEXP_REPLACE(phone_number, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '\1) \2-\3') AS NEW_PHONE_NO
FROM employees1;

---

3️⃣ 테이블 기본 및 관리

✅ 테이블 기본

• 행(Row): 각 레코드
• 열(Column): 데이터 항목
• 필드(Field): 행과 열이 만나는 값

✅ 테이블 생성

CREATE TABLE 테이블명 (
  열명1 데이터타입1,
  열명2 데이터타입2,
  ...
);

📌 주요 데이터 타입

타입	설명
CHAR(n)	고정길이 문자
VARCHAR2(n)	가변길이 문자
NUMBER(p,s)	숫자 (p: 전체 자리, s: 소수점 이하)
DATE	날짜
CLOB/BLOB	대용량 문자/바이너리

✅ INSERT 문법

INSERT INTO 테이블명 (열1, 열2, ...) VALUES (값1, 값2, ...);

---

4️⃣ 테이블 구조 변경

작업	명령어
열 추가	ALTER TABLE 테이블명 ADD (열명 데이터타입)
열 이름 변경	ALTER TABLE 테이블명 RENAME COLUMN A TO B
열 타입 변경	ALTER TABLE 테이블명 MODIFY (열명 새타입)
열 삭제	ALTER TABLE 테이블명 DROP COLUMN 열명
행 삭제	DELETE FROM 테이블명 WHERE 조건
테이블 삭제	DROP TABLE 테이블명

---

5️⃣ 테이블 복사 및 분할

✅ 테이블 복사

 

목적	명령어
전체 복사	CREATE TABLE 새테이블 AS SELECT * FROM 원본테이블
틀만 복사	CREATE TABLE 새테이블 AS SELECT * FROM 원본테이블 WHERE 1=0
일부 컬럼만 복사	CREATE TABLE 새테이블 AS SELECT 열1, 열2 FROM 원본테이블

✅ 테이블 분할 예시

1. 연도별 테이블 생성

CREATE TABLE order_1996 AS SELECT * FROM orders WHERE 1=0;

1. 조건별로 데이터 삽입

INSERT ALL
  WHEN TO_CHAR(order_date, 'yyyy') = '1996' THEN INTO order_1996
  WHEN TO_CHAR(order_date, 'yyyy') = '1999' THEN INTO order_1999
  ...
SELECT * FROM orders;

---

🧠 마무리 요약

• 분석함수는 집계값을 유지하면서 개별 행 분석 가능!
• 정규표현식은 문자 패턴 매칭, 추출, 치환에 강력!
• 테이블 관리는 CREATE, ALTER, INSERT, DELETE로 구조와 데이터를 자유롭게 다룰 수 있음!
• 분할 전략은 트래픽 분산과 성능 향상에 핵심! 🚀