oracle olapº¯Êý
/*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;
Ïà¹ØÎĵµ£º
Èí¼þ: VMware Workstation 6.0.2 build-59824
10201_database_linux32.zip
rhel.5.0.server.centoschina.com.dvd.iso
Îĵµ:ORACLE10G¹Ù·½°²×°Îĵµ B19306_01_Oracle10gR2.chm
²Î¿¼:Öܾ¼ÀÏʦµÄORACLE10G OCPÊÓÆµ(½²µÄÕæµÄ ......
ORACLE ÁÙʱ±í¿Õ¼äʹÓÃÂʹý¸ßµÄÔÒò¼°½â¾ö·½°¸(2009-11-14 19:59:02)
±êÇ©£ºoracle ÁÙʱ±í¿Õ¼ä ʹÓÃÂÊ100 ½â¾ö·½°¸ it
·ÖÀࣺ¼¼Êõ²©ÂÛ
ÔÚÊý¾Ý¿âµÄÈÕ³£Ñ§Ï°ÖУ¬·¢ÏÖ¹«Ë¾Éú²úÊý¾Ý¿âµÄĬÈÏÁÙʱ±í¿Õ¼ätempʹÓÃÇé¿ö´ïµ½ÁË30G£¬Ê¹ÓÃÂÊ´ïµ½ÁË100%£» ´ýµ÷ÕûΪ32Gºó£¬Ê¹ÓÃÂÊ»¹ÊÇΪ100%£¬µ¼Ö´ÅÅ̿ռäʹÓýôÕÅ¡£¸ù¾ÝÁÙʱ±í¿Õ¼äµÄÖ÷ ......
OracleÌåϵ½á¹¹£ºÄÚ´æ½á¹¹ºÍ½ø³Ì½á¹¹
2009-11-24 21:48
¡¡£¨Ò»£©ÄÚ´æ½á¹¹ºÍ½ø³Ì½á¹¹ OracleÊý¾Ý¿âµÄ×ÜÌå½á¹¹ÈçÏÂͼ£º
1£ºOracleʵÀý£¨Instance£©
ÔÚÒ»¸ö·þÎñÆ÷ÖУ¬Ã¿Ò»¸öÔËÐеÄOracleÊý¾Ý¿â¶¼ÓëÒ»¸öÊý¾Ý¿âʵÀýÏàÁªÏµ£¬ÊµÀýÊÇÎÒÃÇ
·ÃÎÊÊý¾Ý¿âµÄÊֶΡ£
......
oracle ÈÕÆÚº¯Êý
ÔÚoracleÊý¾Ý¿âµÄ¿ª·¢ÖУ¬³£ÒòΪʱ¼äµÄÎÊÌâ´ó·ÑÖÜÕ£¬ËùÒÔÌØµØ½«ORACLEÊý¾ÝµÄÈÕÆÚº¯ÊýÊÕ²ØÖ´ˡ£Ä˹©
ËûÈÕËù²éÒ²¡£
add_months(d,n) ÈÕÆÚd¼Ón¸öÔÂ
last_day(d) °üº¬dµÄÔÂ?µÄ×îºóÒ»ÌìµÄÈÕÆÚ
new_time(d,a,b) a?ÇøµÄÈÕÆÚºÍ??dÔÚb?ÇøµÄÈÕÆÚºÍ??
next_day(d,day) ±È ......
Éí¾ÓOracle ¹Ø¼üÓ¦ÓõĿª·¢ºÍά»¤ÍŶӣ¬ÄúÒ»¶¨ÉîÖªÑз¢¹ÜÀíµÄÖØÒª¡£ÈçºÎÀûÓÃרҵ»¯Oracle ÍŶӿª·¢½â¾ö·½°¸£¬ÊµÏÖ¸ßЧµÄÍŶӿª·¢¡¢×î¼ÑÓ¦ÓÃÐÔÄܺÍÀíÏëµÄ½»¸¶ÖÊÁ¿£¬ÊÇQuest Software±¾´ÎÓëÄú̽ÌֵĺËÐÄ»°Ìâ¡£
´ÓÊý¾Ý¿âµÄÉè¼Æ¡¢½¨Ä£¡¢±àÂ룬µ½Ó ......