데이터베이스 퀴즈 및 튜닝 문제
안녕하세요. 새해가 밝았네요.
안좋았던 작년 일들은 잊어버리고 새해엔 새출발 새뜻으로 원하시는일 이루시기 바랍니다.
그런 의미로다가.. 쿼리 한문제 내겠습니다 ㅡ.ㅡ
<원본데이터>
| DT | PACKET |
| 20110101050000 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[25]26[26]01[27]02[28]07[29]00[30]01 |
| 20110101051051 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[36]00[37]00[38]00[39]00 |
| 20110101052051 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[33]01[34]72[35]85[36]00[37]00 |
| 20110101053050 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[36]00[37]00[38]00[39]00 |
| 20110101054050 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[36]00[37]00[38]00[39]00 |
| 20110101055050 | [0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04...[20]81[21]00[22]02[23]64[24]04 |
<결과데이터>
| DT | PACKET |
| 20110101050000 | [0]02 |
| 20110101050000 | [1]00 |
| 20110101050000 | [2]00 |
| 20110101050000 | [3]04 |
| …. | …. |
| 20110101050000 | [29]00 |
| 20110101050000 | [30]01 |
| 20110101051051 | [0]02 |
| 20110101051051 | [1]00 |
| 20110101051051 | [2]00 |
| …. | …. |
| 20110101051051 | [39]00 |
| 20110101052051 | [0]02 |
| 20110101052051 | [1]00 |
| ….. | …. |
| 20110101055050 | [24]04 |
패킷데이터를 순서대로 잘라서 위 결과데이터와 같이 나열하시면 됩니다.
※ 샘플데이터가 충분하지 못한것 같습니다.. '[' 기준으로 자르는게 아니라 '[숫자]' 기준으로 자르는겁니다.
샘플데이터엔 없지만 '[1]00[200' 데이터가 있을경우 [1]00. [200 로 나오는게 아니라 [1]00[200으로 나타내야 합니다.
>> 자답
select
dt,
regexp_substr(a.packet,'[^ ]+',1,b.lv) packet
from (
select
dt,
regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1') packet,
regexp_count(packet,'(\[[[:digit:]]{1,}\])') cnt
from test
) a inner join (select level lv
from (select max(regexp_count(packet,'(\[[[:digit:]]{1,}\])')) max_cnt from test)
connect by level <= max_cnt) b
on b.lv <= a.cnt
order by a.dt,b.lv
by v상이v
[2011.01.03 10:22:57]
SELECT DT, SUBSTR(PACKET,V1,V2-V1) V
FROM (
SELECT DT
,REGEXP_INSTR(PACKET, '([[]([^]|[]+)[]])',1,B.LV) V1
,LEAD(REGEXP_INSTR(PACKET, '([[]([^]|[]+)[]])',1,B.LV)) OVER(PARTITION BY DT ORDER BY B.LV) V2
,B.LV
,PACKET
FROM TEST A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) B
)
WHERE V2 != 0
ORDER BY DT,LV
한방에 할수 있을텐데..
정규식에 취약해서...ㅜㅜ
업무좀 보고 후에 다시 해보겠습니다...
FROM (
SELECT DT
,REGEXP_INSTR(PACKET, '([[]([^]|[]+)[]])',1,B.LV) V1
,LEAD(REGEXP_INSTR(PACKET, '([[]([^]|[]+)[]])',1,B.LV)) OVER(PARTITION BY DT ORDER BY B.LV) V2
,B.LV
,PACKET
FROM TEST A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) B
)
WHERE V2 != 0
ORDER BY DT,LV
한방에 할수 있을텐데..
정규식에 취약해서...ㅜㅜ
업무좀 보고 후에 다시 해보겠습니다...
by park1q
[2011.01.03 11:23:45]
select dt, regexp_substr(packet,'([[]\d+[]])+\d+',1,n.no) packet
from t ,
(select level no from dual connect by level <=40) n
where n.no <= length(packet) - length(replace(packet,'['))
order by dt, n.no
from t ,
(select level no from dual connect by level <=40) n
where n.no <= length(packet) - length(replace(packet,'['))
order by dt, n.no
by v상이v
[2011.01.03 15:31:47]
SELECT DT
,REGEXP_SUBSTR(REGEXP_REPLACE(PACKET, '([[]([^]|[]+)[]])',';\1'),'([^;]+)',1,B.LV) V
FROM TEST A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 40) B
ORDER BY A.DT,B.LV
,REGEXP_SUBSTR(REGEXP_REPLACE(PACKET, '([[]([^]|[]+)[]])',';\1'),'([^;]+)',1,B.LV) V
FROM TEST A
,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 40) B
ORDER BY A.DT,B.LV
by xx
[2011.01.03 18:44:04]
상이님, park1q님이 참여해주셨네요.
테스트환경이 안되어서 확인이 안되네요^^
근데.. park1q님 쿼리는 '[숫자]'기준으로 되는게 맞나요?
'[' 갯수로만 카운트를 하면 '[1]00[200' 이런 데이터에서 제대로 나오지 않을꺼 같네요.
테스트환경이 안되어서 확인이 안되네요^^
근데.. park1q님 쿼리는 '[숫자]'기준으로 되는게 맞나요?
'[' 갯수로만 카운트를 하면 '[1]00[200' 이런 데이터에서 제대로 나오지 않을꺼 같네요.
by park1q
[2011.01.04 15:17:18]
[1]00[200 는 처리가 안됩니다
length(replace(packet,'[')) 를 length(replace(packet,']')) 로 바꾸면 처리는 됩니다만 [1]00 으로 출력됩니다. 근데 [1]00[200 이런 데이타가 있을까 하는생각도 드는데..ㅠㅠ
length(replace(packet,'[')) 를 length(replace(packet,']')) 로 바꾸면 처리는 됩니다만 [1]00 으로 출력됩니다. 근데 [1]00[200 이런 데이타가 있을까 하는생각도 드는데..ㅠㅠ
by camela
[2011.01.04 17:13:10]
-- 답안 올려주신거에서 카운트 구하는 부분만 틀리게 풀어봤습니다.
-- regexp_count() 는 11g 이상에서 사용가능한거 같은데 그 함수 쓰지 않고
-- 카운트를 해봤습니다.
select a.dt
, a.packet
, regexp_substr(a.packet,'([^#]+)',1,rownum) as packet
from (select dt
, regexp_replace(packet,'(\[[[:digit:]]+\])','#\1') as packet
, length(regexp_replace(regexp_replace(packet,'\[[[:digit:]]+\]','#'),'[^#]','')) as cnt
from test) a
,(select level as no
from dual
connect by level <= 100) b
where b.no <= a.cnt
;
-- regexp_count() 는 11g 이상에서 사용가능한거 같은데 그 함수 쓰지 않고
-- 카운트를 해봤습니다.
select a.dt
, a.packet
, regexp_substr(a.packet,'([^#]+)',1,rownum) as packet
from (select dt
, regexp_replace(packet,'(\[[[:digit:]]+\])','#\1') as packet
, length(regexp_replace(regexp_replace(packet,'\[[[:digit:]]+\]','#'),'[^#]','')) as cnt
from test) a
,(select level as no
from dual
connect by level <= 100) b
where b.no <= a.cnt
;
by xx
[2011.01.04 22:06:09]
10g 다뤄볼 기회가 없어서 테스트를 못해봤는데 확인해주셔서 감사합니다^^
regexp_count는 11g에 새로 추가된거군요..
regexp_count는 11g에 새로 추가된거군요..
by xx
[2011.01.04 22:16:26]
그리고 park1q님 샘플데이터에 '[1]00[200' 이런 자료는 없습니다.
문제출제 의도가 regexp를 응용해보자.. 이기 때문에
'['기준으로 데이터를 잘라낸다면 굳이 regexp를 사용할 필요가 없어집니다.
replace와 substr만 사용하면 되니까요.
그래서 문제 아래에다가 '['기준이 아닌 '[숫자]' 단서를 추가한겁니다.
기존 특정문자(예를 들어 '[')를 기준으로 데이터를 처리하던것을 regexp함수를 사용함으로써 특정문자에서 일정한패턴으로도 처리할수 있다는걸 보여준겁니다..
※ 저도 공부하는겸해서 regexp함수와 분석함수를 응용한 문제를 많이 내도록 하겠습니다. 감사합니다.
문제출제 의도가 regexp를 응용해보자.. 이기 때문에
'['기준으로 데이터를 잘라낸다면 굳이 regexp를 사용할 필요가 없어집니다.
replace와 substr만 사용하면 되니까요.
그래서 문제 아래에다가 '['기준이 아닌 '[숫자]' 단서를 추가한겁니다.
기존 특정문자(예를 들어 '[')를 기준으로 데이터를 처리하던것을 regexp함수를 사용함으로써 특정문자에서 일정한패턴으로도 처리할수 있다는걸 보여준겁니다..
※ 저도 공부하는겸해서 regexp함수와 분석함수를 응용한 문제를 많이 내도록 하겠습니다. 감사합니다.
by gsjung
[2011.07.29 13:58:15]
- regexp_count() 를 사용할수 없을때
(원래, regexp 함수를 사용하지 않고 처리하는 것을 고민했는데 regexp_replace()를 SQL로 처리하는 게 아직 마무리 되지 않아서 우선 regexp_count() 를 사용하지 않았을 경우를 찾아보았습니다.)
SELECT A.DT
,SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1) PAKET
FROM
(
SELECT A.DT,A.PKT,A.CNT
,B.RNO
,A.LEN
/*
PKT 값에서 ' ' 문자의 각 시작위치를 추적하여 값을 구분한다.
항목 시작위치
RNO=1 이면 PKT문자열의 1번째 문자 시작위치+0 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,1)+0,1)
RNO=2 이면 PKT문자열의 1번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,1)+1,1)
RNO=3 이면 PKT문자열의 2번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,2)+1,1)
RNO=4 이면 PKT문자열의 3번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,3)+1,1)
RNO=5 이면 PKT문자열의 4번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,4)+1,1)
...
RNO=α 이면 PKT문자열의 (α-1)번째 문자 시작위치+1 에서 시작해서 1번째 위치 : INSTR(PKT,' ',INSTR(PKT,' ',1,α-1)+1,1)
아래 SPOS에서는 값을 시작위치를 찾는 것이기 때문에 INSTR(PKT,' ',INSTR(PKT,' ',1,α-1)+1,1)+1 로 하였다.
항목 종료위치
RNO=1 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 2번째 위치 -> INSTR(PKT,' ',1,2)
RNO=2 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 3번째 위치 -> INSTR(PKT,' ',1,3)
...
RNO=α 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 (α+1)번째 위치 : INSTR(PKT,' ',1,(α+1))
하지만 하나의 DT에 대하여 RNO 마지막 값이 δ 일때에 α가 마지막값 δ이면 δ＞α+1 되므로 문자열 범위를 넘어선다.
δ=α 이면 INSTR(PKT,' ',1,(+1)) 는 0 나오게 된다.
따라서 결과가 0이 나오면 LENGTH(PKT)을 취한다.
*/
,INSTR(PKT,' ',INSTR(PKT,' ',1,(CASE WHEN B.RNO-1 = 0 THEN 1 ELSE B.RNO-1 END))+(CASE WHEN B.RNO = 1 THEN 0 ELSE 1 END),1)+1 SPOS
,(CASE WHEN INSTR(PKT,' ',1,B.RNO+1) = 0 THEN LENGTH(PKT) ELSE INSTR(PKT,' ',1,B.RNO+1)-1 END) EPOS
FROM
(
SELECT DT,PACKET
,regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1') PKT
,LENGTH(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1')) LEN
,(
LENGTH(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1'))
-LENGTH(REPLACE(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1'),' ' ,''))
) CNT
FROM
(
SELECT '20110101040000' DT,'[0]02[1]00[2]00[3]04[4]02[5]11[6]00[7]04[8]05[9]05' PACKET FROM DUAL UNION ALL
SELECT '20110101050000' DT,'[0]02[1]00[2]00[3]04[200[4]02[5]00[6]00[7]04[8]05[9]05[10]05[11]06[12]07[13]07[14]07[15]07[16]07[17]07[18]07[19]08[20]08[21]08[22]08[23]08[24]08[25]11[26]02[27]02[28]07[29]03[30]01' PACKET FROM DUAL UNION ALL
SELECT '20110101051051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]04[10]03[11]03[12]02[13]01[14]07[15]05[16]04[17]04[18]04[19]03[20]08[21]09[22]01[23]14[24]21[25]22[26]11[27]06[28]08[29]08[30]01[30]39[30]71[30]01[30]21[30]43[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101052051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]01[9]02[10]02[11]04[12]03[13]03[14]01[15]06[16]08[17]01[18]01[19]02[20]02[21]01[22]07[23]05[24]05[25]08[26]01[27]02[28]04[29]00[30]01[30]07[30]01[33]01[34]72[35]85[36]00[37]00' PACKET FROM DUAL UNION ALL
SELECT '20110101053050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]09[9]09[10]09[11]09[12]08[13]08[14]08[15]08[16]05[17]03[18]02[19]07[20]06[21]06[22]06[23]06[24]02[25]12[26]05[27]07[28]05[29]09[30]07[30]06[30]05[30]04[30]03[30]03[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101054050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]03[10]04[11]08[12]55[13]82[14]65[15]72[16]44[17]69[18]83[19]23[20]04[21]65[22]98[23]04[24]04[25]26[26]01[27]66[28]77[29]04[30]53[30]64[30]68[30]02[30]23[30]91[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101055050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]72[8]38[9]03[10]87[11]62[12]34[13]76[14]84[15]74[16]21[17]43[18]14[19]34[20]81[21]00[22]02[23]64[24]04' PACKET FROM DUAL
)
) A
,(SELECT LEVEL RNO FROM DUAL CONNECT BY LEVEL <= 100) B
WHERE A.CNT >= B.RNO
) A
ORDER BY 1,2
;
(원래, regexp 함수를 사용하지 않고 처리하는 것을 고민했는데 regexp_replace()를 SQL로 처리하는 게 아직 마무리 되지 않아서 우선 regexp_count() 를 사용하지 않았을 경우를 찾아보았습니다.)
SELECT A.DT
,SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1) PAKET
FROM
(
SELECT A.DT,A.PKT,A.CNT
,B.RNO
,A.LEN
/*
PKT 값에서 ' ' 문자의 각 시작위치를 추적하여 값을 구분한다.
항목 시작위치
RNO=1 이면 PKT문자열의 1번째 문자 시작위치+0 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,1)+0,1)
RNO=2 이면 PKT문자열의 1번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,1)+1,1)
RNO=3 이면 PKT문자열의 2번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,2)+1,1)
RNO=4 이면 PKT문자열의 3번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,3)+1,1)
RNO=5 이면 PKT문자열의 4번째 문자 시작위치+1 에서 시작해서 1번째 위치 -> INSTR(PKT,' ',INSTR(PKT,' ',1,4)+1,1)
...
RNO=α 이면 PKT문자열의 (α-1)번째 문자 시작위치+1 에서 시작해서 1번째 위치 : INSTR(PKT,' ',INSTR(PKT,' ',1,α-1)+1,1)
아래 SPOS에서는 값을 시작위치를 찾는 것이기 때문에 INSTR(PKT,' ',INSTR(PKT,' ',1,α-1)+1,1)+1 로 하였다.
항목 종료위치
RNO=1 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 2번째 위치 -> INSTR(PKT,' ',1,2)
RNO=2 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 3번째 위치 -> INSTR(PKT,' ',1,3)
...
RNO=α 이면 PKT문자열의 1번째 문자 시작위치에서 시작해서 (α+1)번째 위치 : INSTR(PKT,' ',1,(α+1))
하지만 하나의 DT에 대하여 RNO 마지막 값이 δ 일때에 α가 마지막값 δ이면 δ＞α+1 되므로 문자열 범위를 넘어선다.
δ=α 이면 INSTR(PKT,' ',1,(+1)) 는 0 나오게 된다.
따라서 결과가 0이 나오면 LENGTH(PKT)을 취한다.
*/
,INSTR(PKT,' ',INSTR(PKT,' ',1,(CASE WHEN B.RNO-1 = 0 THEN 1 ELSE B.RNO-1 END))+(CASE WHEN B.RNO = 1 THEN 0 ELSE 1 END),1)+1 SPOS
,(CASE WHEN INSTR(PKT,' ',1,B.RNO+1) = 0 THEN LENGTH(PKT) ELSE INSTR(PKT,' ',1,B.RNO+1)-1 END) EPOS
FROM
(
SELECT DT,PACKET
,regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1') PKT
,LENGTH(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1')) LEN
,(
LENGTH(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1'))
-LENGTH(REPLACE(regexp_replace(packet,'(\[[[:digit:]]{1,}\])',' \1'),' ' ,''))
) CNT
FROM
(
SELECT '20110101040000' DT,'[0]02[1]00[2]00[3]04[4]02[5]11[6]00[7]04[8]05[9]05' PACKET FROM DUAL UNION ALL
SELECT '20110101050000' DT,'[0]02[1]00[2]00[3]04[200[4]02[5]00[6]00[7]04[8]05[9]05[10]05[11]06[12]07[13]07[14]07[15]07[16]07[17]07[18]07[19]08[20]08[21]08[22]08[23]08[24]08[25]11[26]02[27]02[28]07[29]03[30]01' PACKET FROM DUAL UNION ALL
SELECT '20110101051051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]04[10]03[11]03[12]02[13]01[14]07[15]05[16]04[17]04[18]04[19]03[20]08[21]09[22]01[23]14[24]21[25]22[26]11[27]06[28]08[29]08[30]01[30]39[30]71[30]01[30]21[30]43[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101052051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]01[9]02[10]02[11]04[12]03[13]03[14]01[15]06[16]08[17]01[18]01[19]02[20]02[21]01[22]07[23]05[24]05[25]08[26]01[27]02[28]04[29]00[30]01[30]07[30]01[33]01[34]72[35]85[36]00[37]00' PACKET FROM DUAL UNION ALL
SELECT '20110101053050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]09[9]09[10]09[11]09[12]08[13]08[14]08[15]08[16]05[17]03[18]02[19]07[20]06[21]06[22]06[23]06[24]02[25]12[26]05[27]07[28]05[29]09[30]07[30]06[30]05[30]04[30]03[30]03[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101054050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]03[10]04[11]08[12]55[13]82[14]65[15]72[16]44[17]69[18]83[19]23[20]04[21]65[22]98[23]04[24]04[25]26[26]01[27]66[28]77[29]04[30]53[30]64[30]68[30]02[30]23[30]91[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101055050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]72[8]38[9]03[10]87[11]62[12]34[13]76[14]84[15]74[16]21[17]43[18]14[19]34[20]81[21]00[22]02[23]64[24]04' PACKET FROM DUAL
)
) A
,(SELECT LEVEL RNO FROM DUAL CONNECT BY LEVEL <= 100) B
WHERE A.CNT >= B.RNO
) A
ORDER BY 1,2
;
by gsjung
[2011.07.29 16:51:13]
/*
REGEXP 사용하지 않을 경우 입니다.
중간 집합에 컬럼들을 그대로 두었으니 중간집합의 데이터도 같이 확인하시면 됩니다.
중요한 부분에 대해서는 주석을 참조하세요.
궁금한 사항은 kyusu74 골뱅이 네이버닷컴 로 하시면 됩니다
*/
SELECT DT,PACKET,MIN(SPOS) SPOS, MAX(EPOS) EPOS
,SUBSTR(PACKET,MIN(SPOS), MAX(EPOS)-MIN(SPOS)+1) PK
FROM
(
SELECT DT,PACKET,LEN,CNT,RNO,SPOS,EPOS
,POS
/*
패턴인식 블록에서 패턴을 인식하였으면 아래와 같이 만들어진다.
RNO=3인 경우 POS=0 인 것은 RNO=2 에 포함되어야 한다는 것이다.
따라서 GP 값을 만들어서 다음 SQL 블록에서 GROUP BY 를 통하여 SP(시작위치),EP(종료위치) 의 값을 조정한다.
RNO SP EP POS GP
1 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 1 1 6 1 1
2 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 2 7 13 2 2
3 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 3 14 16 0 2
4 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 4 17 22 4 4
5 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 5 23 28 5 5
6 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 6 29 32 0 5
7 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 7 33 35 0 5
8 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 8 36 41 8 8
9 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 9 42 47 9 9
10 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 10 48 53 10 10
11 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 11 54 59 11 11
12 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 12 60 65 12 12
13 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 13 66 70 13 13
*/
,MAX(POS) OVER (PARTITION BY DT,PACKET ORDER BY RNO,SPOS) GP
FROM
(
/* 패턴 인식 SQL 블록 */
SELECT A.DT,A.PACKET,A.LEN,A.CNT,A.RNO,SPOS,EPOS
,SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1) VAL
,UPPER(REPLACE(REPLACE(REPLACE(SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1),'AN','an'),'BN','bn'),'N','')) CHK
/*
패턴 문자열에서 AN 으로 시작하는 것으로 문자열을 CNT 만큼의 ROW 로 나누었다.
그리고 [:digit] 에 대한 패턴을 판단한다.
예를 들어 DT='20110101040000' 에 대한 PACKET 값을 보면 '[00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05' 값이다.
PACKET
따라서 'N'을 바로 제거 할수 없고
'A' (='[') 로 시작하고 숫자가 존재하는 경우 'an' 으로 변경하고
'B' (=']') 로 시작하면서 다음에 숫자가 존재하는 경우 'bn'으로 변경한다.
그리고 나서 N을 제거한 다음 UPPER 로 변경하면
'[:digit]'에 대한 패턴을 인식하기 위해 'ANB'가 존재하는 지 확인하면 된다.
존재하면 RNO 를 존재하지 않으면 0 설정하고 다름 SQL 블록에서 GROUP BY 할 값을 만든다.
0인 것은 실제 시작 위치가 아니었기 때문에 이전 ROW에 포함되어야한다.
*/
,DECODE(
INSTR(
UPPER(
REPLACE(
REPLACE(
REPLACE(
SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1)
,'AN','an'
)
,'BN','bn')
,'N',''
)
)
,'ANB',1)
,0,0,RNO
) POS
FROM
(
SELECT A.DT,A.PACKET,A.LEN,A.CNT,A.PKT,B.RNO
/* 예상 시작위치와 예상 종료위치를 구한다 */
,INSTR(PKT,'A',INSTR(PKT,'A',1,(CASE WHEN B.RNO-1 = 0 THEN 1 ELSE B.RNO-1 END))+(CASE WHEN B.RNO = 1 THEN 0 ELSE 1 END),1) SPOS
,(CASE WHEN INSTR(PKT,'A',1,B.RNO+1) = 0 THEN LENGTH(PKT) ELSE INSTR(PKT,'A',1,B.RNO+1)-1 END) EPOS
FROM
(
/*
PACKET 길이와 예상 시각 위치의 개수를 구한다.
*/
SELECT DT,PACKET
,PATTERN_STR PKT
,LENGTH(PATTERN_STR) LEN
,LENGTH(PATTERN_STR) - LENGTH(REPLACE(PATTERN_STR,'A','')) CNT /* 예상 시작위치 */
--,UPPER(REPLACE(REPLACE(REPLACE(PATTERN_STR,'AN','an'),'BN','bn'),'N','0')) CHK2
FROM
(
/*
PACKET 에서 패턴값을 생성한다.
'['->'A', 숫자 -> 'N', ']'->'B'
*/
SELECT DT
,PACKET
,TRANSLATE(PACKET,'[0123456789]','ANNNNNNNNNNB') PATTERN_STR
FROM
(
SELECT '20110101040000' DT,'[00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05' PACKET FROM DUAL UNION ALL
SELECT '20110101050000' DT,'[0]02[1]00[2]00[3]04[200[4]02[5]00[6]00[7]04[8]05[9]05[10]05[11]06[12]07[13]07[14]07[15]07[16]07[17]07[18]07[19]08[20]08[21]08[22]08[23]08[24]08[25]11[26]02[27]02[28]07[29]03[30]01' PACKET FROM DUAL UNION ALL
SELECT '20110101051051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]04[10]03[11]03[12]02[13]01[14]07[15]05[16]04[17]04[18]04[19]03[20]08[21]09[22]01[23]14[24]21[25]22[26]11[27]06[28]08[29]08[30]01[30]39[30]71[30]01[30]21[30]43[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101052051' DT,'[0]02[1]00[2]00[3]04]][4]02[5]00[6]00[7]04[8]01[9]02[10]02[11]04[12]03[13]03[14]01[15]06[16]08[17]01[18]01[19]02[20]02[21]01[22]07[23]05[24]05[25]08[26]01[27]02[28]04[29]00[30]01[30]07[30]01[33]01[34]72[35]85[36]00[37]00' PACKET FROM DUAL UNION ALL
SELECT '20110101053050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]09[9]09[10]09[11]09[12]08[13]08[14]08[15]08[16]05[17]03[18]02[19]07[20]06[21]06[22]06[23]06[24]02[25]12[26]05[27]07[28]05[29]09[30]07[30]06[30]05[30]04[30]03[30]03[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101054050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]03[10]04[11]08[12]55[13]82[14]65[15]72[16]44[17]69[18]83[19]23[20]04[21]65[22]98[23]04[24]04[25]26[26]01[27]66[28]77[29]04[30]53[30]64[30]68[30]02[30]23[30]91[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101055050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]72[8]38[9]03[10]87[11]62[12]34[13]76[14]84[15]74[16]21[17]43[18]14[19]34[20]81[21]00[22]02[23]64[24]04' PACKET FROM DUAL
) A
)
) A
,(SELECT LEVEL RNO FROM DUAL CONNECT BY LEVEL <= 100) B
WHERE A.CNT >= B.RNO
) A
-- ORDER BY DT,RNO
)
)
GROUP BY DT,PACKET,GP
ORDER BY 1,2,3
;
REGEXP 사용하지 않을 경우 입니다.
중간 집합에 컬럼들을 그대로 두었으니 중간집합의 데이터도 같이 확인하시면 됩니다.
중요한 부분에 대해서는 주석을 참조하세요.
궁금한 사항은 kyusu74 골뱅이 네이버닷컴 로 하시면 됩니다
*/
SELECT DT,PACKET,MIN(SPOS) SPOS, MAX(EPOS) EPOS
,SUBSTR(PACKET,MIN(SPOS), MAX(EPOS)-MIN(SPOS)+1) PK
FROM
(
SELECT DT,PACKET,LEN,CNT,RNO,SPOS,EPOS
,POS
/*
패턴인식 블록에서 패턴을 인식하였으면 아래와 같이 만들어진다.
RNO=3인 경우 POS=0 인 것은 RNO=2 에 포함되어야 한다는 것이다.
따라서 GP 값을 만들어서 다음 SQL 블록에서 GROUP BY 를 통하여 SP(시작위치),EP(종료위치) 의 값을 조정한다.
RNO SP EP POS GP
1 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 1 1 6 1 1
2 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 2 7 13 2 2
3 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 3 14 16 0 2
4 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 4 17 22 4 4
5 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 5 23 28 5 5
6 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 6 29 32 0 5
7 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 7 33 35 0 5
8 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 8 36 41 8 8
9 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 9 42 47 9 9
10 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 10 48 53 10 10
11 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 11 54 59 11 11
12 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 12 60 65 12 12
13 20110101040000 [00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05 13 66 70 13 13
*/
,MAX(POS) OVER (PARTITION BY DT,PACKET ORDER BY RNO,SPOS) GP
FROM
(
/* 패턴 인식 SQL 블록 */
SELECT A.DT,A.PACKET,A.LEN,A.CNT,A.RNO,SPOS,EPOS
,SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1) VAL
,UPPER(REPLACE(REPLACE(REPLACE(SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1),'AN','an'),'BN','bn'),'N','')) CHK
/*
패턴 문자열에서 AN 으로 시작하는 것으로 문자열을 CNT 만큼의 ROW 로 나누었다.
그리고 [:digit] 에 대한 패턴을 판단한다.
예를 들어 DT='20110101040000' 에 대한 PACKET 값을 보면 '[00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05' 값이다.
PACKET
따라서 'N'을 바로 제거 할수 없고
'A' (='[') 로 시작하고 숫자가 존재하는 경우 'an' 으로 변경하고
'B' (=']') 로 시작하면서 다음에 숫자가 존재하는 경우 'bn'으로 변경한다.
그리고 나서 N을 제거한 다음 UPPER 로 변경하면
'[:digit]'에 대한 패턴을 인식하기 위해 'ANB'가 존재하는 지 확인하면 된다.
존재하면 RNO 를 존재하지 않으면 0 설정하고 다름 SQL 블록에서 GROUP BY 할 값을 만든다.
0인 것은 실제 시작 위치가 아니었기 때문에 이전 ROW에 포함되어야한다.
*/
,DECODE(
INSTR(
UPPER(
REPLACE(
REPLACE(
REPLACE(
SUBSTR(A.PKT,A.SPOS,A.EPOS-A.SPOS+1)
,'AN','an'
)
,'BN','bn')
,'N',''
)
)
,'ANB',1)
,0,0,RNO
) POS
FROM
(
SELECT A.DT,A.PACKET,A.LEN,A.CNT,A.PKT,B.RNO
/* 예상 시작위치와 예상 종료위치를 구한다 */
,INSTR(PKT,'A',INSTR(PKT,'A',1,(CASE WHEN B.RNO-1 = 0 THEN 1 ELSE B.RNO-1 END))+(CASE WHEN B.RNO = 1 THEN 0 ELSE 1 END),1) SPOS
,(CASE WHEN INSTR(PKT,'A',1,B.RNO+1) = 0 THEN LENGTH(PKT) ELSE INSTR(PKT,'A',1,B.RNO+1)-1 END) EPOS
FROM
(
/*
PACKET 길이와 예상 시각 위치의 개수를 구한다.
*/
SELECT DT,PACKET
,PATTERN_STR PKT
,LENGTH(PATTERN_STR) LEN
,LENGTH(PATTERN_STR) - LENGTH(REPLACE(PATTERN_STR,'A','')) CNT /* 예상 시작위치 */
--,UPPER(REPLACE(REPLACE(REPLACE(PATTERN_STR,'AN','an'),'BN','bn'),'N','0')) CHK2
FROM
(
/*
PACKET 에서 패턴값을 생성한다.
'['->'A', 숫자 -> 'N', ']'->'B'
*/
SELECT DT
,PACKET
,TRANSLATE(PACKET,'[0123456789]','ANNNNNNNNNNB') PATTERN_STR
FROM
(
SELECT '20110101040000' DT,'[00]02[01]00][09[02]00[03]04[]00[98[04]02[05]11[06]00[07]04[08]05[9]05' PACKET FROM DUAL UNION ALL
SELECT '20110101050000' DT,'[0]02[1]00[2]00[3]04[200[4]02[5]00[6]00[7]04[8]05[9]05[10]05[11]06[12]07[13]07[14]07[15]07[16]07[17]07[18]07[19]08[20]08[21]08[22]08[23]08[24]08[25]11[26]02[27]02[28]07[29]03[30]01' PACKET FROM DUAL UNION ALL
SELECT '20110101051051' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]04[10]03[11]03[12]02[13]01[14]07[15]05[16]04[17]04[18]04[19]03[20]08[21]09[22]01[23]14[24]21[25]22[26]11[27]06[28]08[29]08[30]01[30]39[30]71[30]01[30]21[30]43[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101052051' DT,'[0]02[1]00[2]00[3]04]][4]02[5]00[6]00[7]04[8]01[9]02[10]02[11]04[12]03[13]03[14]01[15]06[16]08[17]01[18]01[19]02[20]02[21]01[22]07[23]05[24]05[25]08[26]01[27]02[28]04[29]00[30]01[30]07[30]01[33]01[34]72[35]85[36]00[37]00' PACKET FROM DUAL UNION ALL
SELECT '20110101053050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]09[9]09[10]09[11]09[12]08[13]08[14]08[15]08[16]05[17]03[18]02[19]07[20]06[21]06[22]06[23]06[24]02[25]12[26]05[27]07[28]05[29]09[30]07[30]06[30]05[30]04[30]03[30]03[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101054050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]04[8]05[9]03[10]04[11]08[12]55[13]82[14]65[15]72[16]44[17]69[18]83[19]23[20]04[21]65[22]98[23]04[24]04[25]26[26]01[27]66[28]77[29]04[30]53[30]64[30]68[30]02[30]23[30]91[36]00[37]00[38]00[39]00' PACKET FROM DUAL UNION ALL
SELECT '20110101055050' DT,'[0]02[1]00[2]00[3]04[4]02[5]00[6]00[7]72[8]38[9]03[10]87[11]62[12]34[13]76[14]84[15]74[16]21[17]43[18]14[19]34[20]81[21]00[22]02[23]64[24]04' PACKET FROM DUAL
) A
)
) A
,(SELECT LEVEL RNO FROM DUAL CONNECT BY LEVEL <= 100) B
WHERE A.CNT >= B.RNO
) A
-- ORDER BY DT,RNO
)
)
GROUP BY DT,PACKET,GP
ORDER BY 1,2,3
;
) 버튼을 클릭하여 작성 하시면 됩니다.