티스토리 뷰

반응형

안녕하세요, 끙정입니다.

 

지난 시간에 배웠던 ORDER BY의 연장선으로,

효율적인 출력 방법 몇 가지를 알아보겠습니다.

 

SELECT문은 매우 빠르지만, 출력해야 하는 행의 개수가 수만개가 넘어가면 어마어마한 시간이 걸립니다.

따라서 불필요한 정보를 굳이 출력해야 할 필요가 없다면,

효율적으로 출력하는 것이 좋습니다.

 

1. DISTINCT

가장 먼저 살펴볼 것은 DISTINCT입니다.

간단히 말해서 중복을 제거하고 하나의 값으로만 출력을 해주는 것입니다.

 

/* 그냥 출력 */
SELECT region FROM tCity;

/* SELECT 다음에 DISTINCT를 넣어줍니다. */
SELECT DISTINCT region FROM tCity;

 

두 쿼리의 출력 결과는 아래와 같습니다.

 

 

DISTINCT 키워드로 중복을 제거하려면 같은 종류의 값을 모아야 합니다.

SQL Server는 정렬을 수행하며 중복값을 신속히 찾는데,

오라클과 Maria DB는 그렇지 않습니다.

따라서 중복 제거 후의 결과 순서가 다릅니다.

 

정렬도 동시에 해주고 싶다면 ORDER BY 를 동시에 써야 합니다.

 

/* ORDER BY를 끝에 넣어준다. */
SELECT DISTINCT region FROM tCity ORDER BY region;

 

자동으로 정렬까지 되는 SQL Server(좌), 정렬이 안되는 Oracle(중), ORDER BY를 써서 SQL Server와 결과가 같아진 Oracle(우)

 

2. 의사 컬럼(Pseudo Column) / ROWNUM

파이썬의 판다스에서 데이터 프레임의 상위 몇 개만 보고 싶을 때, .head()를 쓰는 것처럼,

SQL도 굳이 모든 행을 다 볼 필요는 없는 경우가 있습니다.

오라클은 이러한 처리를 위해서 의사 컬럼을 제공합니다.

의사 컬럼은 인위적으로 추가한 가짜 컬럼입니다.

 

오라클은 필드 목록에 없어도 항상 ROWNUM과 ROWID를 생성합니다.

ROWNUM은 순서대로 붙이는 행번호이며, ROWID는 행마다 붙이는 고유값입니다.

 

/* ROWNUM, ROWID는 대소문자 구분이 없다. */
SELECT name, ROWNUM, ROWID FROM tCity;

 

오직 오라클에서만

 

ROWID는 행끼리의 구분을 위해 붙이는 내부 식별자입니다.

이제 우리는 위 목록에서 앞쪽 4개만 출력할 것입니다.

 

/* ROWNUM은 눈에 보이지 않아도 항상 존재한다. */
/* ROWNUM을 WHERE 조건절을 통해 제한한다. */

SELECT * FROM tCity WHERE ROWNUM <= 4;

 

위에서부터 4개만 출력이 되었다!

 

그러나 특정 조건 아래에서 ROWNUM으로 출력하려면 문제가 조금 생깁니다.

예를 들어, 면적이 넓은 상위 4개의 도시를 고른다고 해보겠습니다.

면적순으로 정렬하고 그중 앞쪽 4개를 출력해보겠습니다.

 

/* area로 정렬하고, ROWNUM으로 상위 4개만 출력하면 되겠지?! 데헷 */
SELECT * FROM tCity ORDER BY area DESC WEHRE ROWNUM <= 4;

 

그러나 오류가 뜬다

 

전에도 말씀드렸다시피, ORDER BY 는 WHERE 절보다 앞에 올 수 없습니다.

ORDER BY 는 무조건 SELECT 문의 제일 마지막에 수행 됩니다.

그렇다면 순서를 바꿔 볼까요?

 

/* 그럼 ROWNUM으로 4개를 출력하고 정렬을 하면 될까? */
SELECT * FROM tCity WHERE ROWNUM <= 4 ORDER BY area DESC;

 

잘 나온건가?

 

얼핏보면 잘 나온 것 같지만 틀렸습니다.

area 순으로 상위 4개를 뽑는다면 1819인 홍전이 제일 먼저 나와야 합니다.

이유는 간단하죠.

테이블에서 입력 순서대로 4개를 뽑고, 그 4개에서 정렬을 한 것입니다.

 

결론을 말씀드리면, ROWNUM만으로는 해당 쿼리를 완성할 수 없습니다.

서브쿼리를 사용해야 합니다.

 

/* 서브쿼리를 통해 area를 기준으로 정렬한 테이블을 가져오고 */
/* 그 테이블에서 WHERE절을 통해 상위 4개를 가져온다. */
SELECT *
FROM (SELECT * FROM tCity ORDER BY area DESC)
WHERE ROWNUM <= 4;

 

이제야 제대로 나왔네요.

 

3. TOP, LIMIT

SQL Server와 Maria DB는 오라클의 의사 컬럼(with ROWNUM)과는 달리 LIMIT과 TOP을 제공합니다.

훨씬 편하고 쉽습니다.

 

3-1. TOP

먼저 SQL Server는 TOP으로 행수를 제한합니다.

SELECT 와 필드목록 사이에 TOP을 끼워 넣어주면 됩니다.

 

/* SELECT [TOP N] * FROM 테이블 */

SELECT TOP 4 * FROM tCity ORDER BY area DESC;

 

너무 쉽게 출력이 된다.

 

TOP n PERCENT 구문을 통해서 비율로 출력하는 것도 가능합니다.

 

/* TOP n PERCENT ~ */
SELECT TOP 30 PERCENT * FROM tCity ORDER BY area DESC;

 

아름답습니다.

 

TOP 구문은 일부만 읽기 때문에 연산 속도가 월등히 빠릅니다.

50만명의 성적을 저장한 tExam 테이블에서 상위 100등만 조사한다고 가정 했을 때,

모든 학생을 내림차순으로 정렬한 후 앞쪽 100개를 참조하는 방법과,

TOP 구문으로 상위 100개를 출력하는 것은 어마어마한 차이입니다.

 

/* 50만 행이면 몇 분이 걸릴지 모르는 쿼리 */
SELECT * FROM tExam ORDER BY Score DESC;

/* 단 1초면 끝나는 쿼리 */
SELECT TOP 100 * FROM tExam ORDER BY Score DESC;

 

개수가 아닌 비율로 제한할 때는 동점자 문제가 발생합니다.

50만명 중에 상위 1%를 추출하면 대략 5000명이고,

5000등과 5001등이 동점이라면 어디서 잘라야 할 지 애매합니다.

이럴 경우 WITH TIES를 넣어줄 수 있습니다.

 

SELECT TOP 1 PERCENT WITH TIES * FROM tExam ORDER BY Score DESC;

 

동점자는 정렬할 때만 발생하므로 WITH TIES는 ORDER BY 가 있을 때만 지정할 수 있습니다.

 

3-2. LIMIT

Maria DB는 TOP과 비슷한 구문으로 LIMIT을 지원합니다.

MySQL PostgreSQL도 LIMIT을 지원합니다.

TOP 구문과는 다르게 쿼리문의 제일 마지막에 붙입니다.

 

SELECT ... LIMIT [건너뛸 개수], 총개수
/* 건너뛸 개수를 생략하면 0으로 인식합니다. */

/* 0부터 4까지 출력합니다. */
SELECT * FROM tCity ORDER BY area DESC LIMIT 4;

 

 

LIMIT는 TOP과는 다르게 비율을 지정할 수는 없다는 단점이 있습니다.

그러나 앞의 레코드를 뛰어넘을 수는 있습니다.

다음과 같이 말이죠.

아주 막강한 기능입니다.

 

/* OFFSET으로 2를 주었고, 3까지를 주었다. */
/* 2등까지 건너 뛰고, 그 다음 3개를 보여준다. */
SELECT * FROM tCity ORDER BY area DESC LIMIT 2, 3;

/* LIMIT 2, 3 은 LIMIT 3 OFFSET 2 와도 같은 의미다. */
SELECT * FROM tCity ORDER BY area DESC LIMIT 2 OFFSET 3;

 

 

4. OFFSET FETCH

DBMS 별로 이렇게 일부 레코드만 조회하는 문법이 다르다보니 많은 어려움이 있었습니다.

그래서 SQL 표준이 새로 만든 OFFSET FETCH라는 것이 생겼습니다.

일부분을 특정하기 위해서는 순서가 지정되어야 하므로,

OFFSET FETCH는 반드시 ORDER BY 문과 함께 합니다.

그래서 OFFSET FETCH는 별도 구문이 아닌, ORDER BY 의 옵션입니다.

 

ORDER BY 기준필드 OFFSET 건너뛸행수 ROWS FETCH NEXT 출력할행수 ROWS ONLY

/* ROW 대신에 ROW도 가능 */
/* NEXT 대신에 FIRST도 가능 */

 

OFFSET FETCH를 사용해서 면적순으로 상위 4개의 도시를 출력해 보겠습니다.

 

/* 기본적으로 이렇게 쿼리를 쓴다. */
SELECT * FROM tCity ORDER BY area DESC OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;

/* OFFSET 을 주지 않는다면 OFFSET 0 ROWS는 생략 가능하다. */
SELECT * FROM tCity ORDER BY area DESC FETCH NEXT 4 ROWS ONLY;

/* 정렬을 하지 않는다면 ORDER BY 는 생략 가능하다. */
SELECT * FROM tCity FETCH NEXT 4 ROWS ONLY;

 

오라클에서는 위 쿼리와 같이 OFFSET 0 ROWS 를 생략 가능하고,

ORDER BY 도 경우에 따라 생략 가능합니다.

단, 오라클에서만 가능합니다.

 

오직 오라클에서만.

 

SQL Server 반드시 ORDER BY로 정렬을 하며 같이 사용해야 합니다.

또한 OFFSET 0 ROWS를 생략할 수 없습니다.

 

SQL Server는 허용하지 않는다.

 

마지막으로 Maria DB는 아예 OFFSET FETCH를 지원하지 않습니다.

LIMIT이 거의 똑같은 기능을 수행하기 때문이지요.

 

LIMIT이라는 갓킹제너럴 구문이 있는데 OFFSET FETCH가 웬말이냐~

 

그러나 통일이 안되어 있어서 다 알고 있어야 한다는 게 조금 아쉽긴 합니다.

 

 

 

 

 

그만 알아보겠습니다.

 

출처

http://www.yes24.com/Product/Goods/101637633?OzSrank=1

 

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24

DBMS에 제약 없이 SQL을 활용한다!핵심 원리를 알려주는 SQL 바이블 DBMS 제품이나 개발툴이 아닌 SQL 언어 그 자체를 배우는 바이블 도서다. 특정 DBMS에 종속적인 사용법보다는 표준화된 데이터 관리

www.yes24.com

 

반응형
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
«   2025/07   »
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
글 보관함