Ví dụ truy vấn phức tạp với nhiều CTE

Trong bài giảng này tụi mình sẽ làm phép thuật truy vấn thực thụ luôn! Tụi mình sẽ tạo ra một vài ví dụ sử dụng nhiều CTE để cho thấy cách chúng có thể kết hợp với nhau để xây dựng các truy vấn phức tạp và nhiều bước. Mấy ví dụ này cực kỳ hữu ích ngoài đời, nhất là khi gặp các bài toán phân tích dữ liệu khó nhằn.

Ví dụ 1: Phân tích kết quả học tập của sinh viên

Giả sử tụi mình có một database của trường đại học với ba bảng:

Bảng students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Bảng grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Bảng courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Nhiệm vụ: lấy danh sách sinh viên có điểm trung bình trên 85, kèm theo điểm trung bình và tên các môn học mà họ đang học.

Truy vấn:

WITH high_achievers AS (
    -- Tìm sinh viên có điểm trung bình cao
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Tìm các môn học mà mỗi sinh viên đã đăng ký
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Kết hợp kết quả lại
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Giải thích:

1. Ở CTE đầu tiên (high_achievers), tụi mình tính điểm trung bình cho từng sinh viên và chọn ra những bạn có điểm trên 85.
2. Ở CTE thứ hai (student_courses), tụi mình ghép sinh viên với các môn học của họ.
3. Ở truy vấn chính, tụi mình kết hợp dữ liệu từ hai CTE để lấy danh sách sinh viên, điểm trung bình và các môn học mà họ đang học.

Ví dụ 2: Báo cáo bán hàng cho shop online

Giả sử tụi mình làm việc với một shop online và có các bảng sau:

Bảng orders (đơn hàng):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Bảng customers (khách hàng):

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Bảng order_items (sản phẩm trong đơn hàng):

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Nhiệm vụ: tạo báo cáo hiển thị cho từng khách hàng:

• Tổng số đơn hàng.
• Tổng số tiền của tất cả đơn hàng trong tháng vừa rồi.
• Danh sách tất cả sản phẩm khác nhau mà họ đã mua.

Truy vấn:

WITH recent_orders AS (
    -- Lấy các đơn hàng trong tháng vừa rồi
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Đếm tổng số đơn hàng và tổng tiền cho từng khách
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Lấy các sản phẩm khác nhau mà mỗi khách đã mua
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Kết hợp kết quả lại
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Giải thích:

1. Ở CTE đầu tiên (recent_orders), tụi mình lấy các đơn hàng trong tháng vừa rồi.
2. Ở CTE thứ hai (customer_summary), tụi mình tính tổng số đơn và tổng tiền cho từng khách.
3. Ở CTE thứ ba (customer_products), tụi mình lấy các sản phẩm khác nhau mà mỗi khách đã mua.
4. Ở truy vấn cuối, tụi mình kết hợp dữ liệu và dùng ARRAY_AGG() để tạo danh sách sản phẩm duy nhất.

Ví dụ 3: Phân tích cấu trúc nhân sự

Tụi mình có bảng nhân viên:

Bảng employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Nhiệm vụ: xây dựng cấu trúc nhân sự bắt đầu từ giám đốc điều hành. Hiển thị cấp bậc của từng nhân viên trong cấu trúc.

Truy vấn:

WITH RECURSIVE employee_hierarchy AS (
    -- Bắt đầu từ nhân viên không có quản lý (giám đốc điều hành)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Thêm tất cả nhân viên cấp dưới của cấp hiện tại
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Hiển thị cấu trúc nhân sự
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. Trong truy vấn đệ quy, tụi mình bắt đầu từ giám đốc điều hành (nhân viên không có quản lý).
2. Mỗi bước sẽ thêm các nhân viên cấp dưới của cấp hiện tại, tăng level lên một.
3. Ở truy vấn chính, tụi mình lấy toàn bộ cấu trúc, sắp xếp theo cấp bậc và mã nhân viên.

Mẹo hay và lỗi thường gặp

• Dùng quá nhiều CTE: đừng dùng CTE khi có thể thay bằng subquery. Đôi khi CTE làm giảm hiệu năng vì phải materialize dữ liệu.
• Tên CTE: đặt tên CTE ngắn gọn, dễ hiểu để truy vấn dễ đọc hơn.
• Thứ tự thực thi: nhớ là CTE sẽ chạy đúng theo thứ tự bạn khai báo.
• GROUP BY: chỉ dùng GROUP BY khi thật sự cần thiết để tránh thao tác thừa.

Tất cả các ví dụ này cho thấy CTE giúp chia nhỏ bài toán phức tạp thành từng bước, làm cho truy vấn dễ đọc và dễ bảo trì hơn. Giờ thì bạn đã có đủ công cụ để xử lý các bài toán phân tích dữ liệu khó nhằn với PostgreSQL rồi nhé!