MySQL에서 그룹으로 조회 할 때의 정렬 기준과 변경 방법

MySQL에서 흔하게 사용되는 구문 중 하나는 데이터를 그룹으로 조회를 할 때 사용되는 group by 구문이 있습니다. 하지만, 너무 자주 사용되는 구문인 탓인지 group by 구문이 실행되어 데이터를 조회 할 때 어떠한 기준으로 값을 가져오는지 모르는 경우가 있습니다.

저 역시 그 기준을 모르고 있었습니다만, 지인의 질문에 호기심을 느끼고 테스트를 해본 결과 몇 가지 재미있는 사실을 확인하게 되어 이를 공유하고자 합니다.

설명의 이해를 돕기 위해 다음과 같이 날짜(day)를 기준으로 매일 한 번씩 각 웹사이트(site)의 순위(rank)를 기록한 테이블이 있다고 가정합니다. 설명을 돕기 위해 만들어진 테이블이므로 총 9개의 레코드만 존재합니다.

테이블 생성을 위한 쿼리와 초기 데이터 생성을 위한 쿼리는 아래와 같습니다.

CREATE TABLE `test` (
    `day` DATE NULL DEFAULT NULL,
    `site` VARCHAR(50) NULL DEFAULT NULL,
    `rank` INT(11) NULL DEFAULT NULL
);

INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-05', 'smileserv.com', 3);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-05', 'cloudv.kr', 2);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-05', '1000dedi.net', 1);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-06', 'smileserv.com', 1);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-06', 'cloudv.kr', 2);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-06', '1000dedi.net', 3);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-07', 'smileserv.com', 3);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-07', 'cloudv.kr', 1);
INSERT INTO `test` (`day`, `site`, `rank`) VALUES ('2015-09-07', '1000dedi.net', 2);

데이터를 조회 할 때, group by 구문을 사용하는 가장 대표적인 예는 다음과 같습니다.

SELECT day , site , rank FROM test GROUP BY day;

이 조회 결과로 test 테이블에 몇 일간의 기록이 있는지는 알 수는 있지만, 함께 조회된 웹사이트(site)와 순위(rank) 필드가 어떠한 기준에 의해 조회된 것인지 알 수가 없습니다.

그래서 group by 구문으로 조회되는 데이터의 규칙성을 확인하기 위해 테이블의 값을 변경해보며 몇 가지의 테스트를 해본 결과, group by 구문이 실행 될 때 기준이 되는 필드의 값이 처음으로 나타나는 값인 경우에 해당 레코드의 다른 필드 값도 함께 조회가 된다는 것을 확인하게 되었습니다. 이러한 규칙은 MySQL 서버 버전(3.x ~ 5.x)과는 무관하다는 것도 확인 할 수 있었습니다.

즉, group by 구문은 해당 테이블의 전체 데이터를 순차적(오름차순)으로 조회하여 그룹 조회 대상이 되는 – 예제에서는 날짜(day) – 필드의 값이 기존에 없었던 경우에 한하여 출력되는 것으로, 날짜(day) 필드의 값이 `2015-09-05`, ‘2015-09-06’, ‘2015-09-07’ 인 레코드만 조회된 것입니다.

그렇다면 group by 구문을 사용하면서 함께 조회되는 데이터의 정렬 방법은 무엇이 있는지 알아볼 필요가 있었습니다. 사실, 이러한 종류의 데이터는 프로그램 목적에 따라 데이터 통계를 미리 생성하는(data warehouse)기법을 활용하는 것이 효율적입니다만, 일단은 어떻게 하면 group by 와 정렬을 함께 할 수 있는지 알아보았습니다.

예를 들어, 각 날짜(day)별 순위(rank)가 1위인 사이트를 조회하는 쿼리는 다음과 같습니다.

SELECT day , site , rank FROM (
SELECT * FROM test ORDER BY rank ASC
) AS x GROUP BY day;

이 방법은 서브 쿼리(subquery)를 이용하는 것으로, order by 구문으로 조회하기 이전에 순위(rank)를 기준으로 오름차순 정렬을 먼저 실행하도록 하여 날짜(day)별 순위(rank)가 1위인 사이트를 조회하도록 하였습니다.

이러한 방법은 MySQL 서버 버전이 최소 4.1 이상이 되어야 하며, 그 이하 버전에서는 서브 쿼리를 지원하지 않아므로 사용 할 수 없습니다.