[CSS] 참고 사이트

http://www.code-sucks.com/css%20layouts/

http://www.thenoodleincident.com/tutorials/box_lesson/boxes.html

http://intensivstation.ch/en/templates/

http://www.cssplay.co.uk/layouts/

http://www.maxdesign.com.au/articles/css-layouts/

http://www.cssfill.com/

 

http://blog.html.it/layoutgala/

http://tjkdesign.com/articles/one_html_markup_many_css_layouts.asp

http://www.glish.com/css/

http://www.bluerobot.com/web/layouts/

http://webdesign.about.com/od/websitetemplates/a/bl_layouts.htm

http://findmebyip.com/litmus#target-selector

http://pmob.co.uk/

http://openwebdesign.org/browse.php

http://www.solucija.com/templates/free

http://www.benmeadowcroft.com/webdev/

http://www.mollio.org/

[Oracle] 오라클 힌트 사용표

*오라클 힌트 사용표

 

[출처] 오라클 힌트|작성자 멋둥이

INDEX ACCESS OPERATION 관련 HINT
HINT	내용	사용법
INDEX	INDEX를 순차적으로 스캔	INDEX(TABLE명, INDEX명)
INDEX_DESC	INDEX를 역순으로 스캔	INDEX_DESC(TABLE명, INDEX명)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE명, INDEX명)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE명,INDEX명)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE명,INDEX명)
AND_EQUALS	INDEX MERGE 수행	AND_EQUALS(INDEX_NAME, INDEX_NAME)
FULL	FULL SCAN	FULL(TALBE명)
JOIN ACCESS OPERATION 관련 HINT
HINT	내용	사용법
USE_NL	NESTED LOOP JOIN	USE_NL(TABLE1, TABLE2)
USE_MERGE	SORT MERGE JOIN	USE_MERGE(TABBLE1, TABLE2)
USE_HASH	HASH JOIN	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTI JOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMIJOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)
JOIN시 DRIVING 순서 결정 HINT
HINT	내용
ORDERED	FROM 절의 앞에서부터 DRIVING
DRIVING	해당 테이블을 먼저 DRIVING- driving(table)
기타 힌트
HINT	내용
append	insert 시 direct loading
parallel	select, insert 시 여러 개의 프로세스로 수행- parallel(table, 개수)
cache	데이터를 메모리에 caching
nocache	데이터를 메모리에 caching하지 않음
push_subq	subquery를 먼저 수행
rewrite	query rewrite 수행
norewrite	query rewrite 를  수행 못함
use_concat	in절을 concatenation access operation으로 수행
use_expand	in절을 concatenation access operation으로 수행 못하게 함
merge	view merging 수행
no_merge	view merging 수행 못하게 함

[ETC] 턱빠진 고양이

 

[Orace] START WITH ... CONNECT BY 절을 사용한 계층형쿼리

 2010/01/17 17:56

http://blog.naver.com/minis24/80099770688

START WITH ... CONNECT BY 절

 

오라클 같은 관계형 DBMS 에서 관계형(Relation) 이란 계층형(Hierarchical) 과는 상반되는 개념이다.

따라서 SQL을 사용해서 이러한 계층형 정보를 표현하기가 매우 어렵다.

 

관계형이란 말은 서로 평등하고 수평적인 관계를 의미하는 반면에 계층형이란 구조는 계급적이고 수직적인 관계를 가진다.

평면적인 데이터 구조를 수직적인 구조로 표현하는데 SQL 만으로는 한계가 있기 때문에

오라클에서는 이러한 계층적인 정보를 표현할 수 있도록 특별한 문장을 지원하는데 그것이 바로

START WITH ... CONNECT BY 절이다. 

 

문법)

[[START WITH 조건 1]   CONNECT BY 조건 2]  START WITH 조건 1 : 루트 노드를 식별한다. 조건 1을 만족하는 모든 ROW들이 루트노드가 된다. START WITH 절을 생략할 수도 있는데 이 때는 모든 ROW 가 루트노드로 간주된다. 조건 1에는 서브쿼리가 올수 있다.   CONNECT BY 조건 2 부모와 자식노드들 간의 관계를 명시하는 부분이다. 조건 2에는 반드시 PRIOR 연산자를 포함시켜야 한다.PRIOR 연산자는 부모노드의 컬럼을 식별하는데 사용된다. 조건2 에는 서브쿼리가 올수 없다.

 

계층형 쿼리의 로직

 

다음과 같은 테이블에 대해서 계층형 쿼리의 실제로 어떻게 처리하는지 보자.

CREATE TABLE BOM ( ITEM_ID INTEGER NOT NULL, PARENT_ID INTEGER, ITEM_NAME VARCHAR2(20) NOT NULL, ITEM_QTY INTEGER, PRIMARY KEY (ITEM_ID))

 

ITEM_ID	PARENT_ID	ITEM_NAME	ITEM_QTY
1001	NULL	컴퓨터	1
1002	1001	본체	1
...	...	...	...
1004	10001	프린터	1
1006	10002	랜카드	1
...	...	...	...

 

 

첫번째

가장 상위에 있는 루트노드에 해당하는 항목이 무엇인지 알아낸다.

PARENT_ID 가 NULL 인 항목이 가장 상위 품목이므로 START WITH 절 다음에 루트노드를 식별하는 구분인

PARENT_ID IS NULL 을 추가한다.

START WITH PARENT_ID IS NULL

두번째

각각의 항목들 간에 부모와 자식노드를 식별해야 한다.

부모와 자식노드들 간의 관계를 연결하는 부분이 바로 CONNECT BY 절이다.

가장 상위 품목을 제외하고 다른 모든 품목들은 PARENT_ID 컬럼에 상위항목의 값(즉, 부모노드의 ITEM_ID) 을 가지고 있다.

이것을 조건으로 표현하면 PARENT_ID = ITEM_ID 이다

그리고 PARENT_ID 는 부모노드인 ITEM_ID 와 연결되므로 PRIOR 키워드는 ITEM_ID 앞에 붙는다.

 

CONNECT BY PRIOR ITEM_ID = PARENT_ID

PRIOR 연산자

 

오직 계층형 쿼리에서만 사용하는 연산자이다. CONNECT BY 절에서 해당 컬럼의 부모로우를 식별하는데 사용된다.

앞의 로직에서 본체의 PARENT_ID 컬럼에는 컴퓨터의 ITEM_ID 값을 가지고 있으므로 PRIOR 연산자가 ITEM_ID 앞에 붙게 된다.

 

레벨 의사컬럼

 

계층형 정보를 표현할때의 레벨을 나타낸다.

루트 노드가 1LEVEL , 루트의 자식노드가 2LEVEL , 그 자식 노드가 3LEVEL 이 된다.

 

 

ex)

SELECT LPAD(' ' ,2 * (LEVEL-1)) || ITEM_NAME ITEM_NAMES

FROM BOM

START WITH PARENT_ID IS NULL

CONNECT BY PRIOR ITEM_ID = PARENT_ID

 

계층형 쿼리의 정렬

 

계층형 쿼리에는 이미 레벨과 부모-자식관계를 고려해서 순서를 결정하는 로직이 숨어 있다.

그러니깐 굳이 ORDER BY 절을 사용하지 않더라도 계층과 레벨에 따라 스스로 알아서 로우들을 정렬한다.

 

따라서 계층형 쿼리에서 ORDER BY 절을 사용하는것은 오라클 SQL 엔진이 레벨에 따라서 순서를 맞추어 정렬한 결과를

또다시 정렬하는 셈이 된다.

 

그러니 굳이 필요한 경우가 아니라면 계층형 쿼리에서 ORDER BY 절은 사용하지 말자.

 

ORACLE, 오라클, 계층형쿼리, START, WITH, CONNECT, BY

[출처] [ oracle 10g ] START WITH ... CONNECT BY 절을 사용한 계층형쿼리 |작성자 GENERAL

[Oracle] MODEL절을 사용해서 엑셀 흉내내기 [oracle 10g]

MODEL 절은 SELECT 구문과 같이 사용되어 결과셋의 각각의 항목들을 연관배열 처럼 취급해서 이러한 항목들을

일정한 계산규칙에 따라 계산을 수행한뒤 새로운 결과셋을 리턴한다.

 

이 방법을 사용해서  저는 간단하게 월별 합계를 구해봤는데요..

장점은 테이블을 이차원배열로 접근할 수 있어서 엑셀의 함수처럼 계산을 할수 있다는 거거든요..

앞뒤 로우의 특정행에 접근할 수 있다는거 ~~이거 되게 좋네요.....

 

앞의 로우의 값을 사용해서 다음 로우의 값을 예측하는 것이 가능해지니 사용방법은 무궁무진해 질듯 합니다.

 

[구문형식]

SELECT ...   FROM ...   MODEL [main]             [ reference models ]             [ PARTITION BY (<컬럼>) ]             DIMENSION BY (<컬럼>)  .................................... 로우를 식별할 기준이 되는 컬럼을 명시           MEASURES (<컬럼>)  ........................................ 새로 계산이 되는 컬럼 명시                    [ IGNORE NAV ] | [ KEEP NAV ]             [ RULES                    [ UPSERT | UPDATE ]                    [ AUTOMATIC ORDER | SEQUENTIAL ORDER ]                    [ ITERATE (n) [UNTIL <CONDITION> ]             ]                    (<CELL_ASSIGNMENT > = <expression> ...)   ※ RULES 절에는 MEASURES 절에서 명시한 컬럼들만 기술 할 수 있다.

 

 

[ex]

다음과 같은 테이블을 생성한다.

 

[SELECT * FROM MODEL_T]

 

 

[MODEL 절을 사용한 쿼리]

SELECT TERM,         ORI_SAL,         TAXES FROM model_t MODEL     DIMENSION BY (TERM)     MEASURES (SALARY ORI_SAL, SALARY TAXES ) RULES(     ORI_SAL['200701'] = ORI_SAL['200701'] * 0.33,      -- 순서를 함 바꿔봤다.^^     TAXES ['200702'] = TAXES ['200702'] * 0.33,     TAXES ['200703'] = TAXES ['200703'] * 0.33,     TAXES ['200704'] = TAXES ['200704'] * 0.33,     TAXES ['200705'] = TAXES ['200705'] * 0.33,     TAXES ['200706'] = TAXES ['200706'] * 0.33 ) ORDER BY 1   ※ DIMENSION 컬럼이 TERM 이다.    즉, DIMENSION 컬럼의 값을 연관배열의 KEY 처럼 사용해서 로우를 식별한다.    여기서는 '20070x' 라는 컬럼값으로 로우를 식별하고 있다.

 

[결과]

 

SELECT TERM,         SAL FROM model_t MODEL     DIMENSION BY (term )     MEASURES (SALARY sal) RULES(    SAL['QTR1'] = SAL['200701']+SAL['200702']+SAL['200703'],   SAL['TOTAL1'] = sum(sal)[term IN ('200701','200702') ]    SAL['TOTAL2'] = sum(sal)[term IN ('200701','200702') ] ) ORDER BY 1   ※ sum() 함수도 사용할 수 있는데 배열키값에 저런식으로도 사용이 가능하네요...

 

[결과]

 

 

 

※ MODEL 절을 사용할 경우 SELECT 문장의 컬럼명은 MEASURES 절에서 반드시 명시한

    것들만 올수 있다.

    따라서 그룹쿼리를 사용하기전에 MODEL절을 사용하여 계산을 마친 결과셋을 인라인뷰로

    사용해야한다.

    왜냐면 SELECT 구문의 컬럼명이 MODEL 절에 종속적이기 때문이다.

 

WITH TOTAL_T AS (     SELECT NO,                 CAMP_CODE,              GUBUN,              MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8,              MONTH_9, MONTH_10,MONTH_11,MONTH_12 ,              TOTAL     FROM DATA_1_T     MODEL         DIMENSION BY (ID )         MEASURES (TOTAL,MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7,                      MONTH_8, MONTH_9,MONTH_10, MONTH_11,MONTH_12,                      ID NO,GUBUN,CAMP_CODE)         RULES(             TOTAL[ ANY ] = MONTH_1[cv(ID)] + MONTH_2[CV(ID)] + MONTH_3[CV(ID)] + MONTH_4[CV(ID)] +                                     MONTH_5[CV(ID)] + MONTH_6[CV(ID)] + MONTH_7[CV(ID)] + MONTH_8[CV(ID)] +                                     MONTH_9[CV(ID)] + MONTH_10[CV(ID)] + MONTH_11[CV(ID)] + MONTH_12[CV(ID)]         ) )  SELECT             ROW_NUMBER() OVER (ORDER BY 1) RN1,             ROW_NUMBER() OVER( PARTITION BY DTL_RGN_CODE ORDER BY GUBUN ) RN2,                       DECODE(                 ROW_NUMBER() OVER(PARTITION BY DTL_RGN_CODE ORDER BY GUBUN ) ,                 1,                 DTL_RGN_CODE,                 ' ') "그 룹",             NVL2(GUBUN,GUBUN,'합계') GUBUN,             sum(MONTH_1) jan,             sum(MONTH_2) feb,             sum(MONTH_3) mar,             sum(MONTH_4) apr,             sum(MONTH_5) may,             sum(MONTH_6) jun,             sum(MONTH_7) jul,             sum(total) total               from total_t a, CAMP_INFO_T b     where a.CAMP_CODE = b.CAMP_CODE     group by rollup(DTL_RGN_CODE,gubun)

 

 

[결과]

 

[출처] [ oracle 10g ] 오라클 MODEL절을 사용해서 엑셀 흉내내기|작성자 GENERAL

rollup의 기본적인 활용 예

rollup의 기본적인 활용 예   (Posted: 2006-05-22 21:34:23) by 김홍선 (Posts: 3198 - Registered: 2006-04-15)

글쓴이 : 김홍선 rollup 을 활용하는 기본적인 예제이다. 예제1) all_objects 테이블에서 rownum 순서로 10개씩 object_id 합의 소계를 구해 나간다. 쿼리를 주의깊게 보고 응용해보면 좋을 것이다. SELECT   owner, object_name, rn, SUM (object_id)     FROM (SELECT owner, object_name, object_id, ROWNUM rn             FROM all_objects            WHERE ROWNUM <= 100) GROUP BY CEIL (rn / 10), ROLLUP ((owner, object_name, rn)) ORDER BY CEIL (rn / 10) 쿼리결과 --------------------------------------------------------- OWNER  OBJECT_NAME                    RN   SUM(OBJECT_ID) SYS    /1005bd30_LnkdConstant         1            17,818 SYS    /10076b23_OraCustomDatumClosur 2             7,460 SYS    /10297c91_SAXAttrList          3            23,253 SYS    /103a2e73_DefaultEditorKitEndP 4            14,130 SYS    /1048734f_DefaultFolder        5            22,818 SYS    /10501902_BasicFileChooserUINe 6            10,074 SYS    /105072e7_HttpSessionBindingEv 7            23,458 SYS    /106ba0a5_ArrayEnumeration     8            23,513 SYS    /106faabc_BasicTreeUIKeyHandle 9             9,750 SYS    /10744837_ObjectStreamClass2   10           11,666                                                   163,940 SYS    /1079c94d_NumberConstantData   11           17,418 SYS    /10804ae7_Constants            12            7,646 SYS    /108343f6_MultiColorChooserUI  13           19,280 SYS    /10845320_TypeMapImpl          14            9,150 SYS    /10948dc3_PermissionImpl       15            6,330 SYS    /1095ce9b_MultiComboBoxUI      16           19,282 SYS    /109a284b_OracleXMLStaticQuery 17           23,557 SYS    /109cbb8e_SpanShapeRendererSim 18           14,802 SYS    /10a45bfe_ProfilePrinterErrors 19           20,589 SYS    /10a793fd_LocaleElements_iw    20           18,882                                                   156,936 .............. .............. .............. 예제 2) 위의 결과를 약간 변형해 보자. 10개씩 소계가 나오고 마지막에 총계가 나오게 하고 싶다면 어떻게 할 것인가? 위의 쿼리와 달라진 부분을 비교하면서 아래 쿼리를 주의깊게 보자. SELECT   owner, object_name, rn, SUM (object_id)     FROM (SELECT owner, object_name, object_id, ROWNUM rn             FROM all_objects            WHERE ROWNUM <= 100) GROUP BY ROLLUP (CEIL (rn / 10), (owner, object_name, rn)) ORDER BY CEIL (rn / 10) 쿼리결과 ---------------------------------------------------------- OWNER  OBJECT_NAME                     RN   SUM(OBJECT_ID) .............. .............. .............. SYS    /131de077_ShellServer           83           19,593 SYS    /1327266a_BasicTreeUITreeAddSe  84            9,734 SYS    /1336fb89_SunGraphicsEnvironme  85           14,960 SYS    /133f577c_XSUMessages_fiproper  86           23,567 SYS    /1343bb44_EventDispatchThreadE  87           14,666 SYS    /1352fb09_ConfirmRemovePolicyE  88            6,282 SYS    /135a6a83_CacheCustomizerError  89           20,277 SYS    /13701868_Handler               90           21,957                                                    154,934 SYS    /13782417_EnumControl           91           19,064 SYS    /13827da0_KeyboardManagerCompo  92           13,330 SYS    /1385227f_MapRegionContainment  93           13,576 SYS    /13862faa_TranslatorErrorsText  94           20,943 SYS    /1386780a_QsmaReportRepeating   95           24,306 SYS    /1393a6b2_CertificateX509Key    96           16,698 SYS    /13950738_LocaleElements_nl_NL  97           18,914 SYS    /13a9ccaf_MessageIDTerm         98           22,703 SYS    /13b25de9_BasicButtonListenerP  99           10,236 SYS    /13b4be3a_ModifyPermissionExce 100           16,208                                                    175,978                                                  1,528,258 예제3) 이번에는 10개 단위로 소계와 누계를 구해 나가는 쿼리를 만들어 보자. 즉 쿼리 결과가 아래와 같이 나오도록 한다. 쿼리결과 --------------------------------------------------------- OWNER  OBJECT_NAME                    RN              SUM SYS    /1005bd30_LnkdConstant         1            17,818 SYS    /10076b23_OraCustomDatumClosur 2             7,460 SYS    /10297c91_SAXAttrList          3            23,253 SYS    /103a2e73_DefaultEditorKitEndP 4            14,130 SYS    /1048734f_DefaultFolder        5            22,818 SYS    /10501902_BasicFileChooserUINe 6            10,074 SYS    /105072e7_HttpSessionBindingEv 7            23,458 SYS    /106ba0a5_ArrayEnumeration     8            23,513 SYS    /106faabc_BasicTreeUIKeyHandle 9             9,750 SYS    /10744837_ObjectStreamClass2   10           11,666                                                   163,940 -- 소계                                                   163,940 -- 누계 SYS    /1079c94d_NumberConstantData   11           17,418 SYS    /10804ae7_Constants            12            7,646 SYS    /108343f6_MultiColorChooserUI  13           19,280 SYS    /10845320_TypeMapImpl          14            9,150 SYS    /10948dc3_PermissionImpl       15            6,330 SYS    /1095ce9b_MultiComboBoxUI      16           19,282 SYS    /109a284b_OracleXMLStaticQuery 17           23,557 SYS    /109cbb8e_SpanShapeRendererSim 18           14,802 SYS    /10a45bfe_ProfilePrinterErrors 19           20,589 SYS    /10a793fd_LocaleElements_iw    20           18,882                                                   156,936 -- 소계                                                   320,876 -- 누계 .............. .............. .............. GROUPING_ID 함수를 쓴 쿼리는 아래와 같다. SELECT   owner, object_name, rn,          DECODE (GROUPING_ID (1),                  0, SUM (object_id),                  1, SUM (object_id) OVER (ORDER BY CEIL (rn / 10))                 ) SUM     FROM (SELECT owner, object_name, object_id, ROWNUM rn             FROM all_objects            WHERE ROWNUM <= 100) GROUP BY CEIL (rn / 10), ROLLUP (1, (owner, object_name, rn, object_id)) 예제4) 10개 단위로 소계와 소계평균, 그리고 제일 아래쪽에 합계와 합계평균이 나오도록 한다. OWNER  OBJECT_NAME                    RN              SUM --------------------------------------------------------- SYS    /1005bd30_LnkdConstant         1            17,818 SYS    /10076b23_OraCustomDatumClosur 2             7,460 SYS    /10297c91_SAXAttrList          3            23,253 SYS    /103a2e73_DefaultEditorKitEndP 4            14,130 SYS    /1048734f_DefaultFolder        5            22,818 SYS    /10501902_BasicFileChooserUINe 6            10,074 SYS    /105072e7_HttpSessionBindingEv 7            23,458 SYS    /106ba0a5_ArrayEnumeration     8            23,513 SYS    /106faabc_BasicTreeUIKeyHandle 9             9,750 SYS    /10744837_ObjectStreamClass2   10           11,666                                                   163,940 -- 소계                                                    16,394 -- 소계평균 SYS    /1079c94d_NumberConstantData   11           17,418 SYS    /10804ae7_Constants            12            7,646 SYS    /108343f6_MultiColorChooserUI  13           19,280 SYS    /10845320_TypeMapImpl          14            9,150 SYS    /10948dc3_PermissionImpl       15            6,330 SYS    /1095ce9b_MultiComboBoxUI      16           19,282 SYS    /109a284b_OracleXMLStaticQuery 17           23,557 SYS    /109cbb8e_SpanShapeRendererSim 18           14,802 SYS    /10a45bfe_ProfilePrinterErrors 19           20,589 SYS    /10a793fd_LocaleElements_iw    20           18,882                                                   156,936 -- 소계                                                    15,693.6 -- 소계평균 .............. .............. .............. SYS     /13782417_EnumControl           91          19114 SYS     /13827da0_KeyboardManagerCompo  92          13380 SYS     /1385227f_MapRegionContainment  93          13626 SYS     /13862faa_TranslatorErrorsText  94          20993 SYS     /1386780a_QsmaReportRepeating   95          24356 SYS     /1393a6b2_CertificateX509Key    96          16748 SYS     /13950738_LocaleElements_nl_NL  97          18964 SYS     /13a9ccaf_MessageIDTerm         98          22753 SYS     /13b25de9_BasicButtonListenerP  99          10286 SYS     /13b4be3a_ModifyPermissionExce 100          16258                                                    176478 -- 소계                                                     17647.8 -- 소계평균                                                   1533258 -- 합계                                                     15332.58 -- 합계평균 쿼리는 아래와 같다. SELECT   OWNER        , OBJECT_NAME        , RN        , DECODE (GROUPING (0)                , GROUPING (1), SUM (OBJECT_ID)                , AVG (OBJECT_ID)                 ) SUM_OBJECT_ID     FROM (SELECT OWNER                , OBJECT_NAME                , OBJECT_ID                , ROWNUM RN             FROM ALL_OBJECTS            WHERE ROWNUM <= 100) GROUP BY ROLLUP (0, CEIL (RN / 10), 1, (OWNER, OBJECT_NAME, RN)) ORDER BY CEIL (RN / 10)        , RN        , MOD (GROUPING (0) + GROUPING (1), 2) * 글쓴이 : 김홍선 * 위 내용을 이 곳에서 처음 보신분은 다른 곳에 게재하실 때 반드시 출처를 밝혀주시기 바랍니다. * 위 내용에 관해서 잘못된 부분이 있거나 질문이 있으신 분은 답글로 알려주시기 바랍니다.

RE: rollup의 기본적인 활용 예   (Posted: 2006-06-09 17:06:29)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 문제 하나) 테블 : TABA --------------------------------------------- AREA_CODE       USER_NAME       ORDER_STATE --------------------------------------------- A               N01               2 A               N01               3 A               N01               3 A               N01               2 A               N02               2 A               N02               3 B               N04               3 B               N04               4 B               N04               1 C               N05               2 C               N05               3 D               N06               3 D               N06               4 D               N06               4 D               N06               2 ---------------------------------------------- 희망 결과:                                 전부            상태가 3,4 인 수    3,4인페센트       랭킹 --------------------------------------------------------------------------------------- AREA_CODE       USER_NAME       ALL_COUNT       SUCCESS_COUNT    PERCENT         RANKING --------------------------------------------------------------------------------------- A               N01             4               2                0.500           3 A               N02             2               1                0.500           4 A               TOTAL           6               3                0.500           NULL              <소합계 B               N04             3               2                0.667           2 B               TOTAL           3               2                0.667           NULL              <소합계 C               N05             2               1                0.500           NULL C               TOTAL           2               1                0.500           NULL              <소합계 D               N06             4               3                0.75            1 D               TOTAL           4               3                0.75            NULL              <소합계 ---------------------------------------------------------------------------------------------------------------- # ORDER_STATE 가 3 혹은 4인 경우 가  USER_NAME 로 등록되여있는 합계 와의 퍼센트 순위에 따라 USER_NAME당 총건수가 2 이상인 것들만 랭킹을 출력 가운데 합계표시 ..  AREA_CODE 별 소합계를 삽입 포인트: 1. ALL_COUNT       는 AREA_CODE,USER_NAME 를 group 한합게 입니다. 2. SUCCESS_COUNT   는 AREA_CODE,USER_NAME 를 group 로한 ORDERSTATE 가 3, 4인 겁니다 2. (SUCCESS_COUNT / ALL_COUNT) % 를 구하려는겁니다. 3. ALL_COUNT > 2  인 것들만 Ranking 합니다...　2보다 작은것과 TOTAL 는 NULL 랭킹... 데이타는... WITH taba AS      (         SELECT 'A' area_code, 'N01' user_name, 2 order_state           FROM DUAL         UNION ALL         SELECT 'A', 'N01', 3           FROM DUAL         UNION ALL         SELECT 'A', 'N01', 3           FROM DUAL         UNION ALL         SELECT 'A', 'N01', 2           FROM DUAL         UNION ALL         SELECT 'A', 'N02', 2           FROM DUAL         UNION ALL         SELECT 'A', 'N02', 3           FROM DUAL         UNION ALL         SELECT 'B', 'N04', 3           FROM DUAL         UNION ALL         SELECT 'B', 'N04', 4           FROM DUAL         UNION ALL         SELECT 'B', 'N04', 1           FROM DUAL         UNION ALL         SELECT 'C', 'N05', 2           FROM DUAL         UNION ALL         SELECT 'C', 'N05', 3           FROM DUAL         UNION ALL         SELECT 'D', 'N06', 3           FROM DUAL         UNION ALL         SELECT 'D', 'N06', 4           FROM DUAL         UNION ALL         SELECT 'D', 'N06', 4           FROM DUAL         UNION ALL         SELECT 'D', 'N06', 2           FROM DUAL         UNION ALL         SELECT 'E', 'N09', 3           FROM DUAL) 쿼리는... SELECT   area_code, DECODE (user_name, NULL, 'TOTAL', user_name) user_name,          COUNT (*) all_count,          COUNT (DECODE (order_state, 3, 1, 4, 1)) success_count,          ROUND (COUNT (DECODE (order_state, 3, 1, 4, 1)) / COUNT (*),                 3                ) PERCENT,          (CASE              WHEN COUNT (*) > 1 AND user_name IS NOT NULL                 THEN ROW_NUMBER () OVER (ORDER BY COUNT (DECODE (order_state,3,1,4,1))                       * DECODE (user_name, NULL, NULL, 1)                       / DECODE (COUNT (*), 1, NULL, COUNT (*)) DESC NULLS LAST)           END          ) ranking     FROM taba GROUP BY area_code, ROLLUP ((area_code, user_name)) ORDER BY area_code, user_name

RE: rollup의 기본적인 활용 예   (Posted: 2006-10-10 14:15:09)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 문제) 테이블 구조 년월 / 구분 / 종류 / Size / 실적 이렇게 있습니다. 200701  /  R  /  H  /      /   10 200701  /  R  /  R  /      /   20 200701  /  R  /  V  /      /   15 200701  /  S  /  C  /  13  /   4 200701  /  S  /  C  /  25  /   5 200701  /  S  /  C  /  26  /   6 200701  /  S  /  C  /  13  /   7 200701  /  S  /  D  /  13  /   8 200701  /  S  /  D  /  14  /   1 200701  /  S  /  P  /      /   2 200701  /  S  /  P  /      /   6 200701  /  S  /  R  /  200 /   12 200701  /  S  /  R  /  210 /   15 200701  /  S  /  S  /  13  /   10 200701  /  S  /  S  /  25  /   5 200701  /  S  /  S  /  57  /   5 200701  /  S  /  S  /  58  /   7 이런 식으로 들어 있습니다. 중간 소계를 내고 하는 방법은 년월 / 구분 / 종류별 로 소계를 넣는 것이지요. 200701  /  R  /  H  /      /   10 200701  /  R  /  R  /      /   20 200701  /  R  /  V  /      /   15 200701  /  R  /  소계 /   /    45 200701  /  S  /  C  /  13  /   4 200701  /  S  /  C  /  25  /   5 200701  /  S  /  C  /  26  /   6 200701  /  S  /  C  /  13  /   7 200701  /  S  /  소계  /       /   22 200701  /  S  /  D  /  13  /   8 200701  /  S  /  D  /  14  /   1 200701  /  S  /  소계  /       /   9 200701  /  S  /  P  /      /   2 200701  /  S  /  P  /      /   6 200701  /  S  /  소계  /       /   8 200701  /  S  /  R  /  200 /   12 200701  /  S  /  R  /  210 /   15 200701  /  S  /  소계  /       /   27 200701  /  S  /  S  /  13  /   10 200701  /  S  /  S  /  25  /   5 200701  /  S  /  S  /  57  /   5 200701  /  S  /  S  /  58  /   7 200701  /  S  /  소계  /       /   27 구분이 R 일때는 종류에 상관없이 구분에 대해서만 소계를 보여 줘도 되고 안보여 줘도 됩니다. 구분이 S 일때는 위처럼 종류에 따라 소계를 꼭 보여 줘야 하거든요. 답변) 테이블 t 가 다음과 같을 때, with t as ( select '200701' ym, 'R' gb, 'H' type, null sizes, 10 amt from dual union all select '200701',    'R',    'R',     null, 20 from dual union all select '200701',    'R',    'V',     null, 15 from dual union all select '200701',    'S',    'C',    13,     4 from dual union all select '200701',    'S',    'C',    25,     5 from dual union all select '200701',    'S',    'C',    26,     6 from dual union all select '200701',    'S',    'C',    13,     7 from dual union all select '200701',    'S',    'D',    13,     8 from dual union all select '200701',    'S',    'D',    14,     1 from dual union all select '200701',    'S',    'P',     null,  2 from dual union all select '200701',    'S',    'P',     null,  6 from dual union all select '200701',    'S',    'R',    200,   12 from dual union all select '200701',    'S',    'R',    210,   15 from dual union all select '200701',    'S',    'S',    13,    10 from dual union all select '200701',    'S',    'S',    25,     5 from dual union all select '200701',    'S',    'S',    57,     5 from dual union all select '200701',    'S',    'S',    58,     7 from dual) 구분이 R 일때도 종류에 따라 소계를 보여주려면 아래와 같이 하고, SELECT   ym        , gb        , TYPE        , SIZES        , SUM (amt)     FROM t GROUP BY ym        , gb        , TYPE        , ROLLUP ((SIZES, ROWNUM)) 구분이 R 일때는 종류에 따라 소개를 보여주지 않는다고 하면 아래와 같이, SELECT   ym        , gb        , TYPE        , SIZES        , SUM (amt)     FROM t GROUP BY ym        , gb        , DECODE (gb, 'S', TYPE, 'R', '1')        , ROLLUP ((TYPE, SIZES, ROWNUM)) 마지막으로 '소계' 라는 문자열도 추가하고 싶다면 아래와 같이 한다. SELECT   ym        , gb        , NVL2 (TYPE, TYPE, '소계')        , SIZES        , SUM (amt)     FROM t GROUP BY ym        , gb        , DECODE (gb, 'S', TYPE, 'R', '1')        , ROLLUP ((TYPE, SIZES, ROWNUM)) 쿼리 3개를 하나씩 테스트 해보면 되겠다.

RE: rollup의 기본적인 활용 예   (Posted: 2006-10-10 16:05:45)    새창으로 by 쭝쓰기   (Posts: 13 - Registered: 2006-05-18)

RE: rollup의 기본적인 활용 예   (Posted: 2006-11-06 23:25:35)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 문제) with t as ( select '2002' year from dual union all select '2004' from dual union all select '2005' from dual union all select '2006' from dual) 위와 같은 테이블이 있을때, max(year)+1 한 값을 한 행 추가해서 나오도록, 즉 아래와 같이 나오도록 하려면 어떻게 쿼리를 만들어 줘야 할까? 2002 2004 2005 2006 2007 답변) 역시 아래와 같이 rollup 을 쓰면 간단히 된다. 나름대로 쓸모 있는 쿼리 되겠다. SELECT   NVL (YEAR, MAX (YEAR) + 1)     FROM t GROUP BY ROLLUP (YEAR)

RE: rollup의 기본적인 활용 예   (Posted: 2007-02-26 17:04:14)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 문제) 아래와 같이 원시 데이터가 있습니다.    SELECT '2007-02-21' da, '1팀' a, '상담원1' b, '출근' c FROM DUAL UNION ALL    SELECT '2007-02-21' da, '1팀' a,'상담원2' b, '조퇴' c FROM DUAL UNION ALL    SELECT '2007-02-21' da, '1팀' a,'상담원3' b, '교육' c FROM DUAL UNION ALL    SELECT '2007-02-21' da, '2팀' a,'상담원5' b, '퇴사' c FROM DUAL  UNION ALL    SELECT '2007-02-22' da, '1팀' a, '상담원1' b, '결근' c FROM DUAL UNION ALL    SELECT '2007-02-22' da, '1팀' a,'상담원2' b, '출근' c FROM DUAL UNION ALL    SELECT '2007-02-22' da, '1팀' a,'상담원3' b, '교육' c FROM DUAL   --->>>   팀명  이름       21일   22일   ---- --------- ----- -----   1팀   상담원1   출근   결근   1팀   상담원2   조퇴   출근   1팀   상담원3   교육   교육   2팀   상담원5   퇴사 위와 같은 데이터를 이용해서 아래와 같은 결과값을 얻고자 합니다.   팀명  이름       21일   22일   ---- --------- ----- -----   합계  재직인원    4        3        근무인원    2        1   1팀   상담원1   출근   결근   1팀   상담원2   조퇴   출근   1팀   상담원3   교육   교육   소계  재직인원    3        3        근무인원    2        1           2팀   상담원5   퇴사   소계  재직인원    1        0        근무인원    0        0 * 재직인원 = 출근 + 조퇴 + 교육 + 퇴사 (즉, NULL이 아닌 사람의 합) * 근무인원 = 출근 + 조퇴 답변) 복잡한 조건식이 기대 된다는 것을 알 수 있다. 테이블을 아래와 같이 구성하자. with t as ( SELECT '2007-02-21' da, '1팀' a, '상담원1' b, '출근' c FROM DUAL UNION ALL SELECT '2007-02-21' da, '1팀' a,'상담원2' b, '조퇴' c FROM DUAL UNION ALL SELECT '2007-02-21' da, '1팀' a,'상담원3' b, '교육' c FROM DUAL UNION ALL SELECT '2007-02-21' da, '2팀' a,'상담원5' b, '퇴사' c FROM DUAL  UNION ALL SELECT '2007-02-22' da, '1팀' a, '상담원1' b, '결근' c FROM DUAL UNION ALL SELECT '2007-02-22' da, '1팀' a,'상담원2' b, '출근' c FROM DUAL UNION ALL SELECT '2007-02-22' da, '1팀' a,'상담원3' b, '교육' c FROM DUAL) 쿼리는 아래와 같다. SELECT   A1 "팀명"        , B1 "이름"        , (CASE WHEN B IS NOT NULL THEN B_21 ELSE '' || (CASE WHEN A1 IS NULL THEN D_21 ELSE C_21 END) END) "21일"        , (CASE WHEN B IS NOT NULL THEN B_22 ELSE '' || (CASE WHEN A1 IS NULL THEN D_22 ELSE C_22 END) END) "22일" FROM     (SELECT   A                  , B                  , (CASE                        WHEN B IS NOT NULL THEN A                        ELSE (CASE                                 WHEN GROUPING (0) = 0 AND GROUPING (1) = 0 THEN '소계'                                 WHEN A IS NULL AND GROUPING (0) = 0 AND GROUPING (1) = 1 THEN '합계'                              END)                     END                    ) A1                  , (CASE                        WHEN B IS NOT NULL THEN B                        ELSE (CASE                                 WHEN (    GROUPING (0) = 0                                       AND GROUPING (1) = 0)                              OR     (    A IS NULL                                      AND GROUPING (0) = 0                                      AND GROUPING (1) = 1)                                    THEN '재직인원'                                 ELSE '근무인원'                              END                             )                     END                    ) B1                  , MIN (DECODE (SUBSTR (DA, 9, 2), '21', C)) B_21                  , COUNT (DECODE (SUBSTR (DA, 9, 2), '21', C)) C_21                  , COUNT (CASE                              WHEN SUBSTR (DA, 9, 2) = '21'                           AND    C IN ('출근', '조퇴')                                 THEN 1                           END) D_21                  , MIN (DECODE (SUBSTR (DA, 9, 2), '22', C)) B_22                  , COUNT (DECODE (SUBSTR (DA, 9, 2), '22', C)) C_22                  , COUNT (CASE                              WHEN SUBSTR (DA, 9, 2) = '22'                           AND    C IN ('출근', '조퇴')                                 THEN 1                           END) D_22                  , GROUPING (0) GRP0                  , GROUPING (1) GRP1           FROM     T           GROUP BY ROLLUP (0, A, 1, (A, B))) A ORDER BY A NULLS FIRST, B, A1

참고자료   (Posted: 2007-03-16 10:38:31)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 준우아빠님이 제기하신 아래 문제도 참고하시기 바랍니다. http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1000&seq=1235

비율을 같이 구한다...   (Posted: 2007-03-20 10:17:49)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) rollup 과 ratio_to_report 함수를 같이 사용할 때 나타나는 한가지 문제를 주의해 보자. emp 테이블에서 소계를 구하는 하나의 예제를 본다. SELECT   DEPTNO        , EMPNO        , ENAME        , SUM (SAL) SUM_SAL FROM     EMP GROUP BY DEPTNO, ROLLUP ((EMPNO, ENAME))     DEPTNO      EMPNO ENAME         SUM_SAL ---------- ---------- ---------- ----------         10       7782 CLARK            2450         10       7839 KING             5000         10       7934 MILLER           1300         10                             8750         20       7369 SMITH             800         20       7566 JONES            2975         20       7788 SCOTT            3000         20       7876 ADAMS            1100         20       7902 FORD             3000         20                            10875         30       7900 JAMES             950         30       7499 ALLEN            1600         30       7521 WARD             1250         30       7654 MARTIN           1250         30       7698 BLAKE            2850         30       7844 TURNER           1500         30                             9400 부서별로  sal의 합계가 원하는대로 구해졌다. 자, 이 상태에서 sum_sal 옆에 각 사원의 sal 이 해당 부서에서 차지하는 비율을 ratio_to_report 함수를 이용해서 구해보자. 얼핏 생각하기에 ratio_to_report 함수를 쓰면 원하는 결과가 바로 나올것이라 예상된다. 정말로 그런가 한번 보자. (값을 정리하기 위해 round 함수도 사용했다.) SELECT   DEPTNO        , EMPNO        , ENAME        , SUM (SAL) SUM_SAL        , ROUND (100 * RATIO_TO_REPORT (SUM (SAL)) OVER (PARTITION BY DEPTNO), 2) RATIO FROM     EMP GROUP BY DEPTNO, ROLLUP ((EMPNO, ENAME))     DEPTNO      EMPNO ENAME         SUM_SAL      RATIO ---------- ---------- ---------- ---------- ----------         10       7782 CLARK            2450         14         10       7839 KING             5000      28.57         10       7934 MILLER           1300       7.43         10                             8750         50         20       7369 SMITH             800       3.68         20       7566 JONES            2975      13.68         20       7788 SCOTT            3000      13.79         20       7876 ADAMS            1100       5.06         20       7902 FORD             3000      13.79         20                            10875         50         30       7499 ALLEN            1600       8.51         30       7521 WARD             1250       6.65         30       7654 MARTIN           1250       6.65         30       7698 BLAKE            2850      15.16         30       7844 TURNER           1500       7.98         30       7900 JAMES             950       5.05         30                             9400         50 비율의 합계 값이 우리가 원하던 100 이 아니라 50 이 나왔다! 단순히 100 을 곱해준다고 해서 원하는 결과가 안 나온다는 얘기다. 50 즉, 우리가 원한 100 의 반 값이 나온 이유는, 알다시피 소계를 구한 값까지도 비율을 구할 때 분모에 참여했기 때문이다. 따라서 우리가 원하는 값을 얻기 위해서는 ration_to_report 함수에 100 이 아니라 200 을 곱해야 한다! 수정된 쿼리와 결과는 아래와 같다. 이제 우리가 원하던 결과가 제대로 나온다. SELECT   DEPTNO        , EMPNO        , ENAME        , SUM (SAL) SUM_SAL        , ROUND (200 * RATIO_TO_REPORT (SUM (SAL)) OVER (PARTITION BY DEPTNO), 2) RATIO FROM     EMP GROUP BY DEPTNO, ROLLUP ((EMPNO, ENAME))     DEPTNO      EMPNO ENAME         SUM_SAL      RATIO ---------- ---------- ---------- ---------- ----------         10       7782 CLARK            2450         28         10       7839 KING             5000      57.14         10       7934 MILLER           1300      14.86         10                             8750        100         20       7369 SMITH             800       7.36         20       7566 JONES            2975      27.36         20       7788 SCOTT            3000      27.59         20       7876 ADAMS            1100      10.11         20       7902 FORD             3000      27.59         20                            10875        100         30       7499 ALLEN            1600      17.02         30       7521 WARD             1250       13.3         30       7654 MARTIN           1250       13.3         30       7698 BLAKE            2850      30.32         30       7844 TURNER           1500      15.96         30       7900 JAMES             950      10.11         30                             9400        100

소계 참고   (Posted: 2007-04-05 11:42:53)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 문제) 테이블 t가 아래와 같을 때, A   B   C            D          E --- --- --- ---------- ---------- 111 aaa bbb         10         20 111 aaa bbb         10         20 222 aaa bbb         10         20 333 aaa bbb         10         20 333 bbb bbb         10         20 아래와 같은 결과를 얻고 싶다. A   B   C            D          E --- --- --- ---------- ---------- 111 aaa bbb         10         20 111 aaa bbb         10         20 소계                20         40 222 aaa bbb         10         20 소계                10         20 333 aaa bbb         10         20 소계                10         20 333 bbb bbb         10         20 소계                10         20 답변) t를 아래와 같이 만들면, with t as ( select '111' a, 'aaa' b, 'bbb' c, 10 d, 20 e from dual union all select '111', 'aaa', 'bbb', 10, 20 from dual union all select '222', 'aaa', 'bbb', 10, 20 from dual union all select '333', 'aaa', 'bbb', 10, 20 from dual union all select '333', 'bbb', 'bbb', 10, 20 from dual) 쿼리는 아래와 같다. (rownum의 역할을 주의깊게 보도록 하자.) SELECT   NVL2 (ROWNUM, A, '소계') A        , NVL2 (ROWNUM, B, NULL) B        , NVL2 (ROWNUM, C, NULL) C        , SUM (D) D        , SUM (E) E FROM     T GROUP BY A, B, C, ROLLUP (ROWNUM)

예제 하나   (Posted: 2008-10-10 22:13:01)    새창으로 by 김홍선   (Posts: 3198 - Registered: 2006-04-15) 아래의 'Q&A' 게시판에 올라온 질문도 예제로써 참고하자. http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1000&seq=3212

 

출처 : http://www.soqool.com/