Oracle analytic function-sum/max/min/avg/count

SUM分析函数的主要功能:计算组内表达式的累积和。
MIN分析函数的主要功能: 在一个组中的数据窗口中查找表达式的最小值。
MAX分析函数的主要功能:在一个组中的数据窗口中查找表达式的最大值。
AVG分析函数的主要功能:用于计算一个组和数据窗口内表达式的平均值。
COUNT分析函数的主要功能:对一组内发生的事情进行累积计数,如果指定*或一些非空常数,COUNT将对所有行计数,如果指定一个表达式,COUNT返回表达式非空赋值的计数,当有相同值出现时,这些相等的值都会被纳入被计算的值;可以使用 DISTINCT 来记录去掉一组中完全相同的数据后出现的行数。

语法:
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]

实验演示

SELECT department_id,
       first_name || ' ' || last_name employee_name,
       hire_date,
       salary,
       SUM(salary) OVER(PARTITION BY department_id order by hire_date) AS sal_dept_sum, --相同department_id下员工的薪水累积和
       MIN(salary) OVER(PARTITION BY department_id order by hire_date) AS sal_dept_min, --相同department_id下员工薪水的最小值
       MAX(salary) OVER(PARTITION BY department_id order by hire_date) AS sal_dept_max, --相同department_id下员工薪水的最大值
       AVG(salary) OVER(PARTITION BY department_id order by hire_date) AS sal_dept_avg, --相同department_id下员工薪水的平均值
       COUNT(*) OVER(ORDER BY salary) AS count_by_salary, --按照薪水值排序累积计数
       COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 100 PRECEDING AND 400 FOLLOWING) AS sal_moving_count --薪水排序中当前行位置薪水范围在
                                                                                                    --[当前行薪水值-100,当前行薪水值+400]之间的行数.
  FROM employees
 where department_id <= 40; 
DEPARTMENT_ID EMPLOYEE_NAME                  HIRE_DATE               SALARY        SUM        MIN        MAX        AVG      COUNT MOVING_COUNT
------------- ------------------------------ ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
           10 Jennifer Whalen                2003-09-17 00:00:00       4400       4400       4400       4400       4400          1            1
           20 Pat Fay                        2005-08-17 00:00:00       6000       6000       6000       6000       6000          1            1
           20 Michael Hartstein              2004-02-17 00:00:00      13000      19000       6000      13000       9500          2            1
           30 Karen Colmenares               2007-08-10 00:00:00       2500       2500       2500       2500       2500          1            4
           30 Alexander Khoo                 2003-05-18 00:00:00       2600       5100       2500       2600       2550          2            4
           30 Guy Himuro                     2006-11-15 00:00:00       2800       7900       2500       2800 2633.33333          3            3
           30 Shelli Baida                   2005-12-24 00:00:00       2900      10800       2500       2900       2700          4            3
           30 Sigal Tobias                   2005-07-24 00:00:00       3100      13900       2500       3100       2780          5            1
           30 Den Raphaely                   2002-12-07 00:00:00      11000      24900       2500      11000       4150          6            1
           40 Susan Mavris                   2002-06-07 00:00:00       6500       6500       6500       6500       6500          1            1

sal_moving_count的含义是这样的:例如员工Karen Colmenares,他的薪水是2500,那么按照语句求得的范围是[2500-100,2500+400],即[2400-2900]之间。 这个员工所在的行之前(=>2400)是没有记录符合这个条件的,之后(<=2900)符合条件的为3行,算上本身的话,那么sal_moving_count是4,其他的类似。

此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s