Oracle之分析資料

本文主要包括：

   · 使用分析函式執行復雜的計算

   · 使用 MODEL 子句執行行間計算

   · 使用 PIVOT 和 UNPIVOT 子句，它們對於瞭解大量資料的總體趨勢非常有用

   · 執行 top-N 查詢，從結果集返回頂部或底部的 N 行

一、 使用分析函式

分析函式能夠執行復雜的計算，例如查詢每月銷售額最高的商品型別、業績最佳的銷售員，等等，

分析函式可以分為以下幾類：

   · 評級函式   可計算等級、百分位數、n分片（三分片、四分片等）

   · 反百分位函式   可計算對應於百分位數的數值

   · 視窗函式   可計算累積和移動總計

   · 報表函式   可計算諸如市場佔有率之類的結果

   · 延遲與領先函式   可獲得與當前行的距離為若干行的那一行中的值

   · 首函式與末函式   可獲得某個與排序分組中的第一個值與最後一個值

   · 線性迴歸函式   可用一條普通最小平方迴歸曲線來擬合一組數值對

   · 假想評級與分佈函式   可以計算在將一個新行插入到表中之後，它在表中的等級和百分位數

1. 使用評級函式

評級函式（ranking function）用於計算等級、百分位數、n分片等，評級函式如下表所示

1.1 使用 RANK() 和 DENSE_RANK() 函式

範例：使用 RANK() 和 DENSE_RANK() 獲得2003年不同產品型別的銷售評級

SELECT

   prd_type_id,SUM(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC)AS rank,

   DENSE_RANK()OVER(ORDER BY SUM(amount) DESC)AS dense_rank

FROM all_sales 

WHERE year=2003

--AND amount IS NOT NUL     --  在評級函式中，空值被指定為最高的排名1

GROUP BY prd_type_id

ORDER BY prd_type_id;

使用 NULLS FIRST 和 NULLS LAST 子句控制空值的排名

範例：使用 NULLS  LAST 將空值指定為最低

SELECT

   prd_type_id,SUM(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC NULLS LAST)AS rank,

   DENSE_RANK()OVER(ORDER BY SUM(amount) DESC NULLS LAST)AS dense_rank

FROM all_sales 

WHERE year=2003

GROUP BY prd_type_id

ORDER BY prd_type_id;

分析函式與 PARTITION BY 子句結合使用

在需要將分組劃分為子分組時，可以將 PARTITION BY 子句和分析函式結合起來使用

範例：根據月份劃分銷售額

SELECT

   prd_type_id,month,SUM(amount),

   RANK()OVER(PARTITION BY month ORDER BY SUM(amount) DESC)AS rank

FROM all_sales 

WHERE year=2003

AND amount IS NOT NULL

GROUP BY prd_type_id,month

ORDER BY prd_type_id,month;

分析函式與 ROLLUP、CUBE、GROUPING SETS 操作符結合使用

範例：使用 ROLLUP 和 RANK() 獲得不同產品型別 ID 的銷售排名

SELECT

   prd_type_id,sum(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC) AS rank

FROM all_sales 

WHERE year=2003

GROUP BY ROLLUP(prd_type_id)     --  標準分組、合計 

ORDER BY prd_type_id;

範例：使用 CUBE 和 RANK() 根據產品型別 ID 和員工 ID 獲得銷售排名

SELECT

   prd_type_id,emp_id,SUM(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC)AS rank

FROM all_sales 

WHERE year=2003

GROUP BY CUBE(prd_type_id,emp_id)    

ORDER BY prd_type_id,emp_id;

範例：使用 GROUPING SETS 和 RANK() 獲得銷售額小計的排名

SELECT

   prd_type_id,emp_id,SUM(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC)AS rank

FROM all_sales 

WHERE year=2003

GROUP BY GROUPING SETS(prd_type_id,emp_id)     --  兩個分組小計

ORDER BY prd_type_id,emp_id;

1.2 使用 CUME_DIST() 和 PERCENT_RANK() 函式

CUME_DIST()  函式計算一行在組中的相對位置，CUME_DIST() 總是返回大於 0、小於或等於

1 的數，該數表示該行在 N 行中的位置（N為組中所有的行數）；例如，在一個 3 行的組中，

返回的累計分佈值分別為 1/3、2/3、3/3；

PERCENT_RANK() 函式對於一個組中給定的行來說，在計算那行的序號時，先減 1，然後除以

N-1（N為組中所有的行數）；該函式總是返回 0~1（包括1）之間的數

範例：計算銷售額的累計分佈和百分比排名

SELECT 

   prd_type_id,SUM(amount),

   CUME_DIST()OVER(ORDER BY SUM(amount) DESC) CUME_DIST,

   PERCENT_RANK()OVER(ORDER BY SUM(amount) DESC) PERCENT_RANK

FROM all_sales

WHERE year=2003

GROUP BY prd_type_id

ORDER BY prd_type_id;

如：prd_type_id=5 的那行為第一行，則 CUME_DIST()=1/5、PERCENT_RANK()=(1-1)/(5-1)

1.3 使用 NTILE() 函式

NTILE(buckets) 函式可以計算 n 分片的值，如三分片、四分片等，Bucket引數指定了分片的片數，

行數將被分組為 buckets 個片，例如：

   · NTILE(3) 表示指定片數為2，行將分組為兩片

   · NTILE(5) 表示指定片數為5，行將分組為五片

範例：使用 NTILE(4) 將行分組為四片

SELECT 

   prd_type_id,SUM(amount),

   NTILE(4)OVER(ORDER BY SUM(amount) DESC) AS NTILE

FROM all_sales

WHERE year=2003

GROUP BY prd_type_id

ORDER BY prd_type_id;

1.4 使用 ROW_NUMBER() 函式

ROE_NUMBER() 函式從 1 開始，為分組中的每行返回一個數字（行號）

SELECT

   prd_type_id,SUM(amount),

   ROW_NUMBER()OVER(ORDER BY SUM(amount) DESC) ROW_NUMBER

FROM all_sales

WHERE year=2003

GROUP BY prd_type_id

ORDER BY prd_type_id;

2. 使用反百分位函式

反百分位函式的作用與 CUME_DIST() 和 PERCENT_RANK() 相反

   · PERCENTILE_DISC(x) 在每一個分組中檢查累計分佈的數值，直到找到大於或等於 x 的值

   · PERCENTILE_CONT(x) 在每一個分組中檢查百分比排名的數值，直到找到大於或等於 x 的值

範例：使用 PERCENTILE_DISC() 和 PERCENTILE() 獲取百分位數大於或等於 0.6 的銷售總額

SELECT 

   PERCENTILE_CONT(0.6)WITHIN GROUP(ORDER BY SUM(amount) DESC)

   AS PERCENTIL_CONT,

   PERCENTILE_DISC(0.6)WITHIN GROUP(ORDER BY SUM(amount) DESC)

   AS PERCENTILE_DISC

FROM all_sales

WHERE year=2003

GROUP BY prd_type_id;

結果與“使用 CUME_DIST() 和 PERCENT_RANK() 函式”部分的結果相比較，分別與那裡的

累計分佈和百分比排名各為 0.6 和 0.75 的行對應

3. 使用視窗函式（詳見分析函式視窗子句 RANGE/ROWS 差別）

視窗函式可以計算一定範圍內、一定值域內或一段時間內的累積和以及移動平均值

查詢返回一組行，它們稱為結果集；“視窗”這個術語用來描述結果集中行的子集，然後

由視窗函式來處理通過視窗”看“到的行子集，並返回一個值。

可以定義視窗的起點和終點

3.1 計算累積和

範例：通過執行累積和操作計算出從2003年1月到12月的累積銷售額

SELECT

   month,SUM(amount) AS MONTH_AMOUNT,

   SUM(SUM(amount))OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING 

   AND CURRENT ROW) AS CUMULATIVE_AMOUNT     --  當前分組中的第一行到本行

FROM all_sales

WHERE year=2003

GROUP BY month;

範例：計算2003年6月到12月的累積銷售額

SELECT

   month,SUM(amount) AS MONTH_AMOUNT,

   SUM(SUM(amount))OVER(ORDER BY month) AS CUMULATIVE_AMOUNT

FROM all_sales

WHERE year=2003

AND month BETWEEN 6 AND 12

GROUP BY month;

3.2 計算移動平均值

範例：計算本月與前3個月之間（共4個月）銷售額的移動平均值

SELECT 

   month,SUM(amount) AS MONTH_AMOUNT,

   AVG(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

   AS MOVING_AVERAGE

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

3.3 計算中心平均值

範例：計算當前月份前後各一個月內（共三個月）銷售額的中心移動平均值

SELECT

   month,SUM(amount) AS MONTH_AMOUNT,

   AVG(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

   AS MOVING_AVERAGE

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

3.4 用 FIRST_VALUE() 和 LAST_VALUE() 獲取第一行和最後一行

FIRST_VALUE() 和 LAST_VALUE() 函式可以獲取視窗中的第一行和最後一行資料

範例：用FIRST_VALUE() 和 LAST_VALUE() 獲得前一個月和後一個月的銷售額

SELECT

   month,SUM(amount) AS month_amount,

   FIRST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING 

   AND 1 FOLLOWING) AS PREVIOUS_MONTH_AMOUNT,

   LAST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING

   AND 1 FOLLOWING) AS NEXT_MONTH_AMOUNT

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

範例：將當前月份的銷售額除以前一個月的銷售額（儲存在curr_div_prev列中）
          將當前月份的銷售額除以下一個月的銷售額（儲存在curr_div_next列中）

SELECT

   month,SUM(amount) AS month_amount,

   SUM(amount)/FIRST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING 

   AND 1 FOLLOWING) AS CURR_DIV_PREV,

   SUM(amount)/LAST_VALUE(SUM(amount))OVER(ORDER BY month ROWS BETWEEN 1 PRECEDING

   AND 1 FOLLOWING) AS CURR_DIV_NEXT

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

3.5 用 NTH_VALUE() 函式獲取第 n 行

NTH_VALUE() 函式返回視窗中的第 n 行

範例：使用 NTH_VALUE() 函式獲得第2個月的銷售額

SELECT

   month,SUM(amount) AS MONTH_AMOUNT,

   NTH_VALUE(SUM(amount),2)OVER(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING

   AND UNBOUNDED FOLLOWING)AS NTH_VALUE

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

範例：使用 NTH_VALUE() 函式獲得第24名員工銷售產品型別#1、#2、#3的最大銷售額

注：第24名員工的最大銷售額包含在視窗的第4個位置

SELECT

   prd_type_id,emp_id,MAX(amount),

   NTH_VALUE(MAX(amount),4)OVER(PARTITION BY prd_type_id ORDER BY emp_id

   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) NTH_VALUE

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 3

GROUP BY prd_type_id,emp_id

ORDER BY prd_type_id,emp_id;

4. 使用報表函式

報表函式可用於執行跨越分組和組內分割槽的計算

4.1 總計報表

範例：查詢報告2003年前三個月的：

          · 每月銷售額的總和（total_month_amount 列）

          · 所有產品型別銷售額的總和（total_product_type_amount 列）

SELECT 

   month,prd_type_id,SUM(amount),

   SUM(SUM(amount))OVER(PARTITION BY month) AS total_month_amount,

   SUM(SUM(amount))OVER(PARTITION BY prd_type_id)AS total_product_type_id

FROM all_sales

WHERE year=2003

AND month <=3 

GROUP BY month,prd_type_id

ORDER BY month,prd_type_id;

4.2 使用 RATIO_TO_REPORT() 函式

RATIO_TO_REPORT() 函式可以用來計算某個值在一組值得總和中所佔的比率

範例：查詢報告2003年前三個月的：

          · 每種產品型別每個月的銷售額總計（prd_type_amount 列） 

          · 該產品型別的銷售額佔整月銷售額的比率（prd_type_ratio 列）

SELECT  

   month,prd_type_id,

   SUM(amount) AS prd_type_amount,

   RATIO_TO_REPORT(SUM(amount))OVER(PARTITION BY month) AS prd_type_ratio

FROM all_sales

WHERE year=2003

AND month <= 3

GROUP BY month,prd_type_id

ORDER BY month,prd_type_id;

4.3 使用 LISTAGG() 函式

LISTAGG() 函式對分組內的各行排序並且將分組的值集連線起來

範例：從 products 表檢索產品 #1 到 #5，按照價格和產品名排序，並返回最貴的產品

SELECT

   LISTAGG(name,',')WITHIN GROUP (ORDER BY price,name) AS "Product List",

   MAX(price) AS "Most Expensive"

FROM products

WHERE product_id <= 5;

範例：從 products 表檢索產品 #1 到 #5，對於每種產品，使用 LISTAGG() 函式顯示具有

相同 product_type_id 值的產品

SELECT

   product_id,product_type_id,name,

   LISTAGG(name,',')WITHIN GROUP (ORDER BY name)OVER(PARTITION BY product_type_id)

   AS "Product List"

FROM products

WHERE product_id <= 5

ORDER BY product_id,product_type_id;

5. 使用 LAG() 和 LEAD() 函式

LAG() 和 LEAD() 函式可獲得位於距當前行指定距離的那一行中的資料

範例：用 LAG() 和 LEAD() 函式獲得前一個月和後一個月的銷售額

SELECT

   month,SUM(amount) AS month_amount,

   LAG(SUM(amount),1)OVER(ORDER BY month) AS previous_month_amount,

   LEAD(SUM(amount),1)OVER(ORDER BY month) AS next_month_amount

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;   

LAG(SUM(amount),1) 可獲取前一行的銷售額總計

6. 使用 FIRST 和 LAST 函式

FIRST 函式和 LAST 函式可獲得排序分組中的第一個值和最後一個值 

範例：用 FIRST 和 LAST 函式獲得2003年中銷售額最高和最低的月份

SELECT 

   SUM(month)KEEP(DENSE_RANK FIRST ORDER BY SUM(amount))

   AS highest_sales_month,

   SUM(month)KEEP(DENSE_RANK LAST ORDER BY SUM(amounT))

   AS lowest_sales_month

FROM all_sales

WHERE year=2003

GROUP BY month

ORDER BY month;

7. 使用假想評級與分佈函式

假想評級與分佈函式可以計算在將一個新行插入表中之後，它在表中的排名和百分比

下面這些函式可以與假想計算結合使用：

   · RANK()

   · DENSE_RANK()

   · PERCENT_RANK()

   · CUME_DIST()

範例：使用 RANK() 和 PERCENT_RANK() 函式獲得2003年每一種產品型別銷售額的排名和百分比排名

SELECT

   prd_type_id,SUM(amount),

   RANK()OVER(ORDER BY SUM(amount) DESC) AS rank,

   PERCENT_RANK()OVER(ORDER BY SUM(amount) DESC) AS percent_rank

FROM all_sales

WHERE year=2003

AND amount IS NOT NULL

GROUP BY prd_type_id

ORDER BY prd_type_id;

範例：計算銷售額 $500000 的假想排名和百分比排名

SELECT 

   RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS rank,

   PERCENT_RANK(500000)WITHIN GROUP(ORDER BY SUM(amount) DESC) AS percent_rank

FROM all_sales

WHERE year=2003

AND amount IS NOT NULL

GROUP BY prd_type_id

ORDER BY prd_type_id;

二、 使用 MODEL 子句

Oracle Database 10g 中引入的 MODEL 子句可以用來進行行間計算

MODEL 子句允許訪問陣列中的元素那樣訪問行中的某個列

1. MODEL 子句示例

範例：查詢獲取2003年內每個月由#21完成的產品型別為#21完成的產品型別為#1、#2的銷售額，

並根據2003年的銷售資料預測出2004年1月、2月和3月的銷售額

SELECT

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY (prd_type_id)

DIMENSION BY (month,year)

MEASURES(amount sales_amount)

(

   sales_amount[1,2004]=sales_amount[1,2003],

   sales_amount[2,2004]=sales_amount[2,2003]+sales_amount[3,2003],

   sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)

)

ORDER  BY prd_type_id,year,month;

注：

   · PARTITION BY(prd_type_id) 指定結果是根據 prd_type_id 分割槽的

   · DIMENSION BY(month,year) 定義陣列的維度是 month 和 year，這意味著

     必須提供月份和年份才能訪問陣列中的單元

   · MEASURE(amount sales_amount) 表明陣列中的每個單元包含銷售額，同時

     表明陣列名為 sales_amount

2. 用 BETWEEN 和 AND 返回特定範圍內的資料單元

BETWEEN 和 AND 關鍵字可用於訪問某個範圍內的資料單元

範例：將2004年1月的銷售額設定為2003年1月至3月銷售額的平均值再四捨五入為保留兩位小數

SELECT 

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[1,2004]=ROUND(AVG(sales_amount)[month BETWEEN 1 AND 3,2003],2)

)

ORDER BY prd_type_id,year,month;

3. 用 ANY 和 IS ANY 訪問所有的資料單元

可以用 ANY 和 IS ANY 謂詞訪問陣列中所有的資料單元，ANY 和位置標記合用，

IS ANY 和符號合用

範例：將2004年1月的銷售額設定為所有年份月份的銷售額之和再四捨五入為保留兩位小數

SELECT

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id) 

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,ANY],2)

)

ORDER BY prd_type_id,year,month;

4. 用 CURRENTV() 函式獲取某個維度的當前值

CURRENTV() 函式用於獲得某個維度的當前值

範例：將2004年第一個月的銷售額設定為2003同月銷售額的1.25倍

SELECT 

   prd_type_id,year,month,sales_amount

   FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id) 

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[1,2004]=ROUND(sales_amount[currentv(),2003]*1.25,2)

)

ORDER BY prd_type_id,year,month;

5. 用 FOR 迴圈訪問資料單元

可通過 FOR 迴圈訪問資料單元

範例：將2004年前3個月的銷售額設定為2003年相應月份銷售額的1.25倍

注：其中使用了 FOR 迴圈，還通過 INCREMENT 關鍵字定義每一次迴圈迭代中 month 的增量

SELECT  

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=

      ROUND(sales_amount[CURRENTV(),2003]*1.25,2)  -- CURRNETV() 獲取某個維度的當前值

)

ORDER BY prd_type_id,year,month;

6. 處理空值和缺失值

6.1 使用 IS PRESENT

如果資料單元指定的行在 MODEL 子句執行之前存在，那麼 IS PRESENT 返回 TRUE

例如，sales_amount[CURRNTV(),2003] IS PRESENT

範例：將2004年前3個月的銷售額設定為2003年同期銷售額的1.25倍

SELECT

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=

   CASE WHEN sales_amount[CURRENTV(),2003] IS PRESENT THEN 

      ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

   ELSE

      0

   END

)

ORDER BY prd_type_id,year,month;

6.2 使用 PRESENTV() 函式

如果 cell 引用的行在 MODEL 子句執行之前就存在，那麼 PRESENTV(cell,expr1,expr2) 

返回表示式 expr1，如果這行不存在，返回表示式 expr2

例如，

PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENT(),2003]*1.25,2),0)

範例：將2004年前3個月的銷售額設定為2003年同期銷售額的1.25倍

SELECT

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=

   PRESENTV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)

)

ORDER BY prd_type_id,year,month;

6.3 使用 PRESENTNNV() 函式

如果 cell 引用的單元在 MODEL 子句執行之前已經存在，並且該單元的值不為空，那麼 

PRESENTNNV(cell,expr1,expr2) 返回表示式 expr1，如果行不存在或單元值為空值，那麼

返回表示式 expr2

例如，

PRESENTNNV(sales_amount[CURRENTV(),2003],ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)

如果 sales_amount[CURRENT(),2003] 存在且為非空值，那麼上面的表示式將四捨五入為

保留兩位小數後的總小數額；否則返回0

6.4 使用 IGNORE NAV 和 KEEP NAV

預設情況下，MODEL 子句將缺失值的單元視為該單元擁有值 null，具有 null 值的單元也同樣

視為空值，可以使用 IGNORE NAV 改變這種預設行為，它返回如下值之一：

   · 單元為數值空值或缺失值時返回 0

   · 單元為字串空值或缺失值時返回空字串

   · 單元為日期空值或缺失值時返回 01-JAN-2000

   · 單元為其他所有資料庫型別的空值或缺失值時返回空值

也可以使用 KEEP NAV，這是預設行為，KEEP NAV 對空值或缺失數字值返回空值

範例：IGNORE NAV 的用法

SELECT

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL IGNORE NAV

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

(

   sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=

      ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

ORDER BY prd_type_id,year,month;

7. 更新已有單元

預設情況下，如果表示式左端的引用單元存在，就更新單元，如果該單元不存在，就在組中建立新的行

可以用 RULES UPDATE 改變這種預設行為，指出在單元不存在的情況下，不建立新的行

範例： 使用 RULES UPDATE

SELECT  

   prd_type_id,year,month,sales_amount

FROM all_sales

WHERE prd_type_id BETWEEN 1 AND 2

AND emp_id=21

MODEL

PARTITION BY(prd_type_id)

DIMENSION BY(month,year)

MEASURES(amount sales_amount)

RULES UPDATE 

(

   sales_amount[FOR month FROM 1 TO 3 INCREMENT 1,2004]=

      ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

ORDER BY prd_type_id,year,month;

注：由於2004年的單元不存在，同時又指定了 RULES UPDATE，因此不會再陣列中為2004年建立新行

三、 使用 PIVOT 和 UNPIVOT 子句

PIVOT 子句和 UNPIVOT 子句是在 Oracle 11g 中引入的

PIVOT 可以在查詢輸出中將行旋轉為列，同時針對資料作為聚合函式使用；

UNPIVOT 是 PIVOT 的相反操作，它可以在查詢輸出中將列旋轉為行

1. PIVOT 子句的簡單示例

範例：查詢2003年前4個月的產品型別#1、#2 和 #3 的銷售額，注意查詢輸出的資料展示了

每種產品型別在每個月的銷售額的總和

SELECT *

FROM 

(

   SELECT month,prd_type_id,amount

   FROM all_sales

   WHERE year=2003

   AND prd_type_id IN (1,2,3)

)

PIVOT 

( 

   SUM(amount) FOR month IN (1 AS JAN,2 AS FEB,3 AS MAR,4 AS APR)

)

ORDER BY prd_type_id;

注：上面的這條 SELECT 語句具有以下結構

SELECT *

FROM

(

   inner_query

)

PIVOT

(

   aggregate_function FOR pivot_column IN (list_of_values)

)

ORDER BY ...;

2. 轉換多個列

範例：轉換 month 和 prd_type_id 兩列，它們在 FOR 部分被引用，注意 PIVOT 的 IN 部分

中的值列表包含 month 和prd_type_id 這兩列的一個值：

SELECT *

FROM 

(

   SELECT month,prd_type_id,amount

   FROM all_sales

   WHERE year=2003

   AND prd_type_id IN (1,2,3)

)

PIVOT
(

   SUM(amount) FOR (month,prd_type_id) IN

   (

      (1,2) AS JAN_PRDTYPE2,

      (2,3) AS FEB_PRDTYPE3,

      (3,1) AS MAR_PRDTYPE1,

      (4,2) AS APR_PRDTYPE2

   )

);

可以將任何值放在 IN 部分中，以便得到自己感興趣的值

範例：將放到 IN 部分的產品型別值更改了，以得到這些產品型別在指定月份的銷售額

SELECT * 

FROM 

(

   SELECT month,prd_type_id,amount

   FROM all_sales

   WHERE year=2003

   AND prd_type_id IN (1,2,3)

)

PIVOT

(

   SUM(amount) FOR (month,prd_type_id) IN

   (

      (1,1) AS JAN_PRDTYPE1,

      (2,2) AS FEB_PRDTYPE2, 

      (3,3) AS MAR_PRDTYPE3,

      (4,1) AS APR_PRDTYPE1  

   )

);

結果：

   · 產品型別 #1 在 1 月份的銷售額是 $38909.04 

     ...

3. 在轉換中使用多個聚合函式

範例：使用 SUM() 函式得到產品型別在 1 月份和 2 月份的銷售額，用 AVG() 函式得到銷售額的平均值

SELECT *

FROM 

(

   SELECT month,prd_type_id,amount

   FROM all_sales

   WHERE year=2003

   AND prd_type_id IN (1,2,3)

)

PIVOT

(

   SUM(amount) AS sum_amount,

   AVG(amount) AS avg_amount

   FOR (month) IN (1 AS JAN,2 AS FEB

   )

)

ORDER BY prd_type_id;

4. 使用 UNPIVOT 函式

UNPIVOT 函式用於將列旋轉為行，它執行與 PIVOT 相反的操作

範例：使用 UNPIVOT 從 pivot_sales_data 表得到將列旋轉為行的銷售資料

SELECT *

FROM pivot_sales_data

UNPIVOT

(

   amount FOR month IN (JAN,FEB,MAR,APR)

)

ORDER BY prd_type_id;

四、 執行 Top-N 查詢

Oracle 12c 的一項新功能是對執行 Top-N 查詢的原生支援，Top-N查詢包含行限制子句，

行限制子句允許通過指定以下方面的內容來對檢索的行加以限制：

   · 返回的行數，使用 FETCH FIRST 子句

   · 一個偏移量，在限制開始之前，指定行跳過的行數，使用 OFFSET 子句

   · 返回所選行的總數的百分比，使用 PERCENT 子句

使用行限制子句時，還可以新增下列操作之一：

   · ONLY，它在行限制子句中指定返回確切數目的行，可以是行數或百分比

   · WITH TIES，它包括額外的具有相同排序關鍵字值的行，並作為檢索出的最後一行

   （排序關鍵字是在 ORDER BY 子句中指定的列）

1. 使用 FETCH FIRST 子句

範例：使用 FETCH FIRST 從 more_employees 表檢索employee_id 值最小的 5 名員工

SELECT employee_id,first_name,last_name

FROM more_employees

ORDER BY employee_id

FETCH FIRST 5 ROWS ONLY;

2. 使用 OFFSET 子句

OFFSET 子句用來指定行限制開始前跳過的行數

範例：從 more_employees 表檢索 employee_id 值在 6 和 10 之間的員工

SELECT employee_id,first_name,last_name

FROM more_employees

ORDER BY employee_id

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

3. 使用 PERCENT 子句

可以使用 PERCENT 子句指定返回所選的總行數的百分比

範例：從 products 表檢索前 20% 價格最高的產品

SELECT product_id,name,price

FROM products

ORDER BY price DESC

FETCH FIRST 20 PERCENT ROWS ONLY;

4. 使用 WITH TIES 子句

可以使用 WITH TIES 包括其他具有相同排序關鍵字的行作為最後獲取的一行

排序關鍵字是在 ORDER BY 子句中指定的列

範例：從 more_employees 表中使用 WITH TIES 檢索前 10% 工資最低的員工，排序關鍵字是 salary 列

SELECT employee_id,first_name,last_name,salary

FROM more_employees

ORDER BY salary

FETCH FIRST 10 PERCENT ROWS WITH TIES;

本文使用的表：http://pan.baidu.com/s/1zuHRK