彙總函數的撰寫之三

by adonis 23. 十月 2012 14:16

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

前言


原本在寫彙總函數之前,我一直以為撰寫的機率不高,然而在最 近實務的經驗中,我改變了自已的想法。


原來在實務中,還是有非常多可能性會自行撰寫彙總函數的啊!
尤其是內建彙總函數沒有這個功能但你又有需求必需用 SQL的函數完成時。
本期就來設計一個變型的 SUM彙總函數吧!

變型的 SUM彙總函數

本期要帶各位來實作變型的 sum彙總函數,但在看這個變型的彙總函數之前,先看一個我最近實作的一個範例!


我在 iphone手機上開發了一個油耗記錄的小程式(可以在 apple store搜尋油耗,評價最多的就是我寫的),如果只有油耗程式,其實並不怎麼特殊,重點在最後的油耗分享功能,你覺得自已的汽車油耗好嗎?想和別人比較嗎?你可以上傳你的油耗資訊和別人比較比較。

基於這樣的想法,所以我製作了油耗上傳的機制,並且開發了一個網頁,可以讓 iphone的使用者能透過該網頁比較自已與同型車主的油耗資訊,以及查詢油耗的排名資訊。
這個程式主要結合了 iphone的開發、web service、asp .net以及 Jquery mobile還有 SQL Server的開發,簡直就是一個大雜燴啊!不過我們要看的重點主要還是偏向於 SQL Server這部分。
首先先看分享油耗的網頁如下:

image

第一頁主要是列出統計資訊,自已開的車和其他車友之間的油耗比較。

image 第二頁主要是列出排行榜,油耗資訊愈多人分享,數據是愈為的準確。

image

第三頁主要是看分享車主的明細油耗資訊,可以做個比對。

當然,我個人很常看這些資訊(因為數據希望能力求正確),有時後就會發現有這樣奇怪的數據出現:

image

像上圖,竟然會有 0.33這種油耗資訊,若換是你,你會覺得這是個正常數據嗎?我肯定不會認為。
但為何會有這種數據,原因很多,主要是因為大家對於油耗的計算方法各式各樣(其實明明就很單純),所以這種 0.33的油耗如果真的加進來的話,一定會造成整體油耗的資訊不精確。

原本想要使用去頭去尾的平均算法,但後來想想,不太對!因為有些人很勤於記錄油耗資訊,可能累積5000公里的行駛距離,如果要去頭的話,勢必會讓整體平均數據更為不正確,所以原本的計劃後來就改變了。

雖然這數據我不理它,久而久之,長期下來還是個趨於正確的數據,不過基於改好程式的要求,我還是決定自已撰寫彙總函數。
也就是說,並不是所有油耗資訊我們都要照單全收,如果事先算出來該車主上傳的油耗是平均一公升低於2公里的離譜數據,彙總函數可以選擇不納入計算。
這個肯定用一般的 SUM彙總函數是無法做到的啊!畢竟你使用 SUM(值),要就是全算,並不會算一半,也不可能有「條件式」的計算,所以這樣艱難的工作,就交給今天的主角彙總函數了!

由於我的 T-SQL程式相當的複雜,所以實在沒有必要拿出來傷腦筋,但截取其中的一段來看看:

convert(decimall(10,2),sum(totalKM)/sum(totali))

這一段就是會把一公升的平均油耗算出來,而且請注意是使用sum的彙總函數,但這個函數將是所有資料都會加起來。


原本我的想法是,利用彙總函數,輸入兩個參數(行走公里與公升),如果行走公里除以公升小於2(每公升油耗低於2公里),則該筆資料不加總。
但後來我才赫然發現,原來彙總函數支援多參數的使用是在 SQL 2012才支援,看 SQL 2008 help上的說明:

public void Accumulate ( input-type value);

而 SQL 2012的說明:

public void Accumulate ( input-type value[, input-type value, ...]);

所以我就無語了…

怎麼辦呢?後來我想到一個辦法,就是把數值資料用字串傳入,但是中間用分號或逗號隔開,例如輸入的參數是公里與公升資料,傳入的值可能是 「200;15」這樣的單一字串值,然後再利用 C#函數將字串拆解開來存入陣列中處理!這可能也是唯一可行的辦法了!

馬上設計如下的程式(其實想了半天)

public struct subSumLi
{

    private SqlDouble total;
    private SqlDouble value1;
    private SqlDouble value2;

    public void Init()
    {
        // 在此輸入程式碼

        total = 0;

        value1 = 0;
        value2 = 0;

    }

其中 total是最後要回傳回去的值, value1則是預計要取得公里資訊,value2則是要取得公升資訊,先在 Init初始為零。

    public void Accumulate(SqlString Value)
    {

        int i = 0;

        string[] = new string[] { ";" };

        String strTemp = Value.ToString();

        string[] strSplitArr = strTemp.Split(separator, StringSplitOptions.RemoveEmptyEntries);

        foreach (string attStr in strSplitArr)
        {

            if (i == 0)
            {
                value1 = Convert.ToDouble(attStr);
                i = 1;
            }
            else
            {
                value2 = Convert.ToDouble(attStr);
                i = 0;
            }

        }

        if (value1 / value2 < 2)
        {

            //skip

        }
        else
        {

            total += value2;

        }

    }

程式碼解說:先利用separator陣列記錄字串的分隔符號,以本例是分號「;」。

String strTemp = Value.ToString();

將 SqlString資料型別轉型成 String,以便使用裡面的 method Split(方便拆解字串)。

string[] strSplitArr = strTemp.Split(separator, StringSplitOptions.RemoveEmptyEntries);

將帶有;的字串,利用 String的函數 Split,拆解完後放入 strSplitArr陣列中,以本例而言,將會有兩個陣列值,一個是公里(索引為0),一個是公升(索引為1)。

            if (i == 0)
            {
                value1 = Convert.ToDouble(attStr);
                i = 1;
            }
            else
            {
                value2 = Convert.ToDouble(attStr);
                i = 0;
            }

foreach內的程式,主要是把第一個值放 value1這個預期要放公里的變數,而 value2則是要放公升的變數。

        if (value1 / value2 < 2)

重點在這裡!如果兩個相除是小於2公里,則不列入計算,不然則是

            total += value2;
將值加起來。

在結束端程式是這麼寫的:

    public SqlDouble Terminate()
    {
        // 在此輸入程式碼
        if (total == 0)
        {
            total = 1;
        }
        return total;

    }

為何要把 total 在0的時後設定為 1呢?因為怕分母如果為零的話,會出現程式的錯誤,所以把可能會變成0的數值變成1,實際上並不怎麼影響最後的結果(畢竟1很小很小)。

佈署上去之後,運算式會寫的相當的複雜,如下:

convert(decimal(10,2),dbo.subSumKM(convert(varchar,walkkm)+';'+convert(varchar,Litre))/dbo.subSumLi(convert(varchar,walkkm)+';'+convert(varchar,Litre))) as statistic

乖乖這個欄位的運算式好複雜啊!都得怪 SQL 2008沒有支援多參數的關係,所以才把程式搞的那麼複雜,不過問題是解決了,我們用很簡單的範例資料來證明此事:

create table test(a int , b decimal(5,2),c decimal(5,2))

insert test values (1,100,100)
insert test values (1,200,5)
insert test values (1,300,300)
insert test values (1,320.42,20.43)

先做一個假資料,內容如下:

image

你可以用肉眼先算看看,第一列是 100/100得到1,照理是不能列入計算,第二列是40,列入,第三列是 1,也不能列入,第四列超過2,可以列入計算,所以使用下列語法:

select a,dbo.subSumKM(convert(varchar,b)+';'+CONVERT(varchar,c)) AS TOTALkm,dbo.subSumLi(convert(varchar,b)+';'+CONVERT(varchar,c)) as totalli from test
group by a

會得到:

image

也就是只有第二列與第四列的加總,而第一列與第三列沒有加進來,這就是我們要的「有調件的 SUM函數」。

所以說,因為這次的經驗,我發現可以自已寫彙總函數似乎是個不錯的選擇,而且也確實有可能會使用的到,給各位一個參考!

image

Tags:

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List