Chọn bảng mysql

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?

Constructor command With in MySQL CTE

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

Giảm mức độ phức tạp của Truy vấn SQL bằng lệnh With – Source Image. đám mây

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ó

CTE có tính năng hoạt động cao

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

Row_number() sử dụng Partition by State và Order by Sales Desc

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ẻ