| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | ||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | 30 | 31 |
Tags
- node.js
- jQuery
- MySQL
- GPS
- 선택적조인
- rowspan
- Android
- Spring
- PLSQL
- sencha touch
- 전자정부프레임워크
- dock
- 가우스
- tomcat
- swingx
- appspresso
- Ajax
- MFC
- JDOM
- jsr 296
- oracle
- iBATIS
- WebLogic
- Google Map
- ibsheet
- Struts
- Eclipse
- phonegap
- JSON
- PHP
Archives
- Today
- Total
Where The Streets Have No Name
rollup의 기본적인 활용 예 본문
출처 : http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=83&page=1&position=1
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)
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)