3. 实例分析
为了说明以上引入的特征函数在加速查询处理中的作用,让我们具体分析一个实例。 |
试考察一个描述学生收 selfincome)(8) | 入状况的表 Students(name,s | tatus,parentincome, |
其中name是主键,属性 自父母,当status取值0时 得到形如(name,income) 0值时)或是来自父母的收 | status是一种标法量,当status ,表明学生的收入完全是自己劳 的查询结果,其中income或为学 入(当相应的status取值为1时 | 取值1时,表明学生的收入完全来 动所得。针对这个表,假定我们想 生自己的收入(当相应的status取 )。 |
从表students的结构及查询结果的语义分析,完成查询的常规方法应当是 |
SELECT name,income=parentincome |
FROM student |
WHERE student=1 UNION (9) |
SELECT name , income=selfincome |
FROM student |
WHERE student=0 |
这是一个很自然、很直白的查询表达 执行这个查询的一般过程是:首先分别执 存放查询中间结果的临时表并将两个子查 序以便消除可能存在的重复值。至此,才 students要遍历两次而且要对中间结果作 的。查询(9)唯一的优点,是它表达上 | ,但同时也是一个非常低效和非常耗费资源的表达。 行由算子UNION所连结的两个子查询,然后产生一个 询的结果存入以这个临时表中,第3步对临时表作排 得到最终的查询结果。在这样的处理中,除对整个表 排序处理,处理上的烦杂和资源的消耗都是显而易见 的自然直白,谁都想得到。 |
对本例而言,还有更紧凑和更有效的查询表达。例如,不难验证以下的查询 |
SEIECT name,income=parentincome | *status+selfincome*(1-status) |
FROM students; (10) |
从语义上与查询(9) ,因为它只遍历一次表stud 果,不同的查询表达在处理 式,不但是必要的而且是可 | 完全等价。但查询(10)不但消 ents而且避免了可怕的排序操作 效率和资源消耗上可能会相去甚 行的。 | 耗的存储少而且处理上要有效得多 。这个例子说明,对同一个查询结 远。因此,寻求有效的查询表达方 |
查询表达(10)与像( 子句和算子UNION显式给出 查询表达采用什么形式,本 表达(10),不难发现只所 问题稍作些许改变(例如, ,如此等等)问题就不会这 们对任何显式表达在WHERE 案是肯定的,这就是我们在 | 9)那样的常规表达不同之处在 ,首者将查询条件间接地隐藏在 例都属于“条件检索”的查询类 以能给出如此简洁而正确的回签 属性student取0和1以外的值, 么简单了。因此,是否有一种很 子句和相关算子中的选择条件找 下面要介绍的“特征函数法”。 | 于,后者的查询条件由两个WHERE SELECT子句的算术表达式中。无论 型。如果对照一下查询要求和查询 ,实在是有点“事有凑巧”。如果 或者student取两个以上的标法值 一般、很系统的解决方案,能让我 到与之语义等价的算术表达式?答 |
4. 几个典型查询的特征函数解 |
正如上面所讲,特征函数能够实现我 因此,特征函数最直接和简便的运用是针 为了较全面地了解特征函数在解决复杂查 例。对某些实例,我们还将说明它的应用 的特征函数都没有用元函数展开。因此, (5)、(6)替换这里的特征函数。 | 们的愿望,即将显式的布尔条件转化为标量表达式。 对条件检索型的查询,但它的作用并不仅仅止于此。 询中的作用,本节将由易到难介绍和分析若干典型实 领域。为了表达上更紧凑,所有出现在标量表达式中 如果要通过实际运行验证这里的实例,必须先借助于 |
4.1 条件检索 |
由(10)给出的查询可借助于特征函数识为 |
SELECT name, |
income=parentincome | *d [status=1]+selfincome | *d [status=0] |
FROM student (11) |
如果检索条件仅止于此 检索条件远比此处复杂而多 外,加上按学生的年龄分段 依靠父母的学生为一组,凡 三组。在查询结果中,收入 母的收入和学生自己的收入 于用常规方法处理查询的人 相对于原问题而言,要求的 | ,用(11)代替(10)并没有什 样。例如,若将上例的要求稍作 的要求,即以19岁和23岁为年龄 年龄超过23岁者完全自食其力的 (income)一栏有不同的含义: ,对第三组学生,则对应于前两 看来,这样的条件就显得大复杂 扩展是很轻微的。对照查询表达 | 么本质上的意义。但实际问题中的 修改,即在保留status原有语义而 的分界点,凡年龄不超过19岁而且 学生为第二组,所有其他学生为第 对前两组学生,分别对应于他们父 组学生收入的算术平均值。在习惯 了。实际上,这是很自然的要求, 式(11),不难验证 |
SELECT name, |
income=parentincome*d [atatus=1]*d [age<=19]+selfincome*sign (d [status=O]+d [age>23])+( (parentinceome+selfinco me)/2.0*(1一d [status=1])*d [age<=19]-sign(d [sign=0] +d [age>23]) |
FROM students; (12) |
正是上述查询所要求的有效表达方式 一一对应,都具有很典型的级联式IF� ,无论查询条件多复杂(例如有更多的属 等等),条件检索型的查询都具有如(11 件越多则级联数越多,但正则算术表达式 只对表student遍历一次。相反,若按常 来回答,最终的结果是所有子查询结果往 了然。 | 。从income的表达形式看,与查询条件的要求完全是 THEN�ELSE结构。一般而言,在特征函数的参与下 性出现在条件中,同一属性值被划分为更多的区段, ),(12)那样的典型结构。不同之处仅仅在于,条 的逻辑结构都相同。所有这类查询表达,在执行中都 规方法求解,原则上每一个分类条件需要一个子查询 UNION运算所得。两种表达两种效果,孰优孰劣一目 |
4. 2 直方图问题 |
求直方图是统计应用中 ,用常规方法求解并不是一 处理的过程很高效而且很直 在表employee(name,age 如(nokids,onekids,few 、有一个孩子的、有两个或 | 经常要解决的问题白如果统计数 个很轻松的任务。但是,借用特 观。为了说明这一点,让我们看 dept,kids)中,其中kid也表 kids,manykids)的统计结果, 三个孩子的以及有三个以上孩子 | 据来自数据库而且数据量很大的话 征函数可以顺利地解决问题,不但 一个具体的例子。假定统计数据存 示每个雇员的子女数。要求给出形 即分别计算出所有雇员中没有孩子 的雇员总数。 |
如果用常规方法,需要查历表employ manykids的值,然后经3个UNION运算才能 分为4个区段而是8个甚至更多个区段,常 问题的解显然是 | ee四次,分别计算出nokids,onekids,fewkids和 得出最终的结果。如果原问题不是将雇员的子女数划 规方法的低效就更明显不过了。运用特征函数,上述 |
SELECT nokids=SIJM(d [kids=0]), |
Onekids=SUM(d [kids=1]) |
fewkids=SUM(d [2<=kids<=3]) |
manykids=SUM[kids>=4]) |
FROM employee; (13) |
这个查询结果的正确性很容易验证:对于表中任意一行,如果kids=0,则d [kids=0]=1而且d [kids=l]=d [kids>=4]=d [2<=kids <=3]=0,所以该行在区段nokids中求和而不在任何其他三个区段中求和,对于kids的其他取值依此类推,这表明(13)的结果正是原问题所需要的结果。重要的是这个结果不但正确,得到这个结果的途境非常有效,因为处理中只遍历表一次。如果将雇员所拥有的子女数区分为更多的值段,运用特征函数的查询处理仍然只遍历表一次而不是更多,不同之处仅在于选择表中的计算多几项而已,查询表达在逻辑上的复杂性一点也没有增加。 |
同一个基本问题也可以 问题往往很困难。 | 引导出不同的变异。若没上述的 | 基本解作基础,直接解决这些变异 |
变异问题之一:对同一 要求得到如(dept,nokids | 个表employee,按雇员所在的不 ,onekid,fewkids,manykids | 同部门分别计算子女数的分布,即 )的结果。 |
这个问题的解显然是以下的查询表达 |
SELECT dept. |
nokids=SUM(d [kids=0]), |
onekid=SUM(d [kids=1]) |
fewkids=SUM(d [2<=kids <=3] |
manykids=SUM(d [kids>=4]) |
FROM employee;GROUP BY dept; (14) |
变异问题之二:按照年龄区段求雇员 个区段,即:小于25岁的、大于45岁的以 2年龄段。这个问题事实上是要求对表emp fewkids,manykids)的结果,其中 | 子女分布的直方图。为确定起见,雇员的年龄分为三 及年龄在25岁到45岁之间的,分别称为第1、第3和第 loy回给出形如(ageCategoy,nokids,onekids, |
1 若age<25 |
d (a)= 2 若25<=age<=45 3 若age>45 |
(15) |
这个问题虽有相当的难 Sybase的Transact SQL就是 需要的解答 | 度,但对于允许表达式出现在GR 如此),答案也是直接了当的。 | OUP BY子句中的系统(例如, 不难验证以下查询表达正是我们所 |
SELECT ageCategory=1xd [age<25]+2×d [25<=age<=45] +3×d [age>45], |
nokids =SUM(d [kids=0]), |
onekids =SUM(d [kids=1]), |
onekid =SUM( d [kids<=3 AND Kids>=2]) |
manykids =SUM(d [kids>=4]) |
FROM employee |
GROUP BY 1′ d [age<25]+2×d [25<=age<=45]+ 3×d [age>45]; (16) |
这个问题与上一个问题的区别仅仅在 ageCaegory,按照(15)式的定义,很容 | 选择表和GROUP BY子句中使用3年龄段表达式 易验证(16)确是我们所需要的查询。 |
沿着这个思路走下去,还可以处理更 有效性也越能显现出来。 | 复杂的问题。当直方图越来越“宽”时,特征函数的 |
4.3 表转置 |
表转置是一个变换过程 设计中经常遇到的问题。C Date将中文中前一种形式的 示。鉴于SQL的集函数本质 用集函数的给应用处理带来 主意。 | ,它将一个窄而长的表转化成一 .j Date很早就注意到这一点并 表称之为表的“列式”表示,而 上是面向列式表示而不是面向行 灵活性的优点。因此,基表的设 | 种宽而短的表,这是在数据库应用 给出了处理这一问题的一般原则。 将后一种形式的表称为“行式”表 式表示的,所以列式表示有便于运 计多考虑采用列式表示一般是个好 |
针对列式表示的基表作 雇员月奖金的表bonus(nam 示,例如就可以写成bonus 奖金一览表,从bonus'表查 他的查询要求很难有适应性 回签其他处理要求。例如, | 查询时,特征函数是实现表转置 e,month,amount)。这个表显 ’(name, janAmount,…,decAm 询最简便。但这种行式表示的表 。相反,形如bonus的列式表示 针对上述要求的特征函数表示就 | 的有力主具。例如,考察一个记录 然是列式表示,相对于它的行式表 ount)。如果想得到每个雇员各月 本质上只对这一种查询有效,对其 不仅可以回答上述查询而且还可以 是 |
SELECT name, |
janAmount=SUM(amount×d [month=1]), |
febAmount=SUM(amount×d [month=2]), |
. . . . |
decAmount=SUM(amount×d [month=12]) |
FROM bonus |
GROUP BY name; (17) |
读者不妨思考一下,针对bonus表的同一查询要求若不采用特征函数该怎么满足。 |
4.4 求中位数 |
在实验数据处理中,经常有求一组数 位数,存在两种定义,即统计学定义和所 是这组数中的某一个。因此,当有偶数个 ,要视具体应用背景而定。中位数的后一 果数值的个数为奇数(设为n),则中位 的定义,用一个语句求一组数的中位数始 用一般方法来处理这个问题,也得写一个 这个问题的一个很简洁的解。为确定起见 这组数据中不存在重复的值。除此而外, 假定下,数据集data的中位数正是下述查 | 据的“中位数”(median的要求。众所周知,关于中 谓的“财务”定义。按照统计学的定义,中位数必须 数时,必须从两个数中作出选择,或选大者或选小者 种定义取两数(在有偶数个数时)的算术平均值。如 数就是数组中第(n+1)/2个数。不论采用什么样 终是一个难题。既使是训练有素的SQL程序员,要想 很复杂的过程。但是,借用于特征函数,很容易得到 ,考虑这样的一组实验数据data (value)。这表明 我们还要假定所有的数据都是非空的数据。在这样的 询语句的结果: |
SELECT x.value FROM data x, data y |
GROUP BY x.value |
HAVING SUM(d [y.value<=x.v | alue])=(COUNT(×) +1)/2 (18) |
因为,对于每个x.value,表达式SUM(d [y.value<=x.value])的结果是数据集中小于或等于该值数据的个数,所以由这个HAVING子句选择的正是所要求的中位数(细心的读者不难发现,我们这里利用了Sybase两个整数相除的结果是实际相除后再取截断而得到的值。另外,当数据集包含偶数个元素时,取的数是两数中较小的一个,这正符合中位数的统计学定义。) |
上面求中位数的方法很 考察数据集data 2(partit 以是任意数据类型,整个数 是各个子集的中位数: | 容易延拓到数据集往某些属性而 ion,value),其中value仍为非 据集data2经此属性而分割为若 | 分割为若干个子集的情形。例如, 空数值量,但属性partition则可 干个子集。下述查询语句的结果恰 |
SELECT x.partition,x.value |
FROM data2 x,data2 y |
WHERE x. partition=y. Partition |
GROUP BY x.Partition , x.value |
HAVING SUM (d [y.Value<=x.value])=(COUNT(x)+1) /2 (19) |
上式中的自连结是表分割的需要,除此而外上两种方法没有本质上的区别。 |
4.5 求端值 |
在某些实际问题中,所 将求这些数据项中取值最大 sat2),其中sat 1和sat 2 成绩的一览表,即求形如( 最好成绩。 | 设计的表的行数据包含了若干个 或最小的称为“端值问题”。例 代表学生两次考试的成绩。假定 name,bestSat)的结果,其中b | 可以彼此比较分析的数据项。我们 如,考察表scores(name,sat1, 需要得到每个人两次考试中的最好 estSat表示每个学生两次成绩中的 |
某些数据库系统(例如Oracle)有内部函数greatest(value 1,value 2…),可供直接解决问题。在不具备这种函数的系统(例如Sybase等)中,一般解决方法是,第一次遍历全表得以满足条件,sat 1>=sat 2的sat 1,第二次遍历全表得到满足条件sat 2>sat l的sat 2,再将中间结果经UNION运算才能获得最终结果。 |
借助于特征函数只须扫描表一遍而且查询表达非常简单,即: |
SELECT name, |
bestSat=sat 1% d [sat 1>=sat 2]+sat 2×d [sat 2>sat1] |
FROM score; (20) |
假定我们不只要得到每 所得,只须在(20)的选择 | 个学生两次考试中的最好成绩, 表中补加一项,即: | 而且还想知道这个成绩是哪次考试 |
SELECT name |
bestSat=sat l % d [sat1>=sat 2]+sat 2% d [sat 2>sat 1] whichSat=1% d [sat 1>=sat 2]+2% d [sat 2>sat l] |
FROM score; (21) |
这个结果只在sat 1=s 所得)。除此而外似乎不必 兴趣的读者不妨考虑一下这 | at2 时有点歧义但并不错(在这 再做任何解释了。以上只考虑了 个问题的种种变异情况及其解答 | 种情况下,(21)认为是第一次考试 最大值,求最小值可仿此办理。有 。 |
5 几个值得进一步思考的问题 |
由(5)式给出的特征 函数,在计算的复杂性上会 改善特征函数的效率。其次 ,是为了保证元函数abs( 在着降低这一条件强度的可 ,去掉非空假定就显得特别 | 函数表示,并不是唯一的形式, 有差异。因此,选择具有更低计 ,本文开头关于出现在特征函数 )和sign()有定义和整个(5 能性。考虑到几乎所有的主流数 必要了。 | 还可有其他的表现形式。不同的元 算复杂性的函数,有可能更进一步 中的属性必须是非空数值量的假定 )式正确的充分性条件。所以,存 据库系统都支持三值逻辑(3VL) |
尽管本文只考虑了特征函数在查询中 作较系统的考察。另外,在E.birger等 方面(例如,出现在特征函数中的属性, 象数据类型等等)。所有这些,都有待于 | 的作用,我们认为同样的思想也应当针对数据库更新 人原始工作的框架内,也存在着种种明显的可扩充的 既可以是普通的表属性,也应当允许是某种受限的抽 我们更进一步的工作去挖掘或作出分析判断。 |