CTE trong SQL: Hướng dẫn đầy đủ kèm ví dụ

Cách tạo một SQL CTE

Khi tạo CTE, chúng ta dùng từ khóa WITH để bắt đầu định nghĩa CTE. Cú pháp tổng quát của một CTE như sau:

WITH cte_name (column1, column2, ...) AS (
    -- Query that defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query
SELECT ...
FROM cte_name;

Trong đó:

• WITH: Khởi tạo định nghĩa CTE.
• cte_name: Tên gán cho CTE (dùng để tham chiếu về sau).
• Danh sách cột tùy chọn: Chỉ định tên cột cho tập kết quả của CTE.
• Truy vấn chính: Tham chiếu CTE theo tên, xử lý nó như một bảng thông thường.

Hãy xem một ví dụ. Giả sử chúng ta có bảng Employees và muốn chọn nhân viên có mức lương trên 50.000 đô la.

Bước 1: Viết truy vấn nền tảng

Chúng ta bắt đầu bằng cách viết truy vấn SELECT cơ bản:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

Bước 2: Bao bọc truy vấn bằng từ khóa WITH để tạo CTE

Dùng từ khóa WITH để đặt tên cho CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

Bước 3: Dùng CTE trong truy vấn chính

Cuối cùng, tham chiếu đến CTE trong câu lệnh SELECT bằng cách gọi tên CTE đã định nghĩa ở trên.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

Tóm lại các bước trên, chúng ta đã dùng từ khóa WITH để định nghĩa CTE tên HighEarningEmployees. Truy vấn bên trong được dùng để tạo tập dữ liệu tạm thời. Truy vấn chính tham chiếu HighEarningEmployees để hiển thị các cột EmployeeID, FirstName và LastName được chỉ định.

Vì sao SQL CTE hữu ích

Từ ví dụ trên, bạn có thể thắc mắc tại sao dùng CTE khi ngay cả truy vấn đơn giản cũng cho kết quả tương tự. Dưới đây là lý do:

Đơn giản hóa truy vấn phức tạp

CTE chia nhỏ các câu lệnh SQL phức tạp thành các phần nhỏ hơn, dễ quản lý hơn, giúp mã dễ đọc, dễ viết và dễ bảo trì. 

Giả sử chúng ta có ba bảng: Orders, Customers và Products. Chúng ta muốn tìm tổng doanh thu do mỗi khách hàng tạo ra trong năm 2024. Khi viết truy vấn mà không dùng CTE, nó trông rối rắm và khó đọc, khó hiểu.

-- Standard SQL: Hard to read nested logic
SELECT 
    c.CustomerName, 
    SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
JOIN Customers c 
    ON o.CustomerID = c.CustomerID
JOIN Products p 
    ON o.ProductID = p.ProductID
WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

Bằng cách dùng CTE, chúng ta có thể tách riêng logic thành định dạng dễ đọc hơn. Trước tiên, cô lập bước “lọc và nối”, sau đó thực hiện tổng hợp.

-- Standard SQL: Cleaner with CTE
WITH OrderDetails AS (
    SELECT 
        o.OrderID, 
        c.CustomerName, 
        p.Price, 
        o.Quantity, 
        o.OrderDate
    FROM Orders o
    JOIN Customers c 
        ON o.CustomerID = c.CustomerID
    JOIN Products p 
        ON o.ProductID = p.ProductID
    WHERE EXTRACT(YEAR FROM o.OrderDate) = 2024
)
-- Main query
SELECT 
    CustomerName, 
    SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

Tái sử dụng mã

CTE giúp tránh trùng lặp bằng cách cho phép tái sử dụng cùng một tập kết quả. Nếu bạn cần tính một số tổng hợp (như tổng) rồi lọc dựa trên giá trị tổng hợp đó, CTE là lựa chọn hoàn hảo.

Giả sử chúng ta cần tính doanh số trung bình và tổng cho mỗi danh mục sản phẩm. Chúng ta định nghĩa phép tính một lần trong một CTE:

WITH CategorySales AS (
    SELECT 
        Category, 
        SUM(SalesAmount) AS TotalSales, 
        AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- Select from the CTE where the pre-calculated TotalSales is high
SELECT 
    Category, 
    TotalSales, 
    AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

Ứng dụng khác

Ngoài việc đơn giản hóa truy vấn và tái sử dụng mã, CTE còn có các công dụng khác. Tôi không thể trình bày chi tiết mọi cách dùng có thể có của CTE. Khóa học Data Manipulation in SQL của chúng tôi là lựa chọn tuyệt vời nếu bạn muốn tiếp tục luyện tập. Tuy nhiên, tôi sẽ ghi lại một số lý do chính khác tại đây:

• Tổ chức truy vấn và khả năng đọc: CTE cải thiện khả năng đọc mã SQL bằng cách chia truy vấn thành các bước logic, tuần tự. Mỗi bước trong quá trình truy vấn có thể được biểu diễn bởi một CTE riêng, giúp toàn bộ truy vấn dễ theo dõi hơn.
• Duyệt dữ liệu phân cấp: CTE có thể giúp điều hướng các mối quan hệ phân cấp, như cơ cấu tổ chức, quan hệ cha-con, hoặc bất kỳ mô hình dữ liệu nào có cấp bậc lồng nhau. CTE đệ quy hữu ích để truy vấn dữ liệu phân cấp vì chúng cho phép bạn duyệt các cấp một cách lặp.
• Tổng hợp đa cấp: CTE có thể giúp thực hiện tổng hợp ở nhiều cấp độ, chẳng hạn tính doanh số theo các mức chi tiết khác nhau (ví dụ: theo tháng, quý và năm). Dùng CTE để tách các bước tổng hợp này đảm bảo mỗi cấp được tính độc lập và logic.
• Kết hợp dữ liệu từ nhiều bảng: Có thể dùng nhiều CTE để kết hợp dữ liệu từ các bảng khác nhau, khiến bước kết hợp cuối trở nên có cấu trúc hơn. Cách tiếp cận này đơn giản hóa các phép nối phức tạp và đảm bảo dữ liệu nguồn được tổ chức hợp lý để cải thiện khả năng đọc.

Kỹ thuật CTE nâng cao trong SQL

CTE hỗ trợ các kỹ thuật SQL nâng cao, khiến chúng linh hoạt và hữu ích cho nhiều trường hợp sử dụng khác nhau. Dưới đây là một số ứng dụng nâng cao của CTE.

Nhiều CTE trong một truy vấn

Bạn có thể định nghĩa nhiều CTE trong một truy vấn, cho phép các biến đổi và tính toán phức tạp. Phương pháp này hữu ích khi một bài toán cần nhiều giai đoạn xử lý dữ liệu, trong đó mỗi CTE đại diện cho một giai đoạn riêng biệt.

Giả sử chúng ta có dữ liệu bán hàng trong một bảng tên Sales và muốn tính tổng doanh số cho mỗi sản phẩm, xác định sản phẩm có tổng doanh số trên mức trung bình, và xếp hạng các sản phẩm này dựa trên tổng doanh số.

WITH ProductSales AS (
    -- Step 1: Calculate total sales for each product
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
), 
AverageSales AS (
    -- Step 2: Calculate the average of those totals
    -- Note: We can reference the previous CTE (ProductSales) here
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
), 
HighSalesProducts AS (
    -- Step 3: Filter products above the average
    SELECT ps.ProductID, ps.TotalSales
    FROM ProductSales ps
    CROSS JOIN AverageSales av
    WHERE ps.TotalSales > av.AverageTotalSales
)
-- Step 4: Rank the results
SELECT 
    ProductID, 
    TotalSales, 
    RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

Trong ví dụ trên:

• CTE đầu tiên (ProductSales) tính tổng doanh số theo từng sản phẩm.
• CTE thứ hai (AverageSales) tính trung bình tổng doanh số trên tất cả sản phẩm.
• CTE thứ ba (HighSalesProducts) lọc các sản phẩm có tổng doanh số vượt mức trung bình.
• Truy vấn cuối xếp hạng các sản phẩm này dựa trên tổng doanh số.

CTE trong các câu lệnh UPDATE, DELETE và MERGE

Khi được đưa vào các thao tác UPDATE, DELETE và MERGE, CTE có thể đơn giản hóa các tác vụ thao tác dữ liệu, đặc biệt khi xử lý các bộ lọc phức tạp hoặc dữ liệu phân cấp.

Dùng CTE với câu lệnh UPDATE

Giả sử chúng ta có bảng Employees với cột EmployeeSalary. Chúng ta muốn tăng 10% lương cho tất cả nhân viên đã làm việc cho công ty trên 5 năm.

-- Define a CTE to find employees hired more than 5 years ago
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    -- Standard SQL: Compare HireDate to 5 years before today
    WHERE HireDate <= CURRENT_DATE - INTERVAL '5' YEAR
)
-- Update salaries by 10% for long-term employees identified in the CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.10
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees xác định những nhân viên đã làm việc hơn năm năm. Câu lệnh UPDATE dùng CTE này để tăng lương có chọn lọc.

Dùng CTE với câu lệnh DELETE

Giờ hãy giả sử chúng ta có bảng tên Products và muốn xóa tất cả sản phẩm không bán được trong 2 năm qua. Chúng ta có thể dùng CTE để lọc sản phẩm:

-- Define a CTE to identify products not sold in the last 2 years
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- Standard SQL: Filter for dates older than 2 years ago
    WHERE LastSoldDate < CURRENT_DATE - INTERVAL '2' YEAR
)
-- Delete products identified as old from the main table
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts xác định các sản phẩm không bán được trong hai năm qua, sau đó câu lệnh DELETE dùng CTE này để xóa các sản phẩm đó.

Dùng CTE với câu lệnh MERGE

Câu lệnh MERGE trong SQL cho phép cập nhật, chèn hoặc xóa có điều kiện trong bảng đích dựa trên dữ liệu ở bảng nguồn. Trong ví dụ sau, CTE MergedInventory kết hợp dữ liệu hàng tồn mới và hiện có. Câu lệnh MERGE sau đó cập nhật số lượng cho sản phẩm hiện có hoặc chèn sản phẩm mới dựa trên dữ liệu từ CTE.

-- CTE to prepare the source data for the merge
WITH MergedInventory AS (
    SELECT 
        ni.ProductID, 
        ni.Quantity AS NewQuantity
    FROM NewInventoryData ni
)
-- Merge the prepared data into the Inventory table
MERGE INTO Inventory AS target
USING MergedInventory AS source
    ON target.ProductID = source.ProductID
-- Update existing products with new quantities
WHEN MATCHED THEN
    UPDATE SET target.Quantity = source.NewQuantity
-- Insert new products if they don't exist in the inventory
WHEN NOT MATCHED THEN
    INSERT (ProductID, Quantity) 
    VALUES (source.ProductID, source.NewQuantity);

Common Table Expressions (CTE) đệ quy

CTE đệ quy là một loại CTE đặc biệt tự tham chiếu trong định nghĩa của chính nó, cho phép truy vấn thực hiện các thao tác lặp đi lặp lại. Điều này khiến chúng lý tưởng khi làm việc với dữ liệu phân cấp như sơ đồ tổ chức.

Giới thiệu về CTE đệ quy

CTE đệ quy là một loại CTE đặc biệt tự tham chiếu trong định nghĩa của chính nó, cho phép truy vấn thực hiện các thao tác lặp lại. Điều này lý tưởng cho dữ liệu phân cấp hoặc dạng cây, như sơ đồ tổ chức, cấu trúc thư mục hoặc lắp ráp sản phẩm. CTE đệ quy xử lý dữ liệu theo kiểu lặp, trả về kết quả từng bước cho đến khi thành phần đệ quy không trả về hàng mới (điều kiện dừng).

Thành phần neo và thành phần đệ quy

Một CTE đệ quy gồm hai phần chính:

• Thành phần Neo (Anchor): Phần định nghĩa truy vấn cơ sở bắt đầu quá trình đệ quy.
• Thành phần Đệ quy: Phần tự tham chiếu đến CTE, cho phép thực hiện các thao tác “đệ quy”.

Giả sử chúng ta có bảng Employees, trong đó mỗi hàng chứa EmployeeID, EmployeeName và ManagerID. Nếu muốn tìm tất cả cấp dưới trực tiếp và gián tiếp của một quản lý cụ thể, chúng ta bắt đầu với thành phần neo xác định quản lý cấp cao nhất.

Lưu ý: Trong Standard SQL (PostgreSQL, MySQL, SQLite), bạn phải dùng từ khóa RECURSIVE.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: select the top-level manager
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- Starting with the top-level manager
    
    UNION ALL
    
    -- Recursive member: find employees who report to the current managers
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.ManagerID = eh.EmployeeID
)
-- Select the final result from the CTE
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

Cách hoạt động:

1. Neo (Anchor): Truy vấn chạy Thành phần Neo trước, tìm nhân viên có ID 1.
2. Đệ quy: Thành phần Đệ quy chạy, tìm nhân viên có ManagerID khớp với EmployeeID tìm được ở bước trước.
3. Lặp: Quá trình này lặp lại (Cấp 1 tìm Cấp 2, Cấp 2 tìm Cấp 3) cho đến khi không còn nhân viên mới nào được tìm thấy.

Những vấn đề hoặc hạn chế tiềm ẩn của CTE trong SQL

Hiểu các tính năng và hạn chế của CTE là quan trọng để viết các truy vấn hợp lý và dễ đọc. Hãy xem một số hạn chế và vấn đề tiềm ẩn khi dùng CTE trong các cơ sở dữ liệu khác nhau.

Hạn chế trong SQL Server và Azure

Có một số hạn chế theo môi trường đối với SQL CTE khi làm việc với SQL Server hoặc Azure Synapse Analytics. Bao gồm:

• SQL Server: Mức đệ quy tối đa mặc định cho CTE đệ quy là 100. Nếu vượt quá giới hạn này mà không điều chỉnh, sẽ xảy ra lỗi. Định nghĩa CTE không thể lồng trực tiếp bên trong định nghĩa CTE khác (mặc dù bạn có thể xâu chuỗi nhiều CTE liên tiếp).
• Azure Synapse Analytics: Hỗ trợ khác nhau tùy loại pool cụ thể. CTE đệ quy hiện không được hỗ trợ trong Dedicated SQL Pools (trước đây là SQL DW). Tuy nhiên, chúng được hỗ trợ trong Serverless SQL Pools. Ngoài ra, một số thao tác DML (như UPDATE hoặc DELETE với CTE) có thể có hạn chế cú pháp so với SQL Server tiêu chuẩn.

Nếu bạn làm việc với SQL Server, hãy biết rằng DataCamp có rất nhiều tài nguyên hữu ích. Để bắt đầu, tôi khuyên bạn nên học khóa Giới thiệu về SQL Server của DataCamp để nắm vững những điều cơ bản của SQL Server cho phân tích dữ liệu. Bạn có thể thử lộ trình nghề nghiệp SQL Server Developer, bao quát mọi thứ từ giao dịch và xử lý lỗi đến phân tích chuỗi thời gian. Khóa học Truy vấn phân cấp và đệ quy trong SQL Server đi thẳng vào cốt lõi cách viết truy vấn nâng cao trong SQL Server, bao gồm các phương pháp liên quan đến CTE.

Các vấn đề tiềm ẩn khác

Mặc dù CTE hữu ích để đơn giản hóa truy vấn phức tạp, vẫn có một số cạm bẫy phổ biến bạn nên biết. Bao gồm:

• Vòng lặp vô hạn trong CTE đệ quy: Nếu điều kiện dừng cho CTE đệ quy không được đáp ứng, có thể dẫn đến vòng lặp vô hạn, khiến truy vấn chạy mãi. Để tránh CTE đệ quy chạy vô tận, hãy dùng gợi ý OPTION (MAXRECURSION N) để giới hạn số vòng lặp đệ quy tối đa, trong đó N là giới hạn chỉ định.

  • Cách khắc phục: Trong SQL Server, dùng gợi ý OPTION (MAXRECURSION N) để giới hạn số vòng lặp đệ quy tối đa. Trong PostgreSQL, bạn có thể dùng mệnh đề CYCLE để tự động phát hiện vòng lặp.

• Cân nhắc hiệu năng: CTE đệ quy có thể tiêu tốn tài nguyên nếu độ sâu đệ quy lớn hoặc xử lý tập dữ liệu lớn. Để tối ưu hiệu năng, hãy giới hạn dữ liệu xử lý trong mỗi vòng lặp và đảm bảo lọc phù hợp để tránh mức đệ quy quá mức.

Hiệu năng: CTE so với Subquery

Một quan niệm sai phổ biến là CTE vốn dĩ nhanh hơn truy vấn con. Thực tế, hầu hết bộ tối ưu hóa truy vấn hiện đại (như trong SQL Server và PostgreSQL) sẽ “inline” CTE chuẩn, nghĩa là chúng được xử lý giống hệt truy vấn con và không có khác biệt về hiệu năng.

Tuy nhiên, CTE có thể mang lại cải thiện hiệu năng thông qua materialization, trong đó cơ sở dữ liệu tính toán kết quả CTE một lần và lưu tạm để tham chiếu nhiều lần trong truy vấn chính.

Tôi khuyên dùng CTE chủ yếu vì khả năng đọc. Lợi ích hiệu năng còn phụ thuộc tình huống và cách cơ sở dữ liệu cụ thể của bạn xử lý bộ nhớ đệm.

Khi nào dùng CTE so với kỹ thuật khác

Trong khi CTE phù hợp để đơn giản hóa các truy vấn liên quan đến tác vụ lặp lại, bảng dẫn xuất, view và bảng tạm cũng phục vụ các mục đích tương tự. Bảng sau nêu bật ưu, nhược điểm của từng phương pháp và khi nào nên dùng.

Kết luận

Thành thạo CTE cần luyện tập, như mọi thứ khác: tôi khuyên bạn thử lộ trình nghề nghiệp Associate Data Analyst in SQL của DataCamp để trở thành một nhà phân tích dữ liệu vững vàng. Khóa học Reporting in SQL cũng sẽ giúp bạn thành thạo xây dựng báo cáo và bảng điều khiển phức tạp để trình bày dữ liệu hiệu quả. Cuối cùng, bạn nên lấy Chứng chỉ SQL Associate để chứng minh năng lực sử dụng SQL giải quyết bài toán kinh doanh và nổi bật giữa các chuyên gia khác.