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
* 잘못된 부분에 대해 댓글 남겨주시면 감사하겠습니다! 😀
'Computer Science > Database' 카테고리의 다른 글
[SQL/MySQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (0) | 2023.04.30 |
---|---|
[SQL/MySQL] 시간대별 통계 내기 (0) | 2023.04.23 |
[MySQL] GROUP BY 주의사항 (0) | 2023.03.29 |
[MySQL] MAX 함수 (0) | 2023.03.26 |
[MySQL] 가장 최근 날짜 조회하기 (0) | 2023.03.26 |