상위 행의 내용을 검색하는 쿼리 질문드립니다. 0 2 663

by DB장이 [SQL Query] [2019.05.19 22:24:10]


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절로 행범위를 지정해서 어떻게 해보려고 했는데 

어떻게 엮어야 할지 잘 생각이 안나네요...

 

고수님들 답변 부탁드리겠습니다.

감사합니다.

 

 

by 마농 [2019.05.20 07:50:34]
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
;

 


by 이준환 [2019.05.20 17:12:06]
/*
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*/
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입