/* UNION, UNION ALL
UNION : 중복을 제거한 합집합
UNION ALL : 모든 합집합 */
SELECT 1 dummy FROM DUAL
UNION ALL
SELECT 2 dummy FROM DUAL
/* Cartesian Product */
SELECT *
FROM ( SELECT 1 dummy FROM DUAL
UNION ALL
SELECT 2 dummy FROM DUAL
) a,
( SELECT deptno, count(*) cnt
FROM emp
GROUP BY deptno
) b
/* 각각의 합과 총합을 Reporting */
SELECT Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum'),
Decode(a.dummy, 1, Max(b.cnt), Sum(b.cnt))
FROM ( SELECT 1 dummy FROM DUAL
UNION ALL
SELECT 2 dummy FROM DUAL
) a,
( SELECT b1.deptno, dname, count(*) cnt
FROM emp b1, dept b2
WHERE b1.deptno = b2.deptno
GROUP BY b1.deptno, dname
) b
GROUP BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
ORDER BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
/* 각각의 합과 총합을 Reporting */
SELECT a.sys_name 검사항목,
sum(a.cnt) 총갯수,
sum(decode(a.stat, '01', cnt, 0)) 운용,
sum(decode(a.stat, '03', cnt, 0)) 예비운용,
NULL 최종연동시작시간,
NULL 최종연동완료시간
FROM ( SELECT 'iNetPost' sys_name,
stat,
count(*) cnt
FROM tb_office@np_link
WHERE stat IN ('01', '03')
GROUP BY stat
) a
GROUP BY a.sys_name
CREATE OR REPLACE PROCEDURE all_tabspec_procedure IS
v_column_id user_tab_columns.column_id%TYPE;
v_column_name user_tab_columns.column_name%TYPE;
v_data_type user_tab_columns.data_type%TYPE;
v_data_length user_tab_columns.data_length%TYPE;
v_data_precision user_tab_columns.data_precision%TYPE;
v_data_scale user_tab_columns.data_scale%TYPE;
v_nullable user_tab_columns.nullable%TYPE;
v_data_default user_tab_columns.data_default%TYPE;
v_data_length_num VARCHAR2(10);
CURSOR table_spec_cursor(in_table_name user_tab_columns.table_name%TYPE) IS
select LTRIM(RTRIM(column_id)) NO,
LTRIM(RTRIM(column_name)) ITEM_NAME, LTRIM(RTRIM(data_type)) 속성,
LTRIM(RTRIM(data_length)) 길이, data_precision 길이_P,
data_scale 길이_S, nullable 널, data_default 디폴트
from user_tab_columns
where table_name = in_table_name
order by column_id;
CURSOR all_object_cursor IS
-- select object_name from user_objects where object_type in('TABLE', 'VIEW') ;
-- select object_name from user_objects where object_type = 'TABLE' ;
-- select object_name from user_objects where object_type = 'VIEW' ;
select distinct object_name from user_objects where object_name in ('ANNUITY', 'ANNUITY_DET', 'BANK', 'BASE_DET', 'BENEFIT_HIST',
'CARE', 'CARE_DET', 'CHANGE', 'CHANGE_DET', 'COMN_KEEP', 'COUPL', 'FAMILY', 'GROW', 'GROW_DET',
'LOAN', 'PAY_HIST', 'REFER', 'SPECIAL_CON_DET', 'TERM_DET');
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR allobj_cursor_rec IN all_object_cursor LOOP
OPEN table_spec_cursor(allobj_cursor_rec.object_name);
/* OBJECT NAME(TABLE OR VIEW) 인쇄*/
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('==================== ' || allobj_cursor_rec.object_name || ' ====================');
/* TYTLE 인쇄*/
DBMS_OUTPUT.PUT_LINE('NO' || ',' || ' ' || 'COLUMN_NAME' || ',' || ' ' ||
'속성' || ',' || ' ' || '길이' || ',' || '널' || ',' || ' ' || '디폴트');
LOOP
FETCH table_spec_cursor
INTO v_column_id, v_column_name, v_data_type, v_data_length, v_data_precision, v_data_scale, v_nullable, v_data_default;
EXIT WHEN table_spec_cursor%NOTFOUND;
/* （１）데이터Type이「NUMBER」인 경우는
① data_scale이０인 경우는data_precision을data_length로 지정
② data_scale이NULL인 경우는data_length치를 그대로 사용
③ data_scale이０혹은NULL이 아닌 경우는「data_precision｜data_scale」을data_length로 지정한다
여기서는v_data_length_num변수를 사용（data_length는NUMBER TYPE）
④ data_scale이０이아닌 경우는data_precision을data_length로서 사용
（２）데이터Type이「NUMBER」가 아닌 경우는data_length치를 그대로 사용
IF v_data_type = 'NUMBER' THEN
IF v_data_scale = 0 THEN
v_data_length := v_data_precision;
DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || ' ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| ' ' ||
RPAD(v_data_type,10,' ') || ',' || ' ' || v_data_length || ',' || ' ' ||
RPAD(v_nullable,1,' ') || ',' || ' ' || LTRIM(RTRIM(v_data_default)));
ELSE
v_data_length_num := CONCAT(CONCAT(TO_CHAR(v_data_precision),'|'),TO_CHAR(v_data_scale));
DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || ' ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| ' ' ||
RPAD(v_data_type,10,' ') || ',' || ' ' || v_data_length_num || ',' || ' ' ||
RPAD(v_nullable,1,' ') || ',' || ' ' || LTRIM(RTRIM(v_data_default)));
END IF;
ELSE
v_data_length := v_data_length;
DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || ' ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| ' ' ||
RPAD(v_data_type,10,' ') || ',' || ' ' || v_data_length || ',' || ' ' ||
RPAD(v_nullable,1,' ') || ',' || ' ' || LTRIM(RTRIM(v_data_default)));
END IF;
END LOOP;
CLOSE table_spec_cursor;
END LOOP;
END all_tabspec_procedure;
/
세로로 나열된 데이터를 가로로 보여주는 것을 영어로 Pivioting 시킨다고 표현을 하더군요.
SUM(DECODE(...)) 를 보셨는지 모르겠네요.
강력한 기능이죠..이걸 이용하면 pivoting 이 가능합니다.
제가 테스트한 데이터와 쿼리를 올립니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
========
# 데이터
========
acct month amt
---------- ----------- -----------
1 1 10
1 2 10
1 3 10
1 4 10
1 5 10
1 6 10
1 7 10
1 8 10
1 9 10
1 10 10
1 11 10
1 12 10
2 1 20
2 2 20
2 3 20
2 4 20
2 5 20
2 6 20
2 7 20
2 8 20
2 9 20
2 10 20
2 11 20
============
# 데이터 생성
============
CREATE TABLE test1( acct NUMBER(2), month NUMBER(2), amt NUMBER(2));
INSERT INTO test1 VALUES(1,1,10);
INSERT INTO test1 VALUES(1,2,10);
INSERT INTO test1 VALUES(1,3,10);
INSERT INTO test1 VALUES(1,4,10);
INSERT INTO test1 VALUES(1,5,10);
INSERT INTO test1 VALUES(1,6,10);
INSERT INTO test1 VALUES(1,7,10);
INSERT INTO test1 VALUES(1,8,10);
INSERT INTO test1 VALUES(1,9,10);
INSERT INTO test1 VALUES(1,10,10);
INSERT INTO test1 VALUES(1,11,10);
INSERT INTO test1 VALUES(1,12,10);
INSERT INTO test1 VALUES(2,1,20);
INSERT INTO test1 VALUES(2,2,20);
INSERT INTO test1 VALUES(2,3,20);
INSERT INTO test1 VALUES(2,4,20);
INSERT INTO test1 VALUES(2,5,20);
INSERT INTO test1 VALUES(2,6,20);
INSERT INTO test1 VALUES(2,7,20);
INSERT INTO test1 VALUES(2,8,20);
INSERT INTO test1 VALUES(2,9,20);
INSERT INTO test1 VALUES(2,10,20);
INSERT INTO test1 VALUES(2,11,20);
INSERT INTO test1 VALUES(2,12,20);
==================================
# 쿼리 1 : SUM(DECODE(...)) 이용
==================================
SELECT acct,
SUM(DECODE(month,1,amt,0)) as MON1,
SUM(DECODE(month,2,amt,0)) as MON1,
SUM(DECODE(month,3,amt,0)) as MON1,
SUM(DECODE(month,4,amt,0)) as MON1,
SUM(DECODE(month,5,amt,0)) as MON1,
SUM(DECODE(month,6,amt,0)) as MON1,
SUM(DECODE(month,7,amt,0)) as MON1,
SUM(DECODE(month,8,amt,0)) as MON1,
SUM(DECODE(month,9,amt,0)) as MON1,
SUM(DECODE(month,10,amt,0)) as MON1,
SUM(DECODE(month,11,amt,0)) as MON1,
SUM(DECODE(month,12,amt,0)) as MON1
FROM test1
GROUP BY acct;
==================================
# 쿼리 2 : 다른 함수 이용
==================================
SELECT acct,
SUM(amt * (1 - ABS(SIGN(month - 1)))) as Mon1,
SUM(amt * (1 - ABS(SIGN(month - 2)))) as Mon2,
SUM(amt * (1 - ABS(SIGN(month - 3)))) as Mon3,
SUM(amt * (1 - ABS(SIGN(month - 4)))) as Mon4,
SUM(amt * (1 - ABS(SIGN(month - 5)))) as Mon5,
SUM(amt * (1 - ABS(SIGN(month - 6)))) as Mon6,
SUM(amt * (1 - ABS(SIGN(month - 7)))) as Mon7,
SUM(amt * (1 - ABS(SIGN(month - 8)))) as Mon8,
SUM(amt * (1 - ABS(SIGN(month - 9)))) as Mon9,
SUM(amt * (1 - ABS(SIGN(month - 10)))) as Mon10,
SUM(amt * (1 - ABS(SIGN(month - 11)))) as Mon11,
SUM(amt * (1 - ABS(SIGN(month - 12)))) as Mon12
FROM test1
GROUP BY acct;
두번째 쿼리 만든사람 대단하지 않습니까..
http://www.ncst.ernet.in/~sybase/faq/Q2.3.html 에 있길래 가져왔는데,
아주 감탄스럽습니다..
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
이상 제가 테스트한 내용이구요, 여러분들의 필요에 맞게 활용하시면 되겠네요.
SUM 뿐만 아니라 AVG, MAX도 DECODE함수와 함께 쓰일 수 있습니다.
정보가 더 필요하다 생각되시면 찾아보면 될거같군요.
sample1)
select codeclass 망구분, codedisp 장비종류, codedesc 장비설명,
sum(decode(equipstatus, '01', cnt, 0)) 운용,
sum(decode(equipstatus, '02', cnt, 0)) 철거예정,
sum(decode(equipstatus, '05', cnt, 0)) 불용대상,
sum(cnt) 시스템수
from
(SELECT d.codedisp codeclass, c.codedisp , c.codedesc, m.equipstatus, count(*) cnt
FROM oss_equipmast m, oss_codemast c,oss_codemast d
WHERE equiptypecode not in ('BTS')
AND c.category = 'EquipType'
AND m.equiptypecode = c.code
AND d.category = 'CodeClass'
AND c.class = d.code
AND c.class != 'TRANSMISSION'
GROUP BY d.codedisp,c.codedisp, c.codedesc, m.equipstatus
)
GROUP BY codeclass, codedisp, codedesc
ORDER BY 1,2;
SELECT MODEL_SEQ_NO
, MIN(DECODE(EXAM_TYPE, '00002', PLATFORM_FAIL, NULL)) PLATFORM_FAIL
, MIN(DECODE(EXAM_TYPE, '00003', FUNC_FAIL, NULL)) FUNC_FAIL
FROM (SELECT A.MODEL_SEQ_NO
, A.EXAM_TYPE
, A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00002', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')' PLATFORM_FAIL --플랫폼검수불합격
, A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00003', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')' FUNC_FAIL --기능검수불합격
FROM T_PE_EXAM_INFO A -- 현재차수
, (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME FROM T_PE_EXAM_INFO WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO AND EXAM_MOTION_STATUS = '00004' GROUP BY MODEL_SEQ_NO, EXAM_TYPE ) B -- 이전차수
, T_PE_EXAMSTD_RESULT C
WHERE 1 = 1
AND A.MODEL_SEQ_NO = :model_seq_no
AND A.MODEL_SEQ_NO = B.MODEL_SEQ_NO
AND A.EXAM_TYPE = B.EXAM_TYPE
AND A.GEREXM_LOOP_TIME = B.GEREXM_LOOP_TIME
AND A.MODEL_SEQ_NO = C.MODEL_SEQ_NO
AND A.EXAM_TYPE = C.EXAM_TYPE
AND A.GEREXM_LOOP_TIME =C.GEREXM_LOOP_TIME
GROUP BY A.MODEL_SEQ_NO, A.EXAM_TYPE, A.GEREXM_LOOP_TIME)
GROUP BY MODEL_SEQ_NO;
SELECT VM.MODEL_SEQ_NO
, VM.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( TESTRESULT_CODE,'00002',DECODE(EXAMSTD_SN, 'EF', 0, 1),0 )) || ')' PLATFORM_FAIL
, V2.FUNC_FAIL
FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
FROM T_PE_EXAM_INFO
WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
AND EXAM_TYPE = '00002'
AND EXAM_MOTION_STATUS = '00004'
GROUP BY MODEL_SEQ_NO, EXAM_TYPE) VM
, T_PE_EXAMSTD_RESULT V1
, (SELECT A1.MODEL_SEQ_NO
, A1.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( A2.TESTRESULT_CODE,'00002',DECODE(A2.EXAMSTD_SN, 'EF', 0, 1),0 )) || ')' FUNC_FAIL
FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
FROM T_PE_EXAM_INFO
WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
AND EXAM_TYPE = '00003'
AND EXAM_MOTION_STATUS = '00004'
GROUP BY MODEL_SEQ_NO, EXAM_TYPE) A1
, T_PE_EXAMSTD_RESULT A2
WHERE 1 = 1
AND A1.MODEL_SEQ_NO = A2.MODEL_SEQ_NO
AND A1.EXAM_TYPE = A2.EXAM_TYPE
AND A1.GEREXM_LOOP_TIME = A2.GEREXM_LOOP_TIME
GROUP BY A1.MODEL_SEQ_NO, A1.GEREXM_LOOP_TIME) V2
WHERE 1 = 1
AND VM.MODEL_SEQ_NO = :MODEL_SEQ_NO
AND VM.MODEL_SEQ_NO = V1.MODEL_SEQ_NO
AND VM.EXAM_TYPE = V1.EXAM_TYPE
AND VM.GEREXM_LOOP_TIME =V1.GEREXM_LOOP_TIME
AND VM.MODEL_SEQ_NO = V2.MODEL_SEQ_NO
GROUP BY VM.MODEL_SEQ_NO, VM.GEREXM_LOOP_TIME, V2.FUNC_FAIL
;
Prev
Rss Feed