분석을 위한 임시 테이블 만들기

PostgreSQL에서 임시 테이블은 데이터를 잠깐 저장하려고 만드는 테이블이고, 세션이나 트랜잭션이 끝나면 자동으로 삭제돼(설정에 따라 달라). 복잡한 작업을 단순화하거나 쿼리 속도를 올릴 때 특히 유용하지.

예를 들어, 네가 미슐랭 3스타 레스토랑에서 요리사라고 생각해봐. 샐러드 하나 만들자고 주방에 테이블 하나를 계속 차지하고 있진 않겠지? 임시 테이블은 딱 그 임시 작업대 같은 거야: 처리하고, 쓰고, 치우는 거지.

장점:

• 데이터 격리: 임시 테이블은 현재 세션이나 트랜잭션 안에서만 보여. 너 말고는 아무도 그 내용에 손 못 대.
• 효율성: 중간 결과를 저장해서 복잡한 쿼리를 반복 실행하지 않아도 돼.
• 깔끔함: 작업 끝나면 임시 테이블은 자동으로 삭제돼.

임시 테이블 생성 문법

PostgreSQL에서는 CREATE TEMP TABLE 명령어로 임시 테이블을 쉽게 만들 수 있어.

기본 문법:

CREATE TEMP TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

• TEMP 또는 TEMPORARY — 테이블이 임시라는 걸 나타내는 키워드야.
• table_name — 테이블 이름. 아무거나 써도 되지만, 의미 있게 짓는 게 좋아.
• column_name — 컬럼 이름.
• data_type — 컬럼 데이터 타입 (INTEGER, TEXT, DATE 등).
• constraints — 제약 조건, 예를 들면 NOT NULL, UNIQUE 등.

간단한 임시 테이블 만드는 예시:

CREATE TEMP TABLE temp_sales (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

• 여기서는 판매 데이터를 저장할 임시 테이블을 만들고 있어.
• id 필드는 각 행마다 자동으로 식별자를 만들어줘.
• amount 필드는 소수점까지 정확하게 값을 저장해 (예: 1234.56).

임시 테이블 활용 예시

예시 1: 분석을 위한 중간 데이터 저장

예를 들어, orders 테이블에 모든 주문이 저장돼 있다고 해보자. 우리는 상품 카테고리별로 총 판매액을 구해야 해. 복잡한 쿼리를 바로 돌리는 대신, 임시 테이블에 데이터를 저장할 수 있어.

임시 테이블 만들기:

CREATE TEMP TABLE temp_category_sales (
    category TEXT,
    total_sales NUMERIC(10, 2)
);

임시 테이블에 데이터 넣기:

INSERT INTO temp_category_sales
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

이제 임시 테이블을 써서, 예를 들어 상위 3개 카테고리를 뽑을 수 있어:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

예시 2: 여러 테이블에서 데이터 합치기

예를 들어, students (학생 정보)랑 enrollments (수강 기록) 두 테이블이 있다고 해. 학생들의 수강 정보를 한 번에 모으고 싶어.

임시 테이블 만들기:

CREATE TEMP TABLE temp_student_enrollments (
    student_id INTEGER,
    student_name TEXT,
    course_id INTEGER,
    enrollment_date DATE
);

데이터 합치기:

INSERT INTO temp_student_enrollments
SELECT s.id, s.name, e.course_id, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id;

이제 이 테이블로 쉽게 분석할 수 있어. 예를 들어, 학생별 수강 횟수 세기:

SELECT student_name, COUNT(course_id) AS enrollment_count
FROM temp_student_enrollments
GROUP BY student_name
ORDER BY enrollment_count DESC;

트랜잭션에서 임시 테이블

추가로, 임시 테이블은 ON COMMIT 키워드로 트랜잭션 범위로 제한할 수도 있어.

ON COMMIT 옵션:

• ON COMMIT DELETE ROWS: 트랜잭션 끝나면 임시 테이블 내용이 비워져.
• ON COMMIT PRESERVE ROWS: 트랜잭션 끝나도 데이터가 남아있어.
• ON COMMIT DROP: 트랜잭션 끝나면 테이블 자체가 삭제돼.

예시:

CREATE TEMP TABLE temp_analysis (
    category TEXT,
    total_sales NUMERIC(10, 2)
) ON COMMIT DROP;

이 경우엔 트랜잭션이 끝나면 임시 테이블이 바로 사라져.

실전 활용과 케이스

임시 테이블은 실제 프로젝트에서 완전 유용해. 몇 가지 상황을 볼까:

케이스:

1. 비즈니스에서 복잡한 리포트: 여러 번 계산이 필요한 복잡한 리포트도 중간 결과를 저장하면서 처리할 수 있어.
2. ETL 데이터 준비: 임시 테이블은 데이터 추출, 변환, 적재(ETL) 과정에서 자주 써.
3. 쿼리 개발 및 테스트: 쿼리 디버깅할 때 데이터 격리를 위해 임시 테이블을 활용해봐.

예를 들어, 판매 리포트를 개발할 때 임시 테이블에 전체 매출이나 지역별 트렌드 같은 중간 결과를 저장하면, 메인 데이터베이스 구조에 영향 안 주고 작업할 수 있어.

특징과 흔한 실수

임시 테이블은 거의 축제 같은 존재지만, 몇 가지 주의할 점이 있어:

• 접근 제한: 임시 테이블은 현재 세션이나 트랜잭션에서만 보여. 세션 끝나면 테이블도 사라져.
• 이름 중복 실수: 한 세션에서 이미 있는 이름으로 임시 테이블을 또 만들 수 없어.
• 데이터 손실: 세션 끝나기 전에 필요한 데이터를 꼭 뽑아가야 해.
• 성능: 임시 테이블에 행이 너무 많으면 메모리 사용량이 늘어날 수 있어.