{"id":470,"date":"2013-02-01T13:43:44","date_gmt":"2013-02-01T04:43:44","guid":{"rendered":"http:\/\/www.freesens.com\/x\/?p=470"},"modified":"2024-01-16T09:48:40","modified_gmt":"2024-01-16T00:48:40","slug":"oracle-sum-over","status":"publish","type":"post","link":"http:\/\/www.freesens.com\/x\/?p=470","title":{"rendered":"oracle sum() over()"},"content":{"rendered":"

\ucd9c\ucc98 :\u00a0http:\/\/blog.naver.com\/hjc426\/130037056133<\/a><\/p>\n

SUM() OVER() ORACLE \ub0b4\uc7a5 \ud568\uc218
\nSCOTT \uacc4\uc815\uc758 EMP TABLE\uc5d0\uc11c \ud14c\uc2a4\ud2b8\ub97c \uc9c4\ud589\ud55c\ub2e4
\n\ub2e4\uc74c\uc758 QUERY \uc744 \ubcf4\uba74 \uc774\ud574\uac00 \uc27d\ub2e4.
\n———————————————————————————————-
\nSELECT ENAME,
\nDEPTNO,
\nSAL,
\nSUM(SAL) OVER (PARTITION BY DEPTNO)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “\ubd80\uc11c\ubcc4 \uae09\uc5ec\ud569\uacc4”,
\nSUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL)\u00a0\u00a0\u00a0 “\ubd80\uc11c\ubcc4 \uae09\uc5ec\uc624\ub984\ucc28\uc21c \ub204\uc801\ud569”,
\nSUM(SAL) OVER (ORDER BY DEPTNO, SAL)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“\ubd80\uc11c\ubcc4 \uae09\uc5ec\uc624\ub984\ucc28\uc21c \ub204\uc801\ud569”,
\nSUM(SAL) OVER ()\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0“\uc804\uccb4 \uae09\uc5ec\ud569”
\nFROM EMP;
\n———————————————————————————————-
\n\uc704 QUERY\u00a0 \uc758 \uacb0\uacfc\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

\"\"<\/p>\n

\n

\uc6d0\ub798 SUM \ud568\uc218\ub294 GROUP \ud568\uc218\ub2e4.
\n\ub530\ub77c\uc11c \uc704\uc640 \uac19\uc740 \ubd80\ubd84\uc5d0\uc11c ENAME, DEPTNO, SAL \ub85c GROUP BY \uc744 \ud574\uc918\uc57c \ud55c\ub2e4.
\n\ud558\uc9c0\ub9cc SUM() OVER()\ub97c \uc0ac\uc6a9\ud560 \ub54c\ub294 GROUP BY \uac00 \ud544\uc694 \uc5c6\ub2e4<\/p>\n

1.\u00a0 SUM(COLUMN1) OVER(PARTITION BY COLUMN2) —>
\n\uc774\ub294 \ud2b9\uc815 \uceec\ub7fc2\ub85c \uadf8\ub8f9\ud551\u00a0\ud55c\u00a0\uceec\ub7fc1\uc758 SUM \uac12\uc744 \ubcf4\uc5ec\uc900\ub2e4
\n\uc704\uc5d0\uc11c\ucc98\ub7fc \ubd80\uc11c\ubcc4 \uae09\uc5ec\ud569\uc774 \ub41c\ub2e4. 10\ubd80\uc11c, 20\ubd80\uc11c \ub4f1\uc758 \uae09\uc5ec \ud569\uc744 \ubcf4\uc5ec\uc900\ub2e4<\/p>\n

2.\u00a0SUM(COLUMN1) OVER(PARTITION BY COLUMN2 ORDER BY COLUMN1) —>
\n\uc774\ub294 \uc704\uc5d0\uc11c\ubcf4\ub294 \uac83\ucc98\ub7fc \uceec\ub7fc2\ub85c \uadf8\ub8f9\ud551\ud55c \ub370\uc774\ud130\ub97c \uceec\ub7fc1\ub85c \uc624\ub984\ucc28\uc21c \uc815\ub82c\uc744 \ud558\uba74\uc11c SUM\uc744 \uad6c\ud55c\ub2e4.
\n\uc774\ub294 \uc704\uc758 \uc608\uc5d0\uc11c \ubcf4\ub294 \ubd80\uc11c\ubcc4 \uae09\uc5ec\uc758 \ub204\uc801\ub41c \ud569\uc744 \uad6c\ud560 \ub54c \uc4f8 \uc218 \uc788\ub2e4.
\n\uc5ec\uae30\uc11c \uc704\uc640 \uac19\uc740 \ubd80\ubd84\uc740 PARTITION\u00a0BY \uc54a\ud558\uace0\u00a0\uc815\ub82c\uc744 \ud1b5\ud574\uc11c\ub3c4 \uad6c\ud604\uc774 \uac00\ub2a5\ud558\ub2e4SUM(COLUMN1) OVER(ORDER BY COLUMN1, COLUMN1)
\n\ub2e4\uc74c\ucc98\ub7fc \uc4f0\uba74 PARTITION \ud55c \uac83\uacfc \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4.<\/p>\n

3. SUM(COLUMN) OVER() —>
\n\ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc774\ub294 \uceec\ub7fc\uc758 \uc804\uccb4 \ud569\uc744 \ub9ac\ud134\ud558\uac8c \ub41c\ub2e4..
\nGROUP BY \ub294 \uc4f0\ub294\ub370 \uc81c\uc57d\uc774 \uc874\uc7ac\ud560 \ub54c\uac00 \uc788\ub2e4.
\n\ub09c \uac1c\uc778\uc801\uc73c\ub85c \uadf8\ub7f0 GROUP BY \ub97c \uc2eb\uc5b4\ud55c\ub2e4.
\n\ud558\uc9c0\ub9cc \uac1c\ubc1c\uc2dc\uc5d0 \uadf8\ub8f9\ubcc4\ub85c \ubcf4\uc5ec\uc918\uc57c \ud560 \ubd80\ubd84\uc740 \ub108\ubb34 \ub9ce\ub2e4.
\n\ubc95\uc778\ubcc4, \ubd80\uc11c\ubcc4 \ub4f1\ub4f1\uc758\u00a0\uc5b4\ub5a4 \ub0b4\uc5ed \uc870\ud68c\uc758 \uacbd\uc6b0….\uc774\ub294 \ubd88\uac00\ud53c\ud560 \uac83\uc774\ub2e4.<\/p>\n

\uc774\ub7f0 TIP\ub3c4 \ucd94\uac00\ub85c \uc54c\uba74 \uac1c\ubc1c\ud560 \ub54c \uc720\uc6a9\ud560 \uac83\uc774\ub2e4.<\/p>\n

\n

 <\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\ucd9c\ucc98 :\u00a0http:\/\/blog.naver.com\/hjc426\/130037056133 SUM() OVER() ORACLE \ub0b4\uc7a5 \ud568\uc218 SCOTT \uacc4\uc815\uc758 EMP TABLE\uc5d0\uc11c \ud14c\uc2a4\ud2b8\ub97c \uc9c4\ud589\ud55c\ub2e4 \ub2e4\uc74c\uc758 QUERY \uc744 \ubcf4\uba74 \uc774\ud574\uac00 \uc27d\ub2e4. ———————————————————————————————- SELECT ENAME, DEPTNO, SAL, SUM(SAL) OVER (PARTITION BY DEPTNO)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “\ubd80\uc11c\ubcc4 \uae09\uc5ec\ud569\uacc4”, SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL)\u00a0\u00a0\u00a0 “\ubd80\uc11c\ubcc4 \uae09\uc5ec\uc624\ub984\ucc28\uc21c \ub204\uc801\ud569”, SUM(SAL) OVER (ORDER BY DEPTNO, SAL)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“\ubd80\uc11c\ubcc4 \uae09\uc5ec\uc624\ub984\ucc28\uc21c \ub204\uc801\ud569”, SUM(SAL) OVER ()\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0“\uc804\uccb4 \uae09\uc5ec\ud569” … Continue reading oracle sum() over()<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[24,70,68,69,28],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-etc","tag-oracle","tag-over","tag-sum","tag-sum-over","tag-28"],"_links":{"self":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=470"}],"version-history":[{"count":2,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/470\/revisions"}],"predecessor-version":[{"id":887,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=\/wp\/v2\/posts\/470\/revisions\/887"}],"wp:attachment":[{"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.freesens.com\/x\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}