SQL Story摘錄（四）————資訊挖掘初步 (轉)[@more@] 寫這本書的最初想法，來自於在第一個公司工作時，與同事的交流和學習。不過釋出這本書的導火索，卻在於一次在CSDN上讀到一篇關於最新資訊的報表問題的貼子。貼子中的問題可以用子查詢和聯接兩種方式完成。由於條件所限，我不能詳細解答，由此發貼的朋友不能理解我的本意，讓我心生遺憾。所以決定將寫書的想法付諸行動，並將這本書貼在CSDN上，與大家一起交流，共同進步。今天正好又見到類似於當日的問題，心生感觸，決定在這裡把它詳細討論一下。

在實際工作中，我們有時會需要建立資料表來變動的資料，並由這些資料統計出我們所需的資訊。其中有一類問題的特點在於最終結果的過濾條件來自分組統計後的資料。這類應用常見於網站、財會、實時系統、資料倉儲與資料探勘等。事實上，這種命題本身已經包括了資料探勘。現在，我們看下面的例子

例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

幫幫忙，搞定這個問題？

根據問題，我們先建立資料表，經分析，表中資料應由貨物名和日期標示，所以設這兩個欄位為主關鍵字：

SERVER版指令碼如下

CREATE TABLE [o].[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)

)

建立表後，請讀者自行將資料插入。

這個問題中，最終報表中的電腦的價格取決於其後一次報價，也就是報價日期最新的那一行資料。典型地屬於前面提到的型別。直覺來講，我先試著選出每種電腦的最新報價日期，這個比較簡單：

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

不過很遺憾，這個語句只能在中執行，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部分。與我分享成功的喜悅和勞動的艱辛。

以下是我初步擬定的《SQL Story》 提綱，也就是說，現在各篇文章的例題屆時會分門別類出現在這本書的合適位置，可能現在的序號編排會有變動。

前言

第一章 基本原則

一、編碼之前

二、編碼原則

三、SQL指令碼設計原則

第二章 關係型資料庫的真相

一、向量空間中的集合

二、集合中的資訊

三、集合運算

四、真實的世界

第三章 不簡單的簡單查詢

一、選擇資訊

二、條件過濾

三、有序與無序

四、資訊統計與資料探勘

第四章 空間的“乘法”——聯接查詢

一、迪卡爾積與資訊爆炸

二、更高維度的空間

三、聯接查詢

四、Join與Where

第五章 SQL“從句”——子查詢

一、從句語法

二、代價評估

三、活用子查詢

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

一、設計方法

二、刪除

三、插入

四、改寫

第七章 終極武器——遊標

一、遊標的為什麼

二、遊標的怎麼辦

三、遊標特技

四、過程化編碼

第八章 不可能的任務

一、趣味問題集錦

二、無限點集

三、無限資訊集

四、探索集合

第九章 不只是SQL——資料庫管理

一、合理的

二、持久存在的程式碼——、儲存函式和

三、檢視用法

四、資料儲存的合理方式

後記

附：另類