Khóa Học SQL Server - Bài 31. Nâng cao hiệu suất truy vấn SQL với Common Table Expression (CTE)

[@npm02]

Thầy thật có tâm với nghề. Mặc dù view không cao nhưng vẫn cố gắng ra video truyền lại những kinh nghiệm cho tụi em.🥰

[@dothanhdat07]

Em đang làm ngân hàng, Khóa học này rất cần thiết cho công việc của em, trân trọng cảm ơn anh đã tâm huyết cho khóa học, mong anh ra thêm các Video mới,

[@cuonganime.5733]

hay quá a, có CTE cái giải quyết dc rất nhiều câu query khó và phức tạp

[@LuckySport2k3]

--Bài 31. Nâng cao hiệu suất truy vấn SQL với Common Table Expression (CTE)
--Sử dụng CTE để tính tổng doanh số bán hàng cho từng sản phẩm từ 2 bảng Order details và product
with Revenue as(select od.ProductID,sum(od.Quantity*od.UnitPrice) as TotalRevenue
from [dbo].[Order Details] as od
group by od.ProductID)
select p.ProductID,p.ProductName,r.TotalRevenue
from[dbo].[Products] as p
join Revenue as r on r.ProductID=p.ProductID
--Sử dụng CTE để tính toán tổng doanh số bán hàng theo từng khách hàng và sau đó sắp xếp danh sách khách hàng theo tổng doanh số giảm dần
with Revenue as (select o.CustomerID,sum(od.Quantity*od.UnitPrice) as TotalRevenue
from [dbo].[Order Details] as od, [dbo].[Orders] as o
where od.OrderID = o.OrderID
group by o.CustomerID)
select c.CompanyName,r.TotalRevenue
from [dbo].[Customers] as c
join Revenue as r on r.CustomerID=c.CustomerID
order by r.TotalRevenue desc
-- Sử dụng CTE tính tổng doanh số bán hàng theo năm từ bảng Orders và order details
with Revenue as (select od.OrderID,sum(od.Quantity*od.UnitPrice) as TotalRevenue
from [dbo].[Order Details] as od
group by od.OrderID)
select year(o.[OrderDate]),sum(r.TotalRevenue) as TotalRevenue_of_year
from[dbo].[Orders] as o
join Revenue as r on r.OrderID=o.OrderID
group by year(o.OrderDate)

[@banana2010able]

thầy giảng rất hay, tỉ mỉ và dễ hiểu

[@45nguyenngocvinh56]

Em cảm Ơn Thầy Rất Nhiều ạ
Chúc thầy luôn luôn khoẻ mạnh ạ

[@thoaiuc]

--3.Sử dụng CTE tính tổng doanh số bán hàng theo năm từ bảng "Orders"
--và "Order Details"
WITH SalePerProduct as (
SELECT OrderID, SUM(Quantity * UnitPrice) as TotalSales
FROM dbo.[Order Details]
GROUP BY OrderID
)
SELECT YEAR(o.OrderDate) as [YEAR], SUM(sp.TotalSales) as [TotalPriceYear]
FROM dbo.Orders as o
JOIN SalePerProduct as sp ON sp.OrderID = o.OrderID
GROUP BY YEAR(o.OrderDate)

[@bunkhanh7931]

A ơi e nghĩ ở phút 19 , dùng subquery với join nếu dùng left thì sai ạ vì left nó lấy toàn bộ category của sản phẩm ở bảng Product , dùng right hay join thì chỉ lấy 1 thôi ạ.

[@nguyenhuuduy1953]

mê thầy quá dạy hay quá

[@hoangquynnguyen2245]

hằng ngày mong chờ anh ra video

[@NguyenTuan-dt8hq]

Mong thầy sớm ra những video tiếp theo 🎉

[@thenwii711]

CTE kiểu như 1 cái function nó return về cái bảng kết quả. Và cái bảng kết quả đó được sử dụng nhiều lần trong các câu truy vấn khác (tính sử dụng lại giống như function)

[@baochi5787]

Dễ hiểu lắm ạ

[@NguyenLe-ju9up]

quá có tâm

[@thoaiuc]

--1.Sử dụng CTE để tính tổng doanh số bán hàng cho từng sản phẩm
--từ hai bảng "Order Details" và "Products" trong cơ sở dữ liệu NorthWind
WITH SalePerProduct AS (
SELECT ProductID, SUM(Quantity * UnitPrice) as TotalSales
FROM dbo.[Order Details]
GROUP BY ProductID
)
SELECT p.ProductName, sp.TotalSales
FROM dbo.Products as p
JOIN SalePerProduct as sp ON p.ProductID = sp.ProductID

[@minhphungtuan3566]

Mong anh ra thêm về sql

[@chichat9266]

Hay quá ạ

[@ダンティフイントゥ]

thanks add

[@nguyenchung7730]

okee

[@thehung.12]

// Bài 3
with TotalSales as (
select od.OrderID, sum(od.Quantity * od.UnitPrice) as "TotalPrice"
from [Order Details] od
group by od.OrderID
)
select year(o.OrderDate) as "Year",
sum(ts.TotalPrice) as "TotalSalesPerYear"
from Orders o
join TotalSales ts on o.OrderID = ts.OrderID
group by year(o.OrderDate)
order by year(o.OrderDate) asc;

[@phutam9097]

ok anh

[@thegooddieyoung4731]

CTE này giống với cái Struct trong C++ nhỉ? Kiểu mình tự định nghĩa ra các đối tượng.

[@thehung.12]

// Bài 2
with TotalSales as (
select od.OrderID, sum(od.Quantity * od.UnitPrice) as "TotalOrders"
from [Order Details] od
group by od.OrderID
)
select o.OrderID, o.CustomerID,
ts.TotalOrders
from Orders o
join TotalSales ts on o.OrderID = ts.OrderID
order by ts.TotalOrders desc;

[@TuyetMinh-cl7yp]

ok

[@tv-nguyen3554]

Hello Thay , ừm hứ

[@trihuu7502]

CTE khác gì so với View vậy anh?

[@daotanphat7316]

CTE có thấy thế được sub query k thầy?

[@34tranbatung34]

cái with as có giống create view as ko ?

[@readyforwar2557]

create view nó lưu vô db lun a'

[@MaiNgoc-rd9gb]

thầy ơi cho e hỏi tại sao dùng sub query với left join chỉ chạy đc 1 lần thôi ạ, hy vọng thầy hoặc bạn nà có thể giải thích giúp em ạ

[@TITVvn]

Cố lên em, chúc em học thật tốt, em có thể đặt câu hỏi kèm theo hình ảnh khi có thắc mắc ở đây nhé: facebook.com/groups/titv.vn

[@thenwii711]

Cái này bạn tìm hiểu về hàm (function) trong lập trình thì sẽ rõ nha bạn, chứ giải thích trong SQL này khó lắm. Function với CTE này mục đích sử dụng gần như tương tự nhau

[@GấmLêThịHồng-y7d]

CTE trong SQL Server khác với CTE trong MySQL ntn ạ? Mong thầy phản hồi câu hỏi của e.
E cảm ơn thầy nhiều ạ,
E đã xem hết các video về MySQL rồi nhưng vẫn còn nhiều hàm nâng cao e ko thấy
Và e có rất nhiều thắc mắc.

[@TITVvn]

Về tư tưởng không khác, về cú pháp có thể khác.

[@longtran8099]

câu 1:
with TotalProduct as(
select productID, sum([UnitPrice]*[Quantity]*(1-[Discount])) as [TotalPrice]
from[dbo].[Order Details]
group by productID
)
select p.ProductID, p.ProductName,
tp.TotalPrice
from [dbo].[Products] p
left join TotalProduct tp
on tp.ProductID = p.ProductID
câu 3:
with price3 as (
select [OrderID], sum([UnitPrice]*[Quantity]*(1-[Discount])) as [TotalOrderPrice]
from [dbo].[Order Details]
group by [OrderID]
)
select year(o.OrderDate) as [year], sum(p3.TotalOrderPrice) as [total Income]
from [dbo].[Orders] o
inner join price3 p3
on o.OrderID = p3.OrderID
group by year(o.OrderDate)
order by year(o.OrderDate) desc
câu 2: anh thầy cho em xin lời giải với!

[@thoaiuc]

Mình làm theo cách như này bạn tham khảo thử và cho mình xin nhận xét ạ
--2. Sử dụng CTE để tính tổng doanh số bán hàng theo từng khách
--hàng và sau đó sắp xếp danh sách khách hàng theo tổng doanh số
--giảm dần
WITH SalePerProduct as (
SELECT OrderID, SUM(Quantity * UnitPrice) as TotalSales
FROM dbo.[Order Details]
GROUP BY OrderID
)
SELECT o.CustomerID, SUM(sp.TotalSales) as [TotalPrice]
FROM dbo.Orders as o
JOIN SalePerProduct as sp
ON sp.OrderID = o.OrderID
JOIN [dbo].[Customers] as c
ON c.CustomerID = o.CustomerID
GROUP BY o.CustomerID
ORDER BY CustomerID DESC

[@giahuynguyenpham3792]

@@thoaiuc Bạn xem truy vấn này của mình ổn hơn không
with SalePerProduct as(
select o.CustomerID,sum(od.Quantity*od.UnitPrice) as TotalSales
from [dbo].[Orders] o
join [dbo].[Order Details] od
on o.OrderID=od.OrderID
group by o.CustomerID
)
select c.CustomerID,c.ContactName, spp.TotalSales
from [dbo].[Customers] c
join SalePerProduct spp
on spp.CustomerID = c.CustomerID
order by spp.TotalSales desc

[@minhquang6960]

bạn ra bao nhiêu row thế mình ra 830 row @@thoaiuc

[@minhquang6960]

With Ordertotal As(
Select [OrderID] , sum([UnitPrice]*[Quantity]) as [Tổng doanh số]
From [dbo].[Order Details] od
Group by [OrderID] )
Select c.[CustomerID], c.[ContactName], od.[Tổng doanh số]
From [dbo].[Customers] c
Inner join [dbo].[Orders] o
On o.CustomerID = c.CustomerID
Inner join Ordertotal od
On od.[OrderID]= o.OrderID

[@BacteCisa]

@@minhquang6960 Mình cũng ra 830. nhma vấn đề mình run bên trong cái CTE thì nó cũng là 830

[@tuongpham986]

--1. Sử dụng CTE để tính tổng doanh số bán hàng cho từng sản phẩm từ hai bảng 'order deitails' và 'products'
with orderdetailName as (
select od.ProductID,sum(od.Quantity*od.UnitPrice) as [Tổng doanh số cho từng sản phẩm]
from [Order Details] as od
group by od.ProductID
)
select oon.ProductID as [Mã sản phẩm] , p.ProductName [Tên sản phẩm], oon.[Tổng doanh số cho từng sản phẩm]
from Products as p
join orderdetailName as oon
on oon.ProductID = p.ProductID

[@tuongpham986]

--3. sử dụng CTE tính tổng doanh số bán hàng theo năm từ bảng orders' và 'order details'
with orderdetailName as (
select od.OrderID,sum(od.Quantity*od.UnitPrice) as [Tổng doanh số cho từng sản phẩm]
from [Order Details] as od
group by od.OrderID
)
select year(o.OrderDate) as [Tổng số năm],
sum(oon.[Tổng doanh số cho từng sản phẩm]) as [Tổng doanh số theo từng năm]
from Orders as o
join orderdetailName as oon
on oon.OrderID = o.OrderID
group by year(o.OrderDate)
order by year(o.OrderDate) asc;

[@tuongpham986]

--Thử thách: Sử dụng CTE .Lấy ra tên khách hàng và tên của nhà cung cấp
--tại city = 'London'

[@vuminhdung8501]

Anh ơi em muốn liên hệ với anh để trao đổi về mua khóa học thì em có thể liên lạc qua đâu ạ

[@TITVvn]

Bạn mua tại: www.titv.vn

[@TínĐinhViết]

BT1 :
with doanhThu as
(
select od.ProductId,sum(od.Quantity*od.SalePrice) as Totals
from OrderDetails od
group by od.ProductId
)
select p.ProductId,p.ProductName,dt.Totals
from Products p join doanhThu dt on dt.ProductId = p.ProductId

[@aoe-ct4480]

#Bài 3 : đang vướng group by năm, pro nào hướng dẫn giúp
WITH DoanhThu AS
(
SELECT od.OrderID,
SUM(od.Quantity*od.UnitPrice) AS 'Tổng DT'
FROM [dbo].[Order Details] od
GROUP BY od.OrderID
)
SELECT
YEAR(o.OrderDate) AS 'Năm',
dt.[Tổng DT]
FROM [dbo].[Orders] o
JOIN DoanhThu dt
ON dt.OrderID = o.OrderID
--GROUP BY YEAR(o.OrderDate)

[@dontcry1201]

Bạn phải SUM(dt.[Tổng DT]) nhé

[@huy12_a46]

sao phải SUM(dt.[Tổng DT ]) v bạn
@@dontcry1201

[@leanhtrung4419]

@@huy12_a46 vì lệnh With ở trên là gộp lại ID xem với tổng doanh số thôi, còn lệnh select dưới là mới chỉ là ID đó có năm bán hàng là bao nhiêu, 100 ID thì có 100 dòng năm bán hàng, nên phải SUM doanh thu của năm mà ID đó bán hàng nữa

[@thehung.12]

// Bài 1
with OrdersTotal as (
select od.ProductID, sum(od.Quantity * od.UnitPrice) as "TotalPrice"
from [Order Details] od
group by od.ProductID
)
select p.ProductID, p.ProductName,
ot.TotalPrice
from Products p
join OrdersTotal ot on p.ProductID = ot.ProductID;

[@thangtranxuan9771]

1

[@truongvinh204]

.

[@CoffeeTran18]

Em vẫn đang tìm cách tối ưu hơn những đoạn SQL Command dưới đây chỉ mới là giải được bài tập ^^
Bài 1
WITH OrderDetailCTE AS (
SELECT
od.ProductID,
SUM(od.UnitPrice * od.Quantity * ( 1 - od.Discount )) AS TotalProductSales
FROM
[Order Details] AS od
GROUP BY
od.ProductID
)
SELECT P.ProductID, P.ProductName, ODCTE.TotalProductSales
FROM Products AS P
JOIN OrderDetailCTE AS ODCTE
ON P.ProductID = ODCTE.ProductID
ORDER BY P.ProductID
BÀI 2
WITH OrderDetailCTE AS (
SELECT od.OrderID,
SUM(od.Quantity * od.UnitPrice * ( 1 - od.Discount )) AS TotalValueEachOrder
FROM [Order Details] AS od
GROUP BY od.OrderID
)
SELECT c.CustomerID, SUM(odCTE.TotalValueEachOrder) AS TotalOrderValueEachCustomer
FROM Customers AS c
JOIN Orders AS o
ON c.CustomerID = o.CustomerID
JOIN OrderDetailCTE AS odCTE
ON o.OrderID = odCTE.OrderID
GROUP BY c.CustomerID
ORDER BY TotalOrderValueEachCustomer DESC
BÀI 3
WITH OrderDetailCTE AS (
SELECT od.OrderID,
SUM(od.Quantity * od.UnitPrice * ( 1 - od.Discount )) AS TotalValueEachOrder
FROM [Order Details] AS od
GROUP BY od.OrderID
)
SELECT YEAR(o.OrderDate) AS [YearOfOrder], SUM(odCTE.TotalValueEachOrder) AS TotalOrderValuePerYear
FROM Orders AS o
JOIN OrderDetailCTE AS odCTE
ON o.OrderID = odCTE.OrderID
GROUP BY YEAR(o.OrderDate)
ORDER BY YEAR(o.OrderDate)

[@CoffeeTran18]

Sau khi làm xong bài 2 mình có thử viết lại bằng cách sử dụng 2 CTE thay vì 1 CTE như sau :
WITH OrderDetailCTE AS (
SELECT od.OrderID,
SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalValueEachOrder
FROM [Order Details] AS od
GROUP BY od.OrderID
),
CustomerOrderTotalCTE AS (
SELECT c.CustomerID,
SUM(odCTE.TotalValueEachOrder) AS TotalOrderValueEachCustomer
FROM Customers AS c
JOIN Orders AS o
ON c.CustomerID = o.CustomerID
JOIN OrderDetailCTE AS odCTE
ON o.OrderID = odCTE.OrderID
GROUP BY c.CustomerID
)
SELECT CustomerID,
TotalOrderValueEachCustomer
FROM CustomerOrderTotalCTE
ORDER BY TotalOrderValueEachCustomer DESC;
Lúc này trong đầu mình chợt nảy ra 1 câu hỏi như sau :
"LIỆU RẰNG SỬ DỤNG NHIỀU CTE CÓ THẬT SỰ TỐT VỀ HIỆU NĂNG VÀ MANG LẠI HIỆU QUẢ CAO ?"
Và đây là những gì mình tìm hiểu được :
Mặc dù việc sử dụng CTE có thể mang lại lợi ích trong một số trường hợp nhất định nhưng điều quan trọng là phải cân nhắc những đánh đổi, bao gồm cả những tác động tiềm ẩn về hiệu suất và các khía cạnh cần xem xét như sau :
1. Optimization Opportunities
2. Reduced Data Processing
3. Query Complexity
4. Resource Utilization
Như vậy có thể thấy cách đầu tiên của mình có vẻ sẽ mang lại nhiều lợi ích hơn mặc dù chỉ dùng 1 CTE duy nhất
Các bạn có thể thảo luận thêm về khía cạnh này

[@HaTran-gp9mv]

@@CoffeeTran18 sao bạn lại lấy 1 - discount nhỉ

[@manhamsterdam2003]

@@CoffeeTran18 WITH RevenuePerCustomer AS (
SELECT o.CustomerID, SUM(od.UnitPrice * od.Quantity) as TotalPrice
FROM [dbo].[Orders] o
INNER JOIN [dbo].[Order Details] od ON o.OrderID = od.OrderID
GROUP BY o.CustomerID
)
SELECT c.CustomerID, c.ContactName, r.TotalPrice
FROM [dbo].[Customers] c
INNER JOIN RevenuePerCustomer r ON c.CustomerID = r.CustomerID
ORDER BY r.TotalPrice

[@hiệpnguyễn-l9r]

@@HaTran-gp9mv thực ra nếu giảm giá mặt hàng thì nhân thêm với discount còn khi bạn tính 1 - discount, bạn thực sự đang tính phần trăm giá không bị giảm,