Cte là gì

Nếu chúng ta đang sẵn có triết lý học SQL mang đến địa điểm Data Analyst hay Business Intelligence (BI) Developer, lệnh With (CTE – comtháng table expression) là 1 giữa những lệnh cực kì đặc biệt quan trọng cơ mà chúng ta đề nghị phải ghi nhận và thạo. Theo cá nhân bản thân, đây là lệnh quan lại 1 trong những 2 lệnh quan trọng đặc biệt duy nhất cùng rất lệnh JOIN (coi chi tiết trên đây).

Bạn đang xem: Cte là gì

Việc thành thục lệnh WITH trong Squốc lộ để giúp đỡ ích tương đối nhiều vào quá trình cách xử lý dữ liệu bao hàm cả việc thực hiện các lệnh query tinh vi cũng giống như tối ưu hóa bộ lưu trữ xử lý. Chúng ta đang làm rõ hầu như điều này tại đoạn sau bài viết.

Tin vui là câu hỏi sử dụng lệnh này lại tương đối đơn giản dễ dàng. Trong bài viết này bọn họ vẫn thuộc khám phá mọi vấn đề sau đây:

Common Table Expression cùng lệnh with trong sql là gì?Hướng dẫn sử dụng lệnh withNhững tác dụng của vấn đề thực hiện lệnh withNhững ví dụ và giải pháp khác nhau để sử dụng lệnh withCách sử dụng lệnh Row_number() cùng với with

Những bạn chưa biết gì về Squốc lộ thì coi bài bác lý giải cơ bản của bản thân trên đây

Bài này mình áp dụng database mẫu được share trên nội dung bài viết này. Nếu tạo nên lỗi khi dùng CTE thì chúng ta buộc phải upgrade bảng XAMPPhường của chính bản thân mình lên phiên bản tiên tiến nhất nhé.


Mục Lục

3 Những công dụng của lệnh WITH CTE4 Cách sử dụng with và ví dụ mẫu

Common Table Expression CTE là gì?

Trước lúc lấn sân vào phân tích và lý giải lệnh with họ hãy cùng tìm hiểu về định nghĩa Common Table Expression trước. Trong MySquốc lộ, tốt nhiều các loại hệ quản ngại trị đại lý tài liệu không giống, bao gồm một định nghĩa quan trọng đặc biệt Gọi là Comtháng Table Expression (CTE). Hiểu một bí quyết dễ dàng thì CTE là nơi chúng ta lưu trữ bảng tài liệu được truy tìm xuất một biện pháp trong thời điểm tạm thời vào bộ nhớ lưu trữ bên dưới một cái thương hiệu để bạn cũng có thể sử dụng lại sau này.

Việc lưu trữ tài liệu một cách tạm thời sẽ giúp đỡ chúng ta phân chia bé dại một lệnh SQL query phức tạp thành phần đa phần nhỏ tuổi rộng nhằm xử lý. Sau kia họ vẫn gồm lại mọi phần này để giải quyết và xử lý một vụ việc toàn diện to hơn.

Cđọng tưởng tượng như lúc các bạn giải những phương thơm trình toán học tập phức tạp, họ hay đang phân tách bé dại bài bác tân oán thành phần lớn nhỏ dại không giống nhau nhằm giải quyết. Và kết quả của từng phần bé dại này sẽ được lưu vào một biến đổi cùng vươn lên là này có thể được thực hiện lại sinh sống đông đảo phần sau.CTE cũng chuyển động với cách thức tương tự như và nó để giúp bọn họ giữ hiệu quả từ lệnh query của chính mình với được áp dụng lại kế tiếp.

Nếu chúng ta từng học tập qua lập trình sẵn, vấn đề Điện thoại tư vấn tên bảng CTE cũng rất như là cùng với Việc gọi một hàm (function) được được knhị báo sẵn. Khác nhau ở trong phần là bảng CTE vẫn chỉ tiến hành một lần với lưu kết quả vào bộ lưu trữ lâm thời, còn hàm trong những ngôn ngữ khác sẽ thực thi những lần các bạn sử dụng.

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

*
Cấu trúc lệnh With vào MySquốc lộ CTE

Lệnh WITH trong Squốc lộ đó là cú pháp để họ sử dụng tác dụng CTE vào MySquốc lộ. Cấu trúc của lệnh này tương đối đơn giản và chúng ta có thể chú ý dưới nhằm nắm rõ rộng.

-- tính và thêm cột tổng mức vốn mỗi hóa đơn sinh hoạt cuối tablewith raw_data as (select * from superstore.orders),sales_per_order as (select Order_ID, sum(sales) as total_salesfrom raw_data -- áp dụng lại CTE tablegroup by Order_ID)select r.*, a.total_salesfrom raw_data as r -- thực hiện lạileft join sales_per_order as a -- Join với CTE tableon r.order_id = a.order_idlimit 100;Nhìn câu lệnh bên trên bạn có thể rút ít ngắn hơn kết cấu lệnh with nlỗi 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_buộc phải as ( -- (3)Select ... from ... -- (hoàn toàn có thể cần sử dụng CTE được khai báo trước đó))-- phần ngừng câu lệnh (4)Select ...From ...Where ...(1): Đây là phần ban đầu của lệnh with. Nó là phần bắt buộc nếu còn muốn sử dụng công dụ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 hảo phía vào của phần toàn thân. 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 trlàm việc đi, bọn họ ko buộc phải sử dụng lệnh WITH nữa mà lại chỉ việc + AS tiếp nối là phần thân. Các bảng CTE cần được phân cách bởi dấu PHẨY ‘,’.

Lưu ý: phần này là trọn vẹn tùy lựa chọn, chúng ta có thể bắt buộc mang lại nó hoặc ko tùy từng độ phức tạp của query.

(4): đây là phần kết thúc của câu lệnh Squốc lộ với cũng là phần sẽ phải gồm. Nó là 1 trong những câu lệnh SELECT hoàn chỉnh đi ngay sau CTE table ở đầu cuối và KHÔNG bao gồm vết PHẨY.

Các bạn cũng có thể sử dụng cục bộ HOẶC KHÔNG những bảng CTE được viết phía trên. quý khách hàng rất có thể knhì báo một bảng CTE nhưng mà không dùng gì cho tới nó.

Phần này sẽ trả về kết quả sau cùng của toàn bộ query. Nếu những bạn có nhu cầu thực hiện hiệu quả này như thể bảng phú nhằm tính toán tiếp thì quấn này lại nhỏng một bảng CTE cùng với một cái tên.

Ngoài lệnh SELECT ở trong phần (4), họ còn rất có thể thực hiện lệnh UPDATE hoặc DELETE. Phần này tôi chỉ triệu tập vào SELECT.

Lưu ý:

Bảng CTE hoàn toàn có thể được áp dụng các lần sinh hoạt nhiều địa điểm khác nhau mà không bị hạn chế con số. Nguyên ổn tắc là chỉ dùng được đều bảng được knhị báo trước kia.Bảng CTE rất có thể được sử dụng trong phần sau lệnh FROM, JOIN hoặc (select … from…) ở WHERECác bảng đang knhị báo hiện có thể ko nên sử dụng đến

Những công dụng của lệnh WITH CTE

Việc bản thân đến lệnh WITH là 1 trong trong những lệnh đặc trưng tốt nhất dựa trên số đông lợi ích mà nó đưa về đến chúng ta. Chúng ta vẫn đi so sánh từng công dụng một.

Tối ưu hóa bộ nhớ cùng tốc độ

Nhỏng bản thân nói bên trên, các bảng CTE rất có thể được thực hiện lại những lần cùng nghỉ ngơi các khu vực khác nhau. Chính công dụng này để giúp chúng ta thống trị giỏi hơn bộ nhớ cùng tốc độ xử lý.

Để làm rõ rộng vấn đề bọn họ cùng coi đoạn code sau:

select a.*,b.total_salesfrom 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épon a.Order_ID = b.order_idLimit 100;Chúng ta rất có thể thấy được rằng bản thân hoàn toàn có thể đã cho ra công dụng tương tự như nhưng mà ko phải mang lại CTE. Nhưng ví như quan sát kỹ rộng bọn họ đã thấy rằng nghỉ ngơi câu lệnh này khối hệ thống sẽ liên kết cùng với server 2 lần để đưa dữ liệu. Một lần nghỉ ngơi dòng thứ 2 và lần còn sót lại sống loại sản phẩm 4.

Xem thêm: Cách Cài Net Framework 3.5 Cho Win 10, Hướng Dẫn Cách Cài Net Framework 3

Việc tạo nên các kết nối đến cùng một bảng dữ liệu đã tiêu tốn khá nhiều tài nguyên. Nếu chúng ta tất cả một bảng tài liệu lên tới mức hàng Terabyte (TB) cùng truy nã xuất bọn chúng nhiều lần thì tổng lượng tài nguim tiêu thú vẫn không hề nhỏ. Hơn nữa vấn đề liên kết tới VPS các lần cũng tiêu hao khá nhiều thời hạn.

Trong trường phù hợp này, bài toán áp dụng bảng CTE sẽ giúp đỡ tàng trữ toàn bộ dữ liệu cần thiết trong bộ nhớ lâm thời cùng sử dụng lại bất kể lúc nào. Chúng giúp giảm thiểu không ít tài nguyên và thời gian xúc tiến lệnh query

Giảm độ tinh vi của query

*
Giảm độ tinh vi của Squốc lộ Query với lệnh With – Nguồn Image: Cloudbolt

Lại quan sát vào đầy đủ câu lệnh số 2. Ở phần LEFT JOIN họ thực hiện một Sub-query để mang tài liệu. Trong những câu lệnh dễ dàng và đơn giản, sử dụng sub-query sẽ tương đối là dễ dãi. Nhưng trong những trường hòa hợp phức hợp, chúng ta khó khăn có thể kiểm soát và điều hành được hồ hết sub-query này.Hãy thử hình dung vào ngôi trường thích hợp bọn họ phải lấy tài liệu trường đoản cú 5 tuyệt 10 bảng tài liệu không giống nhau. Và tổng thể hồ hết cần phải cách xử trí trước khi gộp lại thành một bảng. Trong trường hợp này, việc áp dụng sub-query vẫn dễ dàng tạo ra lỗi vì chưng chúng ta nặng nề điều hành và kiểm soát được code Khi vứt chúng thông thường lại cùng nhau.

Nhưng vấn đề sẽ dễ rộng các nếu như chúng ta cách xử trí 10 bảng dữ liệu này cùng lưu lại và 10 bảng CTE không giống nhau.Việc xử trí đông đảo bảng dữ liệu riêng biệt lẽ đang luôn dễ dãi hơn là gộp tầm thường lại một nơi. Đến sau cuối chúng ta chỉ cần join những bảng dữ liệu vẫn qua xử lý. Nếu có phát sinh lỗi, chúng ta cũng biến thành dễ dàng kiểm soát cùng chỉnh sửa hơn.

Dễ gọi cùng dễ dàng hiểu

Việc tách bài xích tân oán to thành hầu như phần nhỏ để giúp bản thân hay người không giống thuận tiện hiểu và hiểu mục đích bạn muốn có tác dụng. Trên thực tiễn, đang ít nhiều lần chúng ta được kinh nghiệm hiểu code của fan không giống hoặc fan khác phát âm code của khách hàng.

Sử dụng CTE sẽ giúp họ thuận lợi hơn trong Việc truyền tải mục tiêu của bản thân mình cũng giống như súc tích của cả câu lệnh. Thông qua việc khắc tên bảng và ghi chú cho mỗi bảng, bọn họ sẽ giúp đỡ tín đồ không giống hiểu ý nghĩa sâu sắc của chính nó thuận lợi hơn. Thậm chỉ với đến bao gồm bạn đọc code của bản thân sau 2-3 năm.

Tăng tính linc hoạt nhưng sub-query không có

*
CTE gồm tính linh hoạt cao

Không cần tất cả trường vừa lòng sub-query phần đông hoàn toàn có thể thay thế sửa chữa mang đến with. Sẽ có tương đối nhiều ngôi trường vừa lòng cơ mà chúng ta bắt buộc sử dụng with để xử trí vì sub-query ko cung cấp điều này. giữa những trường vừa lòng này là lúc bạn có nhu cầu xử lý tài liệu trùng (duplicate) phát sinh bởi quá trình join tables.

Trong ngôi trường hợp trên, chúng ta có thể vẫn đề xuất mang đến hàm row_number() trong MySQL để chọn ra quý giá ao ước lưu lại. Những việc này gần như là khôn xiết nặng nề hoặc bất khả thi nếu như chỉ sử dụng sub_query. Mình đang trả lời thực hiện hàm Row_number() cùng ví dụ vào sau của nội dung bài viết.Trên đây là những tiện ích thiết yếu mà Comtháng Table Expression sẽ mang đến cho mình vào quá trình thực hiện MySquốc lộ. Bây giờ đồng hồ chúng ta sẽ tìm hiểu coi một vài ba trường đúng theo mà chúng ta có thể sử dụng with.

Khả năng lồng ghnghiền câu lệnh nlỗi Sub-query

Có một điều tương đối độc đáo nghỉ ngơi CTE là những chúng ta cũng có thể lồng ghép bọn chúng cùng nhau theo rất nhiều level khác biệt. Mặc dù rằng bản thân ko khuyến nghị thao tác này trên thực tế vị nó đang làm tăng cường mức độ phức tạp của câu lệnh. Các chúng ta hoàn toàn hoàn toàn có thể thực hiện giả dụ thấy tương xứng.

Cách thực hiện with cùng ví dụ mẫu

Trong phần này bọn họ đang cùng xem qua một vài ví dụ mẫu mã và phương pháp dử dụng with để những bạn cũng có thể phát âm sâu rộng về with. Đầu tiên chúng ta đang ban đầu với câu lệnh dễ dàng và đơn giản độc nhất vô nhị.

Lưu ý:

Trong những ngôi trường đúng theo mà lại câu hỏi tinh vi, mình thường bắt đầu bằng một CTE table đựng cục bộ dữ liệu nhưng bản thân đề nghị.Những ví dụ mình dùng sau đây chỉ nhằm mục tiêu mục tiêu lí giải biện pháp sử dụng with, nó có thể không hẳn là lệnh tối ưu độc nhất vô nhị.

Trường phù hợp 1: Cơ bản

Câu hỏi 1: Số lượng sản phẩm đẩy ra trong ngày với tổng tiền trên Los Angeles

-- raw_table nhằm lưu giữ tài liệu đề xuất thiếtwith raw_data as (select *from superstore.orderswhere City = "Los Angeles")select City, Order_Date, count(Product_ID) as num_sản phẩm, sum(Sales) as total_salesfrom raw_data group by City,Order_Date

Trường đúng theo 2: sử dụng CTE vào join cùng where

Câu hỏi 2: Lấy cục bộ tài liệu của thị trấn bao gồm tổng sales đứng vị trí thứ 3 trực thuộc tiểu bang bao gồm tổng sales đứng thứ 2 với thêm vào thời điểm cuối cột tổng sales của thành phố

-- raw_table để lưu lại toàn cục datawith raw_data as (select *from superstore.orders)-- Tính total sales của state-- cùng rank dựa vào 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 lý giải hàm row_number() bên dưới from raw_data as r group by Country,State)-- tính total sales của city-- cùng filter chỉ lấy thành phố bao gồm state_sales_rank = 2, sales_per_city as(select Country, state,đô thị, sum(Sales) as city_sales, row_number() over (partition by state order by city_sales desc) as city_sales_rankfrom raw_datawhere State = (select state from sales_per_state where state_sales_rank = 2)group by Country,state,city)select rd.*, spc.city_salesfrom raw_data as rd join sales_per_thành phố as spc on spc.thành phố = rd.cityvà spc.city_sales_rank = 3Cách cần sử dụng hàm row_number() trong MySQL

*
Row_number() sử dụng Partition by State cùng Order by Sales DescCâu lệnh này đã tiến hành kích hoạt sau khoản thời gian toàn bộ lệnh trong những số ấy được xúc tiến.Tiếp này sẽ phân thành các phần không giống nhau công dụng dựa vào điều kiện sống Partition by. Cuối cùng nó đã khắc ghi lắp thêm từ cho mỗi cái hiệu quả dựa vào ĐK ngơi nghỉ Order by.

Mình đã lấy sales_per_state để ví dụ. trước hết cứ đọng xem nlỗi tôi đã gồm hiệu quả nhưng ko gồm lệnh Row_number(). Hiện nay chúng ta sẽ sở hữu được bảng tất cả Country, State cùng Sate_sales. Tiếp kia lệnh Row_number sẽ được thực thi.

Nó trước tiên nhó đã team hiệu quả lại theo từng nhóm, tại đây đã là country. Tiếp theo, với từng country như vậy, khối hệ thống vẫn sắp xếp tác dụng theo thiết bị từ bỏ giảm dần dần theo state_sales DESC. Sau lúc bao gồm tác dụng nó vẫn đánh số từ là một cho đến Khi hết một country với đã bắt đầu lại 1 cho country new. Do tài liệu vào ví dụ chỉ có 1 nước đề nghị họ chỉ có một nhóm.

quý khách cũng có thể thực hiện lệnh này nhằm sa thải đầy đủ công dụng trùng (duplicate) với logic tương tự như. Hãy chạy từng CTE vào ví dụ bên trên nhằm hiểu rõ rộng về tác dụng.

Trường đúng theo 3: Sử dụng CTE lồng ghép

Nlỗi bản thân nói bên trên bản thân ko khuyến khích sử dụng CTE lồng ghép nhưng lại mình vẫn trình làng mang lại chúng ta biết nếu như cần.Câu hỏi 3: tựa như nlỗi thắc mắc 2, họ mong muốn lấy thêm kết quả cho thành thị bao gồm tổng sale phải chăng duy nhất vào state có sales tốt tuyệt nhất.

-- raw_table nhằm lưu tổng thể datawith raw_data as (select *from superstore.orders), cau_hoi_2 as (-- Tính total sales của state-- với rank dựa trên total saleswith 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_rankfrom raw_data as r group by Country,State)-- tính total sales của city-- với filter chỉ lấy city gồm state_sales_rank = 2, sales_per_thành phố as(select Country, state,thành phố, sum(Sales) as city_sales, row_number() over (partition by state order by city_sales desc) as city_sales_rankfrom raw_datawhere State = (select state from sales_per_state where state_sales_rank = 2)group by Country,state,city)select rd.*, spc.city_salesfrom raw_data as rd join sales_per_thành phố as spc on spc.thành phố = rd.cityvà spc.city_sales_rank = 3 ), cau_hoi_3 as (-- Tính total sales của state-- với rank dựa trên total saleswith 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_rankfrom raw_data as r group by Country,State)-- tính total sales của city-- cùng filter chỉ lấy city tất cả state_sales_rank = 1, sales_per_thành phố as(select Country, state,đô thị, sum(Sales) as city_sales, row_number() over (partition by state order by city_sales asc) as city_sales_rankfrom raw_datawhere State = (select state from sales_per_state where state_sales_rank = 1)group by Country,state,city)select rd.*, spc.city_salesfrom raw_data as rd join sales_per_city as spc on spc.thành phố = rd.cityvà spc.city_sales_rank = 1 )select * from cau_hoi_2union select * from cau_hoi_3Lưu ý vài ba điều:

Với mỗi cấp độ bắt đầu của with họ đề xuất một từ bỏ With mớiCâu lệnh bên trên chỉ nhằm mục đích mục đích kiểm tra cách cần sử dụng, chưa được buổi tối ưuTại Row_number, mình đã chuyển đổi lệnh Order By … ASC, không còn DESC nữa bởi mình thích mang phải chăng duy nhất đề nghị thu xếp tăng dần sẽ cho ta công dụng ngay ở đoạn tiên phong hàng đầu.

Kết luận

Trong bài viết này viết này bọn họ đã có được giới thiệu qua lệnh WITH, Common Table Expression cùng phần nhiều tác dụng tương tự như biện pháp thực hiện chúng. Mình tin chắc chắn là ví như các bạn gọi với thực hành thực tế đều phần bên trên, các bạn trọn vẹn rất có thể đầy niềm tin vào vấn đề giải quyết các bài xích toán thù phức tạp trong tương lai.

Xem thêm: Cách Đấu Amply Karaoke - Cách Đấu 4 Loa Vào 1 Amply Đúng Kỹ Thuật 100%

Về phần thực hành thực tế, các bạn có thể tự đặt cho bạn một số trong những thắc mắc tương quan đến Sales rồi trường đoản cú thực hành. Khả năng trường đoản cú đặt thắc mắc cũng là một trong giữa những kỹ năng cơ mà bạn phải rèn luyện. Vì Lúc vào môi trường xung quanh thực tế, trách nhiệm của người tiêu dùng là ANALYSE DATA với giới thiệu insights. Không cần cơ hội như thế nào câu hỏi cũng có thể có sẵn cả.

Nếu gặp gỡ khó khăn gì thì quay lại bài viết này để gọi hoặc comment dưới nhé. Và hãy nhờ rằng chia sẻ!


Chuyên mục: Blogs