구절의 순서와 페이징(서브쿼리 사용), 순위함수ROW_NUMBER() ,HAVING, 서브쿼리. 2021-08-10

우선 나는 HIT 칼럼을 기준으로 내림차순 정렬을 한 뒤에, ROWNUM을 붙이고 싶다.

select ROWNUM, member.* from member where rownum between 1 and 5 order by hit desc;

그래서 이런 쿼리를 썼더니 결과는 정렬된 기준으로 ROWNUM이 붙은 것이 아닌, 정렬하기 전 기준으로 붙여져서 뒤죽박죽이 된다.

이런 문제를 해결할려면 어떻게 해야 할까? 두가지 방법이 있는데 그떄 페이징 할때 처럼 서브쿼리로 미리 정렬된 테이블에 ROWNUM을 붙이던가, 아니면 순서함수인 ROW_NUMBER()을 사용하면 된다.

서브쿼리를 이용해서 미리 줄세워 놓은 테이블에 ROWNUM을 붙이는 방법.

select rownum, m.* from (select * from member order by hit) m;

순서합수인 ROW_NUMBER()를 사용하는 방법.

select ROW_NUMBER() over(order by hit desc) as "ROWNUM", m.* from member m; 
--from 절 뒤에 member의 별칭으로 m을 주었고, 
--바로 위에 사례랑 비교를 쉽게 하기 위해 ROWNUM 이란 별칭도 줌.

 

이렇게 되면 원하던 대로, 내림차순 이면서 ROWNUM도 순서대로 잘 잡혀있다.

 

집계함수를 사용했을떄, 추가적인 레코드 조건을 줄려면 어떻게 해야 할까? 예를 들면 PWD 별로 그룹지어서 HIT 수를 집계한다음 hit 값이 3이상인 레코드만 출력하고자 한다.

 

select pwd,count(hit) as hit from member where hit>=3 group by pwd;

언뜻보면 맞아 보인다.

나도 처음에 이 쿼리를 실행 해보고 값이 제대로 나오는 것인줄 알았다.

HIT의 값이 3 이상인 것들만 집계되었다.

그러나 원래 의도는 아니다. 원래는 내가 집계된 "횟수" 를 기준으로, 집계된 hit 횟수가 3 이상인 레코드가 출력되길 바랬는데 위 쿼리는 이미 집계되기도 전에 hit 칼럼(별칭아님) 이 3 이상인 것들만 뽑아놓은 뒤, 그 레코드 들을 집계한 것에 불과하다.

이유는 컴퓨터에서 읽는 순서가, from->where->group by->having->select->order by 이기 때문에, group by의 집계함수 count에 의해 집계되기도 전에 이미 where로 한번 테이블이 걸러지기 떄문이다.

select pwd,count(hit) as hit from member having count(hit)>=3 group by pwd;

이렇게 하면 pwd에 따른 집계에서 hit수가 3 이상으로 집계된 항목만 출력된다.

 

HIT의 횟수가 3 이상인 것들이 집계되었다.위의 결과랑 비교해 보자.

 

select pwd,count(hit) as hit from member having hit>=3 group by pwd;

그렇다면 count 함수를 굳이 2번 쓸 필요 없이, select에서 별칭으로 쓴 hit를 그대로 사용할려고 이렇게 쿼리를 작성해 보았지만 작동이 안되었다. 왜냐하면 select 문의 실행순서는 거의 마지막이기 때문에, 별칭 자체를  사용하지 못할 뿐더러, HAVING은 집계함수를 사용한 레코드의 조건을 설정하는 용도이기 떄문에, HAVING절 안에 집계함수가 아니면 동작하지 않는다.

 

그렇다면 where 절에 집계함수를 쓰면 어떨까?

select pwd,count(hit) as hit from member where count(hit)>=2 group by pwd;

또 이 쿼리도 불가능 하다. 왜냐하면, 애초에 집계함수인 count, sum, average 등은 group by 절 이후에만 쓸수 있기 때문이다.

 

**order by 의 위치는 select 뒤이기 떄문에 select 에서 붙인 별칭으로 정렬 가능하다.

** ROWNUM은 WHERE 절에서 실행되서 테이블에 새로운 ROWNUM이라는 칼럼을 추가하기에, 순서상 뒤에 오는 구절을 실행하면, rownum이 뒤죽박죽 될 수 있다.

**ROWNUM이 where 절에서 실행 되므로 where절에서 실행 순서상 앞인 from절에서 테이블 set을 만들어 놓고, 이 서브쿼리의 별칭을 준다음, where절에서 그 별잉을 이용해서 rownum을 사용하면 오류 없이 페이징이 가능하다.

 

 

 

서브쿼리는 우리가 이전에 페이징을 하면서 한번 배웠다.

페이징을 하는 법을 알아보자. 만약 rownum이 2부터 4까지인 레코드를 출력해보자.

select * from member where ROWNUM BETWEEN 2 and 4;

이러면 아무것도 안나온다. 왜냐하면 rownum은 where절에서 생성이 되는데, 생성되는 조건에 2부터 이므로 처음 생성될떄는 1으로 계속 생성되므로, 아예 결과셋이 만들어지지도 않는다.

select * from(select ROWNUM as num,member.* from member) member where NUM BETWEEN 2 and 4;

이렇게 쿼리를 쓰면 된다.

여기서 from 절 뒤에 나오는 괄호 부분이 바로 서브 쿼리이다.

from 절은 모든 구절중 가장 앞 순서로 실행되기에, 이렇게 원하는 칼럼을 별칭붙여서 뽑아낸 다음, where절로 조건을 줄 수 있다.

 

공부시간 3시간.

순공시간 1시간.

 

오늘은 번아웃이 온거 같았지만, 끝까지 정신력을 잃지 않아서 오늘 공부한 내용중 내게 필요한 것들을 얻어갈수 있었던 거 같다.  무엇이든 다른곳에 갈팡질팡 눈돌리지 말고"끝"까지 하는 사람이 이긴다는 조정식 쌤의 말이 세삼스럽게 떠오른다.ㅎㅎ