¸¹Àº °í¼ö´Ôµé²²¼ over()¸¦ ¼û½¬µíÀÌ »ç¿ëÇϽôµ¥ Àú´Â µµ¹«Áö over()ÀÇ »ç¿ë¹ýÀ» Àß ¸ð¸£°Ú½À´Ï´Ù.
ÀÏ¹Ý ÇÔ¼öó·³ »ç¿ëÇϱ⿣ 2% ÀÌ»óÀÇ ¹º°¡°¡ ´õ ÀÖ´Â °Í °°°í....-_-
over( ÆÄƼ¼Ç#@$#@) ¹¹ ÀÌ·¸°Ô;;;
over()°¡ È¥ÀÚ ´Üµ¶À¸·Î ½ÇÇà µÇ´Â °Ç ¾Æ´Ñ °Í °°±¸¿ä;;¤Ð¤Ð
Á¦¹ß ÀÌÇØ°¡ Àß µÇ°Ô ¿¹Á¦¿Í ÇÔ²² »ç¿ëÇÏ´Â °æ¿ì µî....»ó¼¼È÷ ¼³¸í ºÎŹµå¸³´Ï´Ù.
Àß µÇ¸é ¹ä »ç²²È¿~
ÀÌ ±Û¿¡ ´ëÇÑ ´ñ±ÛÀÌ ÃÑ 4°Ç ÀÖ½À´Ï´Ù.¾Æ·¡ ¹®Àå Çѹø¸¸ ½ÇÇàÇغ¸¼¼¿ä.
¹º°¡ ´À³¦ÀÌ È®~ ¿Ã°Ì´Ï´Ù.
Áöµµ ¸ô¶ó¼ ÇÑÂü Çì¸Ì½À´Ï´Ù.
--------------------------------------------
WITH TEST AS
(
select 'A' grade, 10 cnt from dual union all
select 'A' grade, 20 cnt from dual union all
select 'A' grade, 15 cnt from dual union all
select 'B' grade, 20 from dual union all
select 'C' grade, 30 from dual union all
select 'D' grade, 15 from dual union all
select 'F' grade, 10 from dual
)
SELECT
grade µî±Þ,
cnt Çлý¼ö,
sum(cnt) over () °¹¼ö,
row_number() over(partition by grade order by cnt) ·Î³Ñ,
round((cnt / sum(cnt) over ())*100,2) ±¸¼ººñ,
sum(cnt) over (partition by grade order by grade) ´©ÀûÇлý¼ö,
sum(cnt) over (partition by grade,cnt order by grade) ±×·¹Ä«¿î,
sum(cnt) over (order by grade) ´©Àû,
round((sum(cnt) over (order by grade)/sum(cnt) over ())*100,2) ´©Àû±¸¼ººñ
FROM TEST;
±è¼±°æ(somcandy)´ÔÀÌ 2007-05-04 11:15:44¿¡ ÀÛ¼ºÇÑ ´ñ±ÛÀÔ´Ï´Ù.over() ÇÔ¼ö°¡ ¾Æ´Ï¶ó Analytic function À» ¸»¾¸ÇϽôµíÇϱº¿ä..
Analytic function Àº VERSION : ORACLE 8.1.6 À̻󿡼 Áö¿øµÇ±â ½ÃÀÛÇؼ
9¹öÀü¿¡¼ ¾ÈÁ¤ÀûÀ¸·Î Áö¿øµÇ°í ÀÖ½À´Ï´Ù.
RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )
DENSE_RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...] )
{SUM|AVG|MAX|MIN|COUNT|STDDEV|VARIANCE|FIRST_VALUE|LAST_VALUE}
({<value expression1> | *}) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC| DESC] [NULLS FIRST | NULLS LAST] [,...]
ROWS | RANGE
{{UNBOUNDED PRECEDING | <value expression4> PRECEDING}
| BETWEEN
{UNBOUNDED PRECEDING | <value expression4> PRECEDING}
AND{CURRENT ROW | <value expression4> FOLLOWING}}
OVER
...
{LAG | LEAD}
(<value expression1>, [<offset> [, <default>]]) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])
<offset> Àº ¿É¼ÇÀ̸ç ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é 1 ÀÌ default·Î »ç¿ëµÈ´Ù.
°ú °°ÀÌ »ç¿ëÇÕ´Ï´Ù..
Âü row_number() µµ ÀÖ±º¿ä..
|
|
|