Tạo bảng tạm cho phân tích

Bảng tạm trong PostgreSQL là những bảng được tạo ra để lưu trữ dữ liệu tạm thời và sẽ tự động bị xóa sau khi session hoặc transaction kết thúc (tùy vào cài đặt). Chúng cực kỳ hữu ích để đơn giản hóa các thao tác phức tạp và tăng tốc độ thực thi truy vấn.

Hãy tưởng tượng bạn là đầu bếp trong một nhà hàng với các món ăn ba sao. Bạn đâu có giữ riêng một cái bàn chỉ để nguyên liệu cho một món salad đúng không? Bảng tạm cũng giống như mặt bàn làm việc tạm thời: xử lý xong, dùng xong thì dọn đi.

Lợi ích:

• Cô lập dữ liệu: bảng tạm chỉ truy cập được trong session hoặc transaction hiện tại. Không ai ngoài bạn có thể đụng vào nội dung của nó.
• Hiệu quả: chúng cho phép lưu kết quả trung gian để tránh phải chạy lại các truy vấn phức tạp.
• Sạch sẽ: sau khi xong việc, bảng tạm sẽ tự động bị xóa.

Cú pháp tạo bảng tạm

PostgreSQL cung cấp cách đơn giản và tiện lợi để tạo bảng tạm bằng lệnh CREATE TEMP TABLE.

Cú pháp chung:

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

• TEMP hoặc TEMPORARY — từ khóa chỉ ra bảng này là tạm thời.
• table_name — tên bảng. Bạn đặt gì cũng được, nhưng nên đặt tên có ý nghĩa.
• column_name — tên cột.
• data_type — kiểu dữ liệu của cột (INTEGER, TEXT, DATE v.v.).
• constraints — ràng buộc, ví dụ NOT NULL, UNIQUE v.v.

Ví dụ tạo bảng tạm đơn giản:

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
);

• Ở đây tạo bảng tạm để lưu dữ liệu bán hàng.
• Cột id sẽ tự động sinh mã định danh cho mỗi dòng.
• Cột amount lưu giá trị có phần thập phân (ví dụ 1234.56).

Ví dụ sử dụng bảng tạm

Ví dụ 1: lưu dữ liệu trung gian để phân tích

Giả sử bạn có bảng orders lưu tất cả đơn hàng. Bạn cần xác định tổng doanh số theo từng loại sản phẩm. Thay vì chạy truy vấn phức tạp, bạn có thể lưu dữ liệu vào bảng tạm.

Tạo bảng tạm:

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

Đổ dữ liệu vào bảng tạm:

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

Bây giờ bạn có thể dùng bảng tạm này, ví dụ để lấy top 3 loại sản phẩm:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Ví dụ 2: kết hợp dữ liệu từ nhiều bảng

Giả sử bạn có hai bảng: students (thông tin sinh viên) và enrollments (ghi nhận đăng ký học). Cần gom dữ liệu đăng ký của họ vào một chỗ.

Tạo bảng tạm:

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

Kết hợp dữ liệu:

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;

Bây giờ bạn dễ dàng phân tích bảng này, ví dụ đếm số lần đăng ký của mỗi sinh viên:

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

Bảng tạm trong transaction

Bạn cũng có thể giới hạn bảng tạm trong phạm vi transaction bằng cách dùng từ khóa ON COMMIT.

Các lựa chọn ON COMMIT:

• ON COMMIT DELETE ROWS: xóa hết dữ liệu trong bảng tạm sau khi transaction kết thúc.
• ON COMMIT PRESERVE ROWS: giữ lại dữ liệu sau khi kết thúc.
• ON COMMIT DROP: xóa bảng sau khi transaction kết thúc.

Ví dụ:

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

Trong trường hợp này, bảng tạm sẽ biến mất ngay khi transaction kết thúc.

Ý nghĩa thực tế và case sử dụng

Bảng tạm cực kỳ hợp cho các dự án thực tế. Xem thử vài tình huống:

Case:

1. Báo cáo phức tạp trong kinh doanh: bạn có thể thực hiện nhiều phép tính phức tạp, lưu kết quả trung gian.
2. Chuẩn bị dữ liệu cho ETL: bảng tạm thường dùng trong quá trình trích xuất, chuyển đổi và nạp dữ liệu (ETL).
3. Phát triển và test truy vấn: dùng bảng tạm để cô lập dữ liệu khi debug truy vấn.

Ví dụ, nếu bạn phát triển báo cáo bán hàng, bảng tạm giúp lưu các kết quả trung gian như tổng doanh thu hoặc xu hướng theo vùng, mà không ảnh hưởng đến cấu trúc chính của database.

Đặc điểm và lỗi thường gặp

Làm việc với bảng tạm gần như lúc nào cũng vui, nhưng nhớ vài điểm sau:

• Giới hạn truy cập: bảng tạm chỉ thấy được trong session hoặc transaction hiện tại. Nếu bạn kết thúc session, bảng sẽ biến mất.
• Lỗi trùng tên: trong một session không thể tạo bảng tạm trùng tên với bảng đã có.
• Mất dữ liệu: nhớ lấy dữ liệu cần thiết từ bảng tạm trước khi kết thúc session.
• Hiệu năng: nếu bảng tạm có quá nhiều dòng, có thể ảnh hưởng đến RAM.