Oracle分析函數三——SUM,AVG,MIN,MAX,COUNT

原創於2009年08月02日，2009年10月22日遷移至此。

Oracle 分析函數——SUM,AVG,MIN,MAX,COUNT

SUM

功能描述：該Function Compute組中運算式的累積和。

SAMPLE ：下例計算同一經理下員工的薪水累積值

 

MIN

功能描述：在一個組中的資料視窗中尋找運算式的最小值。

SAMPLE ：下面例子中 dept_min 返回當前行所在部門的最小薪水值

 

MAX

功能描述：在一個組中的資料視窗中尋找運算式的最大值。

SAMPLE ：下面例子中 dept_max 返回當前行所在部門的最大薪水值

 

AVG

功能描述：用於計算一個組和資料視窗內運算式的平均值。

SAMPLE ：下面的例子中列 c_mavg 計算員工表中每個員工的平均薪水報告

 

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 hire_date, 

 salary,

 MIN(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_min,

 MAX(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_max, 

 AVG(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_avg,   

 SUM(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_sum/*,   

 COUNT(*) OVER (ORDER BY salary) AS count_by_salary, 

 COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range*/

FROM employees

 

COUNT

功能描述：對一組內發生的事情進行累積計數，如果指定 * 或一些非空常數， count 將對所有行計數，如果指定一個運算式， count 返回運算式非空賦值的計數，當有相同值出現時，這些相等的值都會被納入被計算的值；可以使用 DISTINCT 來記錄去掉一組中完全相同的資料後出現的行數。

SAMPLE ：下面例子中計算每個員工在按薪水排序中當前行附近薪水在 [n-50,n+150] 之間的行數， n 表示當前行的薪水

例如， Philtanker 的薪水 2200 ，排在他之前的行中薪水大於等於 2200-50 的有 1 行，排在他之後的行中薪水小於等於 2200 ＋ 150 的行沒有，所以 count 計數值 cnt3 為 2 （包括自己當前行）； cnt2 值相當於小於等於當前行的 SALARY 值的所有行數

 

SELECT

 department_id,

 first_name||' '||last_name employee_name,

 salary,

 COUNT(*) OVER (ORDER BY salary) AS count_by_salary, 

 COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range

FROM employees

WHERE department_id in (10,20,30);