当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2013-08-22   作者:cool010   来源:转载   浏览次数:
摘要: 第四章 Group Operations 分组操作   分组操作在SQL编程中经常用到。当你使用SQL取访问数据库时,你经常会遇到如下问题: 1.一个部门中最高的工资是多少? 2.每个部门中有多少个经理? 3.每个产品的客户有多少个? 4.请打印每个区域的月合计销售额?   你需要使用分组操作去解决这些问题. Oracle提供了一系列丰富的特性取处理

第四章 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

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
当表中某字段是clob类型,保存的是xml格式的信息,如何利用sql来解析xml呢? oracle自带强大的xmlPa
  最近项目有一个需求,从订单表查询出每个客户最近一条订单记录。数据库表结构如下图 SELECT * F
什么是分组函数:分组函数作用于一组数据,并对一组数据返回一个值。 组函数类型:AVG COUNT SUM MAX
一,什么是分组函数 分组函数作用于一组数据,并对一组数据返回一个值 二,分组函数类型 Avg ,coun
--1.create database CREATE DATABASE MyDB GO --2.use database USE MyDB GO --3.create table CRE
数据类型 Char 用于描述定长的字符型数据,长度<=2000字节 varchar2 用于描述变长的字符型数据,
问题:求单科成绩最高的记录 物理模型图: 建表语句 create table score ( id int not null, stuid
问题:求单科成绩最高的记录 物理模型图: 建表语句 create table score ( id int not null, stuid
我想对code这一列做分组统计,请问大神sql语句该怎么写? 这是我写的sql语句 SELECT t1.id, t1.code
  //来自于别人 使用Northwind 数据库   首先查询Employees表   查询结果: city列里面只有5个
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号