OLTP, OLAP, CTE란?

OLTP와 OLAP

OLTP (Online Transaction Processing)

• 정의: 실시간 트랜잭션 데이터 처리 시스템

• 특징

  • 빠른 입력·수정·삭제 중심
  • 높은 동시성 (수천~수백만 사용자 지원)
  • 밀리초 단위 응답 속도
  • 데이터 무결성 중심 정규화 구조

• 예시

  • POS(Point-of-Sale) 시스템
  • 온라인 뱅킹 시스템
  • CRM(고객 관계 관리) 소프트웨어

---

OLAP (Online Analytical Processing)

• 정의: 다차원 데이터 분석용 시스템

• 특징

  • 복잡한 분석 쿼리 중심
  • 과거 및 집계 데이터 분석
  • 비정규화 구조 (Star Schema, Snowflake Schema)
  • 초에서 분 단위 응답 시간

• 예시

  • 데이터 웨어하우스
  • BI 대시보드
  • 매출 분석 도구

---

OLTP vs OLAP 비교

항목	OLTP	OLAP
목적	트랜잭션 처리 및 무결성 유지	데이터 분석 및 집계
데이터 구조	정규화된 스키마	비정규화된 스키마
데이터 유형	현재, 상세 데이터	과거, 집계 데이터
쿼리 형태	INSERT, UPDATE, DELETE	SELECT, GROUP BY, JOIN
응답 속도	밀리초 단위	초~분 단위
주 사용자	운영 담당자	분석가, 관리자
대표 모델	dvdrental, ERP, CRM	데이터 마트, DW

---

데이터 관리 구조 예시

구성 흐름
운영 DB(OLTP) → ETL → Data Warehouse(OLAP) → Data Mart → 사용자 환경

활용 예시

• 학사관리, ERP, MES 등 운영 데이터 → ETL 변환 → 분석용 데이터 웨어하우스

---

OLTP vs OLAP 스키마 비교

항목	OLTP 스키마	OLAP 스키마
형태	정규화 (Normalized)	비정규화 (Denormalized)
목적	데이터 무결성, 중복 최소화	조회 성능, 분석 최적화
테이블 수	다수의 작은 테이블	소수의 큰 테이블
관계 복잡도	다수의 관계, JOIN 많음	단순 관계, JOIN 적음
쿼리 방식	단건 조회·수정	대용량 집계·조회

OLTP 예시: dvdrental 정규화 모델(관계형 데이터베이스 스키마)

• 

OLAP 예시

• 스타 스키마(Star Schema)
  • 
• 스노우플레이크 스키마(Snowflake Schema)
  • 

---

Dvdrental 스타 스키마 예시

Fact 테이블: fact_rental

포함 컬럼

• rental_id, customer_id, film_id, store_id, staff_id
• rental_date, return_date, payment_date, payment_amount

SELECT
r.rental_id, r.rental_date,
r.return_date, p.payment_date,
p.amount AS payment_amount,
r.customer_id, c.store_id,
r.staff_id, i.film_id
FROM rental r JOIN payment p
ON r.rental_id = p.rental_id
JOIN customer c
ON r.customer_id = c.customer_id
JOIN inventory i
ON r.inventory_id = i.inventory_id
JOIN staff sf
ON sf.staff_id = r.staff_id

---

CTE (Common Table Expression)

정의
SQL에서 임시 결과셋을 정의하여 SELECT, INSERT, UPDATE, DELETE 등에서 재사용하는 구조
WITH 절을 사용하여 선언

기본 구조

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
)
SELECT * FROM cte_name;

---

CTE 특징

• 가독성 향상: 복잡한 SQL 구조 단순화
• 재사용성 확보: 동일한 서브쿼리 반복 제거
• 재귀 쿼리 가능: 계층적 데이터 표현 지원
• 다중 CTE 지원: 한 쿼리 내 여러 CTE 정의 가능

---

CTE 예제 모음

1️⃣ 고객 정보와 주소 조회

WITH customer_info AS (
  SELECT c.customer_id, c.first_name, c.last_name, a.address, a.district
  FROM customer c
  JOIN address a ON c.address_id = a.address_id
)
SELECT * FROM customer_info
WHERE district = 'California';

---

2️⃣ 카테고리별 영화 수

WITH category_counts AS (
  SELECT c.name AS category, COUNT(*) AS film_count
  FROM film_category fc
  JOIN category c ON fc.category_id = c.category_id
  GROUP BY c.name
)
SELECT * FROM category_counts
ORDER BY film_count DESC;

---

3️⃣ 결제 금액 상위 고객 TOP 5

WITH customer_payments AS (
  SELECT customer_id, SUM(amount) AS total_payment
  FROM payment
  GROUP BY customer_id
)
SELECT cp.customer_id, c.first_name, c.last_name, cp.total_payment
FROM customer_payments cp
JOIN customer c ON cp.customer_id = c.customer_id
ORDER BY cp.total_payment DESC
LIMIT 5;

---

4️⃣ 재귀 CTE – 일자별 대여 건수

WITH RECURSIVE rental_dates AS (
  SELECT TIMESTAMP '2005-05-24' AS rental_day
  UNION ALL
  SELECT rental_day + INTERVAL '1 day'
  FROM rental_dates
  WHERE rental_day + INTERVAL '1 day' <= (SELECT MAX(rental_date) FROM rental)
)
SELECT DATE(rd.rental_day) AS rental_day, COUNT(r.rental_id) AS daily_rentals
FROM rental_dates rd
LEFT JOIN rental r ON DATE(r.rental_date) = DATE(rd.rental_day)
GROUP BY DATE(rd.rental_day)
ORDER BY rental_day;

---

5️⃣ 최근 1년간 결제 없는 고객 비활성화

WITH inactive_customers AS (
  SELECT c.customer_id
  FROM customer c
  LEFT JOIN payment p ON c.customer_id = p.customer_id
  WHERE p.payment_date IS NULL
     OR p.payment_date < CURRENT_DATE - INTERVAL '1 year'
)
UPDATE customer
SET active = 0
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);

---

6️⃣ 대여 30회 이상 고객 VIP 등록

WITH vip_candidates AS (
  SELECT customer_id
  FROM rental
  GROUP BY customer_id
  HAVING COUNT(*) >= 30
)
INSERT INTO vip_customer (customer_id, level)
SELECT customer_id, 'Gold'
FROM vip_candidates;

---

7️⃣ 1년 이상 대여 기록 없는 고객 삭제

WITH inactive_customers AS (
  SELECT c.customer_id
  FROM customer c
  LEFT JOIN rental r ON c.customer_id = r.customer_id
  GROUP BY c.customer_id
  HAVING MAX(r.rental_date) < CURRENT_DATE - INTERVAL '1 year'
)
DELETE FROM customer
WHERE customer_id IN (SELECT customer_id FROM inactive_customers);

---

CTE vs JOIN 비교

항목	JOIN	CTE
결합 방식	테이블 직접 결합	임시 결과셋 정의 후 참조
가독성	복잡한 중첩 구조	블록 단위 명확 구성
재사용성	동일 쿼리 반복 필요	CTE 이름으로 재사용 가능
유지보수성	구조 복잡	논리 분리 용이

---

정리

핵심 요약

• OLTP: 실시간 트랜잭션 처리 시스템
• OLAP: 분석 중심 데이터 처리 시스템
• CTE: SQL 내 임시 결과셋 구조로 가독성·재사용성 강화

활용 포인트

• OLTP → 데이터 무결성 중심 구조
• OLAP → 분석 성능 중심 구조
• CTE → 복잡한 SQL 간소화 및 분석 효율 향상