談?wù)勣D(zhuǎn)置功能的實(shí)現(xià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 這方面的不足。

一、行轉(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é)生成績(jī)表：

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

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

        上面的結(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 班級(jí), 科目, 成績(jī) from 學(xué)生成績(jī)表) pivot (max( 成績(jī)) for 科目 in ('數(shù)學(xué)' as 數(shù)學(xué)最高分, '英語' as 英語最高分, '語文' as 語文最高分))

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í)成績(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。

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

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

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

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)算。

三、 雙向轉(zhuǎn)置

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

        而我們想要得到類似下面結(jié)構(gòu)的學(xué)生成績(jī)表（含義是查看某個(gè)學(xué)生某科目的成績(jī)變化趨勢(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è)人收入表

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

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

        張三 工資 8000 炒股 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)大體如下

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

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

        黃瓜 空值 上漲 上漲 ……

        ……

        雖然需要使用的轉(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)置需求。