[SQL 독학하기] 프로그래머스 오답 풀이 - 상위 N개 레코드 추출하기

Intro

SQL을 독학하기 위해 지난달에 SQL 기본 문법 및 튜닝 강의를 들었다.

매일 3~4시간씩 강의 및 예제 풀이에 매달려 진도 따라가기에만 급급했기 때문에 종강 후 내 SQL 실력이 어느정도인지 가늠이 잘 되지 않았다.

강의에서 기본 교재로 사용했던 책을 빌려 이전에 풀었던 문제를 답안을 보지 않고 다시 푸는 등의 시도를 했지만, 이 역시 익숙한 데이터였기 때문에 실무에서 새로운 데이터셋을 마주쳤을 때를 대비하기 어렵다고 판단했다.

따라서 SQL 연습을 할 수 있는 루트를 찾다보니, 많은 분들이 프로그래머스, 해커랭크, solveSQL, TestDome 등을 이용하시는 듯 했다.

또한, 프로그래머스 문제들을 시작으로 SQL 실력이 어느정도 올라오면 해커랭크로 빠져 더 높은 수준의 문제를 푸는 방향이 주로 추천됐다.

따라서 프로그래머스 문제들을 매일 1~2시간씩 푼 후 틀리거나 헷갈린 문제가 있다면 이곳에 리뷰하고자 한다.

PS) 만일 이 글을 읽으시는 분이 계시다면, 저보다 분명 SQL 실력이 좋은 분일 것입니다.. 글 내용 중 잘못됐거나 더 좋은 쿼리문이 있다면 사정없이 피드백을 날려주시길 부탁드립니다.

 

오늘 리뷰할 문제에서 가장 중점이 되는 부분은 상위 n개 값을 추출할 때 Oracle과 MySQL의 차이이다.

 

Question

[Lv.1] ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
INTAKE_CONDITION	VARCHAR(N)	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_INTAKE	VARCHAR(N)	FALSE

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하는 SQL 문을 작성해주세요.

 

Answer

위 문제에 대한 나의 답안은 아래와 같다.

--Oracle--
SELECT NAME
  FROM ANIMAL_INS
  ORDER BY DATETIME ASC 
           FETCH FIRST 1 ROWS ONLY;

데이터셋에서 동물들이 보호소에 들어온 날짜를 내림차순으로 정렬해 '과거-최신'순으로 날짜를 출력한 뒤, 상위 1개값에서 동물의 이름만 출력하는 로직이다.

그러나 이를 MySQL에서 실행하니, 아래와 같은 오류가 발생했다.

Oracle과 MySQL의 차이를 알게 된 순간

위와 같은 상황을 해결하기 위해 Oracle과 MySQL에서 상위 n개 값을 추출하는 방식을 알아보았다.

Solutions

[Oracle에서 상위 n개 값을 추출할 경우]

Oracle에서 출력되는 행을 제한하는 방법은 2가지이다.

• ROWNUM
• TOP-n Queries

위 두가지 방법을 구체적으로 살펴보자.

1) ROWNUM

ROWNUM은 가짜 컬럼(Pseudo Column)으로, *(Asterisk)를 사용해도 보이지 않는 숨겨진 컬럼이다.

이는 데이터셋 안에 있는 행별 데이터에 번호를 매긴 것인데, 별도로 조건을 줘야 출력된다.

만일 ROWNUM을 활용해 위 문제를 푼다면 아래와 같은 쿼리문을 작성할 수 있다.

SELECT NAME
  FROM(
       SELECT NAME
         FROM ANIMAL_INS
         ORDER BY DATETIME ASC 
      )
  WHERE ROWNUM = 1;

보호시작일을 오름차순으로 정렬해 가장 먼저 보호소에 들어온 동물의 이름이 첫번째 출력 행으로 나오게 하는 서브쿼리를 작성하고, ROWNUM을 사용해 가장 첫번째 데이터만 나오도록 작성했다.

ROWNUM은 데이터셋에 특별한 조건을 주지 않아도 되는 경우에는 유용하지만, 위 문제와 같이 데이터셋을 살짝 변형해야 하는 경우엔 서브쿼리를 사용해야 해서 좀 복잡하다.

 

2) TOP-n Queries

TOP-n Query는 정렬된 결과로부터 위쪽 또는 아래쪽의 N개 행을 반환하는 쿼리이다.

데이터셋에 특정 조건이 붙은 경우 ROWNUM보다 간단하게 원하는 데이터만 제한할 수 있는 문법이다.

이는 ORDER BY절과 함께 사용되어, 특정 컬럼을 기준으로 정렬한 결과에서 일부 결과만을 출력할 수 있다.

Answer 부분에 작성한 답안은 TOP-n Query인 'Fetch first N rows only'를 활용한 쿼리이다.

 

이밖에 다양한 Row limiting절을 살펴보도록 하자.

- 출력되는 행을 비율(%)로 제한하는 경우: Fetch first N percent rows only

(Fetch first N rows only는 상위 행의 개수를 제한하는 문법으로, 상위 비율로 행의 개수를 제한하려면 Percent를 작성해야 한다.)

- 중복값을 갖는 데이터를 함께 출력하는 경우: Fetch first N rows WITH TIES

(WITH TIES 옵션을 주면 여러 행이 N번째 행의 값과 동일할 경우에 같이 출력된다. 예를 들어, 상위 2개 값만 출력하도록 작성해도 WITH TIES 옵션이 끼면 N번째 행과 값이 동일하다는 전제 하에 2개 이상의 행이 출력될 수 있다.)

- 출력이 시작되는 행의 위치를 지정하는 경우: OFFSET N rows

(OFFSET 옵션을 주면 N+1번째 행부터 결과가 출력된다. OFFSET과 FETCH는 함께 조합해서 사용할 수 있는데, 가령 3번째 행을 시작으로 2개 행만 출력하고 싶다면 'ORDER BY 정렬 컬럼 OFFSET 2 ROWS FETCH FIRST 2 ROWS ONLY'로 작성한다.)

 

[MySQL에서 상위 n개 값을 추출할 경우]

Oracle과 마찬가지로 MySQL에서도 ROWNUM을 활용할 수 있으나, TOP-n Queries는 사용할 수 없다.

그렇다면 MySQL에서는 복잡한 서브쿼리를 껴서 상위 값을 추출해야 하는가.. 절대 아니다. 이 부분에서는 MySQL이 Oracle보다 더 쉬운 문법을 제공한다.그것은 바로 LIMIT이다.LIMIT도 TOP-n Queries와 마찬가지로 ORDER BY절과 함께 작성되며, 위 문제에 적용하면 다음과 같이 쿼리문을 작성할 수 있다.

--MySQL--
SELECT NAME
  FROM ANIMAL_INS
  ORDER BY DATETIME ASC LIMIT 1;

 

PS) 본문 중 틀리거나 개선할 부분이 있다면 꼭 답글로 알려주셨으면 좋겠습니다! 열심히 배우겠습니다 :)

 

 

 

Oracle의 SQL 문법 내용은 유연수 선생님의 '초보자를 위한 SQL 200제'를 참고하였습니다.