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

【翻译】 (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,MINDISTINCT

指定聚合函数只应该考虑表达式的不同值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_dtFROM 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-0120 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

查看更多 下一篇