Oracle 유용 구분 정리 해석내용 - PARTITION BY, START WITH...CONNECT BY PRIOR

이전에 유용 구분이라고 작성한 내용을 해석해 보겠다.

우선 기본이 되는 table이 있어야 하는데 없으니까 view를 생성하는 부분이 다음과 같다. 

WITH TVIEW AS (
    SELECT 'a' AS ac , '1a' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '3a' AS acv FROM dual UNION ALL
    SELECT 'e' AS ac , '2e' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '4a' AS acv FROM dual UNION ALL
    SELECT 'b' AS ac , '1b' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '1c' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '2g' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '2c' AS acv FROM dual UNION ALL
    SELECT 'c' AS ac , '3c' AS acv FROM dual UNION ALL
    SELECT 'a' AS ac , '2a' AS acv FROM dual UNION ALL        
    SELECT 'd' AS ac , '1d' AS acv FROM dual UNION ALL
    SELECT 'e' AS ac , '1e' AS acv FROM dual UNION ALL    
    SELECT 'f' AS ac , '1f' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '1g' AS acv FROM dual UNION ALL
    SELECT 'g' AS ac , '3g' AS acv FROM dual UNION ALL
    SELECT 'h' AS ac , '1h' AS acv FROM dual UNION ALL
    SELECT 'h' AS ac , '2h' AS acv FROM dual UNION ALL
    SELECT 'i' AS ac , '1i' AS acv FROM dual
)

이 내용은 다음 과같다.

이 내용을 다음 쿼리를 적용하면 

SELECT ac ,MAX(SYS_CONNECT_BY_PATH (acv, '|')) AS acvl
FROM (
        SELECT 
                ac, acv
                ,(ROW_NUMBER () OVER (PARTITION BY ac  ORDER BY ac, acv  )) RNUM    -- (1)
        FROM TVIEW
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1) 
AND PRIOR AC = AC
GROUP BY AC

다음과 같은 결과를 얻을 수 있다.

각 AC 값을 그룹으로 하는 내용들을 하나의 row를 사용해서 string으로 묶되 정렬된 그리고 구분자를 포한 string을 만드는 것이다. 이 내용이 프로젝트 참여시 간간히 필요할 때가 있어서 작성해 놓았다. 이제 Query의 부분을 진행해 보겠다.

기본 VIEW 형태에서 (1) 부분만을 적용했을 때는 다음과 같은 결과를 얻을 수 있다.

각 그룹 내에서의 ROW_NUMBER가 적용 되었있는 상태다. 물론 그룹내에서 sorting 되어진 상태에서 row_number가 적용된 것이다. PARTITION BY ac  ORDER BY ac, acv 구문을 통해서 ac를 그룹으로 내부에서 order by 적용되고 ROW_NUMBER () OVER ( ...) 구분을 통해서 그룹 내부에서 정렬된 결과로 row_number()가 적용된 것이다.

다음으로 START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1)  구분을 통해서 최종 형태를 얻어 내는 것이다. AC항목의 하나의 그룹단위로 RNUM이 1인 것부터 처리된다. 

SYS_CONNECT_BY_PATH( column, char )

SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리(계층구조)에서만 유효하며, column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.

여기서 참고내용으로 CONNECTED BY 구분을 사용하게 되면 HASH_JOIN이 발생하는데 여기서 오류가 발생할 때도 있다. 이는 예전 글에 정리된 것을 참조하기 바란다. 이 오류를 경험한 것은 9i를 사용할 때 였고 이 후 버전에서 해결이 되었는지는 모르겠다.