SQL Story摘錄（八）————數(shù)據(jù)抽取
2024-07-21 02:09:04
供稿：網(wǎng)友
數(shù)據(jù)抽取
理論上的關(guān)系型數(shù)據(jù)庫(kù)，數(shù)據(jù)是以關(guān)系的形式存在。通常我們都可以把它們視為一種集合。這樣，數(shù)據(jù)一般是以無(wú)序的形式存在的。這種做法的好處自不用多加討論了，不過我們也得承認(rèn)，有時(shí)這樣也會(huì)帶給我們一些麻煩。我最近就遇到這么一件。
我在網(wǎng)上遇上我的一個(gè)老同學(xué)，他提出了這樣一個(gè)問題。有一個(gè)表（假設(shè)就叫mytable），表中有三個(gè)整型字段（假設(shè)就叫a11，a12，a13），其上有一個(gè)唯一鍵約束。現(xiàn)在他想要在這個(gè)表的數(shù)據(jù)中取一些樣品。他希望從中a13的各個(gè)取值中，各取一條記錄。然后我問他，對(duì)a11和a12有什么要求嗎？他說，沒有，怎么取都可以，有沒有規(guī)律都行。我想當(dāng)然的說，簡(jiǎn)單，給我二十分鐘。
有一個(gè)笑話不知你們聽過沒有。說很久以前，一個(gè)大數(shù)學(xué)家講課，有學(xué)生問他能不能證明四色定理。他說，那容易，我現(xiàn)在就證給你們……然后他寫啊寫啊……想啊想啊……一直想到下課也沒證出來(lái)，然后外面打了一個(gè)大大的響雷，他自嘲說，上帝也被我的狂妄激怒了……
什么，你問我剛才的問題怎么樣了？我告訴你，二十分種，不不，四十分鐘，不不，好像是一個(gè)小時(shí)……后來(lái)到半夜了，外面開始打雷……我對(duì)我的同學(xué)說，老兄，我錯(cuò)了，讓我把這問題拿回去想想先……
以下是一個(gè)示例，我隨便錄了些數(shù)據(jù)進(jìn)去
a11 a12 a13
----------- ----------- -----------
0 0 0
0 0 1
0 1 1
1 1 1
1 1 2
1 2 2
2 2 2
2 2 1
2 2 3
2 1 3
2 3 3
3 3 1
3 3 2
3 2 3
3 2 1
3 2 2
3 1 1
3 1 2
3 1 3
一開始，我是想用一個(gè)select max(a11), max(a12), a13 from mytable group by a13，后來(lái)用腳趾頭想了想，發(fā)現(xiàn)不可能。因?yàn)?max(a11)和max(a12)都只依賴于分組子句 group by a13。顯然這樣max(a11)和max(a12) 不一定會(huì)是同一條記錄中的數(shù)據(jù)。就像下面這樣：
a13
----------- ----------- -----------
0 0 0
3 3 1
3 3 2
3 3 3
注意，其實(shí)3, 3, 3這一行是不存在的。
然后我想，select max(a11), max(a12), a13 from mytable group by a13, a12行不行？用sql server 試了試，也不對(duì)。 因?yàn)檫@樣的話，它會(huì)先按 a13, a12來(lái)分組所返回的結(jié)果集，所以會(huì)比我們要的數(shù)據(jù)多的多。就像下面這樣
a13
----------- ----------- -----------
0 0 0
0 0 1
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
2 3 3
諸如此類的傻事我還干了很多，后來(lái)我終于想明白我要什么數(shù)據(jù)了：取a12，a13，使得在所有按a13的值分組后，取每組中最大的a12，然后取整記錄，使得a11是a12和a13滿足前述條件后的最大的一個(gè)值。這樣，我寫出了一個(gè)語(yǔ)句：
select
(select max(i.a11) from mytable i where i.a13 = o.a13 and i.a12 = max(o.a12)) a11
, max(o.a12) a12
, o.a13 a13
from mytable o
group by o.a13
這行語(yǔ)句返回值如下：
a11 a12 a13
----------- ----------- -----------
0 0 0
3 3 1
3 3 2
2 3 3
同樣的，我們可以解釋它為最兩個(gè)結(jié)果集，一個(gè)是 select max(a11), max(a12), a13 from mytable group by a13，一個(gè)是 select max(a12), a13 from mytable group by a13, a12，然所按a12和a13進(jìn)行等值聯(lián)接，這就是：
select max(l.a11) a11
, max(l.a12) a12
, l.a13 a13
from mytable l
join mytable r
on l.a13 = r.a13
group by l.a13, l.a12
having l.a12 = max(r.a12)
我個(gè)覺得這樣子比較清爽，不知各位以為如何？
事實(shí)上，這一類的數(shù)據(jù)抽取，不可能用一個(gè)簡(jiǎn)單查詢得到，因?yàn)槠渲杏幸粋€(gè)字段要同時(shí)參予分組和統(tǒng)計(jì)。只有（事實(shí)上是只要）把我們的問題用sql語(yǔ)言的思考方式描述清楚，答案也就得到了，一切就這么簡(jiǎn)單。
對(duì)于一類完全按行隨機(jī)抽取的問題，除了化為top n問題解決外，還有一個(gè)好主意就是在支持物理行號(hào)的數(shù)據(jù)庫(kù)系統(tǒng)中，用rowid 或標(biāo)識(shí)列之類的技術(shù)來(lái)解決，這樣比較簡(jiǎn)單，就不多說了。
補(bǔ)充一句，以前我說過interbase不支持自動(dòng)標(biāo)識(shí)列，但實(shí)際上，它也可以間接的實(shí)現(xiàn)。在interbase中，我們可以create一種叫 generator的全局變量，然后用一個(gè)觸發(fā)器，結(jié)合一個(gè)函數(shù)叫g(shù)en_id的，就可以做到。雖說有點(diǎn)麻煩，但并非不可能。詳細(xì)情況，大家可以查詢 interbase有關(guān)技術(shù)文檔。
歡迎來(lái)信和我討論技術(shù)問題：[email protected]。
