SQL Story摘錄（四）————信息挖掘初步
2024-07-21 02:09:03
供稿：網友
寫這本書的最初想法，來自于在第一個公司工作時，與同事的交流和學習。不過發布這本書的導火索，卻在于一次在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
幫幫忙，搞定這個問題？
根據問題，我們先建立數據表，經分析，表中數據應由貨物名和日期標示，所以設這兩個字段為主關鍵字：
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——數據庫管理一、合理的索引二、持久存在的代碼——存儲過程、存儲函數和觸發器三、視圖用法四、數據存儲的合理方式后記附：另類聲音
國內最大的酷站演示中心!
