《SQL语言艺术(PDF格式)》第26章

用了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　
然而，?