단일행을 여러행으로 만들기(행복제, Row-Generation) 35 11 39,766

by 마농 [마농] 행복제 ROW-GENERATION CONNECT BY LEVEL [2012.04.03 17:47:39]


단일행을 여러행으로 만들기(행복제, Row-Generation)

○ 행복제 기본 유형

0. 행복제 기본 유형
SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10;


○ 행복제 기본 유형 사용시 유의사항
- dual 테이블을 이용하여 간단하게 원하는 수 만큼의 행을 생성 할 수 있습니다. 참~ 쉽죠잉~
- 유의 할 점은 dual 과 같이 1행 집합에만 적용해야 한다는 것입니다.
- 예를들어 10행의 자료를 각각 10건씩으로 복제 한다고 가정하고 위의 방법을 적용하면...
- 10 * 10 = 100개 행이 조회될꺼라 생각한다면? 아니아니 아니되오~
- 10행의 집합에 Connect By LEVEL <= 10 조건을 주었을 때 결과는?
- 놀라지 마세요~ 11,111,111,110 건이 발생됩니다.
- 수식까지 알 필요는 없지만 굳이 알고자 하신다면? (10^1 + 10^2 + 10^3 + ... + 10^10)
- 암튼 결론은 이렇게 쓰지 말아라 하는것입니다.

○ 행복제 응용 문제 1
레코드에 저장되어 있는 건수만큼 복제하기

 

-- 테스트용 데이터 --
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
SELECT * FROM t;

 

1. 행복제 기본 유형과 조인
SELECT pk, cnt, lv
  FROM t
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= cnt
 ORDER BY pk, lv
;
==> 쓸데 없이 많은 수(99) 만큼 복제하여 조인하는 비효율 발생
==> MAX(cnt) 만큼만 복제 하면 어떨까?
2. MAX(cnt) 만큼만 복제 하는 방법
SELECT pk, cnt, lv
  FROM t
     , (SELECT LEVEL lv FROM (SELECT MAX(cnt) cnt FROM t) CONNECT BY LEVEL <= cnt)
 WHERE lv <= cnt
 ORDER BY pk, lv
;
==> 테이블을 두번 읽는 비효율 발생
==> 테이블에 직접 Connect By Level 조건을 걸면 한번만 읽고 가능하긴 한데...
==> 위에 언급한 유의 사항 때문에 안되겠군... 어떻게 안될까?
3. Connect_By_Root 조건 삽입.
SELECT pk, cnt, LEVEL lv
  FROM t
 CONNECT BY LEVEL <= cnt
 AND CONNECT_BY_ROOT pk = pk
;
==> 어라? 잘 되네? 이게 왜 되는거야? 뭔가 불안한데...
==> 11G 에서는 안됩니다. 고로 사용 불가...
==> ORA-30007: CONNECT BY ROOT 연산자는 START WITH 또는 CONNECT BY 조건에서 지원되지 않습니다.
4. dbms_random.value 조건 삽입.
SELECT pk, cnt, LEVEL lv
  FROM t
 CONNECT BY LEVEL <= cnt
 AND PRIOR pk = pk
 AND PRIOR dbms_random.value IS NOT NULL
;
==> 어라? 잘 되네? 이건 또 왜 되는거야? 불안한데...
==> 이것 역시 11G 에서는 안됩니다. 고로 사용 불가...라고 쓸려고 했는데.
==> 으미.. 잘 되네..참고 싸이트에서는 안된다고 쓰여 있던데...
==> 참고로 내 11g 버전은 11.2.0.1.0
==> 이걸 써야 되는 거야? 쓰지 말아야 하는거야? 고민되네...
5. Model 절 활용
SELECT pk, cnt, lv
  FROM t
 MODEL
 PARTITION BY (pk)
 DIMENSION BY (1 lv) 
 MEASURES(cnt)
 RULES(cnt[FOR lv FROM 1 TO cnt[1] INCREMENT 1] = cnt[1])
;
6. Multiset 을 이용한 방법
SELECT pk, cnt
     , TO_NUMBER(column_value) lv
  FROM t
     , TABLE(CAST(MULTISET(SELECT LEVEL FROM dual CONNECT BY LEVEL <= cnt) AS sys.dbms_debug_vc2coll))
;
7. 11G Recursive SQL
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
, t1(pk, cnt, lv) AS
(
SELECT pk, cnt
     , 1 lv
  FROM t
 UNION ALL
SELECT pk, cnt
     , lv + 1 lv
  FROM t1
 WHERE lv + 1 <= cnt
)
SELECT *
  FROM t1
 ORDER BY pk, lv
;

 


별 별 방법이 다 있군요....

참고 : http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

○ 행복제 응용 문제 2
한 필드에 여러 값이 구분자로 저장되어 있을 때 구분자로 구별하여 행 나누기

 

 

WITH t AS
(
SELECT 1 pk, '1,3,5' v FROM dual
UNION ALL SELECT 2, '2,4,6,8' FROM dual
UNION ALL SELECT 3, '9,10,11' FROM dual
)
SELECT pk, lv
     , REGEXP_SUBSTR(v, '[^,]+', 1, lv) v
  FROM t
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
 WHERE lv <= LENGTH(v) - LENGTH(REPLACE(v, ',')) + 1
 ORDER BY pk, lv
;

 

by 타락천사 [2012.04.03 19:33:14]

우선 스크랩  !!! 천천히 볼께 땡큐

by 우리얍 [2012.04.10 11:10:42]
+_+ 스크랩!!

by 아발란체 [2013.12.19 13:03:32]
임의 행 만들기 끝판왕 ~ ! 잘 보겠습니다.

by 김용학 [2014.03.28 16:04:52]

와우...멋찌네요.
열심히 공부 해 볼게요.

by 박민철 [2014.08.21 15:09:14]

좋은 정보네요~

잘 활용할께요^^


by 마농 [2017.03.31 17:10:45]
-- ○ XMLTABLE 기능 활용 --
-- 1. 특정 구간만큼 행 생성 --
SELECT *
  FROM XMLTABLE('3 to 5' COLUMNS c1 NUMBER PATH '.')
;
-- 2. 레코드에 저장되어 있는 건수만큼 복제
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
SELECT pk, cnt, lv
  FROM (SELECT pk, cnt
             , '1 to ' || cnt x
          FROM t
        )
     , XMLTABLE(x COLUMNS lv NUMBER PATH '.')
;
WITH t AS
(
SELECT 1 pk, 2 cnt FROM dual
UNION ALL SELECT 2, 4 FROM dual
UNION ALL SELECT 3, 6 FROM dual
)
SELECT *
  FROM t
     , XMLTABLE('1 to xs:int(.)' PASSING cnt COLUMNS lv NUMBER PATH '.')
--   , XMLTABLE('1 to xs:int(V)' PASSING XMLELEMENT(V, cnt) COLUMNS lv INT PATH '.')
;
-- 3. 구분자로 구별하여 행 나누기
WITH t AS
(
SELECT 1 pk, '1,3,5' v FROM dual
UNION ALL SELECT 2, '2,4,6,8' FROM dual
UNION ALL SELECT 3, '9,10,11' FROM dual
)
SELECT *
  FROM t
     , XMLTABLE(v COLUMNS x NUMBER PATH '.')
;

 


by jkson [2017.03.31 17:54:57]

XMLTABLE 제대로 공부하면 많은 걸 할 수 있을 것 같습니다. 근데 영문 메뉴얼 찾아보니 너무 어려버요ㅠ


by 우리집아찌 [2022.06.23 13:35:08]

ORACE XE 버젼에서는 XMLTABLE은 생성에 제한이 없는데 CONNECT BY LEVEL <= ?? 방식을 쓰면 천만인지 일억건인지 쯔음에 메모리인지 용량인지 에러가 나더군요..


by 마농 [2017.06.15 14:27:58]
-- MariaDB --
SELECT seq
  FROM seq_1_to_10
;
-- MSSQL --
WITH t AS
(
SELECT 1 lv
 UNION ALL
SELECT lv + 1 lv
  FROM t
 WHERE lv + 1 <= 10
)
SELECT *
  FROM t
;
-- PostgreSQL --
SELECT lv
  FROM generate_series(1, 10) lv
;

 


by 마농 [2021.05.12 10:47:13]
-- 구분자로 구별하여 행 나누기
-- MSSQL 2016 OPENJSON --
WITH t AS
(
SELECT 1 pk, '1,3,5' v
UNION ALL SELECT 2, '2,4,6,8'
UNION ALL SELECT 3, '9,10,11'
)
SELECT *
  FROM t
 CROSS APPLY OPENJSON('[' + v + ']')  -- 숫자만
-- CROSS APPLY OPENJSON('["' + REPLACE(v, ',', '","') + '"]')  -- 문자포함
;
-- MSSQL 2016 STRING_SPLIT --
WITH t AS
(
SELECT 1 pk, '1,3,5' v
UNION ALL SELECT 2, '2,4,6,8'
UNION ALL SELECT 3, '9,10,11'
)
SELECT *
  FROM t
 CROSS APPLY STRING_SPLIT(v, ',')
;

 


by 방랑자 [2022.12.09 11:39:23]

정말 감사드립니다.

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