問題概要

??在日常工作中時(shí)常會(huì)遇到將數(shù)據(jù)表的行列進(jìn)行轉(zhuǎn)換的問題。SQL 提供了靜態(tài)轉(zhuǎn)置的功能 pivot 和 unpivot，但適用范圍很受限，要用 SQL 實(shí)現(xiàn)一些比較復(fù)雜的轉(zhuǎn)置功能常常會(huì)遇到語句過于復(fù)雜的問題，而且缺少一個(gè)標(biāo)準(zhǔn)的解決思路。而集算器的 SPL 語言，則因其語法的靈活和函數(shù)庫的豐富，可以完美地彌補(bǔ) SQL 在這方面的不足。

??下面則通過實(shí)例詳細(xì)闡述一下轉(zhuǎn)置功能的實(shí)現(xiàn)。

基礎(chǔ)篇

一、行轉(zhuǎn)列

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

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

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

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

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

??

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

select CLASS,SUBJECT, max(SCORE) THE_HIGHEST_SCORE from STUDENTSCORE group by CLASS,SUBJECT

??

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

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

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

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

select * from (select CLASS, SUBJECT, SCORE from STUDENTSCORE) pivot (max( SCORE) for SUBJECT in ('Maths' as MAX_MATHS, 'English' as MAX_ENGLISH, 'Chinese' as MAX_CHINSES))

2、集算器的 pivot

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

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

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

??代碼說明：
??A1：第一步連接數(shù)據(jù)庫
??A2：第二步提取數(shù)據(jù)做預(yù)處理 (這一步可進(jìn)一步擴(kuò)展為做匯總或聚合等復(fù)雜的計(jì)算，具體方法請(qǐng)參考相關(guān)文章)
??A3：第三步即實(shí)現(xiàn) pivot 的列轉(zhuǎn)行功能并呈現(xiàn)出來，其效果與 Oracle 的 pivot 是完全一樣的。
??

3、pivot 的其他意義

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

??計(jì)算結(jié)果：
??

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

二、 列轉(zhuǎn)行

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

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

??列轉(zhuǎn)行的功能在業(yè)務(wù)上又有什么意義呢？我們來看這樣一份個(gè)人成績表（PersonalScore）：

??

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

with T1 as (select * from PERSONALSCORE unpivot (SCORE for SUBJECT in (MATHS,ENGLISH, CHINESE))),

T2 as (select NAME NAME, max(SCORE) The_Highest_Score

from T1 group by NAME )

select T1.NAME NAME, T1.SUBJECT Good_Subject, T2.The_Highest_Score Good_Score_Score

from T1 join T2

on T1.NAME = T2.NAME and T1.SCORE =T2.The_Highest_Score

2、集算器的 pivot@r

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

??不必如此煩惱！因?yàn)槲覀冇屑闫鳎?/p>

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

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

高級(jí)篇

一、 雙向轉(zhuǎn)置

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

??

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

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

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

??運(yùn)行結(jié)果：
??

二、 動(dòng)態(tài)列轉(zhuǎn)置

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

??比如有下面一個(gè)記錄收入情況的個(gè)人收入表（PersonalIncome）

??

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

??MANE INCOME_SOURCE_1 INCOMR_AMOUNT_1 INCOME_SOURCE_2 INCOMR_AMOUNT_2 ……

??Zhangsan Wages 8000 Stock 6000 ……

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

??結(jié)果如下：
??

三、 轉(zhuǎn)置同時(shí)列間計(jì)算

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

??

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

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

??VEGETABLES Monday Tuesday Wednesday Thursday ……

??Eggplant Initial Rise Decline Rise ……

??Cucumber Initial Rise Rise ……

??……

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

??獲得“蔬菜的一周價(jià)格走勢(shì)”表如下
??

總結(jié)

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