[SQL] solvesql 카테고리 별 매출 비율 | SUM () OVER (PARTITION BY ~)

문제 

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

 

서브 카테고리 별 매출액을 계산하고 그 매출액이 각 서브 카테고리가 속해있는 카테고리 안에서 비중을 얼마나 차지하는지, 그리고 전체 매출액에서는 비중을 얼마나 차지하는지 계산하는 쿼리를 작성해주세요. 쿼리 결과는 아래 컬럼을 포함해야 하고, 모든 수치 데이터는 소수점 아래 셋째 자리에서 반올림 해 둘째 자리까지 표현해주세요.

• category - 카테고리 이름
• sub_category - 서브 카테고리 이름
• sales_sub_category - 서브 카테고리 별 매출액의 합계
• sales_category - 카테고리 별 매출액의 합계
• sales_total - 전체 매출액
• pct_in_category - 카테고리 매출 중 해당 서브 카테고리 매출의 비율 (%)
• pct_in_total - 전체 매출 중 해당 서브 카테고리 매출의 비율 (%)

 

풀이 (오답노트)

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

 

오답 쿼리에서는 서브쿼리절 안에서 GROUP BY를 카테고리 > 서브 카테고리로 나눠놓고도

SUM(sales)를 그냥 sales의 총합이라고 오해해버려서 메인 쿼리절에서도 계산이 틀렸습니다. 

 

정답 쿼리

SELECT category
      ,sub_category
      ,ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category), 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(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER(PARTITION BY category) * 100, 2) AS pct_in_category
      ,ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER() * 100 ,2) AS pct_in_total
  FROM (
       SELECT category
              ,sub_category
              ,SUM(sales) as sum_sales 
         FROM records
        GROUP BY category, sub_category
  );

 

WITH sub AS 
(
    SELECT category
          ,sub_category
          ,SUM(sales) AS sum_sales
    FROM records
    GROUP BY category, sub_category
)

SELECT category
      ,sub_category
      ,ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category), 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(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER(PARTITION BY category) * 100, 2) AS pct_in_category
      ,ROUND(SUM(sum_sales) OVER(PARTITION BY sub_category) / SUM(sum_sales) OVER() * 100 ,2) AS pct_in_total
  FROM sub;

 

WITH 절을 사용해서도 같은 쿼리를 짰습니다.

먼저 sub 라는 임시 결과 집합을 생성합니다. 이 집합은 records 테이블에서 category와 sub_category를 기준으로 그룹화하고, 각 그룹의 sales 합계를 계산합니다. 여기서 sum_sales는 sub_category별 sales의 합계가 되게 됩니다. 

 

같은 맥락으로, sum_sales는 서브 카테고리별 sales의 합계이므로, 총합계를 구하기 위해서는 

SUM(sum_sales) OVER () 로 SUM ~ OVER 구문을 쓰되 파티션을 넣지 않으면 됩니다.

 

만약 OVER() 없이 SUM(sum_sales)만 넣게 될 경우, 

단일 행이 출력되므로 오답이 됩니다.