概述

對數據庫中的數據用SQL實現行列轉換，不但需要編寫復雜的程序代碼，還需要編寫存儲過程。若引入ORACLE中的分析函數則會使該過程簡便很多。首先找出表中所有關鍵字的屬性個數的最大值，設為n，其次為每個關鍵字新添加n列，并用分析函數查詢關鍵字的屬性所處列的位置，然后將每個關鍵字的多行屬性轉換成多列屬性，最后把生成的多個新列拼成一個串形成一列，從而實現行列轉換。

引言

分析函數的設計目的是為了解決諸如“累計計算”等問題。雖然大部分的問題都可以用PL/SQL解決，但是性能并不理想，首先查詢本身并不容易編寫，其次有些很難在SQL中直接做的查詢但實際上是很普通的操作，比如實現數據表中行列傳換。這樣的問題在SQL中做查詢就很困難。在分析函數出現以前，我們必須使用自聯查詢或者子查詢甚至復雜的存儲過程實現的語句，現在只要一條簡單的SQL語句就可以實現了，而且在執行效率方面也有相當大的提高。本文將以一個實例來描述如何采用分析函數實現數據中的行列互換。

原理

1、分析函數的格式及語法

分析函數是在一個記錄行分組的基礎上計算它們的總值。行的分組被稱窗口，并通過分析語句定義。對于每記錄行，定義了一個“滑動”窗口。該窗口確定“當前行”計算的范圍。窗口的大小可由各行的實際編號或由時間等邏輯間隔確定。

分析函數以如下形式開頭：

Analytic-Function（<Argument>，<Argument>，...）
OVER （<Query-Partition-Clause><Order-By-Clause><Windowing-Clause>）

（1）Analytic-Function：分析函數的名稱，Oracle10gR2帶的內置分析函數有多個，包括：AVG、CORR、COVAR_POP、COVAR_SAMP、COUNT、LAG、LAST、LEAD、MAX、MIN、RANK、SUM等；對于用戶自定義的分析函數，分析函數名稱需要滿足標識符規則。

（2）Arguments：參數，分析函數通常有0到3個參數，參數可以是任何數字類型或是可以隱式轉換為數字類型的數據類型。對于用戶自定義的參數，可以根據實際情況使用。

（3）OVER：是分析函數就必須使用的關鍵字，對于既可作為聚集函數又可作為分析函數的函數，Oracle無法識別，必須用over來標識此函數為分析函數。

（4）Query-Partition-Clause：查詢分組子句，根據劃分表達式設置的規則，PARTITION BY將一個結果邏輯分成N個分組劃分表達式。分析函數獨立應用于各個分組，并在應用時重置。

（5）Order-By-Clause：（按…排序分組），是排序子句，根據一個或多個排序表達式對分組進行排序。

（6）Windowing-Clause窗口生成語句：窗口生成語句用以定義滑動或固定數據窗口，分析函數在分組內進行分析。該語句能夠對分組中任意定義的滑動或固定窗口進行計算。

2、實例原理介紹

本實例是將具有相同關鍵字的多條記錄中的某一不同列合并成一列，例如在一個臨時表中包含有用戶的編號、電話號碼、產品名稱、所在營業區以及相關業務名稱5個字段，而每個用戶的業務可能有多項，這樣創建數據表將會造成冗余，現在要想辦法將表中編號、電話號碼、產品名稱、所在營業區四個字段相同的用戶的相關業務屬性合并成一列解決冗余問題，使用SQL語句會比較困難，甚至需要一定的存儲過程。使用Orcale中的分析函數來實現這樣的行列轉換就比較簡單方便了。

3、實例

1）創建臨時表

create table temp (
num varchar2(15),
name varchar2(20),
sex varchar2(2),
classes varchar2(30),
course_name varchar2(50) );

2）構造數據

insert into temp(num,name,sex,classes,course_name) values ('206211','王藝','男','06-1班','保險學');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','保險學');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','財務管理');
insert into temp(num,name,sex,classes,course_name) values ('206212','肖薇','女','06-2','財務會計');
insert into temp(num,name,sex,classes,course_name) values ('206213','陳雅詩','女','06-2','電子商務');
insert into temp(num,name,sex,classes,course_name) values ('206213','陳雅詩','女','06-2','公共經濟學');
insert into temp(num,name,sex,classes,course_name) values ('206213','陳雅詩','女','06-2','公司理財');
insert into temp(num,name,sex,classes,course_name) values ('206213','陳雅詩','女','06-2','管理學原理');
insert into temp(num,name,sex,classes,course_name) values ('206213','陳雅詩','女','06-2','保險學');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹陽','男','06-1','保險學');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹陽','男','06-1','財務管理');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹陽','男','06-1','財務會計');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹陽','男','06-1','電子商務');
insert into temp(num,name,sex,classes,course_name) values ('206214','李丹陽','男','06-1','公共經濟學');
insert into temp(num,name,sex,classes,course_name) values ('206215','楊伊琳','女','06-3班','環境管理學');
insert into temp(num,name,sex,classes,course_name) values ('206215','楊伊琳','女','06-3班','管理學原理');
insert into temp(num,name,sex,classes,course_name) values ('206215','楊伊琳','女','06-3班','商務談判');
insert into temp(num,name,sex,classes,course_name) values ('206216','李佳琪','男','06-2','土地估計');
Commit;

3）先查一下course_name最多的組合

select max(count(course_name))
from temp
group by num,name,sex,classes;

4） 列的位置

用分析函數中的row_number函數，在num，name，sex，classes相同的情況下course_name所處的列的位置（第幾列）。

row_number函數解釋：返回有序組中一行的偏移量，從而可用于按特定標準排序的行號。

select num,
 name,
 sex,
 classes,
 course_name,
 row_number() over(partition by num, name, sex, classes order by course_name) rn
 from temp;

亂碼。將就下..

5）把course_name的所有的行換成列

select num,name,sex,classes,
 max(decode(rn,1,course_name,null)) course_name_1,
 max(decode(rn,2,course_name,null)) course_name_2,
 max(decode(rn,3,course_name,null)) course_name_3,
 max(decode(rn,4,course_name,null)) course_name_4,
 max(decode(rn,5,course_name,null)) course_name_5
from (select num,name,sex,classes,course_name,
row_number() over(partition by num,name,sex,classes order by course_name) rn
 from temp)
group by num,name,sex,classes;

亂碼。將就下..

6）把轉換后的name拼成一個字符串，放在一行

select num,
 name,
 sex,
 classes,
 (max(decode(rn, 1, course_name, null)) ||
 max(decode(rn, 2, ',' || course_name, null)) ||
 max(decode(rn, 3, ',' || course_name, null)) ||
 max(decode(rn, 4, ',' || course_name, null)) ||
 max(decode(rn, 5, ',' || course_name, null))) name
 from (select num,
 name,
 sex,
 classes,
 course_name,
 row_number() over(partition by num, name, sex, classes order by course_name) rn
 from temp)
 group by num, name, sex, classes;

亂碼。將就下..

總結

本文中的程序能夠實現以下功能：

①計算具有相同關鍵字的最多的組合；

②根據分析函數查詢某一關鍵字所處的列的位置；

③把需合并列的所有的行換成列；

④把需要合并的某幾列拼成一個串。

分析函數除了擁有以上所介紹的功能，還能夠實現諸如求和、Top-N查詢、統計某個范圍的數據行窗口、交叉表查詢等功能。