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

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

    分析函數以如下形式開(kāi)頭：

Analytic-Function（<Argument>，<Argument>，...）

OVER （<Query-Partition-Clause><Order-By-Clause><Windowing-Clause>）

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

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

    （3）OVER：是分析函數就必須使用的關(guān)鍵字，對于既可作為聚集函數又可作為分析函數的函數，Oracle無(wú)法識別，必須用over來(lái)標識此函數為分析函數。

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

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

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

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

    1）創(chuàng )建臨時(shí)表

Drop Table temp；
Create Table temp
（
num varchar2（15 Char），
name varchar2（20 Char），
sex varchar2（2 Char），
classes varchar2（30 Char），
course_name varchar2（50 Char）
）；

    2）構造數據

insert   into    temp（num，name，sex，classes，course_name）  values （'206211'，'王藝'，'男'，'06-1班'，'保險學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'保險學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'財務(wù)管理'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'財務(wù)會(huì )計'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'電子商務(wù)'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'公共經(jīng)濟學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'公司理財'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'管理學(xué)原理'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'保險學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'保險學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'財務(wù)管理'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'財務(wù)會(huì )計'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'電子商務(wù)'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'公共經(jīng)濟學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206215'，'楊伊琳'，'女'，'06-3班'，'環(huán)境管理學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206215'，'楊伊琳'，'女'，'06-3班'，'管理學(xué)原理'）；
insert into temp（num，name，sex，classes，course_name） values （'206215'，'楊伊琳'，'女'，'06-3班'，'商務(wù)談判'）；
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函數解釋?zhuān)悍祷赜行蚪M中一行的偏移量，從而可用于按特定標準排序的行號。

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；

    列的位置參見(jiàn)圖１。

圖１

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

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；

　　結果如圖２所示。