SQLStory摘录（四）————信息挖掘初步

在实际工作中，我们有时会需要建立数据表来存储变动的数据，并由这些数据统计出我们所需的信息。其中有一类问题的特点在于最终结果的过滤条件来自分组统计后的数据。这类应用常见于网站数据库、财会系统、实时系统、数据仓库与数据挖掘等。事实上，这种命题本身已经包括了数据挖掘。现在，我们看下面的例子

例4-4-1：最新报价

网友kikilyq问：

我有一个table:COMPUTER_PRICE，格式如下：

goods price dates

--------------------------------

HP电脑 20000 5.21

HP电脑 20050 5.23

NEC电脑 31200 5.3

NEC电脑 32000 5.5

查询结果要求：要查出每种电脑的最新价格；

上面表的结果为：

goods price dates

---------------------------------

HP电脑 20050 5.23

NEC电脑 32000 5.5

帮帮忙，搞定这个问题？

根据问题，我们先建立数据表，经分析，表中数据应由货物名和日期标示，所以设这两个字段为主关键字：

SQL Server版脚本如下

CREATE TABLE [dbo].[GOODS] (

[GOODS] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[PRICE] [money] NOT NULL ,

[DATE_TIME] [datetime] NOT NULL ,

PRIMARY KEY (GOODS, DATE_TIME)

) ON [PRIMARY]

InterBase版脚本如下

CREATE TABLE GOODS

(

GOODS CHAR(10) NOT NULL,

PRICE NUMERIC(15, 4) NOT NULL,

DATE_TIME TIMESTAMP NOT NULL,

PRIMARY KEY (GOODS, DATE_TIME)

)

建立表后，请读者自行将数据插入。

这个问题中，最终报表中的电脑的价格取决于其后一次报价，也就是报价日期最新的那一行数据。典型地属于前面提到的类型。直觉来讲，我先试着选出每种电脑的最新报价日期，这个比较简单：

SELECT GOODS, MAX(DATE_TIME)

FROM GOODS

GROUP BY GOODS

返回结果

GOODS

---------- ------------------------------------------------------

HP 2002-05-23 00:00:00.000

NEC 2002-05-05 00:00:00.000

显然，只要把各品牌电脑在以上日期的报价显示出来，就是我们所要的结果了。那么直接这么写如何？

SELECT GOODS, PRICE, MAX(DATE_TIME)

FROM GOODS

GROUP BY GOODS

我想这个语句就不用试了，稍有经验的程序员会发现，PRICE列不在统计函数中，也不在GROUP BY 之列，数据库系统无法执行这样的语句。这样的列一定要从另一个数据集中取出，所以我首先想到的是自联接。不过相信大多数朋友会先想到子查询。现在我们先看看子查询如何做，毕竟这样比较直观。最偷懒的办法是直接把PRICE表达为一个插入的标量子查询：

SELECT L.GOODS,

(SELECT R.PRICE FROM GOODS R WHERE R.GOODS = L.GOODS AND R.DATE_TIME = MAX(L.DATE_TIME)) AS PRICE,

MAX(L.DATE_TIME) AS CURRENT_DATE_TIME

FROM GOODS L

GROUP BY L.GOODS

不过很遗憾，这个语句只能在SQL Server中执行，InterBase的提示是invalid column reference（无效的列引用）。不过换一个思路就可以写一个通用版。现在，我们对系统说，我要从表中取出部分行，每个品牌的电脑一行，其日期是这个牌子的最新报价日期：

SELECT L.GOODS, L.PRICE, L.DATE_TIME

FROM GOODS L

WHERE L.DATE_TIME =

(SELECT

MAX(R.DATE_TIME)

FROM GOODS R

WHERE R.GOODS = L.GOODS

GROUP BY R.GOODS)

这个

我想应该还可以写出几个不同的子查询变种，不过大同小异，就不一一尝试了。这个版本看来有些不可靠，因为主查询的WHERE条件中只有DATE_TIME，似乎不能准确地区分出每一行数据。不过放心，这里有一个“诡异”的相互引用，主查询的记录要满足日期等于子查询的返回值，而子查询的货物名（GOODS列）依赖于主查询的货物名（GOODS列）。这样，子查询会针对当前的品牌返回其正确日期，这是相关子查询的绝技，也是造成它在很多场合效率较差的原因。我对子查询的兴趣到此为止了，前面的文章中我说过，联接查询是一种很好的技术，那么这个查询有没有可能用联接来实现呢？前面对子查询的分析在这里会有助于我们的思考。现在我们如果有两个数据集，一个有最大日期，一个有价格，把它们一联接，不就可以了吗？这两个结果集就在上面的子查询中，现在的问题是我们如何把它们联接起来，显然，有一个联接条件是R.GOODS = L.GOODS ，这同时也确定了最终结果集的唯一标识之一——GOODS列，而日期列的过滤条件照搬L.DATE_TIME = MAX(R.DATE_TIME) ，加上GROUP BY结果集中的列，于是就有了：

SELECT L.GOODS, L.PRICE, L.DATE_TIME

FROM GOODS L

JOIN GOODS R

ON L.GOODS = R.GOODS

GROUP BY L.PRICE, L.GOODS, L.DATE_TIME

HAVING L.DATE_TIME = MAX(R.DATE_TIME)

有些朋友可能不明白这个GROUP BY 为何而来，简单说一下。我们做了一个GOODS表的自联接（两个数据集分别是L和R）后，会生成一个迪卡尔积。有些人称之为数据爆炸。不过把矿石炸开之后，倒方便我们在其中找金子了。用JOIN条件一联接，L数据集中的每一种品牌在R数据集中都对应上了自己的所有报价日期。现在我们要对R数据集分组，选出每一品牌的最新报价日期，以其过滤L数据集就应该用

GROUP BY R.DATE_TIME

HAVING L.DATE_TIME = MAX(R.DATE_TIME)

由于我们要取出L数据集中的三列，所以要把它们也列在GROUP BY 中，由于已经有了L.GOODS = R.GOODS 这个联接条件，R.GOODS 倒是可以不写在GROUP BY 中了。由于我在前面的文章中提到过的种种理由，我个人比较喜欢用联接。当然从效率方面讲，当表中每个分组的数据很多时，联接查询的效率会因巨大的迪卡尔积而被抵消，这时应用子查询（如果这是一个记录生产线即时工作情况的表，统计其最新的生产情况，通常就是这样）；而表中每个分组的数据很少，但数据组很多（如大型网上书店，用户可能是一个天文数字，相对来说每个用户买的书就不多了）时，子查询仍要为主查询的每一行反复遍历整个表，效率就太低了，而联接只要处理事先生成的迪卡尔积，以空间上的代价换来了时间上的优势，这时应当用联接查询。

以上各种方案的返回结果集都是：

GOODS PRICE DATE_TIME

---------- --------------------- ------------------------------------------------------

HP 20050.0000 2002-05-23 00:00:00.000

NEC 31000.0000 2002-05-5 00:00:00.000

除特别指明，以上脚本可在MS SQL Server7.0以上版本和InterBase6.0.1 以上版本通用。掌握以设计以上脚本的方法，可以广泛用于各种以统计数据为查询条件的SQL编程。由此开始，我们还可以就两个有趣的方面展开讨论：联接查询和数据分组。

附：本来这个联接查询脚本还应当贴在 kikilyq 的问题后面，不过由于我愚蠢地贴了两个不该贴的贴子（一个有错，一个贴重了），CSDN的网页不允许我再往 kikilyq 的贴子后跟贴了。请kikilyq来这里读吧。抱歉。

关于《SQL Story》：这本书意在提出一些常见的SQL编程问题的解决方案，总结出一些关系型数据库设计和SQL语言编程方面的模式，通过实际问题，帮助读者提高数据库编程的能力，本书还会涉猎关系代数的理论领域。我计划在CSDN上收集一些典型的SQL 编程问题，做为本书的重要内容。作者保证不剽窃任何人的劳动成果，所有解答及分析绝不假手于人。如有高手指点迷津，我一定会在文中明确指出并提出感谢。欢迎各位网友提供素材，在此先表示感激。另外我希望能有一位Oracle高手与我合作，完成这本书的Oracle部分。与我分享成功的喜悦和劳动的艰辛。

以下是我初步拟定的《SQL Story》 提纲，也就是说，现在各篇文章的例题届时会分门别类出现在这本书的合适位置，可能现在的序号编排会有变动。

前言

第一章 基本原则

一、编码之前

二、编码原则

三、SQL脚本设计原则

第二章 关系型数据库的真相

一、向量空间中的集合

二、集合中的信息

三、集合运算

四、真实的世界

第三章 不简单的简单查询

一、选择信息

二、条件过滤

三、有序与无序

四、信息统计与数据挖掘

第四章 空间的“乘法”——联接查询

一、迪卡尔积与信息爆炸

二、更高维度的空间

三、联接查询

四、Join与Where

第五章 SQL“从句”——子查询

一、从句语法

二、代价评估

三、活用子查询

第六章 集合的写操作——Inert、Delete和Update

一、设计方法

二、删除

三、插入

四、改写

第七章 终极武器——游标

一、游标的为什么

二、游标的怎么办

三、游标特技

四、过程化编码

第八章 不可能的任务

一、趣味问题集锦

二、无限点集

三、无限信息集

四、探索集合

第九章 不只是SQL——数据库管理

一、合理的索引

二、持久存在的代码——存储过程、存储函数和触发器

三、视图用法

四、数据存储的合理方式

后记

附：另类声音