적고 쓰고 끄적이며 남기자

컬럼이 400개 이하라는 가정 하에...

오너명 테이블명1 컬럼명1

오너명 테이블명1 컬럼명2

오너명 테이블명1 컬럼명3

오너명 테이블명1 컬럼명4

오너명 테이블명2 컬럼명1

오너명 테이블명2 컬럼명2

오너명 테이블명2 컬럼명3

오너명 테이블명2 컬럼명4

오너명 테이블명2 컬럼명5

오너명 테이블명2 컬럼명6

.

.

.

이런 걸

오너명 테이블명1 컬럼명1 컬럼명2 컬럼명3 컬럼명4

오너명 테이블명2 컬럼명1 컬럼명2 컬럼명3 컬럼명4 컬럼명5 컬럼명6

이렇게 나오게 하는 예제

WITH COLUMNS AS
(
SELECT OWNER, TABLE_NAME, COLUMN_NAME||' '|| CASE WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE = 0 THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NOT NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NULL THEN DATA_TYPE
                                                  ELSE DATA_TYPE || '(' || DATA_LENGTH || ')'
                                                  END AS COLUMN_NAME
, COLUMN_ID
FROM DBA_TAB_COLS
WHERE OWNER = '오너다'
)
SELECT * FROM COLUMNS
PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1, 2 AS COL2, 3 AS COL3, 4 AS COL4, 5 AS COL5, 6 AS COL6, 7 AS COL7, 8 AS COL8, 9 AS COL9, 10 AS COL10, 11 AS COL11, 12 AS COL12, 13 AS COL13, 14 AS COL14, 15 AS COL15, 16 AS COL16, 17 AS COL17, 18 AS COL18, 19 AS COL19, 20 AS COL20, 21 AS COL21, 22 AS COL22, 23 AS COL23, 24 AS COL24, 25 AS COL25, 26 AS COL26, 27 AS COL27, 28 AS COL28, 29 AS COL29, 30 AS COL30, 31 AS COL31, 32 AS COL32, 33 AS COL33, 34 AS COL34, 35 AS COL35, 36 AS COL36, 37 AS COL37, 38 AS COL38, 39 AS COL39, 40 AS COL40, 41 AS COL41, 42 AS COL42, 43 AS COL43, 44 AS COL44, 45 AS COL45, 46 AS COL46, 47 AS COL47, 48 AS COL48, 49 AS COL49, 50 AS COL50, 51 AS COL51, 52 AS COL52, 53 AS COL53, 54 AS COL54, 55 AS COL55, 56 AS COL56, 57 AS COL57, 58 AS COL58, 59 AS COL59, 60 AS COL60, 61 AS COL61, 62 AS COL62, 63 AS COL63, 64 AS COL64, 65 AS COL65, 66 AS COL66, 67 AS COL67, 68 AS COL68, 69 AS COL69, 70 AS COL70, 71 AS COL71, 72 AS COL72, 73 AS COL73, 74 AS COL74, 75 AS COL75, 76 AS COL76, 77 AS COL77, 78 AS COL78, 79 AS COL79, 80 AS COL80, 81 AS COL81, 82 AS COL82, 83 AS COL83, 84 AS COL84, 85 AS COL85, 86 AS COL86, 87 AS COL87, 88 AS COL88, 89 AS COL89, 90 AS COL90, 91 AS COL91, 92 AS COL92, 93 AS COL93, 94 AS COL94, 95 AS COL95, 96 AS COL96, 97 AS COL97, 98 AS COL98, 99 AS COL99, 100 AS COL100, 101 AS COL101, 102 AS COL102, 103 AS COL103, 104 AS COL104, 105 AS COL105, 106 AS COL106, 107 AS COL107, 108 AS COL108, 109 AS COL109, 110 AS COL110, 111 AS COL111, 112 AS COL112, 113 AS COL113, 114 AS COL114, 115 AS COL115, 116 AS COL116, 117 AS COL117, 118 AS COL118, 119 AS COL119, 120 AS COL120, 121 AS COL121, 122 AS COL122, 123 AS COL123, 124 AS COL124, 125 AS COL125, 126 AS COL126, 127 AS COL127, 128 AS COL128, 129 AS COL129, 130 AS COL130, 131 AS COL131, 132 AS COL132, 133 AS COL133, 134 AS COL134, 135 AS COL135, 136 AS COL136, 137 AS COL137, 138 AS COL138, 139 AS COL139, 140 AS COL140, 141 AS COL141, 142 AS COL142, 143 AS COL143, 144 AS COL144, 145 AS COL145, 146 AS COL146, 147 AS COL147, 148 AS COL148, 149 AS COL149, 150 AS COL150, 151 AS COL151, 152 AS COL152, 153 AS COL153, 154 AS COL154, 155 AS COL155, 156 AS COL156, 157 AS COL157, 158 AS COL158, 159 AS COL159, 160 AS COL160, 161 AS COL161, 162 AS COL162, 163 AS COL163, 164 AS COL164, 165 AS COL165, 166 AS COL166, 167 AS COL167, 168 AS COL168, 169 AS COL169, 170 AS COL170, 171 AS COL171, 172 AS COL172, 173 AS COL173, 174 AS COL174, 175 AS COL175, 176 AS COL176, 177 AS COL177, 178 AS COL178, 179 AS COL179, 180 AS COL180, 181 AS COL181, 182 AS COL182, 183 AS COL183, 184 AS COL184, 185 AS COL185, 186 AS COL186, 187 AS COL187, 188 AS COL188, 189 AS COL189, 190 AS COL190, 191 AS COL191, 192 AS COL192, 193 AS COL193, 194 AS COL194, 195 AS COL195, 196 AS COL196, 197 AS COL197, 198 AS COL198, 199 AS COL199, 200 AS COL200, 201 AS COL201, 202 AS COL202, 203 AS COL203, 204 AS COL204, 205 AS COL205, 206 AS COL206, 207 AS COL207, 208 AS COL208, 209 AS COL209, 210 AS COL210, 211 AS COL211, 212 AS COL212, 213 AS COL213, 214 AS COL214, 215 AS COL215, 216 AS COL216, 217 AS COL217, 218 AS COL218, 219 AS COL219, 220 AS COL220, 221 AS COL221, 222 AS COL222, 223 AS COL223, 224 AS COL224, 225 AS COL225, 226 AS COL226, 227 AS COL227, 228 AS COL228, 229 AS COL229, 230 AS COL230, 231 AS COL231, 232 AS COL232, 233 AS COL233, 234 AS COL234, 235 AS COL235, 236 AS COL236, 237 AS COL237, 238 AS COL238, 239 AS COL239, 240 AS COL240, 241 AS COL241, 242 AS COL242, 243 AS COL243, 244 AS COL244, 245 AS COL245, 246 AS COL246, 247 AS COL247, 248 AS COL248, 249 AS COL249, 250 AS COL250, 251 AS COL251, 252 AS COL252, 253 AS COL253, 254 AS COL254, 255 AS COL255, 256 AS COL256, 257 AS COL257, 258 AS COL258, 259 AS COL259, 260 AS COL260, 261 AS COL261, 262 AS COL262, 263 AS COL263, 264 AS COL264, 265 AS COL265, 266 AS COL266, 267 AS COL267, 268 AS COL268, 269 AS COL269, 270 AS COL270, 271 AS COL271, 272 AS COL272, 273 AS COL273, 274 AS COL274, 275 AS COL275, 276 AS COL276, 277 AS COL277, 278 AS COL278, 279 AS COL279, 280 AS COL280, 281 AS COL281, 282 AS COL282, 283 AS COL283, 284 AS COL284, 285 AS COL285, 286 AS COL286, 287 AS COL287, 288 AS COL288, 289 AS COL289, 290 AS COL290, 291 AS COL291, 292 AS COL292, 293 AS COL293, 294 AS COL294, 295 AS COL295, 296 AS COL296, 297 AS COL297, 298 AS COL298, 299 AS COL299, 300 AS COL300, 301 AS COL301, 302 AS COL302, 303 AS COL303, 304 AS COL304, 305 AS COL305, 306 AS COL306, 307 AS COL307, 308 AS COL308, 309 AS COL309, 310 AS COL310, 311 AS COL311, 312 AS COL312, 313 AS COL313, 314 AS COL314, 315 AS COL315, 316 AS COL316, 317 AS COL317, 318 AS COL318, 319 AS COL319, 320 AS COL320, 321 AS COL321, 322 AS COL322, 323 AS COL323, 324 AS COL324, 325 AS COL325, 326 AS COL326, 327 AS COL327, 328 AS COL328, 329 AS COL329, 330 AS COL330, 331 AS COL331, 332 AS COL332, 333 AS COL333, 334 AS COL334, 335 AS COL335, 336 AS COL336, 337 AS COL337, 338 AS COL338, 339 AS COL339, 340 AS COL340, 341 AS COL341, 342 AS COL342, 343 AS COL343, 344 AS COL344, 345 AS COL345, 346 AS COL346, 347 AS COL347, 348 AS COL348, 349 AS COL349, 350 AS COL350, 351 AS COL351, 352 AS COL352, 353 AS COL353, 354 AS COL354, 355 AS COL355, 356 AS COL356, 357 AS COL357, 358 AS COL358, 359 AS COL359, 360 AS COL360, 361 AS COL361, 362 AS COL362, 363 AS COL363, 364 AS COL364, 365 AS COL365, 366 AS COL366, 367 AS COL367, 368 AS COL368, 369 AS COL369, 370 AS COL370, 371 AS COL371, 372 AS COL372, 373 AS COL373, 374 AS COL374, 375 AS COL375, 376 AS COL376, 377 AS COL377, 378 AS COL378, 379 AS COL379, 380 AS COL380, 381 AS COL381, 382 AS COL382, 383 AS COL383, 384 AS COL384, 385 AS COL385, 386 AS COL386, 387 AS COL387, 388 AS COL388, 389 AS COL389, 390 AS COL390, 391 AS COL391, 392 AS COL392, 393 AS COL393, 394 AS COL394, 395 AS COL395, 396 AS COL396, 397 AS COL397, 398 AS COL398, 399 AS COL399, 400 AS COL400)
    )

잘 해석을 해보면

PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1

주황색 부분은 PIVOT해서 보여주고 싶은 열을 지정

파란색 부분은 PIVOT해서 보여주고 싶은 열을 어떤 순으로 보여줄 지 기준(컬럼)을 지정.

안까먹으려고 다시 기록함. 

수십번을 써도... 한동안 안쓰면 자꾸 까먹네. ㅠㅠ

< 중복제거 >

regexp_replace(LISTAGG(ACCT_ID, ',') WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)+', '\1')

위 정규식을 이용하면 된다고 하는데... 

실제 되긴 하지만 

어떤 특정한 경우에는 잘 안되기도 한다. 

그래서 찾은게 아래.

regexp_replace(LISTAGG(ACCT_ID, ',') WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)*(,|$)', '\1\3')

이게 잘 통하지만 이것도 안통하는 경우가 있을 수 있지 않을까...?

<4000BYTE 초과 시 생략 >

regexp_replace(LISTAGG(ACCT_ID, ',' ON OVERFLOW TRUNCATE '후략' WITH COUNT) WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)*(,|$)', '\1\3')

TRUNCATE 뒤의 '후략' 은 생략된 부분을 보여주기 위해 추가한 문자이나 '후략' 없어도 됨

WITH COUNT는 생략된 ROW수를 표시해줌

0.1 을 TO_CHAR(0.1) 하면 .1 로 표현됨

0.1로 하려면. 이렇게 하면 된다.

SELECT TO_CHAR(0.1,'FM9990.99') FROM DUAL 

SELECT REGEXP_SUBSTR('24-61하하8971|04-10/15|9^-^'|| '-', '(.*?)\'|| '-', 1, 2)

FROM DUAL

결과 >

61하하8971|04-

SELECT REGEXP_SUBSTR('FWE2§12§4', '[^'||'§'||']+', 1,2)

FROM DUAL 

결과 > 

12

첫번째보다 두번째가 좀 더 간단하고 쉽다. 

뭔가 다른게 있는것 같기도 한데.... 

두 번째 입력란에 '(.*?)\' || 구분자 를 입력하고 뒤에 자리수를 입력하면

원하는 번째부터 다음번까지의 문자열을 찾을 수 있다. 

REGEXP_INSTR(SL3DONET.BLD_PRE_L, '[^[:digit:]]') > 0

숫자가 아닌 값의 위치 반환

>> 문자로만 이루어진 문자열인지 확인 

WHERE NOT REGEXP_LIKE(c, '[0-9]') 
   WHERE REGEXP_LIKE(c, '^[^0-9]*$') 

>> 숫자로만 이루어진 문자열인지
WHERE NOT REGEXP_LIKE(c, '[^0-9]') 
WHERE REGEXP_LIKE(c, '^[0-9]*$') 

>> 숫자+문자 조합문자열에서 숫자만 추출하기

SELECT REGEXP_REPLACE(c, '[0-9]') 

>> 숫자+문자 조합문자열에서 문자만 추출하기

SELECT REGEXP_REPLACE(c, '[^0-9]'A)

< SUM(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼) >

SUM과 달리 SUM() OVER() 함수는 GROUP BY 가 필요없다. 다시말해 성능 향상을 꾀할 수 있다.

다른 OVER()를 사용하는 함수와 마찬가지로 OVER() 절이 의미하는 바는 동일하다. 

PARTITION BY 컬럼으로 구분하여 합계를 구하고 

ORDER BY 컬럼 순서로 합계를 구한다. 

>쉽게 풀이된 블로그 링크

간단하지 않은 샘플)

위와 같이 매 날짜별로 생성되어있는 데이터를 

DC_YN 열의 값이 동일한 경우 GROUP BY 하고 (파란색 부분의 연속된 값을 각각 하나의 ROW로...)

다를 경우 표시하여 아래와 같이 시작~종료일로 표시하는 SQL은 아래와 같다. 

간단하면서 알고보면 쉽지만, 아무나 생각해낼 수는 없는 풀이방법을 제공해주신 이*철 책임님께 경의를 표합니다. :D

<좋은 SQL>

SELECT PT_NO 환자번호

           , ORD_DTE 처방일

           , ORD_SEQ 처방순번

           ,MIN(DC_YN) DC_YN

           ,MAX(DC_YN) DC_YN

           ,GROUP_NUM 

     , MIN(RSV_DTIM) AS 시작일 

     , MAX(RSV_DTIM) AS 종료일 

FROM ( 

      SELECT RN 

           , PT_NO

           , ORD_DTE

           , ORD_SEQ

           , DC_YN 

           , SUM(CASE WHEN DC_YN = 'D' THEN 1 

                      WHEN DC_YN = 'C' AND 앞수 = 'D' THEN 1 

                      ELSE 0 END) OVER(ORDER BY ORD_SEQ,RN) AS GROUP_NUM 

           , RSV_DTIM 

      FROM ( 

        SELECT ROWNUM RN

             , LAG(DC_YN) OVER(ORDER BY ROWNUM) AS 앞수 

             , A.*  

        FROM 테이블 A 

          WHERE PT_NO = '12345678' 

          AND ORD_DTE = '2004-10-01' 

          AND ORD_SEQ = 4 

      ) A 

) A 

GROUP BY PT_NO

           , ORD_DTE

           , ORD_SEQ, GROUP_NUM 

ORDER BY PT_NO,ORD_DTE,ORD_SEQ,시작일

PLAN은 아래와 같다. (컬럼 60여개, 약 2600만건)

안좋은 SQL

WITH AA AS ( 

      SELECT * FROM (   SELECT ROWNUM RNUM, PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN

        , LAG(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LAG_DC_YN 

        , LEAD(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LEAD_DC_YN

      FROM (SELECT * 

            FROM 테이블 

            ) A 

      ORDER BY PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN)

), BB AS (

        SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LAG_DC_YN,'NULL') ORDER BY RNUM

), CC AS (

        SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LEAD_DC_YN,'NULL') ORDER BY RNUM

)

SELECT BB.*, CC.RSV_DTIM, CC.RNUM - BB.RNUM + 1 ROW_CNT 

FROM BB INNER JOIN CC 

ON BB.RNUM2 = CC.RNUM2

  AND BB.DC_YN = CC.DC_YN;

아래와 같이 PLAN 이 정말 어마무시하게 나온다. 

CONNECT_BY_ROOT

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.

CONNECT_BY_ISLEAF

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0

SYS_CONNECT_BY_PATH

계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.

아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자

출처 : 구루비 http://www.gurubee.net/lecture/1903

 SELECT C.TABLE_NAME, C.COLUMN_NAME 

 FROM USER_CONS_COLUMNS C, 

         USER_CONSTRAINTS S

 WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME 

 AND S.CONSTRAINT_TYPE = 'P'

 AND C.TABLE_NAME = '테이블명'