熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle 9i 分析函數參考手冊

2013-11-13 15:46:06  來源: Oracle 

  Oracle從開始提供分析函數分析函數用於計算基於組的某種聚合值它和聚合函數的不同之處是對於每個組返回多行而聚合函數對於每個組只返回一行
  
  下面例子中使用的表來自Oracle自帶的HR用戶下的表如果沒有安裝該用戶可以在SYS用戶下運行$ORACLE_HOME/demo/schema/human_resources/hr_mainsql來創建
  
  少數幾個例子需要訪問SH用戶下的表如果沒有安裝該用戶可以在SYS用戶下運行$ORACLE_HOME/demo/schema/sales_history/sh_mainsql來創建
  
  如果未指明缺省是在HR用戶下運行例子
  
  開窗函數的的理解
  
  開窗函數指定了分析函數工作的數據窗口大小這個數據窗口大小可能會隨著行的變化而變化舉例如下
  
  over(order by salary) 按照salary排序進行累計order by是個默認的開窗函數
  over(partition by deptno)按照部門分區
  over(order by salary range between preceding and following)
  每行對應的數據窗口是之前行幅度值不超過之後行幅度值不超過
  over(order by salary rows between preceding and following)
  每行對應的數據窗口是之前之後
  over(order by salary rows between unbounded preceding and unbounded following)
  每行對應的數據窗口是從第一行到最後一行等效
  over(order by salary range between unbounded preceding and unbounded following)
  
  主要參考資料《expert oneonone》 Tom Kyte 《Oraclei SQL Reference》第
  
  AVG
  功能描述用於計算一個組和數據窗口內表達式的平均值
  
  SAMPLE下面的例子中列c_mavg計算員工表中每個員工的平均薪水報告該平均值由當前員工和與之具有相同經理的前一個和後一個三者的平均數得來
  
  SELECT manager_id last_name hire_date salary
  AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
  ROWS BETWEEN PRECEDING AND FOLLOWING) AS c_mavg
  FROM employees;
  
  MANAGER_ID LAST_NAME         HIRE_DATE   SALARY   C_MAVG
  
   Kochhar          SEP      
   De Haan          JAN      
   Raphaely         DEC   
   Kaufling         MAY   
   Hartstein         FEB   
   Weiss           JUL   
   Russell          OCT   
  
  
  
  
  CORR
  功能描述返回一對表達式的相關系數它是如下的縮寫
  
  COVAR_POP(exprexpr)/STDDEV_POP(expr)*STDDEV_POP(expr))從統計上講相關性是變量之間關聯的強度變量之間的關聯意味著在某種程度上一個變量的值可由其它的值進行預測通過返回一個~之間的一個數 相關系數給出了關聯的強度表示不相關
  
  SAMPLE下例返回年月銷售收入和月單位銷售的關系的累積系數(本例在SH用戶下運行)
  
  SELECT tcalendar_month_number
  CORR (SUM(samount_sold) SUM(squantity_sold))
  OVER (ORDER BY tcalendar_month_number) as CUM_CORR
  FROM sales s times t
  WHERE stime_id = ttime_id AND calendar_year =
  GROUP BY tcalendar_month_number
  ORDER BY tcalendar_month_number;
  
  CALENDAR_MONTH_NUMBER  CUM_CORR
  
  
       
  
  
  
  
  
  
  
   
  
  
  
  COVAR_POP
  功能描述返回一對表達式的總體協方差
  SAMPLE下例CUM_COVP返回定價和最小產品價格的累積總體協方差
  
  SELECT product_id supplier_id
  COVAR_POP(list_price min_price)
  OVER (ORDER BY product_id supplier_id) AS CUM_COVP
  COVAR_SAMP(list_price min_price)
  OVER (ORDER BY product_id supplier_id) AS CUM_COVS
  FROM product_information p
  WHERE category_id =
  ORDER BY product_id supplier_id;
  
  PRODUCT_ID SUPPLIER_ID  CUM_COVP  CUM_COVS
  
          
         
     
       
          
           
     
  
  
  
  COVAR_SAMP
  功能描述返回一對表達式的樣本協方差
  SAMPLE下例CUM_COVS返回定價和最小產品價格的累積樣本協方差
  
  SELECT product_id supplier_id
  COVAR_POP(list_price min_price)
  OVER (ORDER BY product_id supplier_id) AS CUM_COVP
  COVAR_SAMP(list_price min_price)
  OVER (ORDER BY product_id supplier_id) AS CUM_COVS
  FROM product_information p
  WHERE category_id =
  ORDER BY product_id supplier_id;
  
  PRODUCT_ID SUPPLIER_ID  CUM_COVP  CUM_COVS
  
          
         
     
       
          
           
     
  
  
  
  COUNT
  功能描述對一組內發生的事情進行累積計數如果指定*或一些非空常數count將對所有行計數如果指定一個表達式count返回表達式非空賦值的計數當有相同值出現時這些相等的值都會被納入被計算的值可以使用DISTINCT來記錄去掉一組中完全相同的數據後出現的行數
  
  SAMPLE下面例子中計算每個員工在按薪水排序中當前行附近薪水在[nn+]之間的行數n表示當前行的薪水
  
  例如Philtanker的薪水排在他之前的行中薪水大於等於的有排在他之後的行中薪水小於等於的行沒有所以count計數值cnt(包括自己當前行)cnt值相當於小於等於當前行的SALARY值的所有行數
  
  SELECT last_name salary COUNT(*) OVER () AS cnt
  COUNT(*) OVER (ORDER BY salary) AS cnt
  COUNT(*) OVER (ORDER BY salary RANGE BETWEEN PRECEDING
  AND FOLLOWING) AS cnt FROM employees;
  
  LAST_NAME    SALARY    CNT    CNT    CNT
  
  Olson                    
  Markle                   
  Philtanker                 
  Landry                   
  Gee                     
  Colmenares                
  Patel                   
  
  
  
  CUME_DIST
  功能描述計算一行在組中的相對位置CUME_DIST總是返回大於小於或等於的數該數表示該行在N行中的位置例如在一個行的組中返回的累計分布值為///
  SAMPLE下例中計算每個工種的員工按薪水排序依次累積出現的分布百分比
  
  SELECT job_id last_name salary CUME_DIST()
  OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
  FROM employees WHERE job_id LIKE PU%;
  
  JOB_ID   LAST_NAME           SALARY CUME_DIST
  
  PU_CLERK  Colmenares               
  PU_CLERK  Himuro                 
  PU_CLERK  Tobias                 
  PU_CLERK  Baida                  
  PU_CLERK  Khoo                   
  PU_MAN   Raphaely                 
  
  
  DENSE_RANK
  功能描述根據ORDER BY子句中表達式的值從查詢返回的每一行計算它們與其它行的相對位置組內的數據按ORDER BY子句排
From:http://tw.wingwit.com/Article/program/Oracle/201311/17275.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.