SQL 提供的靜態(tài)轉(zhuǎn)置功能 pivot 和 unpivot 適用范圍很受限，要用 SQL 實現(xiàn)一些比較復(fù)雜的轉(zhuǎn)置功能常常會遇到語句過于復(fù)雜的問題，而且也缺少一個標準的解決思路。而集算器的 SPL 語言，則因其語法的靈活和函數(shù)庫的豐富，恰好可以彌補 SQL 這方面的不足。

成都創(chuàng)新互聯(lián)主要從事成都網(wǎng)站建設(shè)、做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)北安,10余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

一、行轉(zhuǎn)列

1、數(shù)據(jù)庫的 pivot

        pivot 并不是從一開始就存在的功能，只有主流大數(shù)據(jù)庫廠商較新版本產(chǎn)品，例如 Oralce 11g 以上或 SqlServer2005 以上，才支持這個功能。

        從名稱中可以猜到，這個功能是實現(xiàn)行與列的轉(zhuǎn)換，也就是將行中的值作為列名。但是，數(shù)據(jù)庫的行、列，與普通的表格不一樣，不能直接將 X 軸與 Y 軸相互對掉就算大功告成。究其原因，數(shù)據(jù)庫的列是有唯一性的（也就是列名是不能重復(fù)的），而行中存儲的是動態(tài)的數(shù)據(jù)，如果不作為主鍵，就是可以重復(fù)的。所以，pivot 的實際應(yīng)用，基本都要跟隨在分組聚合運算之后，通過分組把用于轉(zhuǎn)置的列（通常都是維度）中每一行數(shù)據(jù)都處理成不重復(fù)的值后，再將各行的值作為列名來展開。

        從具體應(yīng)用的來看，pivot 的作用，其實就是將某一列的聚合結(jié)果，細分為多個更具體的列的聚合結(jié)果，以達到更直觀的視覺效果。

        光說概念是不是比較枯燥，不容易理解？下面我們就以一個具體事例說明，比如 Oracle 數(shù)據(jù)庫中有一個學(xué)生成績表：

        如果想統(tǒng)計每個班的各科最高分，傳統(tǒng)的做法是：

        select 班級, 科目, max(成績) 最高分 from 學(xué)生成績表 group by 班級, 科目

        上面的結(jié)果可以說觀感非常不好：首先，在“班級”一列里，一班、二班重復(fù)出現(xiàn)，很容易就讓人看錯行；其次，在“科目”一列里，語文、數(shù)學(xué)和英語三個科目都放在一起，然而實際上這三個科目的最高分并沒有什么比較的意義。

        事實上，我們應(yīng)該更希望看到以下這樣的結(jié)果：

        這個結(jié)果中，把科目這一列中的三個科目，各自分離出來單獨作為一列，既減少了無用的重復(fù)，又明確了各科目最高分之間的相互獨立性，看上去清晰明了了很多。

        可以說，pivot 就是為了這個目的而誕生的，為了實現(xiàn)上面的結(jié)果，現(xiàn)在的查詢寫法如下：

        select * from (select 班級, 科目, 成績 from 學(xué)生成績表) pivot (max( 成績) for 科目 in ('數(shù)學(xué)' as 數(shù)學(xué)最高分, '英語' as 英語最高分, '語文' as 語文最高分))

2、集算器的 pivot

        有的同學(xué)可能會問，既然數(shù)據(jù)庫中已經(jīng)有了 pivot，那為什么我還需要集算器的 pivot 呢？

        答案是：首先，不是所有的數(shù)據(jù)庫都提供 pivot；其次，就算所有的數(shù)據(jù)庫都提供 pivot，但如果是匯總了多個數(shù)據(jù)庫的數(shù)據(jù)后還想再來個 pivot？那還是要用到集算器的 pivot。

        下面我們來看集算器的 pivot 如何使用

        代碼說明：

        A1：第一步連接數(shù)據(jù)庫

        A2：第二步提取數(shù)據(jù)做預(yù)處理 (這一步可進一步擴展為做匯總或聚合等復(fù)雜的計算，具體方法請參考相關(guān)文章)

        A3：第三步即實現(xiàn) pivot 的列轉(zhuǎn)行功能并呈現(xiàn)出來，其效果與 Oracle 的 pivot 是完全一樣的。

3、pivot 的其他意義

        除了數(shù)據(jù)呈現(xiàn)需求，將行轉(zhuǎn)為列后，還可以使用列間的計算方法。因為列與行的屬性不同，有些列間的計算要在行間實現(xiàn)會比較繁瑣。比如學(xué)校對班級成績的某種考核評比，數(shù)、外、語三科的權(quán)重分別是：0.6、0.3 和 0.1，用兩個班的三科平均分來計算評比指標：

        計算結(jié)果：

        上面的計算，假如要在行間實現(xiàn)，則會麻煩許多，有興趣的同學(xué)可以自己試一下。

二、 列轉(zhuǎn)行

1、數(shù)據(jù)庫的 unpivot

        有行轉(zhuǎn)列，自然就有列轉(zhuǎn)行。還是以 Oracle 為例，它提供的列轉(zhuǎn)行函數(shù)是 unpivot。

        列傳行的功能在業(yè)務(wù)上又有什么意義呢？我們來看這樣一份個人成績表：

        如果想知道的是每個人最擅長哪個科目（也就是每個人的哪一科得分最高），行間計算時用 max 函數(shù)會很方便，而使用列間計算則相對比較繁瑣。這時 unpivot 函數(shù)就派上用場了：

        with T1 as (select * from 個人成績表 unpivot (成績 for 科目 in ( 數(shù)學(xué), 英語, 語文))), T2 as (select 姓名 姓名, max( 成績) 最好成績 from T1 group by 姓名 ) select T1. 姓名 姓名, T1. 科目 擅長科目, T2. 最好成績 該科成績 from T1 join T2 on T1. 姓名 = T2. 姓名 and T1. 成績 =T2. 最好成績

2、集算器的 pivot@r

        那么，如果使用的數(shù)據(jù)庫不是 Oracle 怎么辦？還需要研究新數(shù)據(jù)庫的轉(zhuǎn)置語法細節(jié)么？如果數(shù)據(jù)庫不支持轉(zhuǎn)置語句又怎么辦？需要用 case when 或是子查詢之類的來間接實現(xiàn)類似功能么？

        不必如此煩惱！因為我們有集算器：

        計算結(jié)果，二者是一樣的（在排序上可能略有差異）：

        另外，還需要注意一點：數(shù)據(jù)庫的 unpivot 并不完全是 pivot 的逆運算，因為 pivot 語句中往往包含了聚合函數(shù)，而聚合計算本身是不可逆的，也就是說 unpivot 并不能將 pivot 聚合后的結(jié)果再還原回原先的詳細數(shù)據(jù)。但是集算器的 pivot 因為并不參與聚合計算（聚合計算在 pivot 執(zhí)行之前已經(jīng)單獨執(zhí)行了），所以集算器的 pivot@r 可以說是集算器的 pivot 運算的逆運算。

三、 雙向轉(zhuǎn)置

        有時需要一些更復(fù)雜的轉(zhuǎn)置操作，比如有這樣一個學(xué)生成績表

        而我們想要得到類似下面結(jié)構(gòu)的學(xué)生成績表（含義是查看某個學(xué)生某科目的成績變化趨勢）：

        這里，首先要將數(shù)學(xué)、語文等列合并成科目列，需要列轉(zhuǎn)行的操作；而要將學(xué)期列拆分成學(xué)期一、學(xué)期二等列，需要行轉(zhuǎn)列的操作。

        考慮到數(shù)據(jù)表的結(jié)構(gòu)一般是行數(shù)遠大于列數(shù)，所以我們可以先進行列轉(zhuǎn)行，再進行行轉(zhuǎn)列。由于本表的原始數(shù)據(jù)在行列轉(zhuǎn)換后數(shù)據(jù)與轉(zhuǎn)換前的表中數(shù)據(jù)可以一一對應(yīng)（不需要計算聚合），因此使用集算器的 pivot@r 和 pivot 函數(shù)顯然會更方便。

        運行結(jié)果

四、 動態(tài)列轉(zhuǎn)置

        上面舉的例子都屬于靜態(tài)轉(zhuǎn)置，要求處理的表格和數(shù)據(jù)都是“規(guī)規(guī)矩矩”的。但實際業(yè)務(wù)中卻總有不那么守規(guī)矩的異類存在，而且相信數(shù)量還不少，這時用 SQL 不管是 pivot/unpivot、還是 case when，還是別的啥，都有點力不從心……那該怎么辦？這時集算器的優(yōu)勢就體現(xiàn)出來了：

        比如有下面一個記錄收入情況的個人收入表

        但我們想得到一個類似下面結(jié)構(gòu)的表

        姓名 收入來源 1 收入金額 1 收入來源 2 收入金額 2 ……

        張三 工資 8000 炒股 6000 ……

        我們不確定行轉(zhuǎn)列后，列的數(shù)量，甚至連列名也不能完全確定。這時就不能使用只適用于靜態(tài)轉(zhuǎn)置的 pivot 函數(shù)了，而需要使用動態(tài)轉(zhuǎn)置的方法。而集算器的 SPL 語言在動態(tài)編程方面，要遠比 SQL 語言靈活得多：

        結(jié)果如下：

五、 轉(zhuǎn)置同時列間計算

        假設(shè)我有一張關(guān)于蔬菜的一周價格清單

        而我想由此計算得出關(guān)于各種蔬菜的一周價格走勢，其中走勢又包含四種狀態(tài)：上漲、下降、平穩(wěn)和初始（周一的值）。

        設(shè)計出來的表結(jié)構(gòu)大體如下

        蔬菜 周一 周二 周三 周四 ……

        茄子 空值 上漲 下降 上漲 ……

        黃瓜 空值 上漲 上漲 ……

        ……

        雖然需要使用的轉(zhuǎn)置屬于靜態(tài)類型，但在轉(zhuǎn)置時需要實現(xiàn)列間的計算，這種計算對于 SQL 來說，處理起來非常麻煩。但若使用靈活性更強的集算器的 SPL 語言，則會輕松許多：

        獲得“蔬菜的一周價格走勢”表如下

六、 總結(jié)

        相比于 SQL 提供的 pivot 和 unpivot，集算器 SPL 語言所提供的轉(zhuǎn)置功能要更加靈活，適應(yīng)性也更加廣泛，可以滿足各種復(fù)雜的轉(zhuǎn)置需求。