一.over函數：
用法：over（partition by col1,col2..order by col3,col4)
①Over函數指明在那些字段上做分析，其内跟Partition by表示對資料進行分組。注意Partition by可以有多個字段。
②Over函數可以和其它聚集函數、分析函數搭配，起到不同的作用。例如這裡的SUM，還有諸如Rank，Dense_rank等。
eg：
SQL> select all_sales.*,
2 100 * round(cust_sales / region_sales, 2) || '%' Percent
3 from (select o.cust_nbr customer,
4 o.region_id region,
5 sum(o.tot_sales) cust_sales,
6 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
7 from orders_tmp o
8 where o.year = 2001
9 group by o.region_id, o.cust_nbr) all_sales
10 where all_sales.cust_sales > all_sales.region_sales * 0.2;
CUSTOMER REGION CUST_SALES REGION_SALES PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
4 5 37802 37802 100%
10 6 64315 68065 94%
11 7 12204 12204 100%
分析函數OVER解析：
請注意上面的綠色高亮部分，group by的意圖很明顯：将資料按區域ID，客戶進行分組，那麼Over這一部分有什麼用呢？假如我們隻需要統計每個區域每個客戶的訂單總額，那麼我們隻需要group by o.region_id,o.cust_nbr就夠了。但我們還想在每一行顯示該客戶所在區域的訂單總額，這一點和前面的不同：需要在前面分組的基礎上按區域累加。很顯然group by和sum是無法做到這一點的(因為聚集操作的級别不一樣，前者是對一個客戶，後者是對某個區域的一批客戶)。
這就是over函數的作用了！它的作用是告訴SQL引擎：按區域對資料進行分區，然後累積每個區域每個客戶的訂單總額(sum(sum(o.tot_sales)))。
二.視窗函數：
前面我們介紹的分析函數用于計算/統計一個明确的階段/記錄集，而這裡有部分需求，需要随着周遊記錄集的每一條記錄的同時進行統計。也即是說：統計不止發生一次，而是發生多次。統計不至發生在記錄集形成後，而是發生在記錄集形成的過程中。
1.oracle子句介紹如下：
1）對于全統計或者滾動記錄統計，使用rows between ... preceding and ... following。從字面上猜測它的意思是：在XXX之前和XXX之後的所有記錄。
unbounded preceding and unbouned following的意思針對目前所有記錄的前一條、後一條記錄，也就是表中的所有記錄。
但是rows between 1 preceding and unbounded following) all_sales，實際1在這裡不是從第1條記錄開始的意思，而是指目前記錄的前一條記錄。我們能夠把and unbounded following換成代表目前記錄集curreent row。
eg：列出每月的訂單總額、截至到目前月的訂單總額以及全年的訂單總額，如求平均值則将sum換為avg
SQL> select month,
2 sum(tot_sales) month_sales,
sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
1 610697 610697 6307766
2 428676 1039373 6307766
3 637031 1676404 6307766
2）某一範圍統計，使用 range between interval '2' day preceding and interval '2' day following，查找目前日期的前2天，後2天範圍内的記錄（五天之内）。
eg：統計當天銷售額和五天内的平均銷售額：
select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding
and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
and to_date('31-jul-2001','dd-mon-yyyy')
3）first_value、last_value，用于在視窗記錄集中查找第一條記錄和最後一條記錄。
eg：報表需要顯示目前月、上一個月、後一個月的銷售情況，以及每3個月的銷售平均值
select month,
first_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) prev_month,
sum(tot_sales) monthly_sales,
last_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
and region_id = 6
group by month
order by month;
4）lag函數類似于preceding和following子句，它能夠通過和目前記錄的相對位置而被應用，在比較同一個相鄰的記錄集内兩條相鄰記錄的時候特别有用。
eg：顯示當月的銷售額和上個月的銷售額
elect month,
sum(tot_sales) monthly_sales,
lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year = 2001
and region_id = 6
group by month
order by month;
三.報表函數：
1.對于上面個的全統計每檢索一條記錄就執行一次，它總共執行了12次。這是非常費時的。實際可以用over（）函數替代。
sum(sum(tot_sales)) over(order by month rows between unbounded preceding and unbounded following) win_sales,
等同 sum(sum(tot_sales)) over() rpt_sales
over函數的空括号表示該記錄集的所有記錄都應該被列入統計的範圍，如果使用了partition by則先分區，再依次統計各個分區。
2.RATIO_TO_REPORT函數：
報表函數特(視窗函數)特别适合于報表中需要同時顯示詳細資料和統計資料的情況。例如在銷售報告中經常會出現這樣的需求：列出上一年度每個月的銷售總額、年底銷售額以及每個月的銷售額占全年總銷售額的比例：
方法②：
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round(sum(tot_sales) / sum(sum(tot_sales))
over (partition by region_id), 2) percent_of_region
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
方法③
select region_id, salesperson_id,
sum(tot_sales) sp_sales,
round( ratio_to_report(sum(tot_sales))
over (partition by region_id), 2) sp_ratio
from orders
where year = 2001
group by region_id, salesperson_id
order by region_id, salesperson_id;
Oracle提供的Ratio_to_report函數允許我們計算每條記錄在其對應記錄集或其子集中所占的比例。
四.使用分析函數rand，dense_rank，row_number來為記錄排名：
①ROW_NUMBER：
Row_number函數傳回一個唯一的值，當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增。
②DENSE_RANK：
Dense_rank函數傳回一個唯一的值，除非當碰到相同資料時，此時所有相同資料的排名都是一樣的。
③RANK：
Rank函數傳回一個唯一的值，除非遇到相同的資料時，此時所有相同資料的排名是一樣的，同時會在最後一條相同記錄和下一條不同記錄的排名之間空出排名。
在over函數中order by的前面增加一個分組子句：partition by region_id。則為分組排名，
Partition by 子句在排列函數中的作用是将一個結果集劃分成幾個部分，這樣排列函數就能夠應用于這各個子集。
eg：
各個地區的訂單總額進行排名
SQL> select region_id, customer_id,
sum(customer_sales) total,
2 rank() over(partition by region_id
order by sum(customer_sales) desc) rank,
3 dense_rank() over(partition by region_id
order by sum(customer_sales) desc) dense_rank,
4 row_number() over(partition by region_id
order by sum(customer_sales) desc) row_number
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
5 4 1878275 1 1 1
5 2 1224992 2 2 2
5 5 1169926 3 3 3
6 6 1788836 1 1 1
6 9 1208959 2 2 2
五.分析函數Top/Bottom N、First/Last、NTile
1.空值NUll排名：
對于某些排名，值為null的排名第一，如何解決呢？可以用NULLS LAST/FIRST告訴Oracle讓空值排名最後後第一。
eg：
SQL> select region_id, customer_id,
2 sum(customer_sales) cust_total,
3 sum(sum(customer_sales)) over(partition by region_id) reg_total,
4 rank() over(partition by region_id
order by sum(customer_sales) desc NULLS LAST) rank
5 from user_order
6 group by region_id, customer_id;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
10 26 1808949 6238901 1
10 27 1322747 6238901 2
10 30 1216858 6238901 3
10 28 986964 6238901 4
10 29 903383 6238901 5
10 31 6238901 6
2.Top/Bottom N查詢：
在日常的工作生産中，我們經常碰到這樣的查詢：找出排名前5位的訂單客戶、找出排名前10位的銷售人員等等。可以用1中的rank值取某個範圍的值，eg：where rank <= 3;
3.First/Last排名查詢：
oracle中用first、last函數來解決此類問題
eg：找出訂單總額最多、最少的客戶
SQL> select min(customer_id)
2 keep (dense_rank first order by sum(customer_sales) desc) first,
3 min(customer_id)
4 keep (dense_rank last order by sum(customer_sales) desc) last
5 from user_order
6 group by customer_id;
FIRST LAST
---------- ----------
31 1
解釋：
min函數的作用是用于當存在多個First/Last情況下保證傳回唯一的記錄。
從上面的結果我們已經知道Oracle對排名的結果隻“保留”2條資料，這就是keep的作用。告訴Oracle隻保留符合keep條件的記錄。
dense_rank是告訴Oracle排列的政策，first/last則告訴最終篩選的條件。
4.層次查詢分析函數NTile
eg：找出訂單總額排名前1/5的客戶。
SQL> select region_id,
2 customer_id,
3 ntile(5) over(order by sum(customer_sales) desc) til
4 from user_order
5 group by region_id, customer_id;
REGION_ID CUSTOMER_ID TILE
---------- ----------- ----------
10 31 1
9 25 1
10 26 1
6 6 1
8 18 2
5 2 2
9 23 3
6 9 3
7 11 3
5 3 4
6 8 4
8 16 4
6 7 5
10 29 5
5 1 5
Ntil函數為各個記錄在記錄集中的排名計算比例，我們看到所有的記錄被分成5個等級，那麼假如我們隻需要前1/5的記錄則隻需要截取TILE的值為1的記錄就可以了。假如我們需要排名前25%的記錄(也就是1/4)那麼我們隻需要設定ntile(4)就可以了。
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)