[빈도추출] 오라클로 범주형, 연속형 빈도 추출 view 만들기

1) 아래 view를 생성 

2) 해당 view where 조건의 TBLID 컬럼에 테이블명을 입력 후 조회

3) SQL_FREQ_ORACLE 컬럼에 빈도추출하는 sql문장에 생성됨, 복사 후 실행

CREATE OR REPLACE VIEW VW_SQL_FREQ AS

        /*

         * PGM ID   :   VW_SQL_FREQ

         * TITLE    :   컬럼값의 범주형, 연속형 빈도추출 SQL문장 생성

         * AUTHOR   :   임상우 grapro.lim@gmail.com

         * DATE     :   2013.02.21

         * VERSION  :   1.0

         * CONTENTS :   

         * RUNCYCLE :   

         * RUNTIME  :   

         * MODIFIED :   

         */

SELECT  D.TABLE_TYPE

        , A.TNAME               AS TBLID

        , D.COMMENTS            AS TBLNM

        , A.COLNO               AS COLNO

        , A.CNAME               AS COLID

        , CASE

          WHEN INSTR(B.COMMENTS, '{') > 0

          THEN SUBSTR(B.COMMENTS, 1, INSTR(B.COMMENTS, '{')-1 )

          ELSE B.COMMENTS

          END                   AS COLNM

        , CASE

          WHEN INSTR(B.COMMENTS, '{') > 0

          THEN SUBSTR(B.COMMENTS, INSTR(B.COMMENTS, '{')+1, LENGTH(B.COMMENTS)-INSTR(B.COMMENTS, '{')-1 )

          END                   AS COLDEF

        

        , C.COLUMN_POSITION     AS PK

        , DECODE(A.COLTYPE, 'TIMESTAMP(6)', 'TIMESTAMP', A.COLTYPE) AS "TYPE"

        , CASE 

          WHEN DECODE(A.COLTYPE, 'NUMBER', TO_CHAR(A.PRECISION)||','||TO_CHAR(A.SCALE), 'TIMESTAMP(6)', NULL, 'DATE', NULL, TO_CHAR(A.WIDTH)) IN (',',',0') THEN NULL

          ELSE DECODE(A.COLTYPE, 'NUMBER', TO_CHAR(A.PRECISION)||','||TO_CHAR(A.SCALE), 'TIMESTAMP(6)', NULL, 'DATE', NULL, TO_CHAR(A.WIDTH))

          END                   AS WIDTH

        , DECODE(A.NULLS, 'NULL', '', A.NULLS)  "NULLS"

        , CASE

          WHEN A.COLTYPE IN ('VARCHAR2', 'CHAR')

          THEN 'UNION ALL SELECT '''||A.TNAME||''' AS TBLID, (SELECT CASE WHEN INSTR(COMMENTS, ''{'') > 0 THEN SUBSTR(COMMENTS, 1, INSTR(COMMENTS, ''{'')-1 ) ELSE COMMENTS END FROM USER_TAB_COMMENTS WHERE TABLE_NAME = '''||A.TNAME||''') AS TBLNM, '''||A.CNAME||''' AS COLID, (SELECT CASE WHEN INSTR(COMMENTS, ''{'') > 0 THEN SUBSTR(COMMENTS, 1, INSTR(COMMENTS, ''{'')-1 ) ELSE COMMENTS END FROM USER_COL_COMMENTS WHERE TABLE_NAME = '''||A.TNAME||''' AND COLUMN_NAME = '''||A.CNAME||''') AS COLNM, '||A.CNAME||' AS CODVAL, NULL/*## 코드값추출하는FUNCTION대입 ##*/ AS CODVALNM, COUNT(*) AS CNT, SUM(COUNT(*)) OVER () AS TCNT FROM '||A.TNAME||' A GROUP BY '||A.CNAME

          WHEN A.COLTYPE = 'NUMBER'

          THEN 'UNION ALL SELECT '''||A.TNAME||''' AS TBLID, (SELECT CASE WHEN INSTR(COMMENTS, ''{'') > 0 THEN SUBSTR(COMMENTS, 1, INSTR(COMMENTS, ''{'')-1 ) ELSE COMMENTS END FROM USER_TAB_COMMENTS WHERE TABLE_NAME = '''||A.TNAME||''') AS TBLNM, '''||A.CNAME||''' AS COLID, (SELECT CASE WHEN INSTR(COMMENTS, ''{'') > 0 THEN SUBSTR(COMMENTS, 1, INSTR(COMMENTS, ''{'')-1 ) ELSE COMMENTS END FROM USER_COL_COMMENTS WHERE TABLE_NAME = '''||A.TNAME||''' AND COLUMN_NAME = '''||A.CNAME||''') AS COLNM, LPAD(TO_CHAR(DIV-1),2,''0'') AS CODVAL, CASE WHEN DIV = 1  THEN ''MIN'' WHEN DIV = 11 THEN ''MAX'' ELSE TO_CHAR((DIV-1)*10)||''%'' END AS CODVALNM, CNT, TCNT FROM (SELECT DECODE(B.NO,1,A.DIV,11) AS DIV, MAX(DECODE(B.NO,1,A.CNT,A.CNTX)) AS CNT, MAX(TCNT) AS TCNT FROM (SELECT DIV, MIN('||A.CNAME||') AS CNT, MAX('||A.CNAME||') AS CNTX, SUM(COUNT(*)) OVER() AS TCNT FROM (SELECT NTILE(10) OVER(ORDER BY '||A.CNAME||') AS DIV, '||A.CNAME||' FROM '||A.TNAME||') A GROUP BY DIV) A,(SELECT 1 AS NO FROM DUAL UNION ALL SELECT 2 NO FROM DUAL) B GROUP BY DECODE(B.NO,1,A.DIV,11))'

          END AS SQL_FREQ_ORACLE

FROM    COL A

        , USER_COL_COMMENTS B

        , (

        SELECT A.TABLE_NAME, A.COLUMN_NAME, A.COLUMN_POSITION

        FROM   USER_IND_COLUMNS A, USER_INDEXES B

        WHERE  A.INDEX_NAME = B.INDEX_NAME

        AND    B.UNIQUENESS = 'UNIQUE'

        ORDER BY 1,3

        ) C

        , USER_TAB_COMMENTS D

WHERE   A.TNAME = B.TABLE_NAME(+)

AND     A.CNAME = B.COLUMN_NAME(+)

AND     A.TNAME = C.TABLE_NAME(+)

AND     A.CNAME = C.COLUMN_NAME(+)

AND     A.TNAME = D.TABLE_NAME(+)

AND     A.TNAME NOT LIKE 'BIN$%'

ORDER BY D.TABLE_TYPE, A.TNAME, A.COLNO

;