with t as ( select '0000' as cd, '' as hcd from dual union all select '1000' as cd, '0000' as hcd from dual union all select '1100' as cd, '1000' as hcd from dual union all select '1110' as cd, '1100' as hcd from dual union all select '1120' as cd, '1100' as hcd from dual union all select '1130' as cd, '1100' as hcd from dual union all select '2000' as cd, '0000' as hcd from dual union all select '2100' as cd, '2000' as hcd from dual union all select '2200' as cd, '2000' as hcd from dual union all select '2210' as cd, '2200' as hcd from dual union all select '2211' as cd, '2210' as hcd from dual union all select '2212' as cd, '2210' as hcd from dual union all select '2213' as cd, '2210' as hcd from dual ) select cd, level as lv, substr(replace(sys_connect_by_path(cd, '-'), '-'||cd, ''),2) as path from t where cd != '2200' start with hcd is null connect by prior cd = hcd order siblings by cd;
안녕하세요. 쿼리 관련 질문좀 드리겠습니다.
위와 같은 쿼리가 있는데요 제가 질문을 위해 간단히 작성한 쿼리입니다.
결과를 실행시키면 cd와 path를 볼 수 있는데 path는 당연히 root부터 현 cd 전까지의 경로를 보여주는 컬럼입니다.
이 상태에서 각 row의 path 컬럼에 존재하는 cd가 실제로 상위 행에 몇개나 있는지 알고 싶습니다.
예를 들어 11번 행(cd = 2212)의 path에는 0000,2000,2200,2210 4개의 cd가 존재하는데요
12번 보다 위에 있는 1~11행의 cd 컬럼을 검사해서 실제로 4개중 몇개가 있는건지 알고 싶은 겁니다.
즉 cnt 컬럼을 하나 만들고
5번 행 같은 경우는 path에 있는 3개 cd가 1~4행 안에 모두 존재하므로 cnt가 3이 될 것이고
11번 행은 path의 4개 중 1 ~ 10행에 3개만 존재하므로 cnt가 역시 3이 될 것입니다.
처음에는 분석함수 window절로 행범위를 지정해서 어떻게 해보려고 했는데
어떻게 엮어야 할지 잘 생각이 안나네요...
고수님들 답변 부탁드리겠습니다.
감사합니다.
WITH t AS ( SELECT '0000' cd, '' hcd FROM dual UNION ALL SELECT '1000', '0000' FROM dual UNION ALL SELECT '1100', '1000' FROM dual UNION ALL SELECT '1110', '1100' FROM dual UNION ALL SELECT '1120', '1100' FROM dual UNION ALL SELECT '1130', '1100' FROM dual UNION ALL SELECT '2000', '0000' FROM dual UNION ALL SELECT '2100', '2000' FROM dual UNION ALL SELECT '2200', '2000' FROM dual UNION ALL SELECT '2210', '2200' FROM dual UNION ALL SELECT '2211', '2210' FROM dual UNION ALL SELECT '2212', '2210' FROM dual UNION ALL SELECT '2213', '2210' FROM dual ) SELECT cd , LEVEL AS lv , SUBSTR(REPLACE(SYS_CONNECT_BY_PATH(cd, '-'), '-'||cd, ''), 2) AS path , LEVEL - SIGN(INSTR(SYS_CONNECT_BY_PATH(cd, '-'), '2200')) - 1 cnt_1 , LENGTH(REPLACE(SYS_CONNECT_BY_PATH(cd, '-'), '-'||'2200')) / 5 - 1 cnt_2 FROM t WHERE cd != '2200' START WITH hcd IS NULL CONNECT BY PRIOR cd = hcd ORDER SIBLINGS BY cd ;
/* 01 : 원본질의문에서 ROWNUM과 LISTAGG()를 생성 02 : LISTAGG()의 결과값을 ROWNUM번째 delimiter 기준으로 SUBSTR()하여 검색패턴을 생성 (ROWS/RANGE BETWEEN 효과) 03 : 원본질의문의 PATH에 대하여 생성된 검색패턴으로 REGEXP_COUNT()하여 출현횟수 산출 2019.05.20, Jun H. Lee */ with t as ( select '0000' as cd, '' as hcd from dual union all select '1000' as cd, '0000' as hcd from dual union all select '1100' as cd, '1000' as hcd from dual union all select '1110' as cd, '1100' as hcd from dual union all select '1120' as cd, '1100' as hcd from dual union all select '1130' as cd, '1100' as hcd from dual union all select '2000' as cd, '0000' as hcd from dual union all select '2100' as cd, '2000' as hcd from dual union all select '2200' as cd, '2000' as hcd from dual union all select '2210' as cd, '2200' as hcd from dual union all select '2211' as cd, '2210' as hcd from dual union all select '2212' as cd, '2210' as hcd from dual union all select '2213' as cd, '2210' as hcd from dual ) SELECT INVW2.* , REGEXP_COUNT(PATH, PTRN) AS PTRN_CNT /*패턴출현횟수*/ FROM ( SELECT INVW1.* , NVL(SUBSTR(AGG_CD, 1, INSTR(AGG_CD, '|', 1, RNO)-1), AGG_CD) AS PTRN /*패턴*/ FROM ( SELECT ORG_QRY.* , ROWNUM AS RNO /*행번호*/ , LISTAGG(CD, '|')WITHIN GROUP(ORDER BY 1)OVER() AS AGG_CD /*집계코드*/ FROM ( -------------------------------------------------------------------------------------------------------------------------------- select cd, level as lv, substr(replace(sys_connect_by_path(cd, '-'), '-'||cd, ''),2) as path from t where cd != '2200' start with hcd is null connect by prior cd = hcd order siblings by cd -------------------------------------------------------------------------------------------------------------------------------- ) ORG_QRY /*원본질의문*/ ) INVW1 /*인라인뷰1*/ ) INVW2 /*인라인뷰2*/ ;