들어가며
SQL에서 제공하는 그룹 함수인 max(), min()을 이용하면 테이블에서 가장 큰 값과 작은 값을 쉽게 구할 수 있다. 하지만 각 그룹당 최대값과 최소값은 어떻게 구할 수 있을까? 예를 들면, 각 플레이어의 최고 점수는 얼마인가? 각 장르별 가장 인기있는 영화는? 이번 포스트에서는 각 항목에서 상위 1개 행을 구하는 방법에서 시작하여 N개 행을 선택하는 방법 까지 다뤄 보도록 하겠다.
본격적인 내용으로 들어가기 전에 이번 포스트에서 사용할 예제 테이블은 아래와 같다.
CREATE TABLE `fruits` (
`type` VARCHAR(50) NOT NULL COMMENT '과일 종류/그룹',
`variety` VARCHAR(50) NOT NULL COMMENT '과일 품종',
`price` FLOAT NOT NULL DEFAULT 0 COMMENT '가격',
INDEX `fruits_idx` (`type`, `price`)
)
COLLATE='utf8_bin'
;
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('apple', 'gala', '2.79');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('apple', 'fuji', '0.24');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('apple', 'limbertwig', '2.87');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('orange', 'valencia', '3.59');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('orange', 'navel', '9.36');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('pear', 'bradford', '6.05');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('pear', 'bartlett', '2.14');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('cherry', 'bing', '2.55');
INSERT INTO `fruits` (`type`, `variety`, `price`) VALUES ('cherry', 'chelan', '6.33');
SELECT `type`, `variety`, `price` FROM `fruits`;
+--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+------------+-------+각 그룹에서 하나의 최대 행 선택
앞에서 살펴본 예제 테이블에는 apple, orange, pear, cherry과 같은 type 컬럼이 있고 이 컬럼을 기준으로 그룹화 하도록 하겠다. 앞으로 포스트에서 그룹이라는 것은 type 컬럼을 기준으로 한다고 생각하자. 이번 섹션에서 살펴 볼 것은 아래와 같이 각 과일의 그룹 중에서 가장 저렴한 과일을 선택 해보는 것이다.
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+이 문제에 대한 몇가지 해결 방법이 있으며 그 방법들은 공통적으로 크게 두 단계로 구성된다. 첫번째 단계는 찾고자하는 price의 값을 찾은 다음, 다음 단계는 이를 기반으로 행 중에서 타입과 가격이 같은 행을 선택한다.
첫번째 방법은 '셀프-조인'이다. 1단계는 과일을 종류(type)별로 그룹화하고 최소 가격을 선택한다.
select type, min(price) as minprice
from fruits
group by type;
+--------+----------+
| type | minprice |
+--------+----------+
| apple | 0.24 |
| cherry | 2.55 |
| orange | 3.59 |
| pear | 2.14 |
+--------+----------+2단계는 이러한 결과를 동일한 테이블에 다시 조인하여 최저 가격과 같은 행을 찾는 것이다. 첫번째 쿼리는 그룹화 되어 있으므로 그룹화 되지 않은 테이블에 대해 조인 할 수 있도록 서브 쿼리에 넣어야 한다.
select f.type, f.variety, f.price
from (
select type, min(price) as minprice
from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| cherry | bing | 2.55 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
+--------+----------+-------+다른 방법으로는 '서브 쿼리(subquery)'를 사용하는 방법이 있다. 이 방식은 시스템의 쿼리 최적화 방식에 따라 다소 비효율적일 수도 있다. 하지만 시각적으로 좀더 직관적이다.
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+앞에서도 말했지만 서브 쿼리는 동일한 결과를 얻기는 하지만 매 행마다 해당 타입의 최소 값을 찾는 쿼리 서브 쿼리를 수행하기 때문에 최적화적인 측면에서는 비효율적이다.
각 그룹에서 N개의 최대 행 구하기
이제는 좀 더 복잡한 문제로 들어가 보도록 하겠다. SQL의 max(), min()과 같은 그룹함수를 사용하면 각 그룹에서 단일 행을 쉽게 찾을 수 있다. 하지만 그룹 함수는 단일 값만을 반환하기 때문에 각 그룹에서 상위(또는 하위) N개를 찾는 것은 위와 같은 방법으로는 불가능하다.
지금 부터 우리는 각 과일 그룹에서 가장 저렵한 과일 두 개를 선택한다고 가정하고 이 결과를 얻을 수 있는 여러가지 방법에 대해 살펴 보도록 할 것이다. 다음은 첫번째 시도다.
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
or price = (select min(price) from fruits as f where f.type = fruits.type
and price > (select min(price) from fruits as f2 where f2.type = fruits.type));
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+----------+-------+서브 쿼리를 이용해 그룹별 가장 작은 값과, 다시 한번 더 서브 쿼리를 사용해 다음으로 작은 값을 찾아 낸후 해당 값에 속하는 과일을 찾아내고 있다. 이게 무슨 쿼리인가 싶다...
심지어 상위 3개, 4개가 될수록 쿼리의 복잡도는 한도 끝도 없이 복잡해진다. 이런 식으로 그룹당 상위 N개의 행을 선택하다가는 답도 없다.
그럼 좀 더 나은 방법으로 두번째 시도를 해보도록 하자.
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price <= fruits.price
) <= 2;위 쿼리는 서브쿼리를 이용하여 price를 비교한 후 해당 그룹에서 싼 가격을 가진 행이 2개 이하일 경우 선택하게 된다. 이 쿼리는 가독성도 좋고, 쿼리를 다시 작성하지 않고도 N을 변경할 수 있게 해주지만(이게 최대 장점임) 기능적으로는 첫번째 시도한쿼리와 동일하다. 둘다 본질적으로 각 그룹의 행에대해 2차 알고리즘을 수행한다(상위 쿼리와 서브 쿼리의 모든 행에 대해 서로 비교 해본다는 말이다). 인덱스가 제대로 정의 되지 않은 경우라면 풀 스캔을 진행하다 데이터베이스가 뻗어버릴 수도 있다.
과연 이게 최선일까?
물론 아니다. 만일 이게 최선이었다면 이 포스트를 시작하지도 않았을 것이다.
UNION 사용
만일 type과 price에 인덱스가 제대로 걸려있고, 각 그룹에 포함해야 할 레코드 보다 제거해야 할 레코드가 더 많다면 보다 더 효율적인 싱글 패스 방법을 사용할 수 있다. 이 방법은 특히 MySQL와 일부 RDBMS의 경우에 유용하다. 먼저 쿼리를 쪼개서 그룹별로 개별 쿼리를 실행 후 UNION을 이용해 다시 하나로 합치는 것이다. 아래는 MySQL 쿼리 구문이다.
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)변수 사용(in MySQL)
UNION을 사용하면 코드가 훨씬 직관적이고 최적화 측면에서도 나쁘지 않지만 크나큰 제약사항이 있다. 만일 그룹이 정해져 있지 않다면 어떻게 해야 할까? 과일의 종류가 아닌 그룹이 얼마나 될지 모를 경우를 생각해보자. 예를 들어 각 플레이어들의 최근 3경기의 점수 같은것 말이다. 여기서 그룹은 각 플레이어 될것이고 플레이어는 얼마나 될지 미리 예측 할 수 없다. 이런 경우 좋은 선택이 될 수 있는 또다른 방법은 MySQL의 변수를 사용하는 것이다.
set @num := 0, @type := '';
select `type`, `variety`, `price`
from (
select `type`, `variety`, `price`,
@num := if(@type = `type`, @num + 1, 1) AS rownum,
@type := `type`
from fruits
order by `type`, `price`
) as x where x.rownum <= 2;
서브 쿼리에서 `type`과 `price`를 기준으로 정렬 후 순서 대로 번호를 메겨 임시 테이블을 생성 후, 그 임시 테이블을 전체 순회하면서 `rownum`이 2이상인 행들만 추려내는 방식이다.
