꿈꾸는 개발자, DBA 커뮤니티 구루비

속도개선 4

by 커피향 [2014.08.12 10:21:48]

WITH ATABLE AS
(
SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 1 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 2 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 3 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 4 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 5 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 6 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 7 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 8 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 9 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 10 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 11 SEQ, 1 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140101'YMD, '20140101-1' YMDSEQ, 12 SEQ, 1 HVAL from dual
--중략
UNION ALL SELECT '001' vessel,'100002' CREW, '20140131'YMD, '20140131-1' YMDSEQ, 1 SEQ, 0 HVAL from dual
UNION ALL SELECT '001' vessel,'100002' CREW, '20140131'YMD, '20140131-1' YMDSEQ, 2 SEQ, 0 HVAL from dual
)

SELECT ymdseq

, NVL(MAX(LENGTH(REPLACE(x, 'W'))), 0) r6

, NVL(MAX(LENGTH(REPLACE(x, 'R'))), 0) w14

FROM (SELECT ymdseq

--, vessel, crew, ymd, seq

, REGEXP_REPLACE(

REGEXP_REPLACE(

SUBSTR(REPLACE(wm_concat(hval) OVER(ORDER BY ymdseq, seq), ','), -48)

, '0{6,}' , 'R')

, '1{14,}', 'W')

, '[^RW]')

AS x

FROM atable

WHERE vessel = '001'

AND crew = '100002'

AND ymdseq >= '20131227'

AND ymdseq <= '20140131'||'z'

)

WHERE ymdseq >= '20140101'

AND ymdseq <= '20140131'||'z'

GROUP BY ymdseq

ORDER BY ymdseq

;

지난주에 주셨던 wm_concat 함수 이용해서 잘 됩니다.

근데 오라클 10g xe 무료 버젼에서도 돌아가게 해야 한다네요.

10g xe 에서는 wm_concat 함수가 안되는 군요.

9i 함수 xmlagg 를 사용해서 같은 결과를 얻으려면 우째 해야 하는지요.

xmlagg함수는 group by 해서 옆으로 나란히 하던데요.

위 퀴리는 자신을 포함해서 47째 레코드를 옆으로 나란히해야 하는데

제 수준으로 퀴리가 안 만들어 지네요 .

부탁드립니다.

by 마농 [2014.08.12 13:10:00]

-- 1. Self Join 을 통한 Group By 와 XMLAGG
WITH t AS
(
SELECT vessel, crew, ymd, ymdseq, seq, hval
     , ROW_NUMBER() OVER(ORDER BY ymdseq, seq) rn
  FROM atable
 WHERE vessel  = '001'
   AND crew    = '100002'
   AND ymdseq >= '20131227'
   AND ymdseq <= '20140131'||'z'
)
SELECT a.vessel, a.crew, a.ymd, a.ymdseq, a.seq, a.hval
     , XMLAGG(XMLELEMENT(x, b.hval) ORDER BY b.rn).Extract('//text()').getStringVal() x
  FROM t a
     , t b
 WHERE b.rn BETWEEN a.rn - 47 AND a.rn
 GROUP BY a.vessel, a.crew, a.ymd, a.ymdseq, a.seq, a.hval
;

-- 2. 계층쿼리를 이용한 Sys_Connect_By_Path
WITH t AS
(
SELECT vessel, crew, ymd, ymdseq, seq, hval
     , ROW_NUMBER() OVER(ORDER BY ymdseq, seq) rn
  FROM atable
 WHERE vessel  = '001'
   AND crew    = '100002'
   AND ymdseq >= '20131227'
   AND ymdseq <= '20140131'||'z'
)
SELECT vessel, crew, ymd, ymdseq, seq, hval
     , SUBSTR(
       REPLACE(SYS_CONNECT_BY_PATH(hval, '-'), '-')
       , -48) x
  FROM t
 START WITH rn = 1
 CONNECT BY PRIOR rn + 1 = rn
;

by 커피향 [2014.08.12 14:05:28]

마농님 극 찬사를 보내고 싶습니다. 더 좋은 표현이 생각이 안 나는 군요.

감사합니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변