Thanaoscar

Extra Day: CTEs (Common Table Expressions)

Thana Oscar's photo
Thana Oscar
·

5 min read

Bối cảnh: Hệ thống E-commerce (Thương mại điện tử) - Tương tự Shopee

1. Why an extra day?

  • CTEs (Common Table Expressions): Một công cụ mạnh mẽ trong SQL giúp đơn giản hóa các truy vấn phức tạp bằng cách chia nhỏ chúng thành các phần dễ quản lý.

  • Mục tiêu: Hiểu cách sử dụng CTEs để viết các truy vấn phức tạp một cách rõ ràng và hiệu quả hơn.

2. Common Table Expressions (CTEs) - First Example

  • CTE: Một bảng tạm thời được định nghĩa trong phạm vi của một truy vấn.

  • Ví dụ: Tính tổng doanh thu theo từng danh mục sản phẩm:

      WITH category_revenue AS (
    SELECT p.category, SUM(o.total_amount) AS total_revenue
    FROM orders o
    INNER JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
  )
  SELECT * FROM category_revenue;

    Kết quả trả về tổng doanh thu của từng danh mục sản phẩm.

3. Common Table Expressions - More Complex Example

  • Ví dụ phức tạp hơn: Tính tổng doanh thu và số lượng đơn hàng theo từng khách hàng:

      WITH customer_stats AS (
    SELECT c.customer_id, c.customer_name,
           COUNT(o.order_id) AS order_count,
           SUM(o.total_amount) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
  )
  SELECT * FROM customer_stats;

    Kết quả trả về thống kê đơn hàng và doanh thu của từng khách hàng.

4. Challenge 1: Create a simple CTE

  • Yêu cầu: Tạo CTE để tính tổng số lượng sản phẩm đã bán theo từng danh mục (category).

5. Solution 1: Create a simple CTE

  • Giải pháp:

      WITH category_sales AS (
    SELECT p.category, SUM(o.quantity) AS total_quantity
    FROM orders o
    INNER JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
  )
  SELECT * FROM category_sales;

6. Common Table Expressions - Using Multiple CTEs

  • Sử dụng nhiều CTEs: Kết hợp nhiều CTEs trong một truy vấn.

  • Ví dụ: Tính tổng doanh thu và số lượng đơn hàng theo từng danh mục và phương thức thanh toán:

      WITH category_revenue AS (
    SELECT p.category, SUM(o.total_amount) AS total_revenue
    FROM orders o
    INNER JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
  ),
  payment_method_stats AS (
    SELECT o.payment_method, COUNT(o.order_id) AS order_count
    FROM orders o
    GROUP BY o.payment_method
  )
  SELECT * FROM category_revenue, payment_method_stats;

7. Challenge 2: Using Multiple CTEs

  • Yêu cầu: Tạo CTE để tính tổng doanh thu theo từng danh mục (category) và tổng số lượng đơn hàng theo từng phương thức thanh toán (payment_method).

8. Solution 2: Using Multiple CTEs

  • Giải pháp:

      WITH category_revenue AS (
    SELECT p.category, SUM(o.total_amount) AS total_revenue
    FROM orders o
    INNER JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
  ),
  payment_method_orders AS (
    SELECT o.payment_method, COUNT(o.order_id) AS order_count
    FROM orders o
    GROUP BY o.payment_method
  )
  SELECT * FROM category_revenue, payment_method_orders;

9. Recursive CTEs - Concept

  • Recursive CTEs: Cho phép thực hiện các truy vấn đệ quy, thường được sử dụng để xử lý dữ liệu phân cấp (hierarchical data).

  • Ví dụ: Tìm tất cả các danh mục con của một danh mục cha trong hệ thống phân cấp danh mục sản phẩm.

10. Recursive CTEs - Hierarchical Data

  • Ví dụ: Tìm tất cả các danh mục con của danh mục "Electronics":

      WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_category_id
    FROM categories
    WHERE category_name = 'Electronics'
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_category_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_category_id = ct.category_id
  )
  SELECT * FROM category_tree;

11. Recursive CTEs - Termination Condition

  • Điều kiện dừng: Đảm bảo truy vấn đệ quy không chạy vô hạn.

  • Ví dụ: Giới hạn độ sâu của cây danh mục:

      WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_category_id, 1 AS depth
    FROM categories
    WHERE category_name = 'Electronics'
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_category_id, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_category_id = ct.category_id
    WHERE ct.depth < 3 -- Giới hạn độ sâu
  )
  SELECT * FROM category_tree;

12. Challenge 3: Recursive CTE

  • Yêu cầu: Tìm tất cả các danh mục con của danh mục "Home Appliances" với độ sâu tối đa là 2.

13. Solution 3: Recursive CTE

  • Giải pháp:

      WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_category_id, 1 AS depth
    FROM categories
    WHERE category_name = 'Home Appliances'
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_category_id, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_category_id = ct.category_id
    WHERE ct.depth < 2
  )
  SELECT * FROM category_tree;

14. Today's slides

  • Tổng kết lại các kiến thức về CTEs và Recursive CTEs.

  • Slide tham khảo cung cấp ví dụ chi tiết và ứng dụng trong hệ thống E-commerce.

15. Today's summary

  • Hôm nay, bạn đã học:

    • Cách sử dụng CTEs để đơn giản hóa các truy vấn phức tạp.

    • Cách sử dụng Recursive CTEs để xử lý dữ liệu phân cấp.

    • Cách kết hợp nhiều CTEs trong một truy vấn.

  • CTEs là công cụ mạnh mẽ giúp viết các truy vấn SQL dễ đọc và bảo trì hơn.

16. Today's challenges

  • Thử thách 1: Tạo CTE để tính tổng doanh thu và số lượng đơn hàng theo từng khách hàng (customer_id).

  • Thử thách 2: Sử dụng Recursive CTE để tìm tất cả các danh mục con của danh mục "Clothing" với độ sâu tối đa là 3.

  • Thử thách 3: Kết hợp nhiều CTEs để tính tổng doanh thu theo từng danh mục (category) và từng phương thức thanh toán (payment_method).

SQL