Nếu bạn đang định hướng học SQL cho vị trí Data Analyst hoặc Business Intelligence [BI] Developer, lệnh With [CTE – common table expression] là một trong những lệnh cực kỳ quan trọng mà bạn bắt buộc phải biết và thành thạo. Theo cá nhân mình, đây là lệnh quan 1 trong 2 lệnh quan trọng nhất cùng với lệnh THAM GIA [xem chi tiết tại đây]
Việc thực hiện lệnh VỚI trong SQL sẽ giúp ích rất nhiều trong quá trình xử lý dữ liệu bao gồm cả việc thực hiện các truy vấn lệnh phức tạp cũng như tối ưu hóa bộ nhớ xử lý. Chúng ta sẽ làm rõ những điều này ở phần sau bài viết
Tin vui là việc sử dụng lệnh này lại khá đơn giản. Trong bài viết này chúng ta sẽ cùng nhau tìm hiểu những vấn đề sau đây
- Common Table Expression và command with in sql là gì?
- Hướng dẫn sử dụng command with
- Những lợi ích của công việc sử dụng lệnh với
- Những ví dụ và cách khác nhau để sử dụng lệnh với
- Cách sử dụng lệnh Row_number[] same with with
Bạn nào chưa biết gì về SQL thì xem bài hướng dẫn cơ bản của mình tại đây
Bài này mình sử dụng cơ sở dữ liệu mẫu được chia sẻ tại bài viết này. Nếu phát sinh lỗi khi dùng CTE thì các bạn nên nâng cấp bảng XAMPP của mình lên phiên bản mới nhất nhé
Mục Lục
Biểu thức bảng chung CTE là gì?
Trước khi đi vào giải thích lệnh với chúng ta, hãy cùng tìm hiểu về khái niệm Common Table Expression trước. Trong MySQL, hay nhiều loại hệ thống quản trị cơ sở dữ liệu khác, có một khái niệm quan trọng được gọi là Biểu thức bảng chung [CTE]. Hiểu một cách đơn giản thì CTE là nơi chúng ta lưu trữ bảng dữ liệu được truy xuất một cách tạm thời trong bộ nhớ dưới một cái tên để chúng ta có thể sử dụng lại về sau
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 truy vấn SQL phức tạp thành những phần nhỏ hơn để giải quyết. Sau đó chúng ta sẽ viết lại những phần này để giải quyết một vấn đề tổng thể có thể tốt hơn
Cứ hình dung như khi 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ỏ các 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 ở các 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 tôi lưu kết quả từ truy vấn lệnh 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 [hàm] đượ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 thời, còn chức năng trong các ngôn ngữ khác nhau sẽ thực hiện mỗi lần bạn sử dụng
Go command WITH in SQL is what?
Lệnh VỚI trong SQL chính là cú pháp để chúng ta sử dụng chức năng CTE trong MySQL. Cấu trúc của lệnh này khá đơn giản và chúng ta có thể nhìn vào bên dưới để hiểu rõ hơn
-- tính và thêm cột tổng giá trị mỗi hóa đơn ở cuối table
with raw_data as [
select * from superstore.orders
]
,sales_per_order as [
select Order_ID , sum[sales] as total_sales
from raw_data -- sử dụng lại CTE table
group by Order_ID
]
select r.*, a.total_sales
from raw_data as r -- sử dụng lại
left join sales_per_order as a -- Join với CTE table
on r.order_id = a.order_id
limit 100;
Nhìn vào câu lệnh trên chúng ta có thể rút ngắn cấu trúc lệnh với như sau
With tên_bảng_CTE_1 as [ -- [1]
Select .. from .. -- thân của CTE -- [2]
]
, tên_bảng_CTE_2_nếu_cần as [ -- [3]
Select .. from .. -- [có thể dùng CTE được khai báo trước đó]
]
-- phần kết thúc câu lệnh [4]
Select ...
From ...
Where ...
[1]. Đây là phần bắt đầu của lệnh với. Nó là phần bắt buộc nếu muốn sử dụng chức năng của CTE
[2]: đây là phần thân của lệnh with, các bạn có thể thực viết một câu lệnh SELECT hoàn chỉnh phía trong của phần body. Và kết quả của lệnh query sẽ được lưu vào
[3]: đây là một bảng CTE thứ 2. Từ bảng thứ 2 trở đi, chúng ta không cần dùng lệnh WITH nữa mà chỉ cần + AS sau đó là phần thân. Các bảng CTE cần được ngăn cách bởi dấu PHẨY ‘,’.
Lưu ý. phần này hoàn toàn là tùy chọn, bạn có thể cần đến nó hoặc không tùy chọn vào mức độ phức tạp của truy vấn
[4]. đây là phần kết thúc của câu lệnh SQL và cũng là phần bắt buộc phải có. Nó là một câu lệnh CHỌN hoàn chỉnh đi ngay sau bảng CTE cuối cùng và KHÔNG có dấu PHẨY
Các bạn có thể sử dụng toàn bộ HOẶC KHÔNG các bảng CTE được viết ở phía trên. Bạn có thể khai báo một bảng CTE mà không sử dụng gì cho nó
Phần này sẽ trả về kết quả cuối cùng của toàn bộ truy vấn. Nếu các bạn muốn sử dụng kết quả này như một bảng phụ để tính toán tiếp, thì bọc nó lại như một bảng CTE với một cái tên
Ngoài lệnh SELECT ở phần [4], chúng ta còn có thể sử dụng lệnh UPDATE hoặc DELETE. Phần này mình chỉ tập trung vào SELECT
Lưu ý
- Bảng CTE có thể được sử dụng nhiều lần ở nhiều nơi khác nhau mà không bị giới hạn số lượng. Nguyên tắc chỉ được sử dụng những bảng được khai báo trước đó
- CTE table can be used in the following command FROM, JOIN or [select … from…] at WHERE
- Các bảng đã khai báo không được phép sử dụng đến
Những lợi ích của lệnh VỚI CTE
Việc mình ra lệnh VỚI là một trong những mệnh 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 về 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 mã 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 rằng mình hoàn toàn có thể đưa 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 máy chủ 2 lần để lấy dữ liệu. Một lần ở dòng thứ 2 và một 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 với máy chủ 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 thời 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 truy vấn
Giảm độ phức tạp của truy vấn
Nhìn lại 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 lệnh đơn giản, việc sử dụng truy vấn phụ sẽ khá 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 tiểu truy vấn này
Hãy thử cấu hình dung trong trường hợp chúng ta cần lấy dữ liệu từ 5 hoặc 10 bảng dữ liệu khác nhau. Và toàn bộ bộ đồng bộ 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 truy vấn phụ sẽ dễ phát sinh lỗi vì chúng ta khó kiểm soát được mã khi bỏ chúng chung lại với nhau
Nhưng vấn đề sẽ dễ dàng 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ý các bảng dữ liệu riêng có thể sẽ luôn dễ dàng hơn là chia lại một nơi. Đến cuối cùng chúng ta chỉ cần tham gia 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ễ đọ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 mã của người khác hoặc mã đọc của người khác 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 từng bảng, chúng ta sẽ giúp người khác hiểu ý nghĩa của nó dễ dàng hơn. Ngoài ra chỉ là để chính bạn đọc mã của mình sau 2-3 năm
Tăng tính linh hoạt mà truy vấn phụ không có
Không phải tất cả các trường hợp truy vấn phụ đều có thể thay thế bằng. Sẽ có nhiều trường hợp mà bạn buộc phải sử dụng with để xử lý vì truy vấn phụ 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 lặp [duplicate] phát sinh do quá trình tham gia các bảng
Trong trường hợp hợp lệ trên, chúng ta có thể cần đến hàm row_number[] trong MySQL để chọn ra giá trị muốn giữ lại. Những công việc này gần như rất khó hoặc bất khả thi nếu chỉ sử dụng sub_query. Mình sẽ hướng dẫn sử dụng hàm Row_number[] và các ví dụ 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 với
Khả năng lồng ghép câu lệnh dưới dạng Truy vấn phụ
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 cấp độ khác nhau. Mặc dù rằng mình không khuyến khích việc làm này trên thực tế vì nó sẽ làm tăng mức độ 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 sử dụng with và ví dụ
Trong phần này chúng ta sẽ cùng xem qua một vài ví dụ mẫu và cách sử dụng with để các bạn có thể hiểu sâu hơn về with. Đầu tiên chúng ta sẽ bắt đầu với lệnh đơn giản nhất
Lưu ý
- Trong các trường hợp mà câu hỏi phức tạp, mình thường bắt đầu bằng một bảng CTE chứa toàn bộ dữ liệu mà mình cần
- Những ví dụ mình sử dụng dưới đây chỉ nhằm mục đích hướng dẫn cách sử dụng với, nó không thể là lệnh tối ưu nhất
Trường hợp 1. Cơ bản
Câu hỏi 1. Số lượng sản phẩm bán ra trong ngày và tổng tiền tại Los Angeles
-- raw_table để lưu dữ liệu cần thiết
with raw_data as [
select *
from superstore.orders
where City = 'Los Angeles'
]
select City, Order_Date, count[Product_ID] as num_product
, sum[Sales] as total_sales
from raw_data group by City,Order_Date
Trường hợp 2. use CTE in join and where
Câu hỏi 2. Lấy toàn bộ dữ liệu của thành phố có tổng doanh số đứng thứ 3 thuộc tiểu bang có tổng doanh số đứng thứ 2 và thêm vào cuối cột tổng doanh số của thành phố
-- raw_table để lưu toàn bộ data
with raw_data as [
select *
from superstore.orders
]
-- Tính total sales của state
-- và rank dựa trên total sales
, sales_per_state as [
select Country,State
, sum[Sales] as state_sales
, row_number[] over [partition by Country order by state_sales desc] as state_sales_rank
-- xem giải thích hàm row_number[] bên dưới
from raw_data as r
group by Country,State
]
-- tính total sales của city
-- và filter chỉ lấy city có state_sales_rank = 2
, sales_per_city as[
select Country, state,city
, sum[Sales] as city_sales
, row_number[] over [partition by state order by city_sales desc] as city_sales_rank
from raw_data
where State = [select state from sales_per_state
where state_sales_rank = 2]
group by Country,state,city
]
select rd.*, spc.city_sales
from raw_data as rd
join sales_per_city as spc on spc.city = rd.city
and spc.city_sales_rank = 3
Cách sử dụng hàm row_number[] trong MySQL
Câu lệnh này sẽ thực hiện kích hoạt sau khi toàn bộ lệnh trong đó được thực hiện. Tiếp theo đó sẽ chia thành các phần khác nhau theo kết quả dựa trên điều kiện ở Partition by. Cuối cùng nó sẽ đánh dấu thứ tự cho mỗi dòng kết quả dựa trên điều kiện theo thứ tự
Mình sẽ lấy sales_per_state để làm ví dụ. Đầu tiên cứ xem như mình đã có kết quả mà không có lệnh Row_number[]. Lúc này chúng ta sẽ có bảng bao gồm Country, State và Sate_sales. Tiếp theo, lệnh Row_number sẽ được thực thi
Đầu tiên nhóm sẽ kết quả trả về theo từng nhóm, ở đây sẽ là quốc gia. Tiếp theo, với mỗi quốc gia như vậy, hệ thống sẽ sắp xếp kết quả theo thứ tự giảm dần theo state_sales DESC. Sau khi có kết quả, nó sẽ đánh số từ 1 cho đến khi hết một quốc gia và sẽ bắt đầu lại 1 cho quốc gia mới. Dữ liệu trong ví dụ chỉ có 1 nước nên chúng ta chỉ có một nhóm
Bạn cũng có thể sử dụng lệnh này để loại bỏ những kết quả trùng lặp [trùng lặp] với logic tương tự. Hãy chạy từng CTE trong ví dụ trên để hiểu rõ hơn về kết quả
Trường hợp 3. Use CTE lồng ghép
Như mình nói về phía mình không khuyến khích sử dụng CTE lồng ghép nhưng mình vẫn giới thiệu cho các bạn biết nếu cần
Câu hỏi 3. tương tự như câu hỏi 2, chúng tôi muốn lấy thêm kết quả cho thành phố có tổng doanh số bán hàng thấp nhất trong bang có doanh số bán hàng thấp nhất
-- raw_table để lưu toàn bộ data
with raw_data as [
select *
from superstore.orders
]
, cau_hoi_2 as [
-- Tính total sales của state
-- và rank dựa trên total sales
with sales_per_state as [
select Country,State
, sum[Sales] as state_sales
, row_number[] over [partition by Country order by state_sales desc] as state_sales_rank
from raw_data as r
group by Country,State
]
-- tính total sales của city
-- và filter chỉ lấy city có state_sales_rank = 2
, sales_per_city as[
select Country, state,city
, sum[Sales] as city_sales
, row_number[] over [partition by state order by city_sales desc] as city_sales_rank
from raw_data
where State = [select state from sales_per_state
where state_sales_rank = 2]
group by Country,state,city
]
select rd.*, spc.city_sales
from raw_data as rd
join sales_per_city as spc on spc.city = rd.city
and spc.city_sales_rank = 3
]
, cau_hoi_3 as [
-- Tính total sales của state
-- và rank dựa trên total sales
with sales_per_state as [
select Country,State
, sum[Sales] as state_sales
, row_number[] over [partition by Country order by state_sales asc] as state_sales_rank
from raw_data as r
group by Country,State
]
-- tính total sales của city
-- và filter chỉ lấy city có state_sales_rank = 1
, sales_per_city as[
select Country, state,city
, sum[Sales] as city_sales
, row_number[] over [partition by state order by city_sales asc] as city_sales_rank
from raw_data
where State = [select state from sales_per_state
where state_sales_rank = 1]
group by Country,state,city
]
select rd.*, spc.city_sales
from raw_data as rd
join sales_per_city as spc on spc.city = rd.city
and spc.city_sales_rank = 1
]
select * from cau_hoi_2
union
select * from cau_hoi_3
Save note vài điều
- With each new level of with them ta need a from With new
- Lệnh hướng về chỉ mục đích demo cách sử dụng, chưa được ưu tiên tối đa
- Ở Row_number, mình đã thay đổi lệnh Order By … ASC, không còn DESC nữa vì mình muốn lấy mức thấp nhất nên sắp xếp tăng dần sẽ cho ta kết quả ngay tại vị trí số 1
- CTE also could used to implement the command ĐỆ QUI [Recursive]. Các bạn có thể tham khảo thêm tại trang chủ của. Phần này khá phức tạp để giải thích, đặc biệt là các bạn mới nên mình sẽ làm một phần riêng trong các bài sau
Kết luận
Trong bài viết này, bài viết này chúng tôi đã được giới thiệu thông qua lệnh VỚI, Biểu thức bảng chung và những lợi ích cũng như cách sử dụng chúng. Mình tin chắc là nếu các bạn hiểu và thực thi các phần trên, các bạn hoàn toàn có thể tự tin trong việc giải quyết các bài toán phức tạp về sau
Về phần thực hành, các bạn có thể tự đặt cho mình một số câu hỏi liên quan đến Bán hàng rồi tự thực hành. Khả năng tự đặt câu hỏi cũng là một trong những kỹ năng mà bạn cần rèn luyện. Vì khi vào môi trường thực tế, nhiệm vụ của bạn là PHÂN TÍCH DỮ LIỆU và đưa ra thông tin chi tiết. Không phải lúc nào câu hỏi cũng có sẵn
Nếu gặp khó khăn gì thì quay lại bài viết này để đọc hoặc comment bên dưới nhé. Và đừng quên chia sẻ