Bài 7

CTE - Truy vấn nâng cao

Common Table Expressions để viết query phức tạp

Mục tiêu học tập

Hiểu CTE là gì và khi nào dùng
Cú pháp WITH ... AS
Chia nhỏ query phức tạp thành các bước

Lesson 7: CTE - Common Table Expressions

CTE là gì?

Tại sao dùng CTE?

Cú pháp cơ bản

WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT * FROM cte_name;

Ví dụ đơn giản

-- Tính revenue mỗi cửa hàng
WITH store_revenue AS (
  SELECT store_id, SUM(total_amount) as revenue
  FROM sales
  GROUP BY store_id
)
SELECT st.store_name, sr.revenue
FROM store_revenue sr
JOIN stores st ON sr.store_id = st.store_id
ORDER BY sr.revenue DESC;

Nhiều CTEs

WITH 
product_sales AS (
  SELECT 
    product_id,
    COUNT(*) as times_sold,
    SUM(total_amount) as revenue
  FROM sales
  GROUP BY product_id
),
avg_revenue AS (
  SELECT AVG(revenue) as avg_rev
  FROM product_sales
)
SELECT 
  p.product_name,
  ps.times_sold,
  ps.revenue,
  CASE 
    WHEN ps.revenue > ar.avg_rev THEN 'Trên TB'
    ELSE 'Dưới TB'
  END as performance
FROM product_sales ps
JOIN products p ON ps.product_id = p.product_id
CROSS JOIN avg_revenue ar
ORDER BY ps.revenue DESC
LIMIT 20;

Khi nào dùng CTE?

Query có logic phức tạp
Cần tính toán trung gian
Muốn tái sử dụng kết quả con
Muốn code dễ đọc hơn

Trang chủ

SQL Editor

Bài tập thực hành

Bài tập 1: Dùng CTE tính doanh thu theo từng category

Gợi ý

CTE JOIN sales, products, categories

Xem đáp án

WITH category_revenue AS (
  SELECT c.category_id, c.category_name, SUM(s.total_amount) as revenue
  FROM sales s
  JOIN products p ON s.product_id = p.product_id
  JOIN categories c ON p.category_id = c.category_id
  GROUP BY c.category_id, c.category_name
)
SELECT category_name, revenue
FROM category_revenue
ORDER BY revenue DESC;

Bài tập 2: Top 10 khách hàng chi tiêu nhiều nhất (dùng CTE)

Gợi ý

CTE tính total_spent cho mỗi customer

Xem đáp án

WITH customer_spending AS (
  SELECT customer_id, SUM(total_amount) as total_spent
  FROM sales
  WHERE customer_id IS NOT NULL
  GROUP BY customer_id
)
SELECT c.customer_name, c.city, cs.total_spent
FROM customer_spending cs
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spent DESC
LIMIT 10;

Bài tập 3: Phân tích cửa hàng: doanh thu và số giao dịch

Gợi ý

CTE với COUNT và SUM

Xem đáp án

WITH store_stats AS (
  SELECT store_id, COUNT(*) as num_sales, SUM(total_amount) as revenue
  FROM sales
  GROUP BY store_id
)
SELECT st.store_name, st.city, ss.num_sales, ss.revenue, ROUND(ss.revenue / ss.num_sales) as avg_per_sale
FROM store_stats ss
JOIN stores st ON ss.store_id = st.store_id
ORDER BY ss.revenue DESC;

Trang chủ

Đang tải schema...