【正文】 SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 39。它允許去處理游標(biāo)，就好像游標(biāo)是一個(gè)數(shù)組一樣。 DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL 20 Fay 6000 Fay 20 Hartstein 13000 Fay 30 Colmenares 2500 Colmenares 30 Himuro 2600 Colmenares 30 Tobias 2800 Colmenares 30 Baida 2900 Colmenares 30 Khoo 3100 Colmenares 30 Raphaely 11000 Colmenares 79。FIRST_VALUE 功能描述：返回組中數(shù)據(jù)窗口的第一個(gè)值。FIRST 功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個(gè)值的行（可能多行，因?yàn)橹悼赡芟嗟龋虼送暾恼Z(yǔ)法需要在開(kāi)始處加上一個(gè)集合函數(shù)以從中取出記錄 SAMPLE：下面例子中DENSE_RANK按部門(mén)分區(qū)，再按傭金mission_pct排序，F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行，然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值；LAST取出傭金最高的對(duì)應(yīng)的所有行，然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值 SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY mission_pct) OVER (PARTITION BY department_id) Worst, MAX(salary) KEEP (DENSE_RANK LAST ORDER BY mission_pct) OVER (PARTITION BY department_id) Best FROM employees WHERE department_id in (20,80) ORDER BY department_id, salary。)。, 39。密集的序列返回的時(shí)沒(méi)有間隔的數(shù) SAMPLE：下例中計(jì)算每個(gè)員工按部門(mén)分區(qū)再按薪水排序，依次出現(xiàn)的序列號(hào)（注意與RANK函數(shù)的區(qū)別） SELECT , , , DENSE_RANK() OVER (PARTITION BY ORDER BY ) as drank FROM employees e, departments d WHERE = AND IN (39。每次ORDER BY表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。DENSE_RANK 功能描述：根據(jù)ORDER BY子句中表達(dá)式的值，從查詢(xún)返回的每一行，計(jì)算它們與其它行的相對(duì)位置。PU%39。CUME_DIST 功能描述：計(jì)算一行在組中的相對(duì)位置，CUME_DIST總是返回大于0、小于或等于1的數(shù)，該數(shù)表示該行在N行中的位置。 SAMPLE：下面例子中計(jì)算每個(gè)員工在按薪水排序中當(dāng)前行附近薪水在[n50,n+150]之間的行數(shù)，n表示當(dāng)前行的薪水 例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于220050的有1行，排在他之后的行中薪水小于等于2200＋150的行 沒(méi)有，所以count計(jì)數(shù)值t3為2（包括自己當(dāng)前行）；t2值相當(dāng)于小于等于當(dāng)前行的SALARY值的所有行數(shù) SELECT last_name, salary, COUNT(*) OVER () AS t1, COUNT(*) OVER (ORDER BY salary) AS t2, COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS t3 FROM employees。 PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS 1774 103088 0 1775 103087 1794 103096 1825 103093 2004 103086 2005 103086 1815 2416 103088 ? 74。 PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS 1774 103088 0 1775 103087 1794 103096 1825 103093 2004 103086 2005 103086 1815 2416 103088 . . 73。COVAR_POP 功能描述：返回一對(duì)表達(dá)式的總體協(xié)方差。 SAMPLE：下例返回1998年月銷(xiāo)售收入和月單位銷(xiāo)售的關(guān)系的累積系數(shù)（本例在SH用戶(hù)下運(yùn)行） SELECT , CORR (SUM(), SUM()) OVER (ORDER BY ) as CUM_CORR FROM sales s, times t WHERE = AND calendar_year = 1998 GROUP BY ORDER BY 。CORR 功能描述：返回一對(duì)表達(dá)式的相關(guān)系數(shù)，它是如下的縮寫(xiě)： COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2)) 從統(tǒng)計(jì)上講，相關(guān)性是變量之間關(guān)聯(lián)的強(qiáng)度，變量之間的關(guān)聯(lián)意味著在某種程度 上一個(gè)變量的值可由其它的值進(jìn)行預(yù)測(cè)。 SAMPLE：下面的例子中列c_mavg計(jì)算員工表中每個(gè)員工的平均薪水報(bào)告，該平均值由當(dāng)前員工和與之具有相同經(jīng)理的前一個(gè)和后一個(gè)三者的平均數(shù)得來(lái)； SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees。 開(kāi)窗函數(shù)的的理解： 開(kāi)窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化，舉例如下： over（order by salary） 按照salary排序進(jìn)行累計(jì)，order by是個(gè)默認(rèn)的開(kāi)窗函數(shù) over（partition by deptno）按照部門(mén)分區(qū) over（order by salary range between 50 preceding and 150 following） 每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過(guò)50，之后行幅度值不超過(guò)150 over（order by salary rows between 50 preceding and 150 following） 每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前50行，之后150行 over（order by salary rows between unbounded preceding and unbounded following） 每行對(duì)應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following） 主要參考資料：《expert oneonone》 Tom Kyte 《Oracle9i SQL Reference》第6章 oh 2007312 09:1970。 下面例子中使用的表來(lái)自O(shè)racle自帶的HR用戶(hù)下的表，如果沒(méi)有安裝該用戶(hù)，可以在SYS用戶(hù)下運(yùn)行 $ORACLE_HOME/demo/schema/human_resources/。 常用的分析函數(shù)如下所列: row_number() over(partition by ... order by ...) rank() over(partition by ... order by ...) dense_rank() over(partition by ... order by ...) count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...) expr2和expr3類(lèi)型不同的話，expr3會(huì)轉(zhuǎn)換為expr2的類(lèi)型 NULLIF (expr1, expr2) 相等返回NULL，不等返回expr1 ? Oracle分析函數(shù)參考手冊(cè) ============================================= 作者: xsb([url])[/url] 發(fā)表于: 12:22 分類(lèi): DWamp。 end (expr1, expr2) NVL(expr1, expr2)expr1為NULL，返回expr2；不為NULL，返回expr1。 else 39。 when score =70 and score 80 then 39。 when score =60 and score 70 then 39。 then 2 else 0 end 這種用法跟decode一樣沒(méi)什么區(qū)別 case的第2種用法： case when score 60 then 39。 then 1 when 39。 case的第1種用法： case col when 39。 from as 1 0 DUMMY=39。 (case DEPTNO COUNT(*) SUM(SAL) 20 5 10875 30 6 9400 BY 用于對(duì)查詢(xún)到的結(jié)果進(jìn)行排序輸出 SQL select deptno,ename,sal from order by deptno,sal desc。 DEPTNO COUNT(*) SUM(SAL) 10 3 8750 20 5 10875 30 6 9400 對(duì)分組統(tǒng)計(jì)再加限制條件 SQL select deptno,count(*),sum(sal) from group by deptno having count(*)=5。 STDDEV(DISTINCTSAL) (DISTINCT|ALL) 求協(xié)方差 SQL select variance(sal) from 。 MIN(ALLSAL) (distinct|all) 求標(biāo)準(zhǔn)差,ALL表示對(duì)所有的值求標(biāo)準(zhǔn)差,DISTINCT表示只對(duì)不同的值求標(biāo)準(zhǔn)差 SQL select stddev(sal) from 。 AVG(ALLSAL) (DISTINCT|ALL) 求最大值,ALL表示對(duì)所有的值求最大值,DISTINCT表示對(duì)不同的值求最大值,相同的只取一次 SQL select max(distinct sal) from 。 SQL select avg(distinct sal) from 。 SQLWKS insert into table3 values(zhu,)。 SQLWKS insert into table3 values(gao,)。 VSIZE(USER) USER 6 SYSTEM ? (DISTINCT|ALL) all表示對(duì)所有的值求平均值,distinct只對(duì)不同的值求平均值 SQLWKS create table table3(xm varchar(8),sal number(7,2))。 USERENV(LANG) ZHS TERMINAL 返回用戶(hù)的終端或機(jī)器的標(biāo)志 SQL select userenv(terminal) from dual。 USERENV(INSTANCE)