一、概述

OLAP的系統(即Online Aanalyse Process)一般用于系統決策使用。通常和數據倉庫、數據分析、數據挖掘等概念聯系在一起。這些系統的特點是數據量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統計操作為主。

我們來看看下面的幾個典型例子：
①查找上一年度各個銷售區域排名前10的員工
②按區域查找上一年度訂單總額占區域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區域
④查找上一年度銷售最好和最差的產品

我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：

• 需要對同樣的數據進行不同級別的聚合操作
• 需要在表內將多條數據和同一條數據進行多次的比較
• 需要在排序完的結果集上進行額外的過濾操作

1、分析函數和聚合函數的不同之處是什么？

普通的聚合函數用group by分組，每個分組返回一個統計值，而分析函數采用partition by分組，并且每組每行都可以返回一個統計值。

2、分析函數的形式

分析函數帶有一個開窗函數over()，包含三個分析子句:分組(partition by), 排序(order by), 窗口(rows) ，他們的使用形式如下：

function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

• function_name()：函數名稱
• argument：參數
• over( )：開窗函數
• partition_Clause：分區子句，數據記錄集分組，group by...
• order by_Clause：排序子句，數據記錄集排序，order by...
• windowing_Clause：開窗子句，定義分析函數在操作行的集合，三種開窗方式：rows、range、Specifying

注：使用開窗子句時一定要有排序子句！！！

3、OVER解析

OVER解析作用是告訴SQL引擎：按區域對數據進行分區，然后累積每個區域每個客戶的訂單總額(sum(sum(o.tot_sales)))。

①Over函數指明在那些字段上做分析，其內跟Partition by表示對數據進行分組。注意Partition by可以有多個字段。
②Over函數可以和其它聚集函數、分析函數搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。

4、Oracle分析函數簡單實例：

-- 按區域查找上一年度訂單總額占區域訂單總額20%以上的客戶 table : orders_tmp

select * from orders_tmp;

select cust_nbr, region_id, cust_sales, region_sales,  -- 此處可以用tmptb.* , 但不能用 *100 * round(cust_sales / region_sales, 2) || "%" Percent from  (select cust_nbr, region_id,    sum(TOT_SALES) cust_sales,    sum(sum(tot_sales)) over(partition by REGION_ID) as region_sales  from orders_tmp where o.year = 2001 group by CUST_NBR, REGION_ID order by REGION_ID) tmptb where cust_sales > region_sales * 0.2;

二、分析函數：Rank, Dense_rank, row_number，Ntile() 排列

形式：

Rank() Over ([Partition by ] [Order by ] [Nulls First/Last])Dense_rank() Over ([Patition by ] [Order by ] [Nulls First/Last])Row_number() Over ([Partitionby ] [Order by ] [Nulls First/Last])Ntile() Over ([Partition by ] [Order by ])

rank，dense_rank，row_number函數為每條記錄產生一個從1開始至n的自然數，n的值可能小于等于記錄的總數。這3個函數的唯一區別在于當碰到相同數據時的排名策略。

• row_number： 返回一個唯一的值，當碰到相同數據時，排名按照記錄集中記錄的順序依次遞增。
• dense_rank： 返回一個唯一的值，當碰到相同數據時，此時所有相同數據的排名都是一樣的。first、last ：從DENSE_RANK返回的集合中取出排在最后面的一個值的行
• rank： 返回一個唯一的值，當碰到相同的數據時，此時所有相同數據的排名是一樣的，同時會在最后一條相同記錄和下一條不同記錄的排名之間空出排名。

①ROW_NUMBER：12345

②DENSE_RANK：12223

③RANK：12225

-- ①對所有客戶按訂單總額進行排名
-- ②按區域和客戶訂單總額進行排名
-- ③找出訂單總額排名前13位的客戶
-- ④找出訂單總額最高、最低的客戶
-- ⑤找出訂單總額排名前25%的客戶

-- 篩選排名前12位的客戶, table : user_order
-- 1.對所有客戶按訂單總額進行排名, 使用rownum , rownum = 13,14 的數據跟 12 的數據一樣, 但是被漏掉了

select rownum, tmptb.* from  (select * from user_order order by CUSTOMER_sales desc) tmptbwhere rownum <= 12;

-- 2.按區域和客戶訂單總額進行排名 Rank, Dense_rank, row_number

select region_id, customer_id,   sum(customer_sales) total,  rank() over(partition by region_id order by sum(customer_sales) desc) rank,  dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank,  row_number() over(partition by region_id order by sum(customer_sales) desc) row_numberfrom user_ordergroup by region_id, customer_id;

三、分析函數：Top/Bottom N、First/Last、NTile

-- ①對所有客戶按訂單總額進行排名
-- ②按區域和客戶訂單總額進行排名
-- ③找出訂單總額排名前13位的客戶
-- ④找出訂單總額最高、最低的客戶
-- ⑤找出訂單總額排名前25%的客戶

-- 此處 null 被排到第一位 , 可以加 nulls last 把null的數據放到最后

select region_id, customer_id,  sum(customer_sales) cust_sales,  sum(sum(customer_sales)) over(partition by region_id) ran_total,  rank() over(partition by region_id order by sum(customer_sales) desc /* nulls last */) rankfrom user_ordergroup by region_id, customer_id;

-- 找出所有訂單總額排名前3的大客戶

select * from (select region_id,    customer_id,    sum(customer_sales) cust_total,    rank() over(order by sum(customer_sales) desc NULLS LAST) rank  from user_order  group by region_id, customer_id) where rank <= 3;

-- 找出每個區域訂單總額排名前3的大客戶

select *from (select region_id,    customer_id,    sum(customer_sales) cust_total,    sum(sum(customer_sales)) over(partition by region_id) reg_total,    rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank from user_order group by region_id, customer_id)where rank <= 3;

四、匯總

• 匯總
• 滾動匯總
• 分區滾動匯總
• 當前記錄和后一條記錄
• 分區匯總

Sum() Over ([Partition by ] [Order by ])Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And  Following)   Sum() Over ([Partition by ] [Order by ]     Rows Between  Preceding And Current Row)Sum() Over ([Partition by ] [Order by ]     Range Between Interval "" "Day" Preceding    And Interval "" "Day" Following )

五、Min()/Max()：最大值/最小值

形式：

Min()/Max() Keep (Dense_rank First/Last [Partition by ] [Order by ])

• -- min keep first last 找出訂單總額最高、最低的客戶
• -- Min只能用于 dense_rank
• -- min 函數的作用是用于當存在多個First/Last情況下保證返回唯一的記錄, 去掉會出錯
• -- keep的作用。告訴Oracle只保留符合keep條件的記錄。

select    min(customer_id) keep (dense_rank first order by sum(customer_sales) desc) first,   min(customer_id) keep (dense_rank last order by sum(customer_sales) desc) lastfrom user_ordergroup by customer_id;

-- 出訂單總額排名前1/5的客戶 ntile
-- 1.將數據分成5塊

select region_id,customer_id, sum(customer_sales) sales, ntile(5) over(order by sum(customer_sales) desc nulls last) tilefrom user_ordergroup by region_id, customer_id;

-- 2.提取 tile=1 的數據

select * from (select region_id,customer_id,   sum(customer_sales) sales,   ntile(5) over(order by sum(customer_sales) desc nulls last) tile from user_order group by region_id, customer_id)where tile = 1;

-- cust_nbr,month 為主鍵, 去重,只留下month最大的記錄
-- 查找 cust_nbr 相同, month 最大的記錄

select cust_nbr, max(month) keep(dense_rank first order by month desc) max_monthfrom orders_tmp group by cust_nbr;

-- 去重, cust_nbr,month 為主鍵, cust_nbr 相同,只留下month最大的記錄

delete from orders_tmp2 where (cust_nbr, month) not in  (select cust_nbr, max(month) keep(dense_rank first order by month desc) max_monthfrom orders_tmp2 tb group by cust_nbr)

五、first_value/last_value：首記錄/末記錄

形式：

First_value / Last_value(Sum() Over ([Patition by ] [Order by ] Rows Between Preceding And Following ))

六、lag()與lead()：相鄰記錄

Lag(Sum(), 1) Over([Patition by ] [Order by ])

lag和lead函數可以在一次查詢中取出同一字段的前n行的數據和后n行的值。這種操作可以使用對相同表的表連接來實現，不過使用lag和lead有更高的效率。

lag(arg1,arg2,arg3)

第一個參數是列名，

第二個參數是偏移的offset，

第三個參數是超出記錄窗口時的默認值。

-- ①列出每月的訂單總額以及全年的訂單總額
-- ②列出每月的訂單總額以及截至到當前月的訂單總額
-- ③列出上個月、當月、下一月的訂單總額以及全年的訂單總額
-- ④列出每天的營業額及一周來的總營業額
-- ⑤列出每天的營業額及一周來每天的平均營業額

-- ①通過指定一批記錄：例如從當前記錄開始直至某個部分的最后一條記錄結束
-- ②通過指定一個時間間隔：例如在交易日之前的前30天
-- ③通過指定一個范圍值：例如所有占到當前交易量總額5%的記錄

-- 列出每月的訂單總額以及全年的訂單總額
1.實現方法1

select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) total_salesfrom ordersgroup by month;

2.實現方法2

select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over(/*order by month*/) all_sales  -- 加上Order by month , 則數逐條記錄遞增from orders group by month;

-- 列出每月的訂單總額以及截至到當前月的訂單總額
1.實現方法1

select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over(order by month rows between unbounded preceding and current row) current_total_salesfrom orders group by month;

2.實現方法2

select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over(order by month) all_sales  -- 加上Order by month , 則是前面記錄累加到當前記錄from orders group by month;

-- 有時可能是針對全年的數據求平均值，有時會是針對截至到當前的所有數據求平均值。很簡單，只需要將：
-- sum(sum(tot_sales))換成avg(sum(tot_sales))即可。

-- 統計當天銷售額和五天內的平均銷售額 range between interval

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_avgfrom cust_orderwhere 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個月的銷售平均值

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_avgfrom orders_tmpwhere year = 2001 and region_id = 6group by month order by month;

-- 顯示當月的銷售額和上個月的銷售額
-- first_value(sum(tot_sales) over (order by month rows between 1 precedingand 0 following))
-- lag(sum(tot_sales),1)中的1表示以1月為間隔基準, 對應為lead

select  month,     sum(tot_sales) monthly_sales, lag(sum(tot_sales), 1) over (order by month) prev_month_salesfrom orders_tmpwhere year = 2001 and region_id = 6group by month order by month;

七、rollup()、cube()和grouping()：排列組合分組

1)、group by rollup(a, b, c)：
首先會對(a、b、c)進行group by，然后再對(a、b)進行group by，其后再對(a)進行group by，最后對全表進行匯總操作。

2)、group by cube(a, b, c)：
則首先會對(a、b、c)進行group by，然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，最后對全表進行匯總操作。

八、ratio_to_report （）：計算每條記錄在其對應記錄集或其子集中所占的比例。

ratio_to_report(a) over(partition by b) ：求按照b分組后a的值在所屬分組中總值的占比,a的值必須為數值或數值型字段。

Ratio_to_report() 括號中就是分子，over() 括號中就是分母 分母缺省就是整個占比

eg:列出上一年度每個月的銷售總額、年底銷售額以及每個月的銷售額占全年總銷售額的比例：

select region_id, salesperson_id,  sum(tot_sales) sp_sales,  round(ratio_to_report(sum(tot_sales)) over (partition by region_id), 2) sp_ratiofrom orderswhere year = 2001group by region_id, salesperson_idorder by region_id, salesperson_id;

到此這篇關于Oracle分析函數的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。