oracle學習筆記８: 分析函式
分析函式，也稱為視窗函式，通常被認為僅對資料倉儲SQL有用。使用分析函式的查詢，基於對資料行的分組來計算總量值。與一般的聚合函式提供的分組統計類似，但主要不同點就是，分析函式為每個分組返回多行資料。不同於每個分組只得到一行資料，分析函式可以同時得到所有行的詳細資料。
視窗是通過分析子句定義的，每一行限定在一個滑動視窗中。這些滑動視窗確定了用來計算當前行的資料行範圍。每個視窗的大小可以由一定物理數量的資料行或者某種邏輯間隔（例如時間間隔）確定。這些函式能夠比通常的聚集函式多做很多事情，例如可以跨行引用值，生成多層聚集以及對子集資料排序進行更細粒度的控制。
儘管傳的SQL語句也可以用來實現線上分析查詢，但通常這些語句很複雜，並且相對來說效能較差。而使用分析函式，可以避免對同一物件的重複訪問，節約時間和資源。因為既可以返回明細資料，又可以返回分組後的值，分板函式可以很方便地提供累計值，滑動平均值，中心值以及彙總報表。
除了order by子句之外，分析函式在查詢中是最後執行的運算。在執行分析函式之前，所有其他子句，包括where、group by以及having子句都己執行完成。因此，分析函式只能就用於選擇列表或order by子句中。分析函式通常被認為適合在資料倉儲或大的報表應用中使用，但是，在瞭解了其能力和靈活性之後，就能在任何可用的地方使用它們。
分析函式剖析
分析函式有３個基本組成部分：
１. 分割槽子句
２. 排序子句
３. 開窗子句
function1 (argument1,argument2,..argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])
function1 是所呼叫的接收０個或多個引數的分析函式。分割槽子句按照分割槽列的值對資料行進行分組。所有分割槽列的值相同的資料行被組合為一個資料分割槽。
準備測試資料
--非標準化的sales_fact表
drop table sales_fact;
create table sales_fact AS
select c.country_name country,c.country_subRegion region,p.prod_name product,
t.calendar_year year,t.calendar_week_number week,
sum(s.amount_sold) sale,
sum(s.amount_sold*
(
case
when mod(rownum,10)=0 then 1.4
when mod(rownum,5)=0 then 0.6
when mod(rownum,2)=0 then 0.9
when mod(rownum,2)=0 then 1.2
else 1
end
)
) receipts
from sh.sales s,sh.times t,sh.customers cu,sh.countries c, sh.products p
where s.time_id=t.time_id
and s.prod_id=p.prod_id
and s.cust_id=cu.cust_id
and cu.country_id=c.country_id
group by c.country_name,c.country_subregion,p.prod_name,t.calendar_year,t.calendar_week_number;
select * from sales_fact;
SQL> --sale列的動態求和
SQL> select year,
2 week,
3 sale,
4 sum(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between unbounded preceding and current row
8 ) running_sum_ytd
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
1998 1 58.15 58.15
1998 2 29.39 87.54
1998 3 29.49 117.03
1998 4 29.49 146.52
1998 5 29.8 176.32
1998 6 58.78 235.1
1998 9 58.78 293.88
1998 10 117.76 411.64
1998 12 59.6 471.24
1998 14 58.78 530.02
1998 15 58.78 588.8
上面的例子,計算了sale列從年初開始按照產品、國家、地區、年份相組合的動態求和值。partition by sf.product, sf.country, sf.region, sf.year宣告瞭分割槽列，在資料分割槽中，使用order by sf.week子句來指定資料行按照week列進行排序。計算sales列的動態總和值，因此分析函式必須在從年初到當前周的時間視窗中計算。這通過開窗子句rows between unbounded preceding and current row實現。sum(sale)函式在該時間視窗的資料行中計算sale列的和值。因為資料行是按照week列排序的，求和函式就會在從年初到當前周的資料集上運算。
running_sum_ytd列是分析模式下求和函式的輸出。列值在新的一年時進行了重置，因為年份也是分割槽列，所以每年都會有一個新的分割槽。當開始新的一年時，視窗就滑動到下一個資料分割槽中，求和函式從week 1開始聚合。使用傳統的SQL語句來實現這個功能將導致需要使用多次自聯結或成本很高的列級子查詢。
排序子句通過一列或一個表示式的值來對資料分割槽中的行進行排序。在分析型SQL語句中，資料行在資料分割槽中的位置是很重要的，這是由排序子句控制的。在資料分割槽內的資料行按照排序列的值排序。因為在分割槽子句中按照分割槽列的值排序，實際上最終得到的是按照分割槽子句和排序子句中指定的列進行排序後的結果。
與sql語句order by類似，排序可以按照升序也可以按照降序。使用nulls first或nulls last子句可以將空值放到資料分割槽的最上面或最下面。
開窗子句指定了分析函式進行運算的資料子集。這個視窗可以是動態，它有一個很恰當的名字---滑動視窗。可以使用視窗說明子句來指定滑動視窗的上下邊界條件。
視窗說明子句的語法：
[rows | range] between <start expr> and [end expr]
whereas
<start expr> is [unbounded preceding | current row | n preceding | n following]
<end expr> is [unbounded following | current row | n preceding | n following]
關鍵字preceding指定了視窗的上邊界條件，following或current row子句指定了視窗的下邊界條件。滑動視窗提供了簡便的複雜矩陣計算能力。例如，可以使用子句rows between unbounded preceding and current row對sales列動態求和。在這個例子中，視窗最上面的一行是當前分割槽中的第一行而視窗最下面的一行是當前資料行。
並不是所有分析函式都支援開窗語句。
分析函式不能進行巢狀。但可以將所包含的SQL語句放在內嵌檢視中，然後在檢視之外使用分析函式實現巢狀效果。分析函式也可以用在多層巢狀內嵌檢視中。
分析函式列表
聚合函式
聚合函式可以在分析模式或傳統的非分析模式下來執行運算。非分析模式下的聚合函式將結果集削減少為較少的資料行。然而，在分析模式下，聚合函式並不減少輸出結果行數。聚合函式能夠在一行中同時取聚合的和非聚合的列。分析模式的聚合函式可以不需要任何自聯結就可以聚合不同層級的資料。
分析函式在寫需要在不同層級上對資料進行聚合的複雜查詢報表時是非常有用的。考慮某個產品的消費者市場分析報告，調查最受歡迎的廣告主管，這就需要在多個層次例如年齡、性別、商店、行政區、地區以及國家上對銷售資料進行聚合。分析模式下的聚合函式可以輕易地且很有效地實現這個市場分析報告。與非分析的實現方法相對，分析函式可以很顯著地提高SQL語句的清晰度和效能。
跨越整個分割槽的聚合函式
SQL> --sale列的最大值
SQL> select year,
2 week,
3 sale,
4 max(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between unbounded preceding and unbounded following
8 ) max_sale
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE MAX_SALE
---------- ---------- ---------- ----------
1998 1 58.15 172.56
1998 2 29.39 172.56
1998 3 29.49 172.56
1998 4 29.49 172.56
1998 5 29.8 172.56
1998 6 58.78 172.56
1998 9 58.78 172.56
1998 10 117.76 172.56
1998 12 59.6 172.56
1998 14 58.78 172.56
1998 15 58.78 172.56
在一些情況下，可能需要在某個給定分割槽的所有資料行上應用分析函式。例如在一整年中計算sale列的最大值將需要包含資料分割槽中每一行資料的視窗。rows between unbounded preceding and unbounded following提定將max函式應用於資料分割槽的每一行上。unbounded following子句指定視窗大小為包含資料分割槽中的所有行。
細粒度視窗宣告
SQL> --５周時間跨度視窗sale列的最大值
SQL> select year,
2 week,
3 sale,
4 max(sale) over(
5 partition by sf.product, sf.country, sf.region, sf.year
6 order by sf.week
7 rows between 2 preceding and 2 following
8 ) max_weeks_5
9 from sales_fact sf
10 where sf.country in ('Australia')
11 and sf.product = 'Xtend Memory'
12 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE MAX_WEEKS_5
---------- ---------- ---------- -----------
1998 1 58.15 58.15
1998 2 29.39 58.15
1998 3 29.49 58.15
1998 4 29.49 58.78
1998 5 29.8 58.78
1998 6 58.78 117.76
1998 9 58.78 117.76
1998 10 117.76 117.76
1998 12 59.6 117.76
1998 14 58.78 117.76
1998 15 58.78 117.56
YEAR WEEK SALE MAX_WEEKS_5
---------- ---------- ---------- -----------
1998 17 58.78 117.56
1998 18 117.56 117.56
1998 19 58.98 117.56
1998 21 59.6 117.56
1998 23 117.56 117.56
1998 26 117.56 117.56
1998 27 57.52 117.56
1998 28 57.72 117.56
1998 29 57.72 115.44
1998 34 115.44 115.84
1998 35 57.52 115.84
視窗的宣告也可以更加細化。假設想要計算本週之前兩週到本週之後兩週共５周的時間視窗內sale列的最大值，可以使用子句rows between 2 preceding and 2 following來實現這一點。
預設視窗宣告
預設的視窗子句是rows between unbounded preceding and current row。如果你沒有顯示宣告視窗，就將會使用預設視窗。顯式宣告這個子句是避免模稜兩可的好辦法。
lead和lag
lag能夠訪問結果集中前面的行，lead函式支援訪問結果集中後面的行。
在零售行業中，同店銷售額是一個計算得到的矩陣，用來衡量一個門店的業績，通常用銷售資料與去年同季度情況進行比較。在標準化資料模型中，這個矩陣的計算需要訪問另一行，因為當前和前一年的銷售資料在sale列中是存在不同的資料行的。使用lead和lag函式強大的跨行引用能力，可以很輕鬆地來計算出這個矩陣。
另一個例子是需要訪問前一行或後一行資料的百分比增減計算。這個計算也可以使用lead和lag函式非常優雅地實現。
分析型sql中的資料是按照分割槽列的值進行分割槽的。獲取前一行的值是與位置相關的運算，資料分割槽中各行的順序對於維護邏輯上的一致性是很重要的。在一個資料分割槽內部，資料行通過order by子句排序以控制某一行在結果集中的位置。
lag語法：
lag (expression, offset, default) over (partition-clause order-by-clause)`
lead和lag函式不支援開窗子句。這兩個函式僅支援partition by子句和order by子句。
SQL> --lag函式
SQL> col product format A30
SQL> col country format A10
SQL> col region format A10
SQL> col year format 9999
SQL> col week format 99
SQL> col sale format 99999.99
SQL> col receipts format 99999.99
SQL> set lines 120 pages 100
SQL>
SQL> select year,
2 week,
3 sale,
4 lag(sale, 1, sale) over(
5 partition by sf.product, sf.country, sf.region
6 order by sf.year,sf.week
7 ) prior_wk_sales
8 from sales_fact sf
9 where sf.country in ('Australia')
10 and sf.product = 'Xtend Memory'
11 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE PRIOR_WK_SALES
----- ---- --------- --------------
1998 1 58.15 58.15
1998 2 29.39 58.15
1998 3 29.49 29.39
1998 4 29.49 29.49
1998 5 29.80 29.49
1998 6 58.78 29.8
1998 9 58.78 58.78
1998 10 117.76 58.78
1998 12 59.60 117.76
...
lag函式中的第３個引數指定了預設值，這是可選的。如果分析函式引用了不存在的行，則會返回空值。這是預設行為，可以在第３個引數中指定一個其它返回值來修改。
理解資料行的位移
通過指定不同的位移可以用訪問一個資料分割槽中的怕有行。
SQL> select year,
2 week,
3 sale,
4 lag(sale, 10, sale) over(
5 partition by sf.product, sf.country, sf.region
6 order by sf.year,sf.week
7 ) prior_wk_sales_10
8 from sales_fact sf
9 where sf.country in ('Australia')
10 and sf.product = 'Xtend Memory'
11 order by sf.product, sf.country, sf.year, sf.week;
YEAR WEEK SALE PRIOR_WK_SALES_10
----- ---- --------- -----------------
1998 1 58.15 58.15
1998 2 29.39 29.39
1998 3 29.49 29.49
1998 4 29.49 29.49
1998 5 29.80 29.8
1998 6 58.78 58.78
1998 9 58.78 58.78
1998 10 117.76 117.76
1998 12 59.60 59.6
1998 14 58.78 58.78
1998 15 58.78 58.15
1998 17 58.78 29.39
1998 18 117.56 29.49
1998 19 58.98 29.49
1998 21 59.60 29.8
1998 23 117.56 58.78
1998 26 117.56 58.78
1998 27 57.52 117.76
1998 28 57.72 59.6
1998 29 57.72 58.78
1998 34 115.44 58.78
1998 35 57.52 58.78
1998 38 115.84 117.56
1998 39 115.84 58.98
1998 40 57.52 59.6
1998 41 58.32 117.56
1998 42 115.84 117.56
1998 43 57.52 57.52
1998 44 57.52 57.72
上面的例子中，lag函式使用了位移量10訪問往前第10行的資料。
lead函式
lead函式與lag函式類似，只是它可以訪問排序後的結果集中當前值後面的資料行。
--lead函式
select sf.year,sf.week,sf.sale,
lead(sale,1,sale) over(
partition by sf.product,sf.country,sf.region
order by sf.year,sf.week
) prior_wk_sales
from sales_fact sf
where sf.country in ('Australia') and sf.product='Xtend Memory'
order by sf.product,sf.country,sf.year,sf.week;
partition by子句可以用來指定不同中的分割槽邊界，而order by子句可以改變分割槽內的排序順序。有效地選擇分割槽和排序列可以訪問一個結果集中的任一行。
first_value和last_value
first_value和last_value常用在計算排過序的結果集中的最大值和最小值。
生成某個產品在一定市場領域的銷售額最高商店的報表是這些分析函式最經典的應用。
first_value語法：first_value(expression) over (partition-clause order-by-clause windowing-clause)
SQL> --first_value函式
SQL> select year,week,sale,
2 first_value(sale) over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) top_sale_value,
7 first_value(week) over(
8 partition by sf.product,sf.country,sf.region,sf.year
9 order by sf.sale desc
10 rows between unbounded preceding and unbounded following
11 ) top_sale_week
12 from sales_fact sf
13 where sf.country in ('Australia') and sf.product = 'Xtend Memory'
14 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE TOP_SALE_VALUE TOP_SALE_WEEK
---------- ---------- ---------- -------------- -------------
1998 1 58.15 172.56 48
1998 2 29.39 172.56 48
1998 3 29.49 172.56 48
1998 4 29.49 172.56 48
1998 5 29.8 172.56 48
1998 6 58.78 172.56 48
1998 9 58.78 172.56 48
1998 10 117.76 172.56 48
1998 12 59.6 172.56 48
1998 14 58.78 172.56 48
1998 15 58.78 172.56 48
YEAR WEEK SALE TOP_SALE_VALUE TOP_SALE_WEEK
---------- ---------- ---------- -------------- -------------
1998 17 58.78 172.56 48
1998 18 117.56 172.56 48
1998 19 58.98 172.56 48
1998 21 59.6 172.56 48
1998 23 117.56 172.56 48
1998 26 117.56 172.56 48
1998 27 57.52 172.56 48
1998 28 57.72 172.56 48
1998 29 57.72 172.56 48
1998 34 115.44 172.56 48
1998 35 57.52 172.56 48
partition by sf.product,sf.country,sf.region,sf.year子句使用指定的這些列來對資料進行分割槽。order by sf.sale desc子句將資料行按照sale列值的降序排列。
視窗的上下邊界條件通過子句rows between unbounded preceding and unbounded following指定。在product,country,region,year的層級上來獲取最大的銷售值，從而窗中就包含一個資料分割槽中的所有行。
實際操作中，資料是按照product,country,region,year和sale列排序的。sale列的排序是降序的。由於宣告瞭按照sale列的值降序排列，每個資料分割槽中第一行資料都將具有最大的值。因此，first_value(sale)子句獲取資料分割槽中最大的sale列值。
SQL> --last_value函式
SQL> select year,week,sale,
2 last_value(sale) over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) low_sale
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product = 'Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE LOW_SALE
---------- ---------- ---------- ----------
1998 1 58.15 28.76
1998 2 29.39 28.76
1998 3 29.49 28.76
1998 4 29.49 28.76
1998 5 29.8 28.76
1998 6 58.78 28.76
1998 9 58.78 28.76
1998 10 117.76 28.76
1998 12 59.6 28.76
1998 14 58.78 28.76
1998 15 58.78 28.76
YEAR WEEK SALE LOW_SALE
---------- ---------- ---------- ----------
1998 17 58.78 28.76
1998 18 117.56 28.76
1998 19 58.98 28.76
1998 21 59.6 28.76
1998 23 117.56 28.76
1998 26 117.56 28.76
1998 27 57.52 28.76
1998 28 57.72 28.76
1998 29 57.72 28.76
1998 34 115.44 28.76
1998 35 57.52 28.76
類似的，可以使用last_value函式計算最小值或最大值。last_value函式在資料行視窗中獲取最後一行的列值。如上面要計算sale列的最小值,那麼可以使用last_value(sale)子句及order by sf.sale desc子句的組合來排序。order by sf.sale desc按降序排，last_value(sale)獲取最後一行，那麼最後一行的值將會最小值。
可以有效地控制來使用視窗宣告的粒度生成複雜報表。例如，rows between 10 preceding and 10 following子句指定了在一個21行資料的視窗中求最大值或最小值。
空值通過[respect nulls | ignore nulls]子句處理。respect nulls子句是預設值。預設情況下，如果第一行中的列值是空值，first_value函式就會返回空值。如果指定了ignore nulls子句，則first_value函式將會返回在視窗中第一個列值不為空的行的值。
其它分析函式
nth_value
first_value和last_value函式能夠獲取排過序的結果集中的第一行或最後一行資料，但用它們獲取任意行的資料還不是很直接。
nth_value，可以獲取排過序的結果集中的任意一行，而不僅是第一行或最後一行。first_value = nth_value(column_name,1)
nth_value支援開窗子句，開窗子句實現了動態滑動視窗的能力。因此，可以寫高效的簡單查詢來回答覆雜的問題。如，對於某個產品哪家店的銷售在12周的時間內第2高？
nth_value函式語法如下：
nth_value (measure, n) [ from first | from last ] [ respect nulls | ignore nulls ]
over (partitioning-clause order-by-clause windowing-clause)
--nth_value函式
select sf.year,sf.week,sf.sale,
nth_value(sf.sale,2) over (
partition by sf.product,sf.country,sf.region,sf.year
order by sf.sale desc
rows between unbounded preceding and unbounded following
) sale_2nd_top
from sales_fact sf
where sf.country in ('Australia') and sf.product='Xtend Memory'
order by sf.product,sf.country,sf.year,sf.week;
sql語句在sf.product,sf.country,sf.region,sf.year層級上取week列的值和sale列值第二高的值。因為資料是按照sale列降序進行排列的，結果集中的第２行就是sale列中第２高的值。partition by sf.product,sf.country,sf.region,sf.year宣告瞭分割槽列。
本函式要在oracle 11gR2版本以上才能執行。
rank
rank函式以數值形式返回一個資料行在排序後的結果集中的位置。如果資料行是按某一列進行排序的，則這一行在視窗中的位置就反映了該值在視窗內資料行中的排名。在排名並列的情況下，具有同樣值的行將具有同樣的排名而接下來的排名就會被跳過，從而在排名值上留下空隙。這意味著某兩行可能具有同一排名，排名也不一定是連續的。
rank對於計算最上面或最下面n行是非常有用的，如查詢銷售量在前１０位的同就是零售業資料倉儲中一個典型的查詢。如果需要寫查詢來計算某個結果集中最上面或最下面n個元素的值，就可以使用rank或dense_rank函式。
rank函式的語法：rank() over (partition-clause order-by-clause)
SQL> --rank函式的使用：銷售額前１０位的周
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 rank() over (
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.sale desc
6 ) sales_rank
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week
10 ) where sales_rank<=10
11 order by 1,4;
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 2
1998 18 117.56 3
1998 23 117.56 3
1998 26 117.56 3
1998 38 115.84 6
1998 42 115.84 6
1998 39 115.84 6
1998 34 115.44 9
1998 52 86.38 10
1999 17 148.12 1
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1999 47 147.78 2
1999 15 135.1 3
1999 44 130.72 4
1999 42 120.59 5
1999 25 107.44 6
1999 22 107.44 6
1999 34 105.8 8
1999 37 105.8 8
1999 8 103.11 10
2000 46 246.74 1
2000 21 187.48 2
計算按照sf.product,sf.country,sf.region,sf.year的列值來計算的銷售額前10行的值。partition by sf.product,sf.country,sf.region,sf.year宣告瞭分割槽列，order by sf.sale desc子句宣告瞭在資料分割槽中的資料行按sale列的值降序排列。rank函式用來計算資料行在分割槽中的排名。這個sql語句被包在一個內嵌檢視中，然後應用sales_rank<=10謂語取出sale列值在前10位的周。
同時還要注意，開窗子句在rank函式中不適用，rank函式是應用在資料分割槽中的所有行上的。
rank函式為並列排名分配了同樣的排名值。
dense_rank
dense_rank是rank函式的變體。rank和dense_rank函式的區別在於當存在並列的時候dense_rank函式不會跳過排名值。dense_rank對於查詢結果集中頂部、底部或中間n行的資料是非常有用的。
SQL> --dense_rank函式
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 dense_rank() over (
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.sale desc
6 ) sales_rank
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.product,sf.country,sf.year,sf.week
10 ) where sales_rank<=10
11 order by 1,4;
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 2
1998 18 117.56 3
1998 23 117.56 3
1998 26 117.56 3
1998 38 115.84 4
1998 39 115.84 4
1998 42 115.84 4
1998 34 115.44 5
1998 52 86.38 6
1998 21 59.6 7
YEAR WEEK SALE SALES_RANK
---------- ---------- ---------- ----------
1998 12 59.6 7
1998 19 58.98 8
1998 17 58.78 9
1998 15 58.78 9
1998 14 58.78 9
1998 6 58.78 9
1998 9 58.78 9
1998 51 58.32 10
1998 41 58.32 10
1999 17 148.12 1
1999 47 147.78 2
dense_rank函式在排名值需要連續的時候民是很有用的。例如，在一個班級的學生花名冊上排名前10的學生就不能被跳過。另一方面，rank函式在排名值不需要連續時是有很用的。
dense_rank函式中空值的排序位置可以通過nulls first或nulls last子句控制。對於升序排列，nulls last是預設值，對於降序排列，nulls first是預設值。
使用dense_rank 的另一種方法是，將它與first或last函式一起用。這兩個函式都是聚合和分析函式，用來找出在按照特定規則排序後的集合中，排在第一位或最後一位的一系列值。當需要有序分組中最後一行的第一個值，而所需的值又不是排序鍵時，使用first和last函式，不需要回頭再聯結表本身就可以得到正確的值。
語法：
Aggregate syntax:
aggregate function KEEP
(dense_rank [first | last] order by expression [desc | asc] nulls [first | last])
analytic syntax:
aggregate function KEEP
(dense_rank [first | last] order by expression [desc | asc] nulls [first | last])
over (partition-clause)
注意，dene_rank函式是如何作為特定聚集函式(min,max,sum,avg,count,variance或stddev)修飾符的。keep關鍵字可以使語法更清晰，並且還可以限定聚集函式表明只返回聚集函式的第一個或最後一個值。當應用在這種場景下時，dense_rank表明oracle權在具有最小first或最大last dense_rank值的資料行上實現聚集。
在first｜last keep函式中使用dense_rank函式
SQL> get E:\bjc2016\study\pln.sql
1 --set serveroutput off;
2 select xplan.*
3 from (select max(sql_id) keep
4 (dense_rank last order by last_active_time) sql_id,
5 max(child_number) keep
6 (dense_rank last order by last_active_time) child_number
7 FROM V$SQL
8 WHERE UPPER(SQL_TEXT) LIKE '%&1%'
9 and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE %'
10 ) sqlinfo,
11* table(dbms_xplan.display_cursor(sqlinfo.sql_id, sqlinfo.child_number, 'ALLSTATS LAST')) xplan;
在這個sql中，想要返回的是v$sql檢視中最近執行的滿足sql文字中包含字串&1的sql語句的sql_id和child_number。dense_rank函式確保返回的是通過last_active_time得出的最後一條執行的語句。如果沒有這個函式，就需要在內嵌檢視中使用last_value函式，並對這個檢視返回的資料集按照sql_id和child_number分組，執行max聚集運算。也就是說，必須增加一個額外的步驟。這種方法確實是一種更簡短且高效的得到所需結果的方法。
row_number
row_number函式為有序結果庥中的每一行分配唯一的行編號。如果宣告瞭分割槽子句，則為每一行分配一個基於其在該有序分割槽中位置的唯一編號。如果沒有宣告分割槽子句，則為結果集中的每一行分配唯一編號。
row_number函式可用於獲取頂部、底部或中間n行資料的查詢，與rank和dense_rank函式類似。儘管rank,dense_rank和row_number函式具有類似的功能，在它們之間還是有很微妙的區別，其中一個區別就是row_number函式不支援開窗子句。
row_number語法：row_number() over (partition-clause order-by-clause)
row_number函式是非確定性函式，如果資料分割槽中具有相同的值，row_number函式的值是不確定的。相反地，rank和dense_rank函式是確定性函式，重複執行查詢也會返回一致的資料。
SQL> select sf.year,sf.week,sf.sale,
2 row_number() over(
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 ) sales_rn,
6 rank() over(
7 partition by sf.product,sf.country,sf.region,sf.year
8 order by sf.sale desc
9 ) sales_rank
10 from sales_fact sf
11 where sf.country in ('Australia') and sf.product='Xtend Memory'
12 order by sf.product,sf.country,sf.year,sales_rank;
YEAR WEEK SALE SALES_RN SALES_RANK
---------- ---------- ---------- ---------- ----------
1998 48 172.56 1 1
1998 10 117.76 2 2
1998 26 117.56 4 3
1998 18 117.56 3 3
1998 23 117.56 5 3
1998 39 115.84 6 6
1998 38 115.84 7 6
1998 42 115.84 8 6
1998 34 115.44 9 9
1998 52 86.38 10 10
1998 12 59.6 11 11
YEAR WEEK SALE SALES_RN SALES_RANK
---------- ---------- ---------- ---------- ----------
1998 21 59.6 12 11
1998 19 58.98 13 13
1998 17 58.78 15 14
1998 14 58.78 16 14
1998 9 58.78 17 14
1998 15 58.78 14 14
1998 6 58.78 18 14
1998 41 58.32 19 19
1998 51 58.32 20 19
1998 1 58.15 21 21
1998 47 57.72 22 22
ratio_to_report
分析函式ratio_to_report計算資料分割槽中某個值與和值的比率。如果沒有宣告分割槽子句，這個函式將會計算一個值與整個結果集中和值的比率。這個分析函式可以用於在不同層級上計算比率，它不需要進行自聯結。
ratio_to_report可以計算報表中某個值佔總值的百分比。例如，考慮某個零售連鎖店中某種產品的銷售報表。每家門店都對該產品的銷售總額做出了貢獻，並且知道每家門店的銷售額佔總銷售額的百分比有助於市場趨勢分析。
SQL> select sf.year,sf.week,sf.sale,
2 trunc(100*
3 ratio_to_report(sale) over(partition by sf.product,sf.country,sf.region,sf.year),2) sales_yr,
4 trunc(100*
5 ratio_to_report(sale) over(partition by sf.product,sf.country,sf.region),2) sales_prod
6 from sales_fact sf
7 where sf.country in ('Australia') and sf.product='Xtend Memory'
8 order by sf.product,sf.country,sf.year,sf.week;
YEAR WEEK SALE SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
1998 1 58.15 2.26 .43
1998 2 29.39 1.14 .21
1998 3 29.49 1.15 .22
1998 4 29.49 1.15 .22
1998 5 29.8 1.16 .22
1998 6 58.78 2.29 .43
1998 9 58.78 2.29 .43
1998 10 117.76 4.59 .88
1998 12 59.6 2.32 .44
1998 14 58.78 2.29 .43
1998 15 58.78 2.29 .43
YEAR WEEK SALE SALES_YR SALES_PROD
---------- ---------- ---------- ---------- ----------
1998 17 58.78 2.29 .43
1998 18 117.56 4.58 .87
1998 19 58.98 2.3 .44
1998 21 59.6 2.32 .44
1998 23 117.56 4.58 .87
1998 26 117.56 4.58 .87
1998 27 57.52 2.24 .43
1998 28 57.72 2.25 .43
1998 29 57.72 2.25 .43
1998 34 115.44 4.5 .86
1998 35 57.52 2.24 .43
sql語句計算了兩個比率，sales_yr是在sf.product,sf.country,sf.region,sf.year層級上進行計算的比率，而sales_prod是在sf.product,sf.country,sf.region層級上來計算的。ratio_to_report函式返回一個比率並且被乘以100來算出百分比。
trunc(x[,y])
【功能】返回x按精度y擷取後的值
【引數】x,y，數字型表示式,如果y不為整數則擷取y整數部分，如果y>0則擷取到y位小數，如果y小於0則擷取到小數點向左第y位，小數前其它資料用0表示。
【返回】數字
【示例】
select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;
返回：5555.66 5500 5555
【相近】round(x[,y])
返回擷取後的值，用法同trunc(x[,y]),只是要做四捨五入
percent_rank
percent_rank函式以0到１之間的分數形返回某個值在資料分割槽中的排名。percent_rank的計算公式為(rank-1)/(n-1)其中如果宣告瞭分割槽子句n就是分割槽中的資料行數，如果沒有宣告分割槽子句n就是結果集中所有的資料庫行數。percent_rank函式可以計算某個值在結果集中按百分比所處的相對位置。
這個排名可以計算為相對於當前資料分割槽或相對於整個結果集。例如，計算一家零售連鎖門店的銷售額在某個地區或區域中所處的名次可以找出表現最好或最差的門店。
SQL> select * from (
2 select sf.year,sf.week,sf.sale,
3 100*percent_rank() over(
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sale desc
6 ) pr
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 ) where pr<50
10 order by year,sale desc;
YEAR WEEK SALE PR
---------- ---------- ---------- ----------
1998 48 172.56 0
1998 10 117.76 2.85714286
1998 26 117.56 5.71428571
1998 18 117.56 5.71428571
1998 23 117.56 5.71428571
1998 39 115.84 14.2857143
1998 38 115.84 14.2857143
1998 42 115.84 14.2857143
1998 34 115.44 22.8571429
1998 52 86.38 25.7142857
1998 12 59.6 28.5714286
使用percent_rank函式來計算銷售額前50位的百分比。partition by sf.product,sf.country,sf.region,sf.year order by sale desc子句在由分割槽列product,country,region和year定義的資料分割槽上計算sale列值的百分比。資料行按照sale列的降序排列。為了得出百分比，計算結果乘以了100。
percentile_cont
percentile_cont函式可以計算內插值。例如每個地區或城市中等收入家庭的收入。percentile_count函式接收一個０到１之間的機率值並返回與宣告瞭排序的percent_rank函式計算值相等的內插值百分比。事實上，percentile_cont函式是percent_rank函式的反函式，與percent_rank函式的輸出結合起來看到可以更容易地來理解percentile_cont函式。
如計算一個城市或地區中等收入家庭的收入值，中位值是percent_rank為０.５。percentile_cont(0.5)子句將會返回中位值，因為percentile_cont函式計算值percent_rank為０.５。實際上，median函式是percentile_cont函式的一個預設值為０.５的特例。這個函式忽略空值。同時也不支援開窗子句。
percentile_cont語法：percentile_cont(expr) within group(sort-clause) over (partition-clause over-by-clause)
percentile_cont函式的語法與到目前為止所討論的分析函式的語法稍有不同。新的子句within group (order by sale desc)取代了之前的order by子句，但在功能上與宣告order by子句是一樣的。
SQL> select sf.year,sf.week,sf.sale,
2 percentile_cont(0.5) within group
3 (order by sale desc)
4 over( partition by sf.product,sf.country,sf.region,sf.year) pc,
5 percent_rank() over (
6 partition by sf.product,sf.country,sf.region,sf.year
7 order by sf.sale desc
8 ) pr
9 from sales_fact sf
10 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE PC PR
---------- ---------- ---------- ---------- ----------
1998 48 172.56 58.55 0
1998 10 117.76 58.55 .028571429
1998 18 117.56 58.55 .057142857
1998 23 117.56 58.55 .057142857
1998 26 117.56 58.55 .057142857
1998 39 115.84 58.55 .142857143
1998 42 115.84 58.55 .142857143
1998 38 115.84 58.55 .142857143
1998 34 115.44 58.55 .228571429
1998 52 86.38 58.55 .257142857
1998 21 59.6 58.55 .285714286
percentile_disc
percentile_disc函式在功能上類似percentile_cont函式，只是percentile_cont函式使用了連續分佈模型，而percentile_disc函式使用了離散分佈模型。
SQL> select sf.year,sf.week,sf.sale,
2 percentile_disc(0.5) within group (order by sf.sale desc)
3 over (partition by sf.product,sf.country,sf.region,sf.year) pd_desc,
4 percentile_disc(0.5) within group (order by sf.sale)
5 over (partition by sf.product,sf.country,sf.region,sf.year) pd_asc,
6 percent_rank() over (
7 partition by sf.product,sf.country,sf.region,sf.year
8 order by sf.sale desc
9 ) pr
10 from sales_fact sf
11 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE PD_DESC PD_ASC PR
---------- ---------- ---------- ---------- ---------- ----------
1998 48 172.56 58.78 58.32 0
1998 10 117.76 58.78 58.32 .028571429
1998 18 117.56 58.78 58.32 .057142857
1998 23 117.56 58.78 58.32 .057142857
1998 26 117.56 58.78 58.32 .057142857
1998 39 115.84 58.78 58.32 .142857143
1998 42 115.84 58.78 58.32 .142857143
1998 38 115.84 58.78 58.32 .142857143
1998 34 115.44 58.78 58.32 .228571429
1998 52 86.38 58.78 58.32 .257142857
1998 21 59.6 58.78 58.32 .285714286
ntile
ntile函式對一個資料分割槽中的有序結果集進行劃分，將其分組為各個桶，併為每個小組分配一個唯一的組編號。這個函式在統計分析中是很有用的。例如，如果想移除異常值，正常以外的值，可以將它們分組到頂部或底部的桶中，然後在統計分析的時候將這些值排除。oracle資料庫統計資訊收集包也使用ntile函式計算直方圖資訊邊界。在統計學術語中，ntile函式用於建立等寬直方圖資訊。
SQL> select sf.year,sf.week,sf.sale,
2 ntile(10) over (
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 ) group#
6 from sales_fact sf
7 where sf.country in ('Australia') and sf.product='Xtend Memory';
YEAR WEEK SALE GROUP#
---------- ---------- ---------- ----------
1998 48 172.56 1
1998 10 117.76 1
1998 18 117.56 1
1998 26 117.56 1
1998 23 117.56 2
1998 39 115.84 2
1998 38 115.84 2
1998 42 115.84 2
1998 34 115.44 3
1998 52 86.38 3
1998 12 59.6 3
ntile(10)子句將一個資料分割槽分為10個桶，資料行按sale列的降序排序。ntile函式將資料行劃分為多個桶，每個桶中行的數目相等。由於資料行是按照sale列降序排列的，組編號羅小的資料行sale列的值更大。這一技術可以很容易地剔除異常資料。
stddev
stddev函式可以用來在一個資料分割槽中的某些資料行上計算標準差，或者如果沒有宣告分割槽子句則在整個結果集上計算標準偏差。這個函式為分割槽子句所指定的資料分割槽計算標準偏差，定義為方差的平方根。如果沒有宣告分割槽子句，就將在結果集中的所有資料行上計算標準偏差。
SQL> select sf.year,sf.week,sf.sale,
2 stddev(sale) over (
3 partition by sf.product,sf.country,sf.region,sf.year
4 order by sf.sale desc
5 rows between unbounded preceding and unbounded following
6 ) stddv
7 from sales_fact sf
8 where sf.country in ('Australia') and sf.product='Xtend Memory'
9 order by sf.year,sf.week;
YEAR WEEK SALE STDDV
---------- ---------- ---------- ----------
1998 1 58.15 33.5281435
1998 2 29.39 33.5281435
1998 3 29.49 33.5281435
1998 4 29.49 33.5281435
1998 5 29.8 33.5281435
1998 6 58.78 33.5281435
1998 9 58.78 33.5281435
1998 10 117.76 33.5281435
1998 12 59.6 33.5281435
1998 14 58.78 33.5281435
1998 15 58.78 33.5281435
stddev(sale) 子句在資料分割槽上計算sale列的標準偏差。分割槽子句partition by sf.product,sf.country,sf.region,sf.year宣告瞭分割槽列。開窗子句rows between unbounded preceding and unbounded following宣告瞭資料視窗為資料分割槽中的所有行。從本質上來說，這個sql語句在資料分割槽的所有行上計算sale列的標準偏差。
恰當地宣告分割槽子句和開窗子句，可以在不同的粗粒度水平或細粒度水平上來計算標準偏差。還有很多其它的統計函式可以用來計算統計矩陣，如stddev_samp計算累積取樣標準差，stddev_pop計算總標準偏差等。
listagg
listagg函式能夠將來自多個行中的列值轉化為列表格式。例如，如果你要把部門所有員工的名字連起來，那可以使用這個函式將所有名字放到一個列表中。listagg (string, separator) within group (order-by-clause) over (partition-by-clause)
select listagg(country,',')
within group (order by country desc)
from (
select distinct country from sales_fact
order by country
);
listagg函式的一個侷限就是，它的結果強制為varchar2資料型別的最大長度。從12c開始，varchar2資料型別的最大長度從4000位元組增加到32767節字。
--listagg結果字串的長度限制
select length(acol) from (
select listagg(object_name) within group (order by null) acol
from all_objects where rownum<359);
select length(acol) from (
select listagg(object_name) within group (order by null) acol
from all_objects where rownum<359);
效能調優
分析函式所提供的高效率通常使用它們成為重寫效能不佳的查詢的有效工具。但是相應的，你有時候也需要對分析函式進行除錯。對於這一點來說，我們必須要了解分析函式和執行計劃，分析和謂語以及索引策略方面你需要知道一些很有用的事實。
SQL> create or replace view max_5_weeks_vm as
2 select sf.country,sf.product,sf.region,sf.year,sf.week,sf.sale,
3 max(sf.sale) over(
4 partition by sf.product,sf.country,sf.region,sf.year
5 order by sf.year,sf.week
6 rows between 2 preceding and 2 following
7 ) max_weeks_5
8 from sales_fact sf;
檢視已建立。
SQL> set autotrace traceonly
SQL> select v.year,v.week,v.sale,v.max_weeks_5 from max_5_weeks_vm v
2 where v.country in ('Australia') and v.product='Xtend Memory'
3 and v.region='Australia' and v.year=2000 and v.week<14
4 order by v.year,v.week;
已選擇9行。
執行計劃
----------------------------------------------------------
Plan hash value: 3282346358
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 174 | 311 (1)| 00:00:04 |
|* 1 | VIEW | MAX_5_WEEKS_VM | 3 | 174 | 311 (1)| 00:00:04 |
| 2 | WINDOW SORT | | 3 | 198 | 311 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| SALES_FACT | 3 | 198 | 310 (1)| 00:00:04 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V"."WEEK"<14)
3 - filter("SF"."PRODUCT"='Xtend Memory' AND "SF"."COUNTRY"='Australia'
AND "SF"."REGION"='Australia' AND "SF"."YEAR"=2000)
執行計劃
這個sql語句執行計劃，分４步，從第４步開始執行，直到第一步。
１. sales_fact表使用全表掃描訪問路徑進行訪問。
２. product,country,region和year列上的篩選謂詞被用來篩選出所需的資料行。
３. 在第３步中所篩選出來的資料行上就用分析函式。
４. 在這些分析函式執行完後用用week列上的謂詞。
基於成本的優化器不會為分析函式分配或計算成本，sql語句的成本計算並未考慮分析函式的成本。
謂詞
謂詞應該儘可能性早地應用於表上來減少結果集以獲得更好的效能。資料行必須儘早進行篩選，從而可以在相對較少的資料行上應用分析函式。在執行分析函式時謂詞安全性是需要考慮的很重要的一個方面，因為並不是所有的謂詞都能夠在分析函式之前應用。
上面定義了 max_5_weeks_vm檢視並通過一個含有country,product,region,year和week列上謂詞的SQL語句來訪問該檢視。執行計劃顯示在第３步中應用了下面的篩選謂詞：filter("SF"."PRODUCT"='Xtend Memory' AND "SF"."COUNTRY"='Australia' AND "SF"."REGION"='Australia' AND "SF"."YEAR"=2000)，然而filter("V"."WEEK"<14)並沒有在第３步中應用，而只是在第一步中應用了，表明這個謂詞是在第２步的視窗排序步驟中執行完分析函式以後應用的。除了這個week列上的謂詞以外，其他謂詞都被推進了檢視中。那些謂詞的篩選也都在分析函式執行之前進行。
分割槽列上的謂詞在執行分析函式之前應用，因為一般來說，分割槽列上的謂詞可以很安全地推入到檢視中，但分析函式語法中order by子句中的列不能被安全地前推，因為跨行引用需要訪問同一分割槽中的其它資料行，即使這些資料行並不在最終的結果集中返回。
索引
SQL> create index sales_fact_cp on sales_fact(country,product);
索引已建立。
SQL> select v.year,v.week,v.sale,v.max_weeks_5 from max_5_weeks_vm v
2 where v.country in ('Australia') and v.product='Xtend Memory'
3 and v.region='Australia' and v.year=2000 and v.week<14
4 order by v.year,v.week;
已選擇9行。
執行計劃
----------------------------------------------------------
Plan hash value: 1538189358
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 232 | 107 (1)| 00:00:02 |
|* 1 | VIEW | MAX_5_WEEKS_VM | 4 | 232 | 107 (1)| 00:00:02 |
| 2 | WINDOW SORT | | 4 | 264 | 107 (1)| 00:00:02 |
|* 3 | TABLE ACCESS BY INDEX ROWID| SALES_FACT | 4 | 264 | 106 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | SALES_FACT_CP | 118 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V"."WEEK"<14)
3 - filter("SF"."REGION"='Australia' AND "SF"."YEAR"=2000)
4 - access("SF"."COUNTRY"='Australia' AND "SF"."PRODUCT"='Xtend Memory')
好的索引選擇策略是與表訪問步驟中的謂詞相匹配的，分割槽列上的謂詞被前推到檢視中，並且這些謂詞在執行分析函式之前應用。因此，如果SQL語句使用這些謂詞的話，可能對分割槽列進行索引是更好的方法。
上面的例子中，在country和product列上增加了一個新索引，執行計劃第４步顯示使用了基於索引的訪問。謂詞資訊部分顯示所有４個分割槽列上的謂詞都在執行分析函式之前的第４步和第３步中應用。但week列上的謂詞直到執行計劃中的第１步才進行了應用。因此，將week列加入索引是沒有用的，因為直到分析函式執行完成後才會應用這一列上的謂詞。
高階話題
１. 動態分析語句
２. 分析函式的巢狀
３. 並行
４. PGA大小
動態SQL
關於分析sql語句的一個普遍問題是可否在分割槽或排序列上使用繫結變數，答案是不可以，可果想要靈活地動態修改或排序列，需要使用動態sql語句。
如果只是動態調整分割槽列，那麼可以建立一個儲存過程包來獲取儲存過程中的邏輯。
create or replace procedure analytic_dynamic_prc(part_col_string varchar2,
v_country varchar2,
v_product varchar2) is
type numtab is table of number(18, 2) index by binary_integer;
l_year numtab;
l_week numtab;
l_sale numtab;
l_rank numtab;
l_sql_string varchar2(512);
begin
l_sql_string := '
select * from (
select year,week,sale,
rank() over(
partition by ' || part_col_string || '
order by sale desc
) sales_rank
from sales_fact=
where county in (' || chr(39) || v_country || chr(39) || ')
and product= ' || chr(39) || v_product || chr(39) || '
order by product,country,year,week
) where sales_rank <= 10
order by 1,4';
execute immediate l_sql_string bulk collect
into l_year, l_week, l_sale, l_rank;
for i in 1 .. l_year.count loop
dbms_output.put_line(l_year(i) || ' |' || l_week(i) || ' |' ||
l_sale(i) || ' |' || l_rank(i));
end loop;
end;
/
SQL> create or replace procedure analytic_dynamic_prc(part_col_string varchar2,
2 v_country varchar2,
3 v_product varchar2) is
4 type numtab is table of number(18, 2) index by binary_integer;
5 l_year numtab;
6 l_week numtab;
7 l_sale numtab;
8 l_rank numtab;
9 l_sql_string varchar2(512);
10
11 begin
12 l_sql_string := '
13 select * from (
14 select year,week,sale,
15 rank() over(
16 partition by ' || part_col_string || '
17 order by sale desc
18 ) sales_rank
19 from sales_fact
20 where country in (' || chr(39) || v_country || chr(39) || ')
21 and product= ' || chr(39) || v_product || chr(39) || '
22 order by product,country,year,week
23 ) where sales_rank <= 10
24 order by 1,4';
25 execute immediate l_sql_string bulk collect
26 into l_year, l_week, l_sale, l_rank;
27 for i in 1 .. l_year.count loop
28 dbms_output.put_line(l_year(i) || ' |' || l_week(i) || ' |' ||
29 l_sale(i) || ' |' || l_rank(i));
30 end loop;
31 end;
32 /
過程已建立。
SQL> set serveroutpu on
SQL> exec analytic_dynamic_prc ('product,country,region','Australia','Xtend Memory');
1998 |48 |172.56 |9
2000 |46 |246.74 |3
2000 |21 |187.48 |5
2000 |43 |179.12 |7
2000 |34 |178.52 |8
2001 |16 |278.44 |1
2001 |4 |256.7 |2
2001 |21 |233.7 |4
2001 |48 |182.96 |6
2001 |30 |162.91 |10
2001 |14 |162.91 |10
PL/SQL 過程已成功完成。
SQL> exec analytic_dynamic_prc ('product,country,region,year','Australia','Xtend Memory');
1998 |48 |172.56 |1
1998 |10 |117.76 |2
1998 |18 |117.56 |3
1998 |23 |117.56 |3
1998 |26 |117.56 |3
1998 |38 |115.84 |6
1998 |42 |115.84 |6
1998 |39 |115.84 |6
1998 |34 |115.44 |9
1998 |52 |86.38 |10
1999 |17 |148.12 |1
1999 |47 |147.78 |2
1999 |15 |135.1 |3
PL/SQL 過程已成功完成。
巢狀分析函式
分析函式不能進行巢狀，但可以使用子查詢實現巢狀的效果。
內層子查詢給出出現sale列最大值的year和week列的值，外層查詢中的lag函式取出去年sale列的最大值
SQL> select year, week, top_sale_year,
2 lag(top_sale_year) over (order by year desc) prev_top_sale_yer
3 from (
4 select distinct
5 first_value(year) over(
6 partition by product, country,region,year
7 order by sale desc
8 rows between unbounded preceding and unbounded following
9 ) year,
10 first_value(week) over(
11 partition by product, country,region,year
12 order by sale desc
13 rows between unbounded preceding and unbounded following
14 ) week,
15 first_value(sale) over(
16 partition by product, country,region,year
17 order by sale desc
18 rows between unbounded preceding and unbounded following
19 ) top_sale_year
20 from sales_fact
21 where country in('Australia') and product='Xtend Memory'
22 )
23 order by year,week;
YEAR WEEK TOP_SALE_YEAR PREV_TOP_SALE_YER
---------- ---------- ------------- -----------------
1998 48 172.56 148.12
1999 17 148.12 246.74
2000 46 246.74 278.44
2001 16 278.44
lag和first_value函式的分割槽子句是不同的，分析函式first_value用來在由分割槽列product,country,region及year指定的分割槽上計算sale列的最大值，而lag是獲取僅宣告瞭order by year desc排序子句的前一年sale列的第一行。
並行
通過在sql語句中宣告parallel提示或在物件級設定並行度，分析函式也可是並行的。如果你有大量的資料需要通過分析函式處理，並行是一個很好的選擇。使用多層級巢狀的SQL語句也可以從並行中受益。
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 118 | 4602 | 110 (4)| 00:00:02 |
| 1 | SORT ORDER BY | | 118 | 4602 | 110 (4)| 00:00:02 |
| 2 | WINDOW SORT | | 118 | 4602 | 110 (4)| 00:00:02 |
| 3 | VIEW | | 118 | 4602 | 108 (2)| 00:00:02 |
| 4 | HASH UNIQUE | | 118 | 7552 | 108 (2)| 00:00:02 |
| 5 | WINDOW SORT | | 118 | 7552 | 108 (2)| 00:00:02 |
| 6 | TABLE ACCESS BY INDEX ROWID| SALES_FACT | 118 | 7552 | 106 (0)| 00:00:02 |
|* 7 | INDEX RANGE SCAN | SALES_FACT_CP | 118 | | 3 (0)| 00:00:01 |
上面是巢狀分析函式中SQL查詢使用並行的執行計劃。在這個計算中有兩個window運算，因為SQL語句中巢狀了lag和first_value分析函式。
在並行活動PQ slaves之間資料行的最優分佈對於維護功能的正確性是很關鍵的，這由oracle資料庫自動進行處理。
PGA大小
大多數與分析函式相關的運算都是在程式的程式共享區PGA中執行的，為了得到最優的效能，有個足夠大的記憶體區以避免程式使用硬碟執行分析函式是很重要的。
資料庫初始化引數PGA_AGGREGATE_TARGET(PGAT)控制著PGA的最大大小。預設情況下，一個序列程式最大可以分配到PGA的PGAT值的５%，對於並行程式，最大限制為30%。將PGAT保持在一個較高的值對於提高分析函式的效能是很重要的。
總結：
可以使用分析函式簡化複雜的sql語句，分析函式為我們提供了一種全新的思維方式，能句與分割槽和開窗子句相結合從而簡化複雜的SQL語句，可以解決很多效能問題。
相關文章
- Oracle學習筆記（6）——函式Oracle筆記函式
- async函式學習筆記。函式筆記
- 生成函式 學習筆記函式筆記
- Golang學習筆記-1.6 函式Golang筆記函式
- JavaScript學習筆記 - 原生函式JavaScript筆記函式
- MYSQL學習筆記14: 函式MySql筆記函式
- python學習筆記（六）——函式Python筆記函式
- TS學習筆記（四）：函式筆記函式
- 深度學習——loss函式的學習筆記深度學習函式筆記
- MYSQL學習筆記7: 聚合函式MySql筆記函式
- C++學習筆記（二）——函式C++筆記函式
- OpenCV學習筆記（4）——mixChannels函式OpenCV筆記函式
- OpenCV學習筆記（5）——normalize函式OpenCV筆記ORM函式
- Flutter學習筆記（4）--Dart函式Flutter筆記Dart函式
- js純函式學習筆記（一）JS函式筆記
- c語言學習筆記===函式C語言筆記函式
- MYSQL學習筆記15: 數值函式MySql筆記函式
- PHP 第八週函式學習筆記PHP函式筆記
- 學習筆記：javascript中的Generator函式筆記JavaScript函式
- JavaScript學習筆記（七）—— 再說函式JavaScript筆記函式
- 工作學習筆記（三）to_char函式筆記函式
- pandas之常用基本函式學習筆記函式筆記
- 莫比烏斯函式 - 學習筆記函式筆記
- oracle學習筆記《一》Oracle筆記
- ES6學習筆記(三)【函式，物件】筆記函式物件
- Solidity語言學習筆記————28、純函式Solid筆記函式
- Solidity語言學習筆記————26、回退函式Solid筆記函式
- Python學習筆記_函式_匯入模組Python筆記函式
- python程式設計學習筆記⑦-1函式Python程式設計筆記函式
- day10學習筆記之函式上筆記函式
- PHP 第十五週函式學習筆記PHP函式筆記
- Matlab學習筆記（1）——imshow函式的使用Matlab筆記函式
- 好程式設計師學習筆記：函式程式設計師筆記函式
- JavaScript學習筆記（二）——函式和陣列JavaScript筆記函式陣列
- hive學習筆記之七：內建函式Hive筆記函式
- 【Go】Go語言學習筆記-2-函式Go筆記函式
- Solidity語言學習筆記————27、檢視函式Solid筆記函式
- Solidity語言學習筆記————18、字串和函式Solid筆記字串函式
- Solidity語言學習筆記————30、函式過載Solid筆記函式