SQL Series: Vì sao bạn nên xài SQL CTEs
CTE cho phép bạn lưu trữ một query để xài trong script khi có dịp cần thiết. Tuy vậy, chúng thường bị đánh giá thấp hơn subquery, vốn được xài bởi rất nhiều người. Bài viết này sẽ giải thích lí do vì sao tôi lại thích xài CTE để tạo các SQL query. Cách chúng hoạt động Trong ví dụ ...
CTE cho phép bạn lưu trữ một query để xài trong script khi có dịp cần thiết. Tuy vậy, chúng thường bị đánh giá thấp hơn subquery, vốn được xài bởi rất nhiều người. Bài viết này sẽ giải thích lí do vì sao tôi lại thích xài CTE để tạo các SQL query.
Cách chúng hoạt động
Trong ví dụ dưới đây, tôi dùng CTE để:
- Lấy mọi data trong bảng Email Delivered, nói với SQL rằng nó cần phải giữ đống data đó trong bộ nhớ
- Sau đó lấy những thứ cần thiết trong bảng Unsubscribe và nói với SQL rằng nó cũng cần phải giữ đống data này trong bộ nhớ
- Cuối cùng gộp chúng lại với những flag từ bảng Customers
Tại vị trí mà bạn thấy chữ WITH, là khúc bắt đầu của CTE và tôi đặt tên cho chúng là ‘delivered’ và ‘unsubs’ trước khi cho 2 CTE này biết yêu cầu return gì:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
; WITH delivered as -- start the CTE with a semicolon to terminate anything above (SELECT emailaddress, emailid, senddate FROM marketing.emaildelivered WHERE senddate between '2018-01-01' and '2018-01-31' ), -- add a comma if you need to add a subsequent CTE unsubs as (SELECT emailaddress, emailid, senddate FROM marketing.emailunsubscribe WHERE senddate between '2018-01-01' and '2018-01-31' ) -- no comma for the last CTE SELECT 'January' as monthdelivered, c.country, delivered.emailid, count(distinct delivered.emailaddress) as [countofdelivered], count(distinct unsubs.emailaddress) as [countofunsubd] FROM delivered LEFT JOIN marketing.customers c on delivered.email = unsubs.emailaddress LEFT JOIN unsubs on delivered.email = unsubs.email and delivered.emailid = unsubs.emailid GROUP BY delivered.emailid, c.country ORDER BY delivered.emailid |
Khi tôi thực hiện quá trình “gộp mọi thứ lại”, tôi sẽ gộp các field từ ‘delivered’ dataset như ‘delivered.email’.
Vì sao lại không sử dụng Subquery?
Tôi không thích xài Subquery vì bản thân thích lấy từng dataset riêng biệt hết rồi mới gộp chúng lại với nhau ở bước cuối cùng.
Trong khi đó, Subquery thì cứ gộp mọi data set ngay khi nó vừa có trên tay và điều này có thể sẽ trở nên khá xấu xí nếu bạn không cẩn thận.
- Đầu tiên, để bắt đầu chạy query ở giữa thì phải dùng ‘SELECT AccountID …’ để lấy mọi vị trí lớn hơn 30 trong bảng OrderHistory
- Sau đó JOIN trên bảng Account để check xem Account nào đến từ New Zealand
- Sau đó SELECT sẽ chạy để trả tất cả các field từ ord dataset và 3 cột mà tôi muốn từ bảng Account
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT ord.*, acc.Country acc.City acc.CreatedDateUTC FROM ( SELECT AccountID, OrderID, OrderValue FROM Sales.OrderHistory WHERE OrderValue > '30' ) ord JOIN Sales.Account acc ON ord.AccountID = acc.AccountID WHERE acc.Country = 'New Zealand' |
Vì sao CTE lại được cho là tuyệt vời?
Bạn có thể sử dụng chúng nhiều lần trong suốt tập script của mình và chúng cũng rất dễ đọc, bạn có thể return những gì mình cần sau đó reference nó sau.
Nếu mệt vậy sao lại không tạo thêm một bảng luôn?
Nếu bạn không có quyền được viết (write permission) thì có thể điều này sẽ không thực hiện được và nếu tạo nguyên cái bản mà chỉ để cho một query thì quả thật sẽ rất phí thời gian lẫn công sức.
Liệu có bất kì điểm yếu nào không?
CTE không tồn tại mãi được và chỉ xài được cho query mà bạn đang trong đó. Trái ngược với temp table hoặc view vốn vẫn sống khỏe dù ở ngoài phạm vị của script bạn sử dụng.
Vậy còn hiệu năng thì sao?
SQL server sẽ luôn quyết định cho bạn, thông qua query planner, cách tốt nhất để execute các query. Nếu bạn muốn hỏi một chuyên gia về vấn đề này thì câu trả lời nhận được sẽ là “tùy”. Và quả thật là vậy, khi bạn dùng CTE thì mục đích chính là tính dễ đọc của nó.
Techtalk via Dev.to
