/*sum()over()*/
--ĬÈϼÆËãËùÓÐÐеĺϼÆ
select t.empno,t.ename,t.sal,t.deptno,sum(t.sal)over()
from scott.emp t;
--partition by·Ö×éºÏ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
sum(t.sal)over(partition by t.deptno)
from scott.emp t
order by t.deptno,t.sal;
--partition by order by deptno·Ö×éÀÛ¼Æ
select t.empno,t.ename,t.sal,t.deptno,
sum(t.sal)over(partition by t.deptno order by t.sal)
from scott.emp t;
--rows n preceding È¡µ±Ç°ÐÐ+Ç°nÐÐ=(n+1)ÐÐ
--ͨ¹ýorder by desc¿ÉÒÔÈ¡ºónÐÐ
select t.empno,t.ename,t.sal,t.deptno,
sum(t.sal)over(order by t.deptno,t.sal rows 1 preceding)
from scott.emp t;
--rows 2n+1 È¡µ±Ç°ÐÐ+Ç°nÐÐ+ºónÐÐ=(2n+1)ÐÐ
select t.empno,t.ename,t.sal,t.deptno,
sum(t.sal)over(order by t.deptno,t.sal rows between 1 preceding and 1 following)
from scott.emp t;
/*first_value() over()*/
select deptno,ename,sal,hiredate,
¡¡¡¡first_value(ename) over(partition by deptno order by sal asc rows 5 preceding) first_ename
¡¡¡¡from emp order by hiredate asc;
/*avg()over count() over() max()over() min()over()*/
select deptno,sal,
sum(sal)over(partition by deptno) as sumsal,
¡¡¡¡avg(sal)over(partition by deptno) as avgsal,
¡¡¡¡count(*)over(partition by deptno) as count,
¡¡¡¡max(sal)over(partition by deptno) as maxsal
from emp;
/*rank()over() dese_rank()over() row_number()over()*/
select empno, deptno, sal,
rank() over (order by deptno desc nulls last) as rank,
dense_rank() over (partition by deptno order by sal desc nulls last) as dense_rank,
row_number() over(partition by deptno order by sal desc nulls last) as row_number
from emp;
/*stddev() over()*±ê×¼²î/
select empno, deptno, sal,stddev(sal) over(order by sal)
from emp;
Oracle µÄ 9i/10g ÖÐÒѾ¶ÔÄÚ´æ¹ÜÀíÖð²½×öÁ˺ܴóµÄ¼ò»¯£¬11g Ôò¸ü½øÒ»²½£¬ÒýÈëÁËÒ»¸öеĸÅÄî×Ô¶¯»¯ÄÚ´æ¹ÜÀí(Automatic Memory Management,AMM) . Èç¹û DBA ÕæµÄÏë͵ÀÁµÄ»°£¬Ö»ÐèÒªÉ趨Á½¸ö²ÎÊý¾Í¿ÉÒÔ°Ñ·³ÐĵÄÊÂÇ鶼½»¸ø Oracle ÕÛÌÚÁË(Ö»Òª DBA ×ã¹»ÐÄ¿í)¡£PGA Óë SGA Ò»Æð¸ã¶¨¡£ÕâÁ½¸ö²ÎÊý·Ö±ðÊÇ£º
MEMORY_TARGET--²Ù×÷Ï ......
OracleÊý¾Ý¿âÖÐÌṩÁËͬÒå´Ê¹ÜÀíµÄ¹¦ÄÜ¡£Í¬Òå´ÊÊÇÊý¾Ý¿â·½°¸¶ÔÏóµÄÒ»¸ö±ðÃû£¬¾³£ÓÃÓÚ¼ò»¯¶ÔÏó·ÃÎʺÍÌá¸ß¶ÔÏó·ÃÎʵݲȫÐÔ¡£ÔÚʹÓÃͬÒå´Êʱ£¬OracleÊý¾Ý¿â½«Ëü·Òë³É¶ÔÓ¦·½°¸¶ÔÏóµÄÃû×Ö¡£ÓëÊÓͼÀàËÆ£¬Í¬Òå´Ê²¢²»Õ¼ÓÃʵ¼Ê´æ´¢¿Õ¼ä£¬Ö»ÓÐÔÚÊý¾Ý×ÖµäÖб£´æÁËͬÒå´ÊµÄ¶¨Òå¡£ÔÚOracleÊý¾Ý¿âÖеĴ󲿷ÖÊý¾Ý¿â¶ÔÏó£¬Èç±í¡¢ÊÓͼ¡¢Í ......