Khóa Học SQL Server - Bài 33. Windows Functions và các ứng dụng

Link khóa học SQL Server: titv.vn/course...
Link tải code: github.com/len...
Clip hướng dẫn chuẩn bị CSDL để thực hành: • Khóa Học SQL Server - ...

[@thekop5590]

cảm ơn thầy nhiều 😍😍

[@TriNguyenLeMinh-q6o]

Bài giảng của anh rất hay và chi tiết, khi xem video bài giảng của anh để học em thấy mình có thể hiểu cặn kẽ từ những cái cơ bản, và hiểu được điểm khác nhau từ những hàm có chức năng giống nhau. Cám ơn đã bỏ công sức làm bài giảng ạ! Em xin gửi bài tập ạ -- bt1: tính tổng doanh số bán hàng theo từng năm cho mỗi khách hàng -- và xếp hạng khách hàng dựa trên doanh số bán hàng -- /-> tạo bảng CTE

[@minhhieuhoang7530]

Em cảm ơn bài giảng của thầy ạ

[@quanbui9559]

Em cảm ơn bài giảng của a ạ with TotalPrice as ( select OrderID, sum(Quantity * UnitPrice) as Total from dbo.[Order Details] group by OrderID ) select year(o.OrderDate) as Year, o.CustomerID, sum(tp.Total) as TotalIncome, rank() over(order by sum(tp.Total) desc) as Ranking from dbo.Orders as o join TotalPrice as tp on o.OrderID = tp.OrderID group by year(o.OrderDate), o.CustomerID;

[@QuangHieuLe-gj3xi]

thầy cho hỏi sai ở đâu với ạ: --(xếp hạng nhân viên theo doanh thu ) with TotalValue as (select distinct o.EmployeeID as [EmployeeID], count (od.OrderID) as [TotalOrder], sum(od.ProductID*od.Quantity) as [TotalValue] from [dbo].[Order Details] od join [dbo].[Orders] as o on o.OrderID = od.OrderID group by o.EmployeeID order by o.EmployeeID asc) select o.EmployeeID, tv.TotalOrder, tv.TotalValue, rank () over (partition by tv.TotalValue order by tv.TotalOrder asc) as from [dbo].[Orders] as o join TotalValue as tv on tv.EmployeeID = o.EmployeeID group by o.EmployeeID, tv.TotalOrder, tv.TotalValue

[@ORANGEE-orange]

--///// xếp hạng nhân viên theo doanh thu with totalvalue_cte as (select o.EmployeeID as [EmployeeID], sum(od.ProductID*od.Quantity) as [TotalValue] from [dbo].[Order Details] od join [dbo].[Orders] as o on o.OrderID = od.OrderID group by o.EmployeeID ) select tv.*,e.FirstName+' '+ e.LastName as [ten nhan vien], rank () over ( order by tv.[totalValue] desc) as [xep hang] from totalvalue_cte as tv join dbo.Employees e on e.EmployeeID=tv.EmployeeID; --///////// mình xin góp ý 1 chút là : ở trong phần CTE bạn dùng group by rồi thì không cần phải Select Distinct nữa nhé và cũng chẳng cần phải order by vì yêu cầu của bạn là sắp xếp theo doanh số , bạn chỉ nên lấy ra những cột cần thiết để giảm thời gian truy vấn. Chai yo

[@LuongTran-ky4lb]

Cảm ơn những video bổ ích của a

[@LocNguyen-gt7tq]

Cảm ơn video của thấy: BT: WITH dsbh AS ( SELECT od.OrderID, SUM(od.Quantity*od.UnitPrice) AS [Doanh so ban hang] FROM [dbo].[Order Details] AS od GROUP BY [OrderID] ) SELECT DISTINCT [CustomerID], [Year], [Doanh so ban hang moi nam], DENSE_RANK() OVER (ORDER BY [Doanh so ban hang moi nam] DESC) AS [Ranking] FROM (SELECT [CustomerID], YEAR([OrderDate]) AS [Year], dsbh.[Doanh so ban hang], SUM(dsbh.[Doanh so ban hang]) OVER (PARTITION BY [CustomerID], YEAR([OrderDate])) AS [Doanh so ban hang moi nam] FROM [dbo].[Orders] AS o INNER JOIN dsbh ON dsbh.OrderID=o.OrderID) AS temp ORDER BY [Doanh so ban hang moi nam] DESC

[@tytran3972]

Cảm ơn a vì những chia sẻ hữu ít này ạ

[@minhhieuhoang7530]

Em cảm ơn bài giảng của thầy ạ> WITH CUS_REV AS( SELECT C.ContactName, SUM(OD.Quantity*OD.UnitPrice) AS "TotalRevenue" FROM [Order Details] AS OD INNER JOIN Orders AS O ON O.OrderID=OD.OrderID INNER JOIN Customers AS C ON C.CustomerID=O.CustomerID GROUP BY C.ContactName ) SELECT CR.ContactName,CR.TotalRevenue, DENSE_RANK() OVER(ORDER BY CR.TotalRevenue DESC) AS Rank_Rev FROM CUS_REV AS CR;

[@kantetam_6076]

anh em cho tui xin y kien : with TotalSaleEmployees as ( select e.EmployeeID , Year(OrderDate) as YearSale, sum(od.unitPrice * quantity) as TotalSale from Employees as e join orders as o on o.EmployeeID = e.EmployeeID join [order details] as od on od.OrderID = o.OrderID group by e.EmployeeID , Year(OrderDate) ) select T.EmployeeID, e.FirstName , e.LastName , T.YearSale, T.TotalSale , Rank() over (partition by T.yearsale Order by T.TotalSale DESC) as Ranking from TotalSaleEmployees as T join Employees as e on e.EmployeeID = T.EmployeeID

[@hangphan7335]

Em cảm ơn Thầy đã có những bài giảng thật sự chất lượng ạ. Em gửi bài tập 1: Doanh số theo từng khách hàng qua mỗi năm, xếp hàng KH theo doanh số của mỗi năm: WITH DTtheoCus AS ( SELECT C.CustomerID , YEAR(O.[OrderDate]) AS Yeardate, SUM( OD.[UnitPrice]* OD.[Quantity]) AS TotalDT FROM [dbo].[Order Details] OD JOIN [dbo].[Orders] O ON OD.[OrderID] =O.OrderID JOIN [dbo].[Customers] C ON C.CustomerID = O.CustomerID GROUP BY C.CustomerID, YEAR(O.[OrderDate]) ) SELECT *, RANK() OVER ( PARTITION BY DT.Yeardate ORDER BY DT.TotalDT DESC) AS 'Ranking' FROM [DTtheoCus] DT;

[@TomTechVN]

Em góp bài ạ ! Cảm ơn thầy WITH CTE_CusSpending AS ( SELECT o.CustomerID,YEAR(O.OrderDate) AS YearOrder, SUM(od.Quantity * od.UnitPrice)as TOTALAMOUNT FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID GROUP BY o.CustomerID, YEAR(o.OrderDate) ) SELECT CS.CustomerID , CS.YearOrder , RANK() OVER (PARTITION BY CS.YearOrder ORDER BY CS.TOTALAMOUNT DESC) AS 'Rank' FROM CTE_CusSpending CS

[@Dev_and_symphony]

with TotalBill as ( select SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) as TotalBill, od.OrderID from [Order Details] as od group by od.OrderID ) select YEAR(o.OrderDate) as 'Year', o.CustomerID, SUM(t.TotalBill) as TotalPayment, RANK() over (partition by YEAR(o.OrderDate) order by SUM(t.TotalBill) desc) as Ranking from Orders as o join TotalBill as t on t.OrderID=o.OrderID group by YEAR(o.OrderDate) ,o.CustomerID;

[@HaTran-gp9mv]

Thầy giảng hay quá, thầy dạy trường nào đấy ạ ?

[@minhchiu9334]

Cảm ơn bài giảng của thầy WITH OrderPayments AS ( SELECT OrderID , SUM(Quantity * UnitPrice) as TotalPayment FROM [Order Details] GROUP BY OrderID ) SELECT o.CustomerID , YEAR(o.OrderDate) as OrderedYear , SUM(op.TotalPayment) as TotalPayment , RANK() OVER ( PARTITION BY YEAR(o.OrderDate) ORDER BY SUM(TotalPayment) DESC ) AS Ranking FROM Orders o JOIN OrderPayments op ON op.OrderID = o.OrderID GROUP BY o.CustomerID , YEAR(o.OrderDate)

[@haquan8654]

Em xin gửi phần bài tập ạ with TotalPrice as ( select OD.OrderID, Sum (OD.UnitPrice*OD.Quantity) as [Total] from dbo.[Order Details] as OD Group by OD.OrderID ) select O.OrderID, O.CustomerID, C.ContactName, year(o.OrderDate) as [Year], TP.Total, rank() over( partition by year(o.OrderDate) order by Tp.Total desc) as [Ranking] from dbo.Orders as O join TotalPrice as TP on TP.OrderID=O.OrderID join Dbo.Customers as C on C.CustomerID=O.CustomerID

[@manhthang3211]

Cảm ơn a vì bài giảng, Mn góp ý e bài này với SELECT o.CustomerID, Year(o.OrderDate ) as nam, o.OrderID, od.UnitPrice, od.Quantity, sum(od.UnitPrice * od.Quantity) as doanh_so_ban_hang, dense_rank() over (partition by year(o.OrderDate) order by sum(od.UnitPrice * od.Quantity) desc) as ranking FROM [dbo].[Order Details] od JOIN [dbo].[Orders] o ON od.OrderID = o.OrderID GROUP BY o.CustomerID, Year(o.OrderDate) , o.OrderID, od.UnitPrice, od.Quantity

[@hieuvincent791]

Cám ơn video của anh :) -- BT1: Tính tổng doanh số bán hàng hàng năm cho mỗi khách hàng và xếp hạng khách hàng dựa trên doanh số bán hàng. WITH [Tổng doanh số bán hàng] AS (SELECT OrderID, SUM(UnitPrice * Quantity) AS [Doanh số bán hàng] FROM dbo.[Order Details] GROUP BY OrderID ) SELECT o.CustomerID, YEAR(o.OrderDate) AS [Năm], TDSBH.[Doanh số bán hàng], RANK() OVER (PARTITION BY YEAR(o.Orderdate) ORDER BY TDSBH.[Doanh số bán hàng] DESC) AS [Ranking] FROM [Tổng doanh số bán hàng] AS [TDSBH] JOIN dbo.Orders AS o ON o.OrderID = TDSBH.OrderID;

[@anghuy3509]

code này bạn chưa lọc theo CustomerID nên số Customer ID trùng nau trong 1 năm rất nhiều , số dòng lên đến 830 dòng lận, bạn kiểm tra lại nhé

[@tv-nguyen3554]

HELLO THAY

[@LTBC531]

Kết quả mình ra: 234rows With TotalSales AS ( SELECT od.OrderID, sum([UnitPrice]*[Quantity]) as [Total] FROM [dbo].[Order Details] as od Group by od.OrderID ) SELECT YEAR([OrderDate]) as [YEAR], o.CustomerID, sum(ts.[Total]) as Total, Rank () Over (Partition by YEAR([OrderDate]) Order by sum(ts.[Total]) desc ) FROM [dbo].[Orders] as o Join TotalSales as ts ON ts.OrderID = o.OrderID GROUP BY YEAR([OrderDate]), o.CustomerID;

[@truongnamanh]

Em cảm ơn thầy về bài giảng, em xin phép gủi phần bài giải. -- Bài Tập: -- Tính tổng doanh số bán hàng mỗi năm cho mỗi KH và xếp hạng KH dự trên doanh số bán hàng. WITH Total AS ( SELECT od.OrderID, SUM(od.UnitPrice*od.Quantity) AS [Total_Sale] FROM [Order Details] [od] GROUP BY od.OrderID ) SELECT t.[Total_Sale], o.CustomerID, o.OrderID, YEAR(o.OrderDate) [Year], ROW_NUMBER() OVER (PARTITION BY YEAR(o.OrderDate) ORDER BY t.[Total_Sale] DESC) AS Ranking FROM Orders [o] JOIN Total [t] ON t.OrderID=o.OrderID

[@Tan-tc7tl]

Em gửi bt, ket qua 251 rows WITH OrderTotal AS ( SELECT od.OrderID, SUM(od.[UnitPrice] * od.[Quantity]) AS [TotalPayment] FROM [Order Details] od GROUP BY od.OrderID ) SELECT DISTINCT c.CustomerID, YEAR(o.[ShippedDate]) AS [Year], RANK () OVER ( PARTITION BY YEAR(o.[ShippedDate]) ORDER BY c.CustomerID DESC) AS [TotalPayment] FROM [dbo].[Customers] c JOIN Orders o ON o.CustomerID = c.CustomerID JOIN OrderTotal od ON o.OrderID = od.OrderID

[@tuongpham986]

Bạn đang bị sai câu trả lời rồi. doanh số bán hàng theo năm mà sao bạn lại lấy ShippedDate được với lại trong cái funtion sao bạn lại order by customerID thay vì phải order by [TotalPayment] ở CTE bạn khởi tạo ở trên

[@tuongpham986]

Bạn khởi tạo CTE nhầm mục đích là tái sử dụng lại, bạn tạo nhưng dưới cái funtion bạn đang chưa vận dụng cái CTE

[@NguyenTuan-dt8hq]

with doanh_so as (select orderid, sum(unitprice*quantity) as total from [order details] group by orderid) select customerid, year(orderdate) as year, sum(total) as total_per_year, rank() over (partition by year(orderdate) order by sum(total) desc) as rank_follow_year from orders as o inner join doanh_so as ds on o.orderid = ds.orderid group by customerid,year(orderdate)

[@rabbittanksuperbestmatch4259]

Bài tập WITH OrderPrice AS ( SELECT od.OrderID ,SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS [Total Price] FROM [Order Details] AS od GROUP BY od.OrderID ) SELECT c.CustomerID, c.ContactName, SUM(op.[Total Price]) AS [Total Price], YEAR(o.OrderDate) AS [Oreder Year], RANK() OVER(PARTITION BY YEAR(o.OrderDate) ORDER BY (SUM(op.[Total Price]))) AS [Rank] FROM Orders AS o INNER JOIN OrderPrice AS op ON o.OrderID = op.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.ContactName, YEAR(o.OrderDate);

[@Casesolving]

select o.[CustomerID], sum( od.[UnitPrice]* od.[Quantity]) as 'DoanhSo/MoiKH', rank() over ( order by sum( od.[UnitPrice]* od.[Quantity]) desc) as 'ranking' from [dbo].[Orders] o join [dbo].[Order Details] od on o.OrderID = od.OrderID group by o.CustomerID

[@HuyLeHong-yi7ds]

with OrderTemp as ( select od.OrderID, sum(od.UnitPrice * od.Quantity) as TotalOrder from [Order Details] as od group by od.OrderID ) select c.ContactName, YEAR(o.OrderDate), sum(odt.TotalOrder) as TotalOrderN, rank() over (order by sum(odt.TotalOrder) desc) as Ranking from Orders as o inner join OrderTemp as odt on o.OrderID = odt.OrderID inner join Customers as c on o.CustomerID = c.CustomerID group by c.ContactName, YEAR(o.OrderDate), o.OrderID

[@tprunuspersicav8839]

-- bai tap WITH ten as ( select c.[CustomerID], c.[CompanyName],year(o.[OrderDate]) as [nam],sum(od.[UnitPrice]*od.[Quantity]) as [tongdonhang] from [dbo].[Orders] as [o] JOIN [dbo].[Customers] as [c] on c.[CustomerID] = o.[CustomerID] join [dbo].[Order Details] as [od] on od.[OrderID] = o.[OrderID] group by c.[CustomerID], c.[CompanyName], year(o.[OrderDate]) ) select rank() over ( partition by nam order by [tongdonhang] desc ) as [top khach hang mua hang nam], [CustomerID], [CompanyName], [tongdonhang], nam from ten

[@thachnguyen8539]

SELECT o.CustomerID, YEAR(o.OrderDate) AS "YEAR", SUM(od.Quantity*od.UnitPrice) AS "TOTAL", ROW_NUMBER() OVER ( ORDER BY SUM(od.Quantity*od.UnitPrice) DESC ) AS RANKING FROM dbo.Orders o FULL JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID GROUP BY o.CustomerID, YEAR(o.OrderDate) MONG ANH CHO THÊM KẾT QUẢ BAO NHIÊU DÒNG ĐỂ E BIẾT ĐÚNG SAI Ạ

[@hoangquynnguyen2245]

With doanhthu as (Select OD.[OrderID],Sum([UnitPrice]*[Quantity]) as total From [dbo].[Order Details] OD Group by OD.[OrderID] ) Select o.[CustomerID], CT.[CompanyName], year([OrderDate]) as totalyear, sum(total) as totaldoanhthu, Rank() OVER (PARTITION BY year([OrderDate]) ORDER BY sum(total) DESC) AS Ranking From [dbo].[Orders] O join doanhthu as DT ON o.OrderID = DT.[OrderID] join [dbo].[Customers] as CT ON CT.[CustomerID] = O.[CustomerID] Group by o.[CustomerID], year([OrderDate]), CT.[CompanyName]

[@thehung04]

with TotalSales as ( select od.OrderID, sum(od.Quantity * od.UnitPrice) as Total from [Order Details] od group by od.OrderID ) select o.CustomerID, o.OrderID, year(OrderDate) as Year, ts.Total, rank() over (partition by year(OrderDate) order by ts.Total desc) as Rank from Orders o join TotalSales ts on o.OrderID = ts.OrderID;

[@HhHh-hj5lj]

WITH Total AS ( SELECT od.OrderID,SUM(od.UnitPrice*od.Quantity) AS [Total_Sale] FROM [Order Details] [od] GROUP BY od.OrderID ) SELECT t.[Total_Sale], o.CustomerID, o.OrderID, YEAR(o.OrderDate) [Year], ROW_NUMBER() OVER (PARTITION BY YEAR(o.OrderDate) ORDER BY t.[Total_Sale] DESC) AS Ranking FROM Orders [o] JOIN Total [t] ON t.OrderID=o.OrderID

[@truonginhtan9a182]

Em gửi đáp án bài tập ạ with [Doanh_so_ban_hang_nam] as ( select c.CustomerID, c.ContactName, YEAR(o.OrderDate) as YEAR, SUM(od.Quantity*od.UnitPrice - od.Quantity*od.UnitPrice*od.Discount) as [Tong_tien] from dbo.Customers c left join dbo.Orders o on c.CustomerID = o.CustomerID left join dbo.[Order Details] od on od.OrderID = o.OrderID group by c.CustomerID, c.ContactName, YEAR(o.OrderDate) ) select *, RANK() over (partition by [YEAR] order by [Tong_tien] desc) as RANKING from Doanh_so_ban_hang_nam where [Tong_tien] is not null;

[@Mon93-tt]

sinh viên trường nào mà điểm ối zồi ôi thế này 🤣🤣 Bài tập : xếp hạng sinh viên theo thứ tự giảm dần select ma_sinh_vien,ho_ten,diem_trung_binh, ma_lop_hoc, rank() over (order by diem_trung_binh desc) as 'Hạng' from sinh_vien Bài tập tính tổng doanh số bán hàng select c.CustomerID,a.OrderID,YEAR(a.OrderDate) as Năm, sum(b.UnitPrice*b.Quantity) as 'Tổng giá trị đơn hàng trong năm', rank() over(order by sum(b.UnitPrice*b.Quantity) desc) as rank from Orders as a join Customers as c on a.CustomerID = c.CustomerID join [Order Details] as b on a.OrderID = b.OrderID group by c.CustomerID,a.OrderID,YEAR(OrderDate)

[@NGuyenDuong-sk8yf]

SELECT sv.ma_lop_hoc, sv.ho_ten, sv.diem_trung_binh, RANK() OVER (ORDER BY sv.diem_trung_binh DESC ) AS Rank FROM sinh_vien sv;

[@minhbaohac5589]

with TotalOrder as( select od.OrderID,(od.Quantity * od.UnitPrice) as Total from [Order Details] as od ) select c.CustomerID, c.CompanyName, tor.Total, year(o.OrderDate) as [year], rank() over(order by tor.Total desc) as CustomerRanking from Customers as c join Orders as o on c.CustomerID = o.CustomerID join TotalOrder as tor on o.OrderID = tor.OrderID

[@Codedao0305]

with doanh_so as ( select o.CustomerID ,YEAR(o.OrderDate) as yearOrder ,sum(od.Quantity*od.UnitPrice) TotalPrice from Orders o inner join [Order Details] od on o.OrderID = od.OrderID group by o.CustomerID,YEAR(o.OrderDate) ) select * , RANK() over (partition by (ds.yearOrder) order by ds.TotalPrice desc) as rankOrder from doanh_so ds

[@nguyenthanhnhut4304]

Em gửi đáp án ạ: -- Tính tổng doanh số bán hàng hàng năm cho mỗi khách hàng và -- xếp hạng khách hàng dựa trên doanh số bán hàng SELECT c.CustomerID, c.ContactName, YEAR(o.OrderDate) AS YearOrder, SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) AS [TotalAmount], RANK() OVER (PARTITION BY YEAR(o.OrderDate) ORDER BY SUM(od.Quantity*od.UnitPrice*(1-od.Discount)) DESC) AS Ranking FROM Orders o JOIN [Order Details] od ON o.OrderID = od.OrderID JOIN Customers c ON o.CustomerID = c.CustomerID GROUP BY c.CustomerID, c.ContactName, YEAR(o.OrderDate)

[@NGuyenDuong-sk8yf]

WITH OrderTotal AS ( SELECT od.OrderID, SUM(od.Quantity * od.UnitPrice) Total FROM [Order Details] od GROUP BY od.OrderID ) SELECT o.CustomerID, o.EmployeeID, c.ContactName, YEAR(o.OrderDate) [Year], ot.Total, DENSE_RANK() OVER (PARTITION BY YEAR(o.OrderDate) ORDER BY ot.Total DESC) Rank FROM Orders o JOIN OrderTotal ot ON ot.OrderID = o.OrderID JOIN Customers c ON c.CustomerID = o.CustomerID ;