농부지기

[  ◎ 모든 테이블에서 특정 컬럼에 대한 특정값 찾기  ]     

                        [  ◎ PARTITION BY OUTER JOIN ]     
 

[ Oracle - 정규식 - AND, OR 검색 ]

                   [  ◎ 정규식이란? ]     

  참고 URL : http://skillsocius.com/220184877136

[ Oracle - 금액배분 후 남은 잔여분 재일 큰 거래처에 가감  ]

--문제 : 거래처 구분별로 100,000만원을 각 거래처에 배분하여 인센티브를 지급하려고 한다.
--       단, 배분시 매출액대비 지급율을 계산하여 인센티브를 지급하는데
--       거래처 구분별로 100,000만원이 넘어가거나, 남을 경우  제일 많이 지급되는 거래처에서 가감한다.

WITH SALE_LIST AS ( SELECT '백화점' AS GUBN, '하늘점' AS CUST, '20104' AS YM, 31 AS AMT FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20105' , 24 FROM DUAL UNION ALL SELECT '백화점' , '하늘점' , '20106' , 34 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20101' , 25 FROM DUAL UNION ALL SELECT '백화점' , '누리점' , '20102' , 48 FROM DUAL UNION ALL SELECT '백화점' , '가리봉점' , '20105' , 34 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20105' , 77 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20106' , 85 FROM DUAL UNION ALL SELECT '온라인' , '11ST' , '20107' , 79 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20101' , 64 FROM DUAL UNION ALL SELECT '온라인' , 'GS마켓' , '20112' , 70 FROM DUAL ) SELECT A.* , ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS REAL_INCT_AMT , INCT_AMT - ( CASE WHEN RANKING=1 AND 100000 != SUM(INCT_AMT) OVER(PARTITION BY GUBN) THEN INCT_AMT + (100000 - SUM(INCT_AMT) OVER(PARTITION BY GUBN)) ELSE INCT_AMT END ) AS DIFF_AMT FROM ( SELECT GUBN , CUST , SUM(AMT) AS CUST_SUM , SUM(SUM(AMT)) OVER(PARTITION BY GUBN) AS GUBN_SUM , TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) AS CUST_RAT , ROW_NUMBER() OVER(PARTITION BY GUBN ORDER BY SUM(AMT) DESC) AS RANKING , ROUND(TRUNC(SUM(AMT) / SUM(SUM(AMT)) OVER(PARTITION BY GUBN), 3) * 100000, 0) AS INCT_AMT FROM SALE_LIST GROUP BY GUBN, CUST ) A ORDER BY GUBN, RANKING ;

[ Oracle- 자료 복제 기본 ]

--0. 행복제 응용 문제 1 SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10; --1. 행복제 기본 유형과 조인 -- ==> 쓸데 없이 많은 수(99) 만큼 복제하여 조인하는 비효율 발생 -- ==> MAX(cnt) 만큼만 복제 하면 어떨까? 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 t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) WHERE lv <= cnt ORDER BY pk, lv ; --2. MAX(cnt) 만큼만 복제 하는 방법 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 t , (--a. 여기에서 가장 큰 cnt를 구한다. (이 수만큼 복제 된다.) 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 ; --○ 행복제 응용 문제 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 ;

--http://www.gurubee.net/article/55635#comment_116470

--http://rwijk.blogspot.kr/2007/11/interval-based-row-generation.html

[ Oracle - 순차적으로 누적하기9 ]

일별 월누적매출 구하기

실 데이터는 <표1-실데이터>처럼 존재하며, <표2-결과>와 같은 결과 데이터를 조회

<표1-실데이터>

<표2-결과>

WITH t AS ( SELECT '20090101' dt, 'A' cd, 100 amt FROM dual UNION ALL SELECT '20090101' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'A' , 150 FROM dual UNION ALL SELECT '20090102' , 'B' , 200 FROM dual UNION ALL SELECT '20090102' , 'C' , 100 FROM dual UNION ALL SELECT '20090103' , 'A' , 300 FROM dual UNION ALL SELECT '20090103' , 'B' , 100 FROM dual ) SELECT dt, cd, amt, sum_amt FROM ( SELECT a.dt , b.cd , c.amt , SUM(c.amt) OVER(PARTITION BY b.cd ORDER BY a.dt) sum_amt FROM ( SELECT DISTINCT dt FROM t ) a CROSS JOIN (SELECT DISTINCT cd FROM t) b LEFT OUTER JOIN t c ON a.dt = c.dt AND b.cd = c.cd ) WHERE sum_amt IS NOT NULL ORDER BY dt, cd

[ Oracle - 순차적으로 누적하기8 ]

음수의 연속된 곱을 한방에 해결할수 있는 방법이 있을까요? 1. 절대값만 누적 곱한 후 음수값만 카운트하여 건수가 홀수이면 -1을 곱해주면 끝. WITH test AS ( SELECT 1 id, 1 col1 FROM dual UNION ALL SELECT 2, -2 FROM dual UNION ALL SELECT 3, 3 FROM dual UNION ALL SELECT 4, -4 FROM dual UNION ALL SELECT 6, 6 FROM dual UNION ALL SELECT 7, -7 FROM dual UNION ALL SELECT 9, 9 FROM dual ) SELECT id, col1 , ROUND( EXP(SUM(LN(ABS(col1))) OVER(ORDER BY id)) * DECODE(MOD(COUNT(DECODE(SIGN(col1),-1,1)) OVER(ORDER BY id),2),1,-1,1) , 10) col2 FROM test ; 다만 이때 주의할것은 LN 함수 때문에 정확한 값이 아닌 근사값이 나온다는 것입니다. 그래서 위의 쿼리에서 round 함수를 이용해 값을 보정해 주었습니다. 2. Model 절을 이용하면 정확한 값을 계산 할 수 있습니다. ; WITH test AS ( SELECT 1 id, 1 col1 FROM dual UNION ALL SELECT 2, -2 FROM dual UNION ALL SELECT 3, 3 FROM dual UNION ALL SELECT 4, -4 FROM dual UNION ALL SELECT 6, 6 FROM dual UNION ALL SELECT 7, -7 FROM dual UNION ALL SELECT 9, 9 FROM dual ) SELECT id, col1, col2 FROM test MODEL DIMENSION BY (ROW_NUMBER() OVER(ORDER BY id) rn) MEASURES (id, col1, 0 col2) RULES (col2[ANY] = NVL(col2[CV()-1], 1) * col1[CV()]) ;

[ Oracle - 순차적으로 누적하기7 ]

위 테이블과 같은 데이터 구조에서
시작시간의 빈값에 순차적으로 시작시간 + 걸린시간값이 그룹코드별로 들어가 있는 sql

WITH t AS ( SELECT 'ECOMP01' grp, '6230394' lot, 0.4 dd, TO_DATE('2010-09-29 15:43', 'yyyy-mm-dd hh24:mi') tm FROM dual UNION ALL SELECT 'ECOMP01', '6229155', 0.14, null FROM dual UNION ALL SELECT 'ECOMP01', '6229177', 0.02, null FROM dual UNION ALL SELECT 'ECOMP01', '6230479', 0.41, null FROM dual UNION ALL SELECT 'ECOMP01', '6230694', 0.02, null FROM dual UNION ALL SELECT 'ECOMP01', '6230487', 0.16, null FROM dual UNION ALL SELECT 'ECOMP01', '6230501', 0.03, null FROM dual UNION ALL SELECT 'ECOMP01', '6231286', 0.07, null FROM dual UNION ALL SELECT 'ECOMP01', '6230552', 0.81, null FROM dual UNION ALL SELECT 'ECOMP02', '6228735', 0.17, TO_DATE('2010-10-01 19:18', 'yyyy-mm-dd hh24:mi') FROM dual UNION ALL SELECT 'ECOMP02', '6228948', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6228949', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230272', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230274', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230275', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230273', 2.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230276', 1.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230277', 3.33, null FROM dual UNION ALL SELECT 'ECOMP02', '6230817', 0.67, null FROM dual UNION ALL SELECT 'ECOMP02', '6230819', 0.67, null FROM dual ) SELECT grp, lot, dd, tm , LAST_VALUE(tm - dd IGNORE NULLS) OVER(PARTITION BY grp ORDER BY rownum) + SUM(dd) OVER(PARTITION BY grp ORDER BY rownum) tm2 FROM t ;

[ Oracle - 순차적으로 누적하기6 ]

WITH cod AS ( SELECT '' pcd, 'A' cd FROM dual UNION ALL SELECT 'A', 'B' FROM dual UNION ALL SELECT 'B', 'C' FROM dual UNION ALL SELECT 'B', 'D' FROM dual UNION ALL SELECT 'A', 'E' FROM dual ) , mst AS ( SELECT 'C' cd, 1000 v FROM dual UNION ALL SELECT 'D', 2000 FROM dual UNION ALL SELECT 'E', 5000 FROM dual ) SELECT LEVEL lv , cd , ( SELECT SUM(v) FROM mst WHERE cd IN (SELECT cd FROM cod START WITH cd = a.cd CONNECT BY PRIOR cd = pcd) ) sum_v FROM cod a START WITH pcd IS NULL CONNECT BY PRIOR cd = pcd ;