Học SQL Cho Data Anslyst - Sức Mạnh Của Lệnh With Và MySQL CTE

Chia sẻ ngay tại đây!

• 25
• 25Shares

Nếu bạn đang có định hướng học SQL cho vị trí Data Analyst hay 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 JOIN (xem chi tiết tại đây).

Việc thành thạo lệnh WITH 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 lệnh query 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 tìm hiểu những vấn đề sau đây:

• Common Table Expression và lệnh with trong sql là gì?
• Hướng dẫn sử dụng lệnh with
• Những lợi ích của việc sử dụng lệnh with
• Những ví dụ và cách khác nhau để sử dụng lệnh with
• Cách sử dụng lệnh Row_number() cùng với with

Những bạn 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 database 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

• 1 Common Table Expression CTE là gì?
• 2 Vậy lệnh WITH trong SQL là gì?
• 3 Những lợi ích của lệnh WITH CTE
  • 3.1 Tối ưu hóa bộ nhớ và tốc độ
  • 3.2 Giảm độ phức tạp của query
  • 3.3 Dễ đọc và dễ hiểu
  • 3.4 Tăng tính linh hoạt mà sub-query không có
  • 3.5 Khả năng lồng ghép câu lệnh như Sub-query
• 4 Cách sử dụng with và ví dụ mẫu
  • 4.1 Trường hợp 1: Cơ bản
  • 4.2 Trường hợp 2: sử dụng CTE trong join và where
  • 4.3 Trường hợp 3: Sử dụng CTE lồng ghép
• 5 Kết luận

Common Table Expression CTE là gì?

Trước khi đi vào giải thích lệnh with 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ệ quản trị cơ sở dữ liệu khác, có một khái niệm quan trọng gọi là Common Table Expression (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ể 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 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.

Vậy lệnh WITH trong SQL là gì?

Lệnh WITH 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 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 câu lệnh trên chúng ta có thể rút ngắn lại cấu trúc lệnh with 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 with. Nó là phần bắt buộc nếu muốn 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 <tên_bảng_CTE_1>

(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 <tên_bảng> + 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 là hoàn toàn tùy chọn, bạn có thể cần đến nó hoặc không tùy vào độ phức tạp của query.

(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 SELECT hoàn chỉnh đi ngay sau CTE table 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 dùng gì tới nó.

Phần này sẽ trả về kết quả cuối cùng của toàn bộ query. Nếu các bạn muốn sử dụng kết quả này như là 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ị hạn chế số lượng. Nguyên tắc là chỉ dùng được những bảng được khai báo trước đó.
• Bảng CTE có thể được sử dụng trong phần sau lệnh FROM, JOIN hoặc (select … from…) ở WHERE
• Các bảng đã khai báo có thể không cần dùng đến

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 sử dụng with và ví dụ mẫu

Trong phần này chúng ta sẽ cùng xem qua một vài ví dụ mẫu và cách dử 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 câu 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 CTE table chứa toàn bộ dữ liệu mà mình cần.
• Những ví dụ mình dùng dưới đây chỉ nhằm mục đích hướng dẫn cách sử dụng with, nó có thể chưa phải 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: sử dụng CTE trong join và where

Câu hỏi 2: Lấy toàn bộ dữ liệu của thành phố có tổng sales đứng thứ 3 thuộc tiểu bang có tổng sales đứng thứ 2 và thêm vào cuối cột tổng sales 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 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 thi.Tiếp đó sẽ chia thành các phần khác nhau 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 ở Order by.

Mình sẽ lấy sales_per_state để ví dụ. Đầu tiên cứ xem như mình đã có kết quả mà ko có lệnh Row_number(). Lúc này chúng ta sẽ có bảng gồm Country, State và Sate_sales. Tiếp đó lệnh Row_number sẽ được thực thi.

Nó đầu tiên nhó sẽ nhóm kết quả lại theo từng nhóm, ở đây sẽ là country. Tiếp theo, với mỗi country 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 country và sẽ bắt đầu lại 1 cho country mới. Do 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 (duplicate) 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: Sử dụng CTE lồng ghép

Như mình nói phía trên 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 ta muốn lấy thêm kết quả cho thành phố có tổng sale thấp nhất trong state có sales 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

Lưu ý vài điều:

• Với mỗi level mới của with chúng ta cần một từ With mới
• Câu lệnh phía trên chỉ nhằm mục đích demo cách dùng, chưa được tối ưu
• Ở 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 thấp nhất nên sắp xếp tăng dần sẽ cho ta kết quả ngay ở vị trí số 1.
• CTE còn có thể sử dụng để thực hiện các lệnh ĐỆ QUI (Recursive). Các bạn có thể tham khảo thêm ở trang chủ của MySQL. Phần này khá phức tạp để giải thích, đặc biệt 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 viết này chúng ta đã được giới thiệu qua lệnh WITH, Common Table Expression 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 hành những 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 Sales 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à ANALYSE DATA và đưa ra insights. Không phải lúc nào câu hỏi cũng có sẵn cả.

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ẻ!

Bài viết thuộc quyền sở hữu của Data-Fun.com.

Chia sẻ ngay tại đây!

• 25
• 25Shares

• 25Shares