SQL语言艺术(PDF格式)-第11部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！



用了count（*）。测试存在性有时会以模仿布尔值的方式实现：　

casecount（*）　

when　0then　'N'　

else'Y'　

end　

对于上述实现，只要存在与条件相符的记录，就会读取其中每条记录。其实，只需找到一条记　

录就足以判断要显示　Y　还是　N，通过测试存在性或限制返回记录数可以写出更高效的语句，　

一旦发现条件相符就停止处理即可。　



当要解决的问题与最多、最少、最大、第一、最后有关时，聚合函数（可能会当成　OLAP　函数　

使用）很可能是最佳选择。也就是说，不要认为聚合函数仅支持count、sum、max、min、avg　

等功能，否则就说明你还没有充分理解聚合函数。　

有趣的是，聚合函数在作用范围上非常狭窄。除了计算最大值和最小值，它们唯一能做的就是　

简单的算术运算：count（）每遇到的一行加　1；avg（）一方面将字段值累加，另一方面不断加　1计　

数，最后进行除法运算。　

聚合函数有时可取得令人吃惊的效果，比如通过sum就可以做很多事情。喜欢数学的朋友知道，　

通过对数和次方函数，要在sum和乘积（product）之间转换有多简单。喜欢逻辑的朋友也会知　

道OR　很依赖sum，而AND很依赖乘积。　

下面通过简单的例子说明聚合的强大作用。假设要进行装运（shipment）处理，一次装运由一　

些不同的订单组成，每张订单都必须分别做准备；只有装运涉及的每张订单都完成时装运才准　

备就绪。问题就是，如何判断装运涉及的所有订单都已完成。　

这样的情况常会发生，有多种方法可以判定装运是否就绪。最糟的方法是逐一判断每批装运，　

而每批装运内部进行第二个循环，查看有多少张订单的order_plete字段值为“N”，并返回计　

数为　0　的装运　ID。更好的解决方案是理解“‘N’值的不存在性测试”的意图，并用子查询（无论　

是关系还是非关系）完成：　

select　shipment_id　

from　shipments　

where　not　exists（select　null　from　orders　

where　order_plete　='N'　

andorders。shipment_id　=shipments。shipment_id）　



如果表shipments上没有其他条件了，则上述方法很糟糕，当shipments表数据量大时（而且未完　

成订单占少数），换成以下查询会更高效：　



select　shipment_id　

from　shipments　

where　shipment_id　not　in（select　shipment_id　

from　orders　

where　order_plete　='N'）　



上述查询也可以稍作变形，优化器比较喜欢这个变形，但要求orders表　的shipment_　id字段上有　


…………………………………………………………Page　83……………………………………………………………

索引：　

select　shipments。shipment_id　

from　shipments　

leftouterjoin　orders　

on　orders。shipment_id　=shipments。shipment_id　

andorders。order_plete　='N'　

where　orders。shipment_id　isnull　

另一个替代方案是借助集合操作，该集合操作会使用shipments主键索引，且对orders表进行全表　

扫描：　

select　shipment_id　

from　shipments　

except　

select　shipment_id　

from　orders　

where　order_plete　='N'　

注意，并非所有　DBMS　都实现了　except　操作符，有的DBMS称之为　minus。　



还有一种方法。主要是对装运中所有订单执行逻辑　AND　操作，将order_plete为TRUE的订　

单的ID返回。这类操作在现实中很常见。如前所述，AND　和乘法、OR　和加法之间关系密切。　

关键是把诸如“Y”　和　“N”　的flag值转换为　0　和　1，使用　case　结构即可。要把　order_plete　

转成　0　或　1　的值可以这样写：　



select　shipment_id；　

casewhen　order_plete　='Y'then　1　

else0　

endflag　

from　orders　

到目前为止，一切顺利。如果每批装运包含的订单数固定的话，则很容易对适当字段进行sum　

后检查是否为预期订单数。然而，实际上希望每批装运的flag值相乘，并检查结果是　0　或是　1。　

这个方法是可行的，因为只要有一张以　0　表示的未完成订单，乘法的最后结果就是　0。乘法运　

算可由对数运行协助完成（虽然在以对数处理时，0　不是最简单的值），但我们这个例子要做的　

甚至更简单。　



我们想要的是“第一张订单已完成、且第二张订单已完成……且第　n　张订单已完成”。德摩根定　

律（laws　ofdeMorgan）（　注　4）告诉我们，这等价于“第一张订单未完成、或第二张订单未完成……　

或第　n　张订单未完成”的情况“不成立”。由于使用聚合时，OR　比　AND　更容易处理。检查由　

OR　连结的一连串条件是否不成立，比检查由　AND　连结的一连串条件是否成立，要容易得多。　

我们要考虑的真正“谓词（predicate）”是“订单未完成”，并对　order_plete　标志作转换，如　

果是　N　就转换为　1，如果是　Y　就转换为　0。之后，通过加总flag值，就可检查是否所有订单　

的flag值都是0（都已完成）——如果总和是　0，所有订单都已完成。　

因此，查询可写成：　

select　shipment_id　


…………………………………………………………Page　84……………………………………………………………

from　（select　shipment_id；　

casewhen　order_plete　='N'then　1　

else0　

endflag　

from　orders）　s　

groupbyshipment_id　

havingsum（flag）=0　

甚至可以写得更简洁：　

select　shipment_id　

from　orders　

groupbyshipment_id　

havingsum（case　when　order_plete　='N'then　1　

else0　

end）　=0　

还有更简单的方法。使用另一个聚合函数，而不必转换任何的flag值。注意，从字母的顺序来看，　

“Y”　大于　“N”，如果所有的值都是　“Y”，则最小值就是　“Y”。于是：　

select　shipment_id　

from　orders　

groupbyshipment_id　

havingmin（order_plete）　='Y'　



这个方法利用了“Y”大于　“N”，而没有考虑标志转换为数值。本方法更高效。　



上例使用了　groupby，并以order_plete　值最小作为查询条件，那么，其中不同的子查询（或　

作为子查询替代品的聚集函数）之间是如何比较的呢？如果先做sum操作而后检查总和是否为　

0，必然导致整个orders　表排序。而上例中使用了不太常见的聚合函数min，一般比其他查询快，　

其他查询因访问两个表（shipments　和　orders）而速度较慢。　



先前的例子大量使用了　having　子句。如第4章所述，“粗心的　SQL语句”往往和在聚合语句中使　

用　having　子句有关。下面这个查询（Oracle）就是一例，它要查询过去一个月内每个产品的每　

周销售情况：　

select　product_id；　

trunc（sale_date；　'WEEK'）；　

sum（sold_qty）　

from　sales_history　

groupbyproduct_id；　trunc（sale_date；　'WEEK'）　

havingtrunc（sale_date；　'WEEK'）》=add_month（sysdate；　…1）　

这里的错误在于，having子句中的条件没有使用聚合。于是，DBMS必须处理sales_history中的　

每条记录，进行排序操作、进行聚合操作……然后过滤掉过时的数值，最后返回结果。这类错　

误并不引人注意，直到　sales_history表数据量变得非常大为止。当然，正确的方法是把条件放　

在　where　子句中，确保过滤会发生在早期阶段，而之后要处理的数据集已大为减小。　


…………………………………………………………Page　85……………………………………………………………

必须指出：对视图（即聚合的结果）应用条件时，如果优化器不够聪明，没有在聚合前再次注　

入过滤条件，我们就会遇到完全相同的问题。　

有些过滤条件生效太晚，应该提前，可做如下修改：　

select　customer_id　

from　orders　

where　order_date　0　

在这个查询中，以下　having　的条件乍看起来相当合理：　

havingsum（amount）》0　



然而，如果　amount　只能是正数或零，这种　having　用法就不合理。最好改为：　

where　amount　》0　

此例中，groupby的使用分两种情况。首先：　

select　customer_id　

from　orders　

where　order_date　0　

groupbycustomer_id　

我们注意到，groupby对聚合计算是不必要的，可以用　distinct　取代它，并执行相同的排序和消　

除重复项目的工作：　

select　distinct　customer_id　

from　orders　

where　order_date　0　

把条件放在　where　子句中，能让多余的记录尽早被过滤掉，因而更高效。　



总结：聚合操作的数据应尽量少。　


………………………………………………………………………用户上传之内容结束……………………………………………………………………………………