[SQL/MySQL] Window 함수로 큰 값/작은 값 조회하기

Window 함수는 쿼리 행 집합에 대해 집계와 유사한 작업을 수행한다.

그러나 집계 함수는 쿼리 행을 단일 결과 행으로 그룹화하는 반면, window 함수는 쿼리 행에 대한 결과를 생성한다. 

 

집계함수 

• 모든 행에 대한 단일 글로벌 합계와 국가별 그룹화된 합계를 생성한다. 

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+


mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

 

window 함수

• 쿼리 행 그룹을 단일 출력 행으로 축소하지 않고 각 행에 대한 결과를 생성한다.
• 쿼리의 각 window 연산은 over쿼리 행을 window 함수에서 처리하기 위해 그룹으로 분할하는 방법을 지정하는 절을 포함하여 나타낸다. 
  - 첫번째 over 절은 비어 있으며 전체 쿼리 행 집합을 단일 파티션으로 처리한다. 따라서 window 함수는 전체 합계를 생성하지만 각 행에 대해 수행한다.
  - 두번째 over절은 행을 country별로 분할하여 파티션당(country별) 합계를 생성한다. 이 함수는 각 파티션 행에 대해 합계를 생성한다. 
• window 함수는 오직 조회된 리스트와 order by 절에서만 허용된다. 쿼리 결과 행은 where, group by, having 처리 후에 from 절로부터 결정나고 window 실행은 order by, limit, select distinct 전에 된다. 
• over 절은 많은 집계 함수를 허용하기 때문에 over()절의 여부에 따라  window 함수 또는 non-window 함수에 쓰일 수 있다. 

mysql> SELECT
         year, country, product, profit,
         SUM(profit) OVER() AS total_profit,
         SUM(profit) OVER(PARTITION BY country) AS country_profit
       FROM sales
       ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

 

관련 문제

🔗 [프로그래머스] 식품분류별 가장 비싼 식품의 정보 조회하기

 

식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회해보자.

 

풀이법1. 

SELECT CATEGORY, MAX(PRICE) MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE) MAX_PRICE
                FROM FOOD_PRODUCT
                GROUP BY CATEGORY)
AND CATEGORY IN ('과자', '국', '김치', '식용유')
GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC;

 

풀이법2. RANK 사용하기

SELECT CATEGORY, PRICE MAX_PRICE, PRODUCT_NAME
FROM (
    SELECT *, RANK() OVER(PARTITION BY CATEGORY ORDER BY PRICE DESC) PR
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) PRODUCT 
WHERE PRODUCT.PR = 1
ORDER BY MAX_PRICE DESC;

• CATEGORY를 기준으로 파티션을 나누고 RANK() 함수를 이용하여 순위를 매긴다. RANK가 1인 경우만 조회한다.

 

회고

- 가장 큰 값과 집계 함수나 group by 외의 다른 컬럼을 조회하기 위해 매번 group by와 서브쿼리만 사용했었는데 window 함수로도 조회가 가능할 수 있다는 것을 알게 되었다.

- 그런데 의문이 하나 생겼는데 같은 카테고리인데 제품은 다르고 가격이 똑같을 때는 어떤 식으로 출력될지 궁금하다. 

---

Ref.

1. MySQL 공식문서 / https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

2. 프로그래머스(식품분류별 가장 비싼 식품의 정보 조회하기) / https://school.programmers.co.kr/learn/courses/30/lessons/131116

3. 읽으면 좋을 것 같은 블로그! / https://velog.io/@gillog/MySQL-Partition

 

 

---

* 잘못된 부분에 대해 댓글 남겨주시면 감사하겠습니다! 😀