元函数在SQL查询中应用实例分析【续上篇】

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等 方面（例如，出现在特征函数中的属性， 象数据类型等等）。所有这些，都有待于

的作用，我们认为同样的思想也应当针对数据库更新 人原始工作的框架内，也存在着种种明显的可扩充的 既可以是普通的表属性，也应当允许是某种受限的抽 我们更进一步的工作去挖掘或作出分析判断。