ORDER BY 뒤에 숫자의 의미와 더 강력한 정렬을 위한 CASE WHEN 사용하기
ORDER BY 뒤에 숫자의 의미
ORDER BY 1,2,3은 단순히 숫자를 나열한 것이 아니라, select에서 선택한 컬럼의 순서를 의미한다.
예를 들어, 아래와 같이 이벤트 테이블이 있다고 가정해보자.
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`event_name` varchar(255) NOT NULL, -- 이벤트 이름
`recruitment_start` DATE NOT NULL, -- 모집 시작일
`recruitment_end` DATE NOT NULL, -- 모집 마감일
`presentation_date` DATE NOT NULL, -- 발표일
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
이 테이블에서 recruitment_start, recruitment_end, presentation_date 컬럼을 기준으로 정렬하고 싶은데 숫자로 나타낸다면 아래와 같이 작성하면 된다
1
2
3
SELECT recruitment_start, recruitment_end, presentation_date, ...etc FROM events ORDER BY 1,2,3;
하지만 ORDER BY에 숫자를 사용하는 것은 집계 함수나 복잡한 표현식에서는 사용하는 것은 고려해 볼만하지만 피하는 것이 좋다. 가독성이 떨어지고, 컬럼의 순서가 변경 되면 쿼리가 깨질 수 있기 때문이다.
아래는 집계 함수나 복잡한 표현식에서 ORDER BY에 숫자를 사용하는 예시이다.
1
2
3
4
5
6
7
8
9
10
SELECT
name,
age,
salary,
(salary * 12) as annual_salary,
CASE WHEN age < 30 THEN 'Young' ELSE 'Senior' END as age_group
FROM employees
ORDER BY 4 DESC, 5, age;
강력한 ORDER BY를 위해 CASE WHEN 그리고 숫자 사용하기
ORDER BY에서 CASE WHEN을 사용하면 특정 조건에 따라 정렬을 다르게 할 수 있다.
예를 들어, 나이가 30세 미만인 사람들을 먼저 볼 수 있게 정렬하고, 그 다음에는 연봉을 기준으로 내림차순으로 정렬하고 싶다면 아래와 같이 작성하면 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
name,
age,
salary,
(salary * 12) as annual_salary,
CASE WHEN age < 30 THEN 'Young' ELSE 'Senior' END as age_group
FROM employees
ORDER BY
CASE WHEN age < 30 THEN 1 ELSE 2 END, -- 나이가 30세 미만인 사람을 먼저 정렬
4 DESC -- 연봉을 기준으로 내림차순 정렬
위의 ORDER BY 뒤에 1,2는 단순히 CASE WHEN의 결과이며 처음에 소개한 ORDER BY 뒤에 숫자의 의미와는 다르다.
정렬 순서에 대해 적지 않으면 ASC가 기본이니 숫자가 작을수록 먼저 정렬된다. 만약 내림차순으로 나타내고 싶다면 뒤에 DESC를 붙여주면 된다. ORDER BY CASE WHEN age < 30 THEN 1 ELSE 2 END DESC
이렇게 ORDER BY 뒤에 CASE WHEN을 사용하면 추가적인 조건에 따라 정렬을 할 수 있어 더 강력한 정렬을 할 수 있다.
event 리스트 순서 정렬해보기
위에서 소개한 이벤트 테이블를 이용해 좀 더 복잡한 예시를 살펴보자.
먼저 초기 요구사항은 이벤트가 진행 중, 당첨자 선정 중, 마감된 이벤트를 구분하여 정렬하고 싶다고 가정해보자.
그러면 아래와 같이 ORDER BY 뒤에 CASE WHEN을 사용하면 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
event_name,
recruitment_start,
recruitment_end,
presentation_date
FROM events
ORDER BY
CASE
WHEN recruitment_start <= NOW() AND recruitment_end >= NOW() THEN 1 -- 진행 중
WHEN recruitment_end < NOW() AND presentation_date >= NOW() THEN 2 -- 당첨자 선정 중
ELSE 3 -- 종료
END
이렇게 정렬된 결과에 더해 또 다른 조건을 추가하고 싶다면 ORDER BY 뒤에 추가적인 조건을 붙여주면 된다.
만약 위에 진행, 당첨자 선정, 마감 순의 이벤트로 정렬한 결과를 그룹으로 다시 정렬 하는 요구사항이 있다고 가정해보자.
진행 중인 이벤트는 recruitment_start를 기준으로 내림차순으로 정렬 즉, 가장 최근 시작한 이벤트가 먼저 나오게 해야한다.
당첨자 선정 중인 이벤트는 presentation_date를 기준으로 내림차순으로 정렬 즉, 가장 최근 발표일이 먼저 나오게 해야한다.
마감된 이벤트는 recruitment_end를 기준으로 내림차순으로 정렬 즉, 가장 최근 마감일이 먼저 나오게 해야한다.
이를 위해서는 위에 CASE WHEN 뒤에 추가적인 조건을 붙여주면 된다. 쉽게 말해, ORDER BY를 연속해서 사용한다고 생각하면 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
event_name,
recruitment_start,
recruitment_end,
presentation_date
FROM events
ORDER BY
CASE
WHEN recruitment_start <= NOW() AND recruitment_end >= NOW() THEN 1 -- 진행 중
WHEN recruitment_end < NOW() AND presentation_date >= NOW() THEN 2 -- 당첨자 선정 중
ELSE 3 -- 종료
END,
CASE
WHEN recruitment_start <= NOW() AND recruitment_end >= NOW() THEN recruitment_start -- 진행 중의 이벤트는 이벤트 시작일을 기준으로
WHEN recruitment_end < NOW() AND presentation_date >= NOW() THEN presentation_date -- 당첨자 선정인 이벤트는 모집 종료일을 기준으로
ELSE recruitment_end -- 마감된 이벤트는 모집 종료일을 기준으로
END DESC -- 내림차순으로 정렬한다.
중복되는 부분을 줄이기 위해 아래와 같이 작성할 수도 있다. 이 경우에는 select절에 event_status로 미리 값을 계산해 놓고 ORDER BY에 사용한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
event_name,
recruitment_start,
recruitment_end,
presentation_date,
CASE
WHEN recruitment_start <= NOW() AND recruitment_end >= NOW() THEN 1 -- 진행 중
WHEN recruitment_end < NOW() AND presentation_date >= NOW() THEN 2 -- 당첨자 선정 중
ELSE 3 -- 종료
END as event_status
FROM events
ORDER BY
event_status,
CASE
WHEN event_status = 1 THEN recruitment_start
WHEN event_status = 2 THEN presentation_date
ELSE recruitment_end
END DESC