문제: https://datalemur.com/questions/sql-highest-grossing
내가 짠 쿼리
WITH sub AS (
SELECT category
, product
, total_spend
, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_spend DESC) AS rnum
FROM(
SELECT category
, product
, SUM(spend) AS total_spend
FROM product_spend
WHERE EXTRACT (YEAR FROM transaction_date) = 2022
GROUP BY 1, 2
ORDER BY 1, 3) tb)
SELECT category
, product
, total_spend
FROM sub
WHERE rnum IN (1, 2);
정답은 맞췄지만 쿼리가 마음에 안 든다
서브쿼리 없이 더 간단한 방법이 있을 것 같은데 모르겠다
솔루션 확인
WITH sub AS (
SELECT category
, product
, SUM(spend) AS total_spend
, ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY SUM(spend) DESC) AS rnum
FROM product_spend
WHERE EXTRACT (YEAR FROM transaction_date) = 2022
GROUP BY 1, 2)
SELECT category
, product
, total_spend
FROM sub
WHERE rnum < 3
ORDER BY category, rnum
;
1.
select절에서 로넘버를 생성하기 위해 파티션을 치면 그룹바이를 못 쓸 거라고 막연히 생각을 했다.
그래서 프롬절에 서브쿼리를 써서 그룹바이랑 로넘버랑 분리 시킴
근데 그게 아니었다!
굳이 서브쿼리로 안 나누고 그룹바이 써서 SUM() 쓰고 윈도우 함수로 로넘버도 만들 수 있다.
2.
내가 놓친 부분:
로넘버의 ORDER BY SUM(spend) 이 부분의
SUM(spend)은 그룹바이를 기준으로 집계된다
나는 앞에 PARTITION BY category 를 보고 카테고리를 기준으로 SUM(spend)가 일어날 것이라고 생각을 했던건데
그게 아니었음
파티션 바이 카테고리는 로 넘버를 매기는 그룹을 나눌 뿐 뒤에 SUM()에 영향을 미치지는 않았다
그래서 오더 바이에 SUM()을 쓰려면 별개로 그룹바이가 꼭 필요하다
3.
로넘버로 정답을 맞추긴 했지만 랭크가 더 정확한 답이다
이 문제에서 문법은 완전 동일
RANK() OVER (
PARTITION BY category
ORDER BY SUM(spend) DESC) AS ranking
로넘버와 랭크의 결과 차이는 같은 값의 total_spend가 존재할 때 발생한다
로넘버는 로넘버로 다른 값을 가지고 랭크는 같은 값, 같은 순위를 가진다.
(( total_spend 상위 2개의 값을 가지는 product가 3개 이상일 수 있기 때문에 ))
공통 순위를 나타내는 랭크 함수를 써서 WHERE ranking < 3 을 해줘야 해당하는 모든 product를 나타낼 수 있다
나는 이 상황을 고려하지 않았던 것
로넘버를 쓰고 똑같이 WHERE rnum < 3 을 하면 중복값이 짤려버린다
예시 문제에 중복값이 없어서 정답처리는 된 것 같지만 랭크가 더 적절해보임
3.
웨어나 해빙절에서는 윈도우 함수를 쓸 수 없다
4.
마이에스큐엘과 다른 포스트그래의 연도 추출법! 자주 쓰일 것 같으니 기억해두면 좋을 것 같다
WHERE EXTRACT(YEAR FROM transaction_date) = 2022
마이에스큐엘이 훨씬 쉬운데 다같이 마이에스큐엘만 쓰면 안 될까요 호호