彙總函數的撰寫(一)

by adonis 11. 七月 2012 12:44

作 者:楊先民
審 稿:張智凱

前言

自 SQL Server 2005之後,微軟的 SQL Server就可以撰寫 .NET的程式,例如 Stored procedure、 Function、Trigger、自訂資料型別以及彙總函數,在很早很早期的雜誌中,我有介紹如何自行撰寫自訂資料型別,那時是以民國資料型別為範例,而本期我們將為各位介紹如何撰寫彙總函數。

何謂彙總函數

彙總函數簡單來説就是像 SUM、AVG這種把一個數值資料加以運算以得到某些特定統計用途的目的而稱為彙總函數,SQL Server已經提供了大多數的彙總函數以供我們使用,所以幾乎可以不需要自行撰寫。

Transact-SQL 提供下列彙總函數:

AVG

MIN

CHECKSUM_AGG

SUM

COUNT

STDEV

COUNT_BIG

STDEVP

GROUPING

VAR

MAX

VARP

無外乎就是一些平均、加總、最大、最小值之類的,還有穿插一些標準差,變異數之類的東西,除非你真的有需要,不然基本上通常是不會有自已撰寫的可能性。

當然,這個功能就算你自已不用,微軟也是可以利用 .NET來撰寫彙總函數,所以你也不用擔心這個功能以後會消失,事實上,只要微軟一旦開始支援 .NET,那就是「回不去」啦!

就算你自已不用,微軟也會使用的,舉個最好的例子就是自訂資料型別,你可以自已寫資料型別,而就算你自已不寫,微軟新的資料型別有些也是用 .NET寫出來的,例如hierarchyid與geomerty、geography這些資料型別即是。

而當然有時也真有可能需要自行撰寫彙總函數,所以本期就來看看該如何自行撰寫彙總函數!

使用 Visual Studio開發工具

我們需要使用 Visual Studio當作我們的開發工具,一個現成就可以開發 CLR程式物件的工具程式,寫完程式之後也可以順便佈署,相當方便。

這裡使用的是 Visual Studio 2010,建立一個新的CLR專案(這個部分和之前的 Visual Studio 2008稍有不同)

image

現在 Visual Studio把 CLR的物件獨立放在資料庫中,點選資料庫之後再選擇你要用 VB撰寫,還是用 C#撰寫 CLR的程式物件。

建立新的專案時, Visual Studio會給你一個提示,如果你的彙總函數是要支援 SQL Server 2005的話, .NET Framework要支援2.0的版本,而若是支援 SQL Server 2008的話,則 .NET Framework要支援 3.5的版本(本來有一個警告視窗,不過因為第一次開專案才會出現,我來不及抓圖,再建立一次專案就再也沒有這個提醒視窗了,所以沒有截圖)。

接著,選擇要連接的伺服器名稱以及資料庫,如下圖:

image

 

這個動作主要是之後我們要佈署時可以很簡單的利用佈署選項就可以把寫好的程式安裝在資料庫上,免去一些麻煩的工作。

接著,就可以在專案按右鍵選擇「加入」à「彙總」,如下圖:

image

輸入好你的彙總名稱之後,就會出現如下面的程式框架:

image

這是寫 CLR程式的好處,你只要選擇對應的 CLR類型, Visual Studio就會幫你產生相對應的程式框架,完全不需要自已再刻程式上去。

彙總函數主要有四個 method需要撰寫,分別如下:

1. public void Init();

這個 method主要是設定變數在每個群組的初始值,需要注意彙總函數日後要執行,必需要放在 group by的分組之後,也就是我們要這麼處理計算的資料:

image

以上圖為例,SalesOrderID 43659總共有12筆資料,而未來呈現資料時,將會因為 group by而導致變成只有一筆(group by事實上就是去除重複列),而OrderQty這12筆數字你該如何處理,這就是彙總函數所要做的事,如果你把它們全加起來,那麼就是 SUM函數,如果你把它們加起來,除以個數,就是 AVG,而每個群組開始時,都會執行 Init(),所以通常 Init()裡面寫的是將變數值清空,每個群組值都會執行一次,端看你有多少群組。

所以,我想寫一個 SUM函數,我如此定義:

private SqlInt32 test;

public void Init()

{

// 在此輸入程式碼

test = 0;

}

其中,SqlInt32主要是我的 test變數(其實是一個 class中的 private member)是一個 SQL Server的 integer資料型別(SQL Server 中,int資料型別為 4個 bytes,所以為32位元的整數),並且在 Init()這個 method中設定為0,所以每個群組的 test成員都會被清空為零。

2. public void Accumulate ( input-type value);

這個 method是整個彙總函數的主幹,看你要如何運算分組的資料,如果你想寫的是一個 SUM函數,那可能寫成如下:

public void Accumulate(SqlInt32 Value)

{

// 在此輸入程式碼

test += Value;

}

這裡我已經偷偷的把

public void Accumulate(SqlString value)

置換成

public void Accumulate(SqlInt32 Value)

原因很簡單,因為我輸入進來的資料,是個整數,如果你要處理的是字串,就不用改這段。

而 Accumulate這個 method我只是進行資料的加總,端看你要如何處理這些分組的資料。

3. public void Merge( udagg_class value);

這個 method一直不清楚其真正的用途為何,就 help上的解釋是:合併這個彙總類別的其他實體,查詢處理器使用這個 method合併多個彙總的部分運算。常看範例是寫:

Accumulate(value.Terminate());

就照寫吧,其實不寫也沒什麼關係,似乎不怎麼重要。

4. public return_type Terminate();

這個 method是回傳結果,這裡多半不參與實際的運算,主要注意的是資料型別不要搞錯,必要時要做一些資料型別的轉換。

以我的例子而言,程式如下:

public SqlInt32 Terminate()

{

// 在此輸入程式碼

return test;

}

這裡我已經偷偷的把原本的

public SqlString Terminate ()

改成了

public SqlInt32 Terminate()

因為我是輸入數字,經過運算後回傳數字,所以這邊我做了修改,如果你要處理的是文字,就不用修改。

大致上來説,一個簡單的彙總函數就寫好了。

待續

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List