----------------------------------------------------------------------------------------------------
-- 컬럼 값 Grouping 펑션 생성
CREATE OR REPLACE FUNCTION FN_GROUPING(GBN VARCHAR2, VAL NUMBER) RETURN VARCHAR2
IS
V_RESULT VARCHAR2(2) := NULL;
V_GBN VARCHAR2(3) := UPPER(GBN);
BEGIN
IF V_GBN='G01' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSE V_RESULT := LPAD(VAL,2,'0');
END IF;
ELSIF V_GBN='G02' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL > 10 THEN V_RESULT := '11';
ELSE V_RESULT := LPAD(VAL,2,'0');
END IF;
ELSIF V_GBN='G03' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL >= 4 THEN V_RESULT := '04';
ELSE V_RESULT := LPAD(VAL,2,'0');
END IF;
ELSIF V_GBN='G04' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '01';
ELSIF VAL <= 100 THEN V_RESULT := '02';
ELSIF VAL <= 300 THEN V_RESULT := '03';
ELSIF VAL <= 500 THEN V_RESULT := '04';
ELSIF VAL <= 700 THEN V_RESULT := '05';
ELSIF VAL <= 1000 THEN V_RESULT := '06';
ELSIF VAL <= 1500 THEN V_RESULT := '07';
ELSIF VAL <= 2000 THEN V_RESULT := '08';
ELSIF VAL <= 3000 THEN V_RESULT := '09';
ELSIF VAL <= 5000 THEN V_RESULT := '10';
ELSE V_RESULT := '11';
END IF;
ELSIF V_GBN='G05' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '01';
ELSIF VAL <= 1000 THEN V_RESULT := '02';
ELSIF VAL <= 3000 THEN V_RESULT := '03';
ELSIF VAL <= 5000 THEN V_RESULT := '04';
ELSIF VAL <= 10000 THEN V_RESULT := '05';
ELSIF VAL <= 20000 THEN V_RESULT := '06';
ELSIF VAL <= 30000 THEN V_RESULT := '07';
ELSIF VAL <= 50000 THEN V_RESULT := '08';
ELSIF VAL <= 100000 THEN V_RESULT := '09';
ELSE V_RESULT := '10';
END IF;
ELSIF V_GBN='G06' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '01';
ELSIF VAL <= 100000 THEN V_RESULT := '02';
ELSIF VAL <= 300000 THEN V_RESULT := '03';
ELSIF VAL <= 500000 THEN V_RESULT := '04';
ELSIF VAL <= 1000000 THEN V_RESULT := '05';
ELSIF VAL <= 2500000 THEN V_RESULT := '06';
ELSIF VAL <= 5000000 THEN V_RESULT := '07';
ELSIF VAL <= 7000000 THEN V_RESULT := '08';
ELSIF VAL <= 10000000 THEN V_RESULT := '09';
ELSIF VAL <= 20000000 THEN V_RESULT := '10';
ELSE V_RESULT := '11';
END IF;
ELSIF V_GBN='G07' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '01';
ELSIF VAL <= 50000 THEN V_RESULT := '02';
ELSIF VAL <= 100000 THEN V_RESULT := '03';
ELSIF VAL <= 200000 THEN V_RESULT := '04';
ELSIF VAL <= 500000 THEN V_RESULT := '05';
ELSIF VAL <= 700000 THEN V_RESULT := '06';
ELSIF VAL <= 1000000 THEN V_RESULT := '07';
ELSIF VAL <= 1500000 THEN V_RESULT := '08';
ELSIF VAL <= 2000000 THEN V_RESULT := '09';
ELSIF VAL <= 3000000 THEN V_RESULT := '10';
ELSIF VAL <= 4000000 THEN V_RESULT := '11';
ELSIF VAL <= 5000000 THEN V_RESULT := '12';
ELSIF VAL <= 10000000 THEN V_RESULT := '13';
ELSE V_RESULT := '14';
END IF;
ELSIF V_GBN='G08' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '01';
ELSIF VAL <= 3 THEN V_RESULT := '02';
ELSIF VAL <= 6 THEN V_RESULT := '03';
ELSIF VAL <= 9 THEN V_RESULT := '04';
ELSIF VAL <= 12 THEN V_RESULT := '05';
ELSIF VAL <= 18 THEN V_RESULT := '06';
ELSIF VAL <= 24 THEN V_RESULT := '07';
ELSIF VAL <= 30 THEN V_RESULT := '08';
ELSIF VAL <= 36 THEN V_RESULT := '09';
ELSE V_RESULT := '10';
END IF;
ELSIF V_GBN='G09' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL >= 4 THEN V_RESULT := '40';
ELSE V_RESULT := LPAD(VAL*10,2,'0');
END IF;
ELSIF V_GBN='G10' THEN
IF VAL IS NULL THEN V_RESULT := 'MM';
ELSIF VAL <= 0 THEN V_RESULT := '00';
ELSIF VAL >= 99 THEN V_RESULT := '99';
ELSE V_RESULT := LPAD(VAL,2,'0');
END IF;
ELSE V_RESULT := 'XX';
END IF;
RETURN V_RESULT;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
----------------------------------------------------------------------------------------------------
-- Max Straight 값 구하기 펑션 생성
CREATE OR REPLACE FUNCTION FN_MAX_CONT(VAL NUMBER) RETURN NUMBER
IS
V_RESULT INTEGER := 0;
TEMP_RESULT INTEGER := 0;
V_VAL VARCHAR2(100) := TO_CHAR(VAL);
LEN INTEGER := LENGTH(V_VAL);
IND INTEGER := 0;
BEGIN
WHILE IND < LEN LOOP
IND := IND + 1;
IF SUBSTR(V_VAL, IND, 1) = '1'
THEN
TEMP_RESULT := TEMP_RESULT + 1;
IF V_RESULT < TEMP_RESULT
THEN
V_RESULT := TEMP_RESULT;
END IF;
ELSE
TEMP_RESULT := 0;
END IF;
END LOOP;
RETURN (V_RESULT);
EXCEPTION
WHEN OTHERS THEN
RETURN (-1);
END;
----------------------------------------------------------------------------------------------------
-- Straight 값 구하기 펑션 생성
CREATE OR REPLACE FUNCTION FN_CNT_CONT(VAL NUMBER) RETURN NUMBER
IS
V_RESULT INTEGER := 0;
V_VAL VARCHAR2(100) := TO_CHAR(VAL);
LEN INTEGER := LENGTH(V_VAL);
BEGIN
WHILE LEN > 0 LOOP
IF SUBSTR(V_VAL, LEN, 1) = '1'
THEN
V_RESULT := V_RESULT + 1;
ELSE
RETURN (V_RESULT);
END IF;
LEN := LEN - 1;
END LOOP;
RETURN (V_RESULT);
EXCEPTION
WHEN OTHERS THEN
RETURN (-1);
END;
----------------------------------------------------------------------------------------------------
-- 12개월치 데이타로 다양한 파생컬럼 생성하기
WITH DATA AS (
SELECT '200810' YYYYMM, 1 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 3 X2, 10000 X3 FROM DUAL UNION ALL
SELECT '200809' YYYYMM, 2 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 2 X2, 30000 X3 FROM DUAL UNION ALL
SELECT '200808' YYYYMM, 3 YYYYMM_NO, '10001' ACCOUNT, 0 X1, 1 X2, 20000 X3 FROM DUAL UNION ALL
SELECT '200807' YYYYMM, 4 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 0 X2, 50000 X3 FROM DUAL UNION ALL
SELECT '200806' YYYYMM, 5 YYYYMM_NO, '10001' ACCOUNT, 0 X1, 0 X2, 60000 X3 FROM DUAL UNION ALL
SELECT '200805' YYYYMM, 6 YYYYMM_NO, '10001' ACCOUNT, 0 X1, 2 X2, 70000 X3 FROM DUAL UNION ALL
SELECT '200804' YYYYMM, 7 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 2 X2, 40000 X3 FROM DUAL UNION ALL
SELECT '200803' YYYYMM, 8 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 2 X2, 30000 X3 FROM DUAL UNION ALL
SELECT '200802' YYYYMM, 9 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 1 X2, 20000 X3 FROM DUAL UNION ALL
SELECT '200801' YYYYMM, 10 YYYYMM_NO, '10001' ACCOUNT, 0 X1, 4 X2, 10000 X3 FROM DUAL UNION ALL
SELECT '200712' YYYYMM, 11 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 2 X2, 10000 X3 FROM DUAL UNION ALL
SELECT '200711' YYYYMM, 12 YYYYMM_NO, '10001' ACCOUNT, 1 X1, 1 X2, 20000 X3 FROM DUAL UNION ALL
SELECT '200810' YYYYMM, 1 YYYYMM_NO, '22222' ACCOUNT, 0 X1, NULL X2, 100 X3 FROM DUAL UNION ALL
SELECT '200809' YYYYMM, 2 YYYYMM_NO, '22222' ACCOUNT, NULL X1, NULL X2, NULL X3 FROM DUAL UNION ALL
SELECT '200808' YYYYMM, 3 YYYYMM_NO, '22222' ACCOUNT, NULL X1, NULL X2, NULL X3 FROM DUAL UNION ALL
SELECT '200807' YYYYMM, 4 YYYYMM_NO, '22222' ACCOUNT, 0 X1, NULL X2, NULL X3 FROM DUAL UNION ALL
SELECT '200806' YYYYMM, 5 YYYYMM_NO, '22222' ACCOUNT, 0 X1, NULL X2, NULL X3 FROM DUAL UNION ALL
SELECT '200805' YYYYMM, 6 YYYYMM_NO, '22222' ACCOUNT, 0 X1, NULL X2, 600 X3 FROM DUAL UNION ALL
SELECT '200804' YYYYMM, 7 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 1 X2, 500 X3 FROM DUAL UNION ALL
SELECT '200803' YYYYMM, 8 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 1 X2, 400 X3 FROM DUAL UNION ALL
SELECT '200802' YYYYMM, 9 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 1 X2, 200 X3 FROM DUAL UNION ALL
SELECT '200801' YYYYMM, 10 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 4 X2, 100 X3 FROM DUAL UNION ALL
SELECT '200712' YYYYMM, 11 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 2 X2, 100 X3 FROM DUAL UNION ALL
SELECT '200711' YYYYMM, 12 YYYYMM_NO, '22222' ACCOUNT, NULL X1, 1 X2, 200 X3 FROM DUAL
)
SELECT ACCOUNT
, FN_GROUPING('G10',
MAX(DECODE(YYYYMM_NO,1,X1,NULL)))
AS X1_R1 /* R1 : 변수xx */
, FN_GROUPING('G10',
MIN(CASE WHEN YYYYMM_NO BETWEEN 1 AND 3 THEN X2 ELSE NULL END))
AS X2_MN_R3 /* Min R3 : 변수xx */
, FN_GROUPING('G10',
MAX(CASE WHEN YYYYMM_NO BETWEEN 1 AND 3 THEN X2 ELSE NULL END))
AS X2_MX_R3 /* Max R3 : 변수xx */
, FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),1,1,0) ELSE 0 END))
AS X1_CY_R12 /* Count(Value Y) R12 : 변수xx */
, FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),0,1,0) ELSE 0 END))
AS X1_C0_R12 /* Count(Value 0) R12 : 변수xx */
, FN_GROUPING('G10',FN_CNT_CONT(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),1,1,0) * POWER(10,(YYYYMM_NO-1)) ELSE 0 END)))
AS X1_SY_R12 /* Straight(Value Y) R12 : 변수xx */
, FN_GROUPING('G10',FN_CNT_CONT(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),0,1,0) * POWER(10,(YYYYMM_NO-1)) ELSE 0 END)))
AS X1_S0_R12 /* Straight(Value 0) R12 : 변수xx */
, FN_GROUPING('G10',FN_MAX_CONT(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),1,1,0) * POWER(10,(YYYYMM_NO-1)) ELSE 0 END)))
AS X1_MY_R12 /* Max Straight(Value Y) R12 : 변수xx */
, FN_GROUPING('G10',FN_MAX_CONT(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),0,1,0) * POWER(10,(YYYYMM_NO-1)) ELSE 0 END)))
AS X1_M0_R12 /* Max Straight(Value 0) R12 : 변수xx */
, FN_GROUPING('G10',NVL(LENGTH(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 12 THEN DECODE(SIGN(X1),1,1,NULL) * POWER(10,(YYYYMM_NO-1)) ELSE 0 END)),0))
AS X1_TM_R12 /* Term R12 : 변수xx */
, FN_GROUPING('G05',
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN X3 ELSE 0 END) / 6)
AS X3_AV_R6 /* Average R6 : 변수xx */
, FN_GROUPING('G05',CASE WHEN NVL(
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN DECODE(X3,NULL,0,1) END),0) =0 THEN 0 ELSE
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN X3 ELSE 0 END) /
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN DECODE(X3,NULL,0,1) END) END)
AS X3_AY_R3 /* Average(Value Y) R3 : 변수xx */
, FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN DECODE(SIGN(X1),1,1,0) ELSE 0 END)) ||
FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 7 AND 12 THEN DECODE(SIGN(X1),1,1,0) ELSE 0 END))
AS X1_PY_R6R6 /* Patten Count(Value Y) R6R6 : 변수xx */
, FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN DECODE(SIGN(X1),0,1,0) ELSE 0 END)) ||
FN_GROUPING('G10',
SUM(CASE WHEN YYYYMM_NO BETWEEN 7 AND 12 THEN DECODE(SIGN(X1),0,1,0) ELSE 0 END))
AS X1_P0_R6R6 /* Patten Count(Value 0) R6R6 : 변수xx */
, FN_GROUPING('G10',
MAX(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN X2 ELSE NULL END)) ||
FN_GROUPING('G10',
MAX(CASE WHEN YYYYMM_NO BETWEEN 6 AND 12 THEN X2 ELSE NULL END))
AS X2_PX_R6R6 /* Patten Max R6R6 : 변수xx */
, FN_GROUPING('G10',
MIN(CASE WHEN YYYYMM_NO BETWEEN 1 AND 6 THEN X2 ELSE NULL END)) ||
FN_GROUPING('G10',
MIN(CASE WHEN YYYYMM_NO BETWEEN 6 AND 12 THEN X2 ELSE NULL END))
AS X2_PN_R6R6 /* Patten Min R6R6 : 변수xx */
, FN_GROUPING('G10',
MAX(DECODE(YYYYMM_NO,1,X1,NULL))) ||
FN_GROUPING('G10',
MAX(DECODE(YYYYMM_NO,2,X1,NULL)))
AS X1_PT_R1R1 /* Patten R1R1 : 변수xx */
FROM DATA
GROUP BY ACCOUNT
ORDER BY ACCOUNT
;
'전산Tip > Oracle' 카테고리의 다른 글
| 오라클 펑션 - 영문/숫자/한글 제외 기타 문자 제거 (조합형 한글 제거) (0) | 2010.04.14 |
|---|---|
| 오라클 권한 설명 및 확인 (0) | 2008.10.14 |
| 오라클 10g - 컬럼명의 언더바 기준으로 단어 분리하기 (0) | 2008.10.02 |