【通译】 (Mastering Oracle SQL) 第四章分组操作 (一)_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,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_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

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

第四章 Group Operations 分组操作

4.1 Aggregate Functions 聚合函数

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

4.1.2 Use of DISTINCT and ALL 使用DISTINCT?和 ALL

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