SQL 실전! 실제 DB에서 연습해요(4) - Lv5. 가장 많이 팔린 품목은?

1. 각 고객이 구매한 모든 제품의 총 금액을 계산하고, 고객 이름, 총 구매 금액, 주문 수를 출력하는 SQL 쿼리를 작성해주세요.

SELECT c.CustomerName,
	sum(total) TotalAmount,
	count(OrderId) OrderCount
from Customers c
left join 
(
select o.OrderId, o.CustomerID ,p.ProductName, o.Quantity * p.Price total
from Orders o
left join Products p on o.ProductID = p.ProductID
) a
on c.CustomerID = a.CustomerID
group by 1

 

• orders와 products테이블을 조인하여 주문에 따른 금액을 먼저 계산한 후, customers테이블과 조인한 테이블을 한번 더 조인하여 결과를 만듦

 

2. 각 제품 카테고리별로 가장 많이 팔린 제품의 이름과 총 판매량을 조회하는 SQL 쿼리를 작성해주세요.

 

select Category, ProductName Top_Product, TotalSold 
from 
(
select p.Category,
	p.ProductName,
	sum(o.Quantity) TotalSold,
	rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID
group by 1, 2
) a
where a.rn = 1

---

 

rank()함수 사용에 있어서 약간 모호하게 알고 있는 것 같아서 다시 한 번 정리를 해보았다.

 

RANK() 사용 위치 문제

• RANK()는 윈도우 함수로, 테이블 전체 또는 특정 파티션 내에서 순위를 매기는 역할
• 이를 올바르게 사용하려면 집계 함수를 사용하는 쿼리를 서브쿼리로 작성하거나, GROUP BY와 함께 사용해야 함

select p.Category,
		p.ProductName,
		rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID

 

이렇게 사용했다가 에러가 나서 수정을 해보았다.

 

#group by 함수로 그룹화할 범위를 명확히 지정해주는 경우
select p.Category,
		p.ProductName,
		rank() over(partition by p.Category order by sum(o.Quantity) desc) rn
from Orders o
left join Products p on o.ProductID = p.ProductID
group by 1, 2 

#서브쿼리로 각 상품별로 판매량을 먼저 집계 후 group by 없이 rank()를 사용하는 경우
SELECT Category,
       ProductName,
       RANK() OVER (PARTITION BY Category ORDER BY total_quantity DESC) AS rank
FROM (
    SELECT p.Category,
           p.ProductName,
           SUM(o.Quantity) AS total_quantity
    FROM Orders o
    LEFT JOIN Products p ON o.ProductID = p.ProductID
    GROUP BY p.Category, p.ProductName
) sub;

 

반드시 알고가자 !! -> 윈도우 함수는 그룹별로 집계되어있는 결과가 명확해야 한다는 것!!

 

* 241231 수정

인줄 알았으나.. 집계함수를 쓰지 않아도 윈도우 함수는 잘 ~ 돌아감

SELECT CATEGORY,
        PRODUCT_NAME,
        rank() over(partition by CATEGORY order by PRICE desc) as ranking,
        PRICE
from FOOD_PRODUCT

 

아주 멀쩡히 돌아감... 저건 그냥 order by 에 sum이 들어가서 집계함수가 필요해서 넣어야 했던 것 뿐임 없어도 잘 돌아간다!

(어차피 partition by 에서 범위는 이미 지정해주기 때문!)

---