【翻译】 (Mastering Oracle SQL) 第四章 分组操作 (一)

第四章 Group Operations 分组操作

 

分组操作在SQL编程中经常用到。当你使用SQL取访问数据库时，你经常会遇到如下问题:

1.一个部门中最高的工资是多少?
2.每个部门中有多少个经理？

3.每个产品的客户有多少个?

4.请打印每个区域的月合计销售额？

 

你需要使用分组操作去解决这些问题. Oracle提供了一系列丰富的特性取处理分组操作。这些特性包括 聚合函数，GROUP BY 子句， HAVING 子句，以及GROUP BY 子句的扩展—ROOLUP,CUBE和GROUPING SETS.

 

4.1 Aggregate Functions 聚合函数

 

一个聚合函数通过一个表达式来处理多行数据，并返回一个值，绝大多数聚合函数的语法如下:

aggregate_function([DISTINCT | ALL] expression

 

其中各个元素如下:

aggregate_function

聚合函数名，如 SUM,COUNT,AVG,MAX,MIN

DISTINCT

指定聚合函数只应该考虑表达式的不同值

ALL

指定聚合函数只应该考虑表达式的所有值，包括所有的重复行, ALL 是默认选项

expression

 

指定需要进行聚合运算的一个列或者表达式

 

下面看一些例子，下面的SQL使用MAX函数来查找所有职员的最大工资

 

SELECT MAX(salary) FROM employee;



MAX(SALARY)

-----------

       5000

 

在后续章节,我们会使用一系列略微复杂的例子来演示聚合函数的不同行为, 为了这些例子，我们将使用下面这个cust_order表

 

DESC cust_order



 Name                             Null?    Type

 -------------------------------- -------- --------------

 ORDER_NBR                        NOT NULL NUMBER(7)

 CUST_NBR                         NOT NULL NUMBER(5)

 SALES_EMP_ID                     NOT NULL NUMBER(5)

 SALE_PRICE                                NUMBER(9,2)

 ORDER_DT                         NOT NULL DATE

 EXPECTED_SHIP_DT                 NOT NULL DATE

 CANCELLED_DT                              DATE

 SHIP_DT                                   DATE

 STATUS                                    VARCHAR2(20)



SELECT order_nbr, cust_nbr, sales_emp_id, sale_price,

order_dt, expected_ship_dt

FROM cust_order;



ORDER_NBR CUST_NBR SALES_EMP_ID SALE_PRICE ORDER_DT  EXPECTED_ 

--------- -------- ------------ ---------- --------- --------- 

     1001        1         7354         99 22-JUL-01 23-JUL-01

     1000        1         7354            19-JUL-01 24-JUL-01

     1002        5         7368            12-JUL-01 25-JUL-01

     1003        4         7654         56 16-JUL-01 26-JUL-01

     1004        4         7654         34 18-JUL-01 27-JUL-01

     1005        8         7654         99 22-JUL-01 24-JUL-01

     1006        1         7354            22-JUL-01 28-JUL-01

     1007        5         7368         25 20-JUL-01 22-JUL-01

     1008        5         7368         25 21-JUL-01 23-JUL-01

     1009        1         7354         56 18-JUL-01 22-JUL-01

     1012        1         7354         99 22-JUL-01 23-JUL-01

     1011        1         7354            19-JUL-01 24-JUL-01

     1015        5         7368            12-JUL-01 25-JUL-01

     1017        4         7654         56 16-JUL-01 26-JUL-01

     1019        4         7654         34 18-JUL-01 27-JUL-01

     1021        8         7654         99 22-JUL-01 24-JUL-01

     1023        1         7354            22-JUL-01 28-JUL-01

     1025        5         7368         25 20-JUL-01 22-JUL-01

     1027        5         7368         25 21-JUL-01 23-JUL-01

     1029        1         7354         56 18-JUL-01 22-JUL-01



20 rows selected.

4.1.1 NULLs and Aggregate Functions 空值和聚合函数

 

cust_order表中的sale_price列是可为空的，也就是说有些行包含空值，为了测试空值对聚合函数的影响，我们执行如下SQL: 

 

SELECT COUNT(*), COUNT(sale_price) FROM cust_order;



COUNT(*) COUNT(SALE_PRICE)

-------- -----------------

      20                14

 

显而易见,COUNT(*) 和 COUNT(sale_price)的结果是不一样的，这是因为同COUNT(*)不同，COUNT(sale_price)忽略了空值。究其原因，是因为COUNT(*)统计行，而不是具体的列值。空值并不影响行数的统计，除了COUNT(*)以外，还有另外一个聚合函数也不忽略空行，那就是GROUPING.其他的聚合行数都忽略空行，我们将要在第十三章再讨论GROUPING.让我们来看看空值被忽略的例子。

 

SUM, MAX, MIN, AVG等都忽略空值，因此如果你使用AVG去计算cust_order表中的平均销售价格，这个平均价格实际上是14行的，就是那些价格为非空的那些行。

 

SELECT COUNT(*), SUM(sale_price), AVG(sale_price)

FROM cust_order;



       COUNT(*) SUM(SALE_PRICE) AVG(SALE_PRICE)

--------------- --------------- ---------------

             20             788      56.2857143

 

请注意，AVG(sale_price)并不等于SUM(sale_price) / COUNT(*). SUM(sale_price) / COUNT(*)的值应等于788 / 20 = 39.4. 但是AVG函数忽略了空行，它将合计值除以14而不是20.

AVG(sale_price)等于 SUM(sale_price) / COUNT(sale_price) (788 / 14 = 56.2857143)

 

如果你需要计算表中所有行数的平均值，而不仅仅是那些有非空值的. 这时你就需要使用在AVG函数内部使用NVL函数将列中的空值赋值为0或者其他有用的值.(DECODE,CASE或者COALESCE函数可以用来替代NVL，详见第九章)，下面是一些例子：

 

 

SELECT AVG(NVL(sale_price,0)) FROM cust_order;



AVG(NVL(SALE_PRICE,0))

----------------------

                  39.4

 

 

请注意使用NVL会让所有的20行都参与到平均值计算中, 并且sale_price 的所有的空值都被假定为0.

 

4.1.2 Use of DISTINCT and ALL 使用DISTINCT 和 ALL

 

大多数的聚合函数允许使用DISTINCT或者ALL和表达式一起. DISTINCT允许你去忽略那些表达式的那些重复的值, 而ALL会将所有值都考虑在内. 请注意cust_nbr列有重复的值. 请观察下面SQL的执行结果:

 

SELECT COUNT(cust_nbr), COUNT(DISTINCT cust_nbr), COUNT(ALL cust_nbr)

FROM cust_order;



COUNT(CUST_NBR) COUNT(DISTINCTCUST_NBR) COUNT(ALLCUST_NBR)

--------------- ----------------------- ------------------

             20                       4                 20

 

cust_nbr列有4个不同值. 因此， COUNT(DISTINCT cust_nbr)返回4，而COUNT(cust_nbr) and COUNT(ALL cust_nbr) 都是20. ALL是默认选项，如果你不在表达式前使用DISTINCT或者 ALL 的话，函数会考虑所有具有非空值的所有行。 

 

在这里有一个重要的事实，ALL并不会让聚合函数去考虑哪些空行. 也就是说下面例子中的COUNT(ALL SALE_PRICE)仍然返回14，而不是20.

 

SELECT COUNT(ALL sale_price) FROM cust_order;



COUNT(ALLSALE_PRICE)

--------------------

                  14

 

 

既然ALL是默认选项，你完全可以在聚合函数中显式的使用. 然而哪些有多个参数的聚合函数却不允许使用DISTINCT. 这些函数包含 CORR, COVAR_POP, COVAR_SAMP以及所有的线性函数.

 

另外,一些只有一个参数的函数也不允许使用DISTINCT. 这类函数包含STTDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, 和 GROUPING.

 

如果你在以上不允许使用的函数中使用了DISTINCT，你会得到一个错误，如下：

 

 

SELECT STDDEV_POP(DISTINCT sale_price)

FROM cust_order;



SELECT STDDEV_POP(DISTINCT sale_price)

       *

ERROR at line 1:

ORA-30482: DISTINCT option not allowed for this function

 

而使用ALL不会引起错误，如下:

 

SELECT STDDEV_POP(ALL sale_price)

FROM cust_order;



STDDEV_POP(ALLSALE_PRICE)

-------------------------

               29.5282639