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

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

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

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

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

    （1）Analytic-Function：分析函數(shù)的名稱，Oracle10gR2帶的內(nèi)置分析函數(shù)有多個(gè)，包括：AVG、CORR、COVAR_POP、COVAR_SAMP、COUNT、LAG、LAST、LEAD、MAX、MIN、RANK、SUM等；對(duì)于用戶自定義的分析函數(shù)，分析函數(shù)名稱需要滿足標(biāo)識(shí)符規(guī)則。

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

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

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

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

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

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

insert   into    temp（num，name，sex，classes，course_name）  values （'206211'，'王藝'，'男'，'06-1班'，'保險(xiǎn)學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'保險(xiǎn)學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'財(cái)務(wù)管理'）；
insert into temp（num，name，sex，classes，course_name） values （'206212'，'肖薇'，'女'，'06-2'，'財(cái)務(wù)會(huì)計(jì)'）；
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)濟(jì)學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206213'，'陳雅詩(shī)'，'女'，'06-2'，'公司理財(cái)'）；
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'，'保險(xiǎn)學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'保險(xiǎn)學(xué)'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'財(cái)務(wù)管理'）；
insert into temp（num，name，sex，classes，course_name） values （'206214'，'李丹陽(yáng)'，'男'，'06-1'，'財(cái)務(wù)會(huì)計(jì)'）；
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)濟(jì)學(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'，'土地估計(jì)'）；
Commit；
    3）先查一下course_name最多的組合

select max（count（course_name））
from   temp
group  by num，name，sex，classes；

    4） 列的位置

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

row_number函數(shù)解釋：返回有序組中一行的偏移量，從而可用于按特定標(biāo)準(zhǔn)排序的行號(hào)。

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）把轉(zhuǎn)換后的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；

　　結(jié)果如圖２所示。