[SQL] solvesql 카테고리 별 매출 비율 | sum() over ()

문제

https://solvesql.com/problems/revenue-pct-per-category/

https://solvesql.com/problems/revenue-pct-per-category/

 

코드

gpt를 조져서 얻어낸 답안

with all_sale as ( -- 서브 카테고리 별로 매출액을 구한다
  select 
    category, 
    sub_category, 
    sum(sales) as sales_sub_category
  from records
  group by category, sub_category
),
sales_with_totals as ( -- 서브 카테고리, 카테고리, 총 매출액을 구한다.
  select 
    category,
    sub_category,
    round(sales_sub_category, 2) as sales_sub_category,
    round(sum(sales_sub_category) over (partition by category), 2) as sales_category,
    round(sum(sales_sub_category) over (), 2) as sales_total
  from all_sale
)
select -- 서브 카테고리 / 카테고리, 서브 카테고리 / 총 매출의 비율을 구한다
  category,
  sub_category,
  sales_sub_category,
  sales_category,
  sales_total,
  round(sales_sub_category * 100 / sales_category, 2) as pct_in_category,
  round(sales_sub_category * 100 / sales_total, 2) as pct_in_total
from sales_with_totals

 

이를 최적화해보겠다

with all_sale as ( -- 서브 카테고리 별 매출액을 구한다
  select 
    category, 
    sub_category, 
    sum(sales) as sales_sub_category
  from records
  group by 1, 2
)
 -- 서브 카테고리, 카테고리, 총 매출액을 구한다 
 -- 그 이후에 서브 카테고리 / 카테고리, 서브 카테고리 / 총 매출액 비율을 구한다
 select
  category,
  sub_category,
  round(sales_sub_category, 2) as sales_sub_category,
  round(sum(sales_sub_category) over (partition by category), 2) as sales_category,
  round(sum(sales_sub_category) over (), 2) as sales_total,
  round(sales_sub_category * 100 / (sum(sales_sub_category) over (partition by category)), 2) as pct_in_category,
  round(sales_sub_category * 100 / (sum(sales_sub_category) over ()), 2) as pct_in_total
from all_sale

 

짠 ~ !

 

SELECT
  category,
  sub_category,
  ROUND(SUM(sales), 2) AS sales_sub_category,
  ROUND(SUM(SUM(sales)) OVER (PARTITION BY category), 2) AS sales_category,
  ROUND(SUM(SUM(sales)) OVER (), 2) AS sales_total,
  ROUND(SUM(sales) * 100 / SUM(SUM(sales)) OVER (PARTITION BY category), 2) AS pct_in_category,
  ROUND(SUM(sales) * 100 / SUM(SUM(sales)) OVER (), 2) AS pct_in_total
FROM records
GROUP BY 1, 2
ORDER BY 1, 2

^ 최종버전이다

gpt한테 물어봐서 짠 건데 gpt 천잰 듯 ..?

 

 

핵심 요지

1. sum(A) over (partition by B)

 

먼저 그룹화랑 partition 별로 합계 구하는 거는 한 select문 안에 동시에 할 수 없다! 가능하다

category와 sub_category 별로 그룹화를 해서 서브카테고리의 매출 합을 구한 값을 중간 결과로 저장한 후에

다음 select문에서에서 partition으로 서브 카테고리, 카테고리별 합계를 구해야 된다.

 

만약 over () -> 이렇게 비워놓으면 parition 없이 총 매출에 대한 합계가 들어간 컬럼을 만들 수 있다.

그렇다고 over()를 생략하면 전체 합계에 대한 단일행만 결과로 나오기 때문에 조심해야 된다.

 

category와 sub_category로 그룹화한 후에

sales_sub_category를 sum(sales)로 구한다.

그리고 나머지 카테고리 별 매출액과 전체 매출액은 sum() over (partition by)로 구한다.

 

 

2. over 절을 통해 계산된 값을 정의한 컬럼명은 같은 select 문 내에서 다시 참조할 수 없다

 

gpt를 조져서 얻어낸 설명

 

sales_cateogry는 over절을 사용해서 계산한 값인데

만약 sales_sub_category / sales_category 비율을 구하고 싶으면

같은 select문이 아닌 중간 결과를 저장한 후에

다음 select문에서 계산해야 한다.

 

서브쿼리를 사용하기 싫으면 분모에 들어갈 sales_category를 over절 함수로 대체해야 한다.

즉,

sales_sub_category / sales_category => X

sales_sub_category / (sum(sales_sub_category) over (partition by category)) => O

 

느낀 점

어렵다 ㅜ

이거를 10분 안에 어떻게 풀지 ... ㅎㅜ