SQL

[MYSQL] ROWNUM 으로 번호를 매길 때 특정 조건만 카운팅하는 법

진짠 2023. 11. 25. 18:16
728x90
개요

 

mysql 쿼리문을 사용하여 목록을 추출할 때 순번을 매기는 것으로 ROWNUM을 종종 사용했다. 그런데 이번에 내가 하고 싶었던 것은 목록의 갯수만큼 무조건 +1을 하여 카운팅하는 로직이 아닌 특정 조건에만 +1을 하여 카운팅 하는 쿼리를 만들고 싶었다.

 

쿼리 고수분들이라면 더 간단하거나 쉬운 방법을 찾을 수도 있었겠지만 내가 찾은 방법은 이렇다.

 

의식의 흐름

 

일단 추출하고 싶은 목록을 쿼리로 짠다.

 

SELECT TR.REGISTER_SEQ
       , TR.ORDER_DEGREE
  FROM T_REGISTER TR
 WHERE 1 = 1
    //조건절
 ORDER BY TR.REGISTER_SEQ

 

예시로 만든 간단한 쿼리는 이렇다.

 

'T_REGISTER' 이라는 테이블의 'REGISTER_SEQ, ORDER_DEGREE' 라는 컬럼 두개를 가져온다. 별다른 WHERE 조건은 없고 정렬 순서만 REGISTER_SEQ의 오름차순으로 해주었다.

 

그리고 그 다음은 추출한 쿼리문의 rownum를 만드는 것이다. @ROWNUM 을 이용하여 변수를 ROWNUM이라는 이름의 사용자 정의 변수를 사용한다는 것이다. 본래 컬럼에는 없는 새로운 컬럼을 만드는 것이기 때문에 위와 같이 만들어준 후 초기화를 시켜주는 과정이 필요하다.(프로그래밍 언어는 다 거기서 거기인 듯 하다.)

 

증가하는 ROWNO
SELECT A.*,
	   @ROWNUM := @ROWNUM + 1 AS ROWNO
FROM (
    SELECT TR.REGISTER_SEQ
           , TR.ORDER_DEGREE
      FROM T_REGISTER TR
     WHERE 1 = 1
        //조건절
     ORDER BY TR.REGISTER_SEQ
     ) A, (SELECT @ROWNUM := 0 ) R

 

기본적인 ROWNUM 생성은 이런 식으로 진행하였다. 기본 쿼리를 인라인 뷰로 생성해주고 @ROWNUM 초기값을 0으로 준 뒤 SELECT로 조회하는 쿼리를 추가로 만들었다.

 

그리고 SELECT 절에서 ROWNUM을 +1씩 해주면 흔히 아는 1에서 부터 하나씩 증가하는 ROWNO 컬럼을 만들 수 있다.

 

차감하는 ROWNO
SELECT A.*,
	   @ROWNUM := @ROWNUM - 1 AS ROWNO
FROM (
    SELECT TR.REGISTER_SEQ
           , TR.ORDER_DEGREE
      FROM T_REGISTER TR
     WHERE 1 = 1
        //조건절
     ORDER BY TR.REGISTER_SEQ
     ) A, (SELECT @ROWNUM := #{__totalCount} + 1 ) R

 

총 개수에서 하나씩 차감되는 rowno을 만들고 싶다면 다음과 같이 쿼리를 짠다. 예제에서는 이클립스에서 mybatis 환경을 이용 중이기 때문에 #{__totalCount} 라는 변수를 불러왔다. 이것은 해당 쿼리의 총 개수를 가리킨다. 쿼리로 불러온 목록의 개수를 알아야 rownum의 값을 초기화 할 수 있기 때문이다.

 

특정조건에서 카운팅되는 ROWNO

 

SET @ROWNUM := #{__divTotalCount} + 1, @PREV_REGISTER_SEQ := NULL;
SELECT B.*
FROM (
    SELECT 
           @ROWNUM := CASE WHEN @PREV_REGISTER_SEQ = A.REGISTER_SEQ THEN @ROWNUM ELSE @ROWNUM - 1 END AS ROWNO,
           @PREV_REGISTER_SEQ := A.REGISTER_SEQ,
           A.*
    FROM (
        SELECT TR.REGISTER_SEQ
               , TR.ORDER_DEGREE
          FROM T_REGISTER TR
		  LEFT JOIN T_PRODUCT TP
            ON TR.PRODUCT_SEQ = TP.PRODUCT_SEQ
         GROUP BY TR.REGISTER_SEQ, TP.PRODUCT_SEQ   
         ORDER BY TR.REGISTER_SEQ
         ) A
     ORDER BY ROWNO
 ) B

 

조금은 복잡한 쿼리문이 나와버렸다. 이 쿼리에 특정조건은 목록의 이전 register_seq와 현재 register_seq를 조회해서 다를 경우에만 카운팅을 해주는 것이다.

 

테이블은 총 2개를 사용한다. T_REGISTER 와 T_PRODUCT는 1대다 관계를 가지고 있다. T_REGISTER(접수) 테이블은 PRODUCT_SEQ 컬럼을 가지고 T_PRODUCT(제품) 테이블 데이터를 조회합니다. 그래서 LEFT JOIN을 사용하여 조회하면 데이터가 다음과 같이 나오게 된다.

 

 

해당 REGISTER_SEQ를 가진 제품이 여러개 존재할 수 있으므로 REGISTER_SEQ가 여러번 출력된다.

실행계획이 데이터를 하나씩 조회할 때 이전 REGISTER_SEQ와 비교할 수 있도록 이전 REGISTER_SEQ를 담아둘 변수가 필요했고 그것을 @prev_register_seq 라고 선언했다.

 

SET @ROWNUM := #{__divTotalCount} + 1, @PREV_REGISTER_SEQ := NULL;

 

이 부분이 선언부이다. ROWNUM은 셀 번호를 의미하며 #{__divTotalCount} 는 'REGISTER_SEQ를 GROUP BY한 데이터 개수' 이다.

 

해당 count 쿼리는 다음과 같다.

 

    SELECT 
           COUNT(*)
    FROM (
        SELECT TR.REGISTER_SEQ
          FROM T_REGISTER TR
		  LEFT JOIN T_PRODUCT TP
            ON TR.PRODUCT_SEQ = TP.PRODUCT_SEQ
         GROUP BY TR.REGISTER_SEQ
         ) A

 

REGISTER_SEQ가 다를 경우에만 카운트가 변하기 때문에 같은 SEQ끼리는 동일한 값으로 취급해야 하기 때문이다.

 

그리고 PREV_REGISTER_SEQ 는 NULL로 초기화하며 다음 데이터 조회 시 이전 REGISTER_SEQ값을 담는다. 그것을 나타낸 것이 다음 부분이다.

 

    SELECT 
           @ROWNUM := CASE WHEN @PREV_REGISTER_SEQ = A.REGISTER_SEQ THEN @ROWNUM ELSE @ROWNUM - 1 END AS ROWNO,
           @PREV_REGISTER_SEQ := A.REGISTER_SEQ,
           A.*

 

@PREV_REGISTER_SEQ 는 실행계획이 데이터를 한줄씩 조회할 때 다음과 같은 상태변화를 가지고 있다.

 

최초:NULL(초기값) -> 1줄조회:NULL -> 2줄조회:1줄데이터의 REGISTER_SEQ -> 3줄조회:2줄데이터의 REGISTER_SEQ ...
 

 

만약 내가 정한 조건이 아닌 다른 조건을 사용하여 ROWNO 값을 컨트롤하고 싶다면 @ROWNUM의 CASE WHEN 부분을 조정하고 그에 맞춰 토탈카운트나 GROUP BY, 테이블 조인 부분을 수정하면 되지 않을까 싶다.

728x90