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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

 写这本书的最初想法，来自于在第一个公司工作时，与同事的交流和学习。不过发布这本书的导火索，却在于一次在CSDN上读到一篇关于最新信息的报表问题的贴子。贴子中的问题可以用子查询和联接两种方式完成。由于条件所限，我不能详细解答，由此发贴的朋友不能理解我的本意，让我心生遗憾。所以决定将写书的想法付诸行动，并将这本书贴在CSDN上，与大家一起交流，共同进步。今天正好又见到类似于当日的问题，心生感触，决定在这里把它详细讨论一下。
在实际工作中，我们有时会需要建立数据表来存储变动的数据，并由这些数据统计出我们所需的信息。其中有一类问题的特点在于最终结果的过滤条件来自分组统计后的数据。这类应用常见于、财会系统、实时系统、数据仓库与数据挖掘等。事实上，这种命题本身已经包括了数据挖掘。现在，我们看下面的例子

例4-4-1：最新报价

网友kikilyq问：

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

goodspricedates

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

HP电脑200005.21

HP电脑200505.23

NEC电脑312005.3

NEC电脑320005.5





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

上面表的结果为：

goodspricedates

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

HP电脑200505.23

NEC电脑320005.5

帮帮忙，搞定这个问题？





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

版脚本如下

CREATETABLE[dbo].[GOODS](

[GOODS][char](10)COLLATEChinese_PRC_CI_ASNOTNULL,

[PRICE][money]NOTNULL,

[DATE_TIME][datetime]NOTNULL,

PRIMARYKEY(GOODS,DATE_TIME)

)ON[PRIMARY]

InterBase版脚本如下

CREATETABLEGOODS

(

GOODSCHAR(10)NOTNULL,

PRICENUMERIC(15,4)NOTNULL,

DATE_TIMETIMESTAMPNOTNULL,

PRIMARYKEY(GOODS,DATE_TIME)

)

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

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

SELECTGOODS,MAX(DATE_TIME)

FROMGOODS

GROUPBYGOODS

返回结果

GOODS

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

HP2002-05-2300:00:00.000

NEC2002-05-0500:00:00.000

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

SELECTGOODS,PRICE,MAX(DATE_TIME)

FROMGOODS

GROUPBYGOODS

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

SELECTL.GOODS,

(SELECTR.PRICEFROMGOODSRWHERER.GOODS=L.GOODSANDR.DATE_TIME=MAX(L.DATE_TIME))ASPRICE,

MAX(L.DATE_TIME)ASCURRENT_DATE_TIME

FROMGOODSL

GROUPBYL.GOODS

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

SELECTL.GOODS,L.PRICE,L.DATE_TIME

FROMGOODSL

WHEREL.DATE_TIME=

(SELECT

MAX(R.DATE_TIME)

FROMGOODSR

WHERER.GOODS=L.GOODS

GROUPBYR.GOODS)

这个

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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>