<\/p>\n
\uc624\ub77c\ud074\uc5d0\uc11c\ub294 RANK Function\uc744 \uc0ac\uc6a9\ud574\uc11c \uc21c\uc704\ub97c \uac04\ud3b8\ud558\uac8c \ubd80\uc5ec\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
RANK Function\ub294 oracle 8i(8.1.6) \ubd80\ud130 \uac00\ub2a5\ud569\ub2c8\ub2e4.
\n8.1.6 \uc774\uc804 \ubc84\uc804\uc5d0\uc11c\ub294 \uc0ac\uc6a9 \ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. ORA-923 error \uac00 \ubc1c\uc0dd \ud569\ub2c8\ub2e4.<\/p>\n
plsql \ub0b4\uc5d0\uc11c\ub294 oracle 9i \ubd80\ud130 \uac00\ub2a5\ud569\ub2c8\ub2e4. 8.1.6\uc5d0\uc11c\ub294 ORA-900 error\uac00 \ubc1c\uc0dd \ud569\ub2c8\ub2e4.<\/p>\n
-- scott\uc720\uc800\ub85c \uc811\uc18d\uc744 \ud569\ub2c8\ub2e4. \r\n]$ SQLPLUS scott\/tiger\r\n\r\n-- RANK() \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec \uae09\uc5ec \uc21c\uc73c\ub85c \uc21c\uc704\ub97c \ubd80\uc5ec\ud55c \uc608\uc81c \uc785\ub2c8\ub2e4. \r\n-- RK\uc758 \ucd9c\ub825\uac12\uc744 \ubcf4\uba74 \uae09\uc5ec\uac00 \uac19\uc73c\uba74 \uac19\uc740 \uc21c\uc704\ub85c \ubd80\uc5ec\uac00 \ub429\ub2c8\ub2e4. \r\nSQL> SELECT empno, ename, sal, \r\n RANK() OVER (ORDER BY sal DESC ) as rk\r\n FROM emp;\r\n\r\n EMPNO ENAME SAL RK\r\n--------- ---------- ---------- ----------\r\n 7839 KING 5000 1\r\n 7788 SCOTT 3000 2\r\n 7902 FORD 3000 2\r\n 7566 JONES 2975 4\r\n 7698 BLAKE 2850 5\r\n 7782 CLARK 2450 6\r\n 7499 ALLEN 1600 7\r\n 7844 TURNER 1500 8\r\n 7934 MILLER 1300 9\r\n 7521 WARD 1250 10\r\n 7654 MARTIN 1250 10\r\n 7876 ADAMS 1100 12\r\n 7900 JAMES 950 13\r\n 7369 SMITH 800 14<\/pre>\n\uadf8\ub8f9\ubcc4\ub85c \uc21c\uc704\ub97c \ubd80\uc5ec \ud558\ub294 \ubc95
\n\uc704 \uc608\uc81c\ub294 deptno\ub97c \ud30c\ud2f0\uc158\uc73c\ub85c \ub098\ub204\uc5b4\uc11c \ubd80\uc11c\ubcc4\ub85c \uc21c\uc704\ub97c \ubd80\uc5ec \ud569\ub2c8\ub2e4.
\n\ud2b9\uc815\ud55c \uadf8\ub8f9\ubcc4\ub85c \uc21c\uc704\ub97c \ubd80\uc5ec\ud558\uace0 \uc2f6\uc744\ub54c \uc0ac\uc6a9 \ud558\uba74 \ud3b8\ud569\ub2c8\ub2e4. <\/p>\nSQL> SELECT deptno, ename, sal, \r\n RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk \r\n FROM emp ;\r\n \r\n DEPTNO ENAME SAL RK\r\n------- ---------- ---------- ---------\r\n 10 KING 5000 1\r\n 10 CLARK 2450 2\r\n 10 MILLER 1300 3\r\n 20 SCOTT 3000 1\r\n 20 FORD 3000 1\r\n 20 JONES 2975 3\r\n 20 ADAMS 1100 4\r\n 20 SMITH 800 5\r\n 30 BLAKE 2850 1\r\n 30 ALLEN 1600 2\r\n 30 TURNER 1500 3\r\n 30 WARD 1250 4\r\n 30 MARTIN 1250 4\r\n 30 JAMES 950 6<\/pre>\nDENSE_RANK() \ud568\uc218<\/p>\n
DENSE_RANK() – \uc911\ubcf5 RANK\uc758 \uc218\uc640 \ubb34\uad00\ud558\uac8c numbering\uc744 \ud569\ub2c8\ub2e4.
\n1\ub4f1, 2\ub4f1, 2\ub4f1 \uc774\ub807\uac8c 2\ub4f1\uc774 \uc911\ubcf5\ub418\uc5c8\ub294\ub370 4\ub4f1\uc774 \uc544\ub2c8\ub77c 3\ub4f1\uc774 \ubd80\uc5ec \ub429\ub2c8\ub2e4. <\/p>\nSQL>SELECT empno, ename, sal, \r\n DENSE_RANK() OVER (ORDER BY sal DESC ) as rk\r\n FROM emp; \r\n \r\n EMPNO ENAME SAL RK\r\n--------- ---------- ---------- ---------\r\n 7839 KING 5000 1\r\n 7788 SCOTT 3000 2\r\n 7902 FORD 3000 2\r\n 7566 JONES 2975 3\r\n 7698 BLAKE 2850 4\r\n 7782 CLARK 2450 5\r\n 7499 ALLEN 1600 6\r\n 7844 TURNER 1500 7\r\n 7934 MILLER 1300 8\r\n 7521 WARD 1250 9\r\n 7654 MARTIN 1250 9\r\n 7876 ADAMS 1100 10\r\n 7900 JAMES 950 11\r\n 7369 SMITH 800 12 <\/pre>\nOVER analytic_clause
\n\ud574\ub2f9 \ud568\uc218\uac00 \ucffc\ub9ac \uacb0\uacfc \uc9d1\ud569\uc5d0 \ub300\ud574 \uc801\uc6a9\ub418\ub77c\ub294 \uc9c0\uc2dc\uc5b4\ub85c\uc368 FROM, WHERE, GROUP BY\uc640 HAVING\uad6c \uc774\ud6c4\uc5d0 \uacc4\uc0b0\ub418\uc5b4 \uc9c4\ub2e4.
\nSELECT \uad6c \ub610\ub294 ORDER BY \uad6c\uc5d0 Analytic Function\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/p>\n– PARTITION BY \uad6c
\n\ud558\ub098 \uc774\uc0c1\uc758 \uceec\ub7fc \ub610\ub294 \uc801\ud569\ud55c \ud45c\ud604\uc2dd\uc774 \uc0ac\uc6a9\ub420 \uc218 \uc788\uace0 \ud558\ub098 \uc774\uc0c1\uc758 \uceec\ub7fc \ub610\ub294 \ud45c\ud604\uc2dd\uc5d0 \uc758\ud55c \uadf8\ub8f9\uc73c\ub85c \ucffc\ub9ac\uc758 \uacb0\uacfc\ub97c \ud30c\ud2f0\uc158\ud55c\ub2e4. \uc774 \uad6c\uac00 \uc0dd\ub7b5\ub418\uba74 \ub2e8\uc77c \uadf8\ub8f9\ucc98\ub7fc \ucffc\ub9ac \uacb0\uacfc \uc9d1\ud569\uc774 \ucc98\ub9ac\ub41c\ub2e4.<\/p>\n– ORDER BY \uad6c
\n\ud558\ub098 \uc774\uc0c1\uc758 \uceec\ub7fc \ub610\ub294 \uc801\ud569\ud55c \ud45c\ud604\uc2dd\uc774 \uc0ac\uc6a9\ub420 \uc218 \uc788\uace0 \ud558\ub098 \uc774\uc0c1\uc758 \uceec\ub7fc \ub610\ub294 \ud45c\ud604\uc2dd\uc744 \uae30\uc900\uc73c\ub85c \ud30c\ud2f0\uc158 \ub0b4\uc758 \ub370\uc774\ud130\ub97c \uc815\ub82c\ud55c\ub2e4. \ud45c\ud604\uc2dd\uc740 \uceec\ub7fc\uc758 \ubcc4\uce6d \ub610\ub294 \uc704\uce58\ub97c \ub098\ud0c0\ub0b4\ub294 \uc22b\uc790\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4.<\/p>\n\ubd80\uc11c\ubcc4 \uc9c1\uc5c5\ubcc4 \uae09\uc5ec \ud569\uacc4\uc640, \uc9c1\uc5c5\ubcc4 \uae09\uc5ec \ud569\uacc4\uac00 \uac00\uc7a5 \ud070 \uae08\uc561<\/p>\n
SELECT deptno, job, SUM(SAL) sum_sal,\r\nMAX(SUM(sal)) OVER (PARTITION BY deptno)\r\nFROM emp\r\nGROUP BY deptno, job\r\n<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
\uc624\ub77c\ud074\uc5d0\uc11c\ub294 RANK Function\uc744 \uc0ac\uc6a9\ud574\uc11c \uc21c\uc704\ub97c \uac04\ud3b8\ud558\uac8c \ubd80\uc5ec\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. RANK Function\ub294 oracle 8i(8.1.6) \ubd80\ud130 \uac00\ub2a5\ud569\ub2c8\ub2e4. 8.1.6 \uc774\uc804 \ubc84\uc804\uc5d0\uc11c\ub294 \uc0ac\uc6a9 \ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. ORA-923 error \uac00 \ubc1c\uc0dd \ud569\ub2c8\ub2e4. plsql \ub0b4\uc5d0\uc11c\ub294 oracle 9i \ubd80\ud130 \uac00\ub2a5\ud569\ub2c8\ub2e4. 8.1.6\uc5d0\uc11c\ub294 ORA-900 error\uac00 \ubc1c\uc0dd \ud569\ub2c8\ub2e4. — … Continue reading