Mục lục
CTE có thể được xem như một bảng chứa dữ liệu tạm thời tương tự như một bảng dẫn xuất (derived table) ở chỗ nó không được lưu trữ như một đối tượng và chỉ kéo dài trong suốt thời gian của câu truy vấn. Không giống như bảng dẫn xuất, CTE có thể tự tham chiếu tới bản thân của nó và có thể tham chiếu nhiều lần trong một câu truy vấn.
Việc lưu trữ dữ liệu một cách tạm thời sẽ giúp chúng ta chia nhỏ một lệnh SQL query phức tạp thành những phần nhỏ hơn để giải quyết. Sau đó chúng ta sẽ gồm lại những phần này để giải quyết một vấn đề tổng thể lớn hơn.
Cứ hình dung như khi các bạn giải các phương trình toán học phức tạp, chúng ta thường sẽ chia nhỏ bài toán thành nhiều phần nhỏ khác nhau để giải quyết. Và kết quả của mỗi phần nhỏ này sẽ được lưu vào một biến và biến này có thể được sử dụng lại ở những phần sau.
CTE cũng hoạt động với nguyên tắc tương tự và nó sẽ giúp chúng ta lưu kết quả từ lệnh query của mình và được sử dụng lại sau đó.
Nếu các bạn từng học qua lập trình, việc gọi tên bảng CTE cũng khá giống với việc gọi một hàm (function) được được khai báo sẵn. Khác nhau ở chỗ là bảng CTE sẽ chỉ thực hiện một lần và lưu kết quả vào bộ nhớ tạm, còn hàm trong các ngôn ngữ khác sẽ thực thi mỗi lần bạn sử dụng.
Mục đích của CTE
CTE được sinh ra giúp giải quyết các vấn đề:
- Tạo truy vấn đệ quy (recursive query).
- Thay thế View trong một số trường hợp.
- Cho phép nhóm một cột từ truy vấn con.
- Tham chiếu tới bảng kết quả nhiều lần trong cùng một lệnh.
Ưu điểm của CTE
CTE có nhiều ưu điểm như khả năng đọc dữ liệu được cải thiện và dễ dàng bảo trì các truy vấn phức tạp. Các truy vấn có thể được phân thành các khối nhỏ, đơn giản. Những khối này được sử dụng để xây dựng các CTE phức tạp hơn cho đến khi tập hợp kết quả cuối cùng được tạo ra.
CTE có thể được định nghĩa trong function, store procedure, view, trigger.
Những lợi ích của lệnh WITH CTE
Việc mình cho lệnh WITH là một trong những lệnh quan trọng nhất dựa trên những lợi ích mà nó mang lại cho chúng ta. Chúng ta sẽ đi phân tích từng lợi ích một.
Tối ưu hóa bộ nhớ và tốc độ
Như mình nói phía trên, các bảng CTE có thể được sử dụng lại nhiều lần và ở nhiều nơi khác nhau. Chính đặc tính này sẽ giúp chúng ta quản lý tốt hơn bộ nhớ và tốc độ xử lý.
Để hiểu rõ hơn vấn đề chúng ta cùng xem đoạn code sau:
select a.*,b.total_sales from superstore.orders as a left join (select Order_ID, sum(Sales) as total_sales from superstore.orders group by Order_ID) as b -- lệnh lồng ghép on a.Order_ID = b.order_id Limit 100;
Chúng ta có thể thấy được rằng mình hoàn toàn có thể cho ra kết quả tương tự nhưng không cần đến CTE. Nhưng nếu nhìn kỹ hơn chúng ta sẽ thấy rằng ở câu lệnh này hệ thống sẽ kết nối với server 2 lần để lấy dữ liệu. Một lần ở dòng thứ 2 và lần còn lại ở dòng thứ 4.
Việc tạo nhiều kết nối đến cùng một bảng dữ liệu sẽ tiêu tốn khá nhiều tài nguyên. Nếu chúng ta có một bảng dữ liệu lên đến hàng Terabyte (TB) và truy xuất chúng nhiều lần thì tổng lượng tài nguyên tiêu thụ sẽ rất lớn. Hơn nữa việc kết nối tới server nhiều lần cũng tiêu tốn khá nhiều thời gian.
Trong trường hợp này, việc sử dụng bảng CTE sẽ giúp lưu trữ toàn bộ dữ liệu cần thiết trong bộ nhớ tạm và sử dụng lại bất cứ lúc nào. Chúng giúp giảm thiểu rất nhiều tài nguyên và thời gian thực thi lệnh query.
Giảm độ phức tạp của query
Lại nhìn vào những câu lệnh số 2. Ở phần LEFT JOIN chúng ta sử dụng một Sub-query để lấy dữ liệu. Trong các câu lệnh đơn giản, sử dụng sub-query sẽ khá là thuận tiện. Nhưng trong những trường hợp phức tạp, chúng ta khó có thể kiểm soát được những sub-query này.
Hãy thử hình dung trong trường hợp chúng ta cần lấy dữ liệu từ 5 hay 10 bảng dữ liệu khác nhau. Và toàn bộ đều cần được xử lý trước khi gộp lại thành một bảng. Trong trường hợp này, việc sử dụng sub-query sẽ dễ phát sinh lỗi vì chúng ta khó kiểm soát được code khi bỏ chúng chung lại với nhau.
Nhưng vấn đề sẽ dễ hơn nhiều nếu chúng ta xử lý 10 bảng dữ liệu này và lưu và 10 bảng CTE khác nhau.Việc xử lý những bảng dữ liệu riêng lẽ sẽ luôn dễ dàng hơn là gộp chung lại một nơi. Đến cuối cùng chúng ta chỉ cần join các bảng dữ liệu đã qua xử lý. Nếu có phát sinh lỗi, chúng ta cũng sẽ dễ dàng kiểm tra và chỉnh sửa hơn.
Dễ đọc và dễ hiểu
Việc tách bài toán lớn thành những phần nhỏ sẽ giúp mình hay người khác dễ dàng đọc và hiểu mục đích mình muốn làm. Trên thực tế, sẽ không ít lần các bạn được yêu cầu đọc code của người khác hoặc người khác đọc code của bạn.
Sử dụng CTE sẽ giúp chúng ta dễ dàng hơn trong việc truyền tải mục đích của mình cũng như logic của cả câu lệnh. Thông qua việc đặt tên bảng và ghi chú cho mỗi bảng, chúng ta sẽ giúp người khác hiểu ý nghĩa của nó dễ dàng hơn. Thậm chỉ là cho chính bạn đọc code của mình sau 2-3 năm.
Tăng tính linh hoạt mà sub-query không có
Không phải tất cả trường hợp sub-query đều có thể thay thế cho with. Sẽ có nhiều trường hợp mà bạn buộc phải dùng with để xử lý vì sub-query không hỗ trợ điều này. Một trong những trường hợp này là khi bạn muốn xử lý dữ liệu trùng (duplicate) phát sinh do quá trình join tables.
Trong trường hợp trên, chúng ta có thể sẽ cần đến hàm row_number() trong MySQL để chọn ra giá trị muốn giữ lại. Những việc này gần như rất khó hoặc bất khả thi nếu chỉ dùng sub_query. Mình sẽ hướng dẫn sử dụng hàm Row_number() và ví dụ trong sau của bài viết.
Trên đây là những lợi ích chính mà Common Table Expression sẽ mang lại cho bạn trong quá trình sử dụng MySQL. Bây giờ chúng ta sẽ tìm hiểu xem một vài trường hợp mà chúng ta có thể sử dụng with.
Khả năng lồng ghép câu lệnh như Sub-query
Có một điều khá thú vị ở CTE là các bạn có thể lồng ghép chúng với nhau theo nhiều level khác nhau. Mặc dù rằng mình không khuyến khích làm việc này trên thực tế vì nó sẽ làm tăng độ phức tạp của câu lệnh. Các bạn hoàn toàn có thể sử dụng nếu thấy phù hợp.
Cách khởi tạo CTE
Chúng ta có thể định nghĩa một CTE với mệnh đề WITH
[WITH [, ...]] ::= cte_name [(column_name [, ...])] AS (cte_query)
Ví dụ
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Tạo một Common Table Expression đệ quy
Một CTE đệ quy là một CTE tự tham chiếu đến chính nó. CTE đệ quy hữu ích khi làm việc với dữ liệu phân cấp vì CTE tiếp tục thực thi cho đến khi truy vấn trả về toàn bộ phân cấp.
Ví dụ tạo một table Employees
CREATE TABLE Employees ( EmployeeID int NOT NULL PRIMARY KEY, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, ManagerID int NULL ) INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)
Sau khi tạo Employees table, sau câu lệnh SELECT, đứng trước mệnh đề WITH bao gồm một CTE có tên cteReports được tạo:
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
Vì vậy, CTE có thể là một công cụ hữu ích khi bạn cần tạo các tập kết quả tạm thời có thể được truy cập trong câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc MERGE.
Nguồn tham khảo