CTE (Common Table Expression) - Biểu thức bảng tạm

CTE cho phép chia query phức tạp thành nhiều bước nhỏ, dễ đọc và dễ debug.

WITH
-- Bước 1: Top 10 sản phẩm bán chạy nhất
most_popular AS (
    SELECT products.*, COUNT(*) as sales
    FROM products
    JOIN orders_products USING(product_id)
    WHERE created_at BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY products.product_id
    ORDER BY COUNT(*) DESC LIMIT 10
),
-- Bước 2: Users đủ điều kiện tham gia raffle
eligible_users AS (
    SELECT DISTINCT users.* FROM users
    JOIN users_raffle USING(user_id)
    WHERE correct_answers > 8
)
-- Bước 3: Kết hợp 2 bước trên
SELECT * FROM eligible_users
JOIN orders_products USING(user_id)
JOIN most_popular USING(product_id);

Mỗi CTE step có thể test độc lập → debug dễ hơn hẳn so với nested subquery.

Ứng dụng: xóa dòng trùng lặp ngay trong database

Thay vì viết logic chunking phức tạp ở tầng ứng dụng:

-- PostgreSQL:
WITH duplicates AS (
    SELECT id, ROW_NUMBER() OVER(
        PARTITION BY firstname, lastname, email
        ORDER BY age DESC   -- giữ lại row có age cao nhất
    ) AS rownum
    FROM contacts
)
DELETE FROM contacts
USING duplicates
WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

Liên quan

• Subquery - CTE là dạng subquery có tên, dễ debug hơn
• Kỹ thuật thao tác dữ liệu hiệu quả - CTE dedup và các kỹ thuật khác
• Lateral Join - giải pháp khác cho “top N per group”