談談轉置功能的實現

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

一、行轉列

1、數據庫的 pivot

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

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

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

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

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

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

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

        事實上，我們應該更希望看到以下這樣的結果：

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

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

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

2、集算器的 pivot

        有的同學可能會問，既然數據庫中已經有了 pivot，那為什么我還需要集算器的 pivot 呢？

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

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

        代碼說明：

        A1：第一步連接數據庫

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

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

3、pivot 的其他意義

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

        計算結果：

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

二、 列轉行

1、數據庫的 unpivot

        有行轉列，自然就有列轉行。還是以 Oracle 為例，它提供的列轉行函數是 unpivot。

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

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

        with T1 as (select * from 個人成績表 unpivot (成績 for 科目 in ( 數學, 英語, 語文))), 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

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

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

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

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

三、 雙向轉置

        有時需要一些更復雜的轉置操作，比如有這樣一個學生成績表

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

        這里，首先要將數學、語文等列合并成科目列，需要列轉行的操作；而要將學期列拆分成學期一、學期二等列，需要行轉列的操作。

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

        運行結果

四、 動態列轉置

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

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

        但我們想得到一個類似下面結構的表

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

        張三 工資 8000 炒股 6000 ……

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

        結果如下：

五、 轉置同時列間計算

        假設我有一張關于蔬菜的一周價格清單

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

        設計出來的表結構大體如下

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

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

        黃瓜 空值 上漲 上漲 ……

        ……

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

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

六、 總結

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