SQL Story摘錄（三）————可擴充套件設計 (轉)

SQL Story摘錄（三）————可擴充套件設計 (轉)[@more@]

面向集合的結構化設計。這一點很多人都知道，可真正能夠活用的就太少了。舉一個簡單的例子：
例1-3：有一個簡單的資料表Orders，某商店的訂單資訊：
CREATE TABLE [o].[ORDERS] (
[ID] [int] NTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrderDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CU_INX_OrderDate] ON [dbo].[ORDERS]([OrderDate]) WITH FILLFACTOR = 50 ON [PRIMARY]
GO
ALTER TABLE [dbo].[ORDERS] WITH NOCHECK ADD
CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED([ID])
ON [PRIMARY]
GO
表中現在有以下資料：
ID CustomerID OrderDate
----------- ----------- ------------------------------------------------------
1 1 1999-1-4
2 10 1999-3-5
3 22 1999-5-2
4 2 1999-6-7
5 2 2000-3-6
7 101 2001-5-3
8 10 2001-6-5
6 101 2002-4-2
那麼，我們如何生成一個1999-2002的年度訂單數報表呢（四年只有8個訂單？我為了演示方便才這樣做的，這並不代表真實的情況：P）？現在，我給出實際報表的資料格式，讀者們請先試一下這個語句的寫法
CustomerID 1999 2000 2001 2002
-------------- ------ ------ ------ ------
1 1 0 0 0
2 1 1 0 0
10 1 0 1 0
22 1 0 0 0
101 0 0 1 1
最直觀的想法，是在前臺，用其它語言實現這一功能。不過有一個辦法，可以用語言來實現它。而且不一定比你想像的更復雜：
CustomerID,
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
GROUP BY CustomerID
我想這時會有朋友提出InterBase不支援Case的問題。不過即使如此，我還是要向大家推薦這種寫法。因為它優美、簡潔，不僅我們讀著好懂，還可以很方便地寫出來自動生成它。事實上，Case關鍵字已是SQL標準之一，大勢所趨，會有越來越多的支援它的。
那麼它又是怎麼來的呢？我在設計這個語句時是這樣的思路：
1、我們需要一個同時在時間和客戶兩個座標軸上展開的報表；
2、縱向上，我們要為每一位客戶建立一行資料，這個比較好辦，我們首先確定了這個語句會有一個基本
SELECT CustomerID,
………………
FROM ORDERS
GROUP BY CustomerID
如果不區分年度，已下語句就是我們要的結果
SELECT CustomerID,
COUNT(ID) AS ORDERS_COUNT,
FROM ORDERS
GROUP BY CustomerID
3、設所有訂單為一全集，那麼這個集合的總數用以下語句來統計：
SELECT COUNT(ID) FROM ORDERS
橫向上，我們為每一年度的訂單數定義一列，以1999年為例，取年份為1999年的訂單子集的元素數為
SELECT SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999"
FROM ORDERS
其它年份依此類推，我們得到每一年的訂單數：
SELECT SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
其返回結果如下：
1999 2000 2001 2002
----------- ----------- ----------- -----------
4 1 2 1

 

(所影響的行數為 1 行)
4、顧及到關係型資料庫“詭異”的NULL值問題後，綜合2、3步，我們得出最終的語句：
SELECT CustomerID,
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 1999 THEN 1 ELSE 0 END) AS "1999",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2000 THEN 1 ELSE 0 END) AS "2000",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2001 THEN 1 ELSE 0 END) AS "2001",
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2002 THEN 1 ELSE 0 END) AS "2002"
FROM ORDERS
GROUP BY CustomerID
現在這個報表結構清晰明白。擴充套件性極強。比如明年我們需要年的統計資料，只要再依葫蘆畫瓢，來一列
SUM(CASE WHEN YEAR(isnull(OrderDate, 0)) = 2003 THEN 1 ELSE 0 END) AS "2003"
加在最後就可以了，它是全集中的2003年資料的子集。還有，用來判斷空值的isnull不一定所有的資料庫都有，沒關係，只要在Case的分支里加一行
WHEN OrderDate is NULL THEN 0
就可以了。基於這個思想，我們可以很容易地寫出一個，只要給定起訖年份，就可以生成一個完整的年度報表。由於所有的運算都在端執行，並且是隨著資料檢索一次就完成了。它的速度快於客戶端的報表。而且傳輸的資料量也少，可以有效減輕負載。
在《6.5技術內幕》中，有一個類似的例子。不過作者使用的語句結構比我的複雜，他的例子中，From關鍵字是從一個子查詢匯出表中選擇的資料，這讓我百思不得其解。也許6.5版的MS SQL Server還不支援我的寫法，也許那樣寫更好。作者並沒有說明，我也一直沒有機會接觸到MS SQL Server6.5。
對於InterBase，我還沒有辦法用足夠優雅的語句生成這個報表。這主要是由於InterBase不支援Case。不過，如果你對語句的效能和美感要求不高的話，下面這個語句可以實現與以上的SQL Server版本相同的功能：
SELECT O.CUSTOMERID,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 1999))
as COUNT_1999,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2000))
as COUNT_2000,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2001))
as COUNT_2001,
(SELECT COUNT(I.ID)
FROM ORDERS I
WHERE (I.CUSTOMERID = O.CUSTOMERID)
AND (EXTRACT(YEAR FROM I.ORDERDATE) = 2002))
as COUNT_2002
FROM ORDERS O
GROUP BY O.CUSTOMERID
依照SQL Server版本，我們完成了InterBase版的年度報表。不同的是由於使用了相關子查詢統計資料，它的會差一些(好在你不需要即時你的年度報表吧)。不過由於它同樣是基於面向集合的設計構架，至少我們保證了它的可擴充套件性。只是很明顯的，當子查詢版本中增加一列年度統計，所帶來的開銷增長會比Case版本多很多。如果你對速度要求較高，還是在客戶端另寫程式生成吧。
InterBase資料庫的會在這個示例中遇到很多不滿意的地方：不支援自動標識列、沒有聚簇、沒有Case、沒有……更可恨的是，這個資料系統的開放原始碼版本沒有附帶ODBC或ADO，在得到一個免費的資料庫系統後，我們卻要為它花幾十美元去買一套ODBC驅動？
不過，InterBase正在得到開放原始碼社群的支援，Borland公司也透過DBExpress和InterClient技術來為InterBase提供開放的介面（目前DBExpress驅動基本上也只存在於Borland昂貴的企業版開發工具中:(）。只要每一個InterBase的程式設計師和使用者都為這個屬於我們自己的做出貢獻，它的前途還很光明。

面向集合的設計方法雖然只適用於特定的目標，並不是通用的軟體設計方法。但也不是三言兩語能說清的，以後的章節中，我們會一直實際這種設計方法，還會有專門的章節討論這個問題。那時，我們的示例資料庫也建設的比較完整了，我也許會給出更實用的年度訂單統計報表。現在，我們先簡單地總結一下：
1、定義我們要生成的結果集的結構；
2、找出結果集的資料來源，定義全集；
3、定義結果集的取值範圍，定義所取的子集；
4、完成操作。