Oracle分析函數(shù)用法詳解
一、概述
OLAP的系統(tǒng)(即Online Aanalyse Process)一般用于系統(tǒng)決策使用。通常和數(shù)據(jù)倉庫、數(shù)據(jù)分析、數(shù)據(jù)挖掘等概念聯(lián)系在一起。這些系統(tǒng)的特點是數(shù)據(jù)量大，對實時響應的要求不高或者根本不關注這方面的要求，以查詢、統(tǒng)計操作為主。
我們來看看下面的幾個典型例子：
①查找上一年度各個銷售區(qū)域排名前10的員工
②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區(qū)域
④查找上一年度銷售最好和最差的產(chǎn)品
我們看看上面的幾個例子就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：
- 需要對同樣的數(shù)據(jù)進行不同級別的聚合操作
- 需要在表內將多條數(shù)據(jù)和同一條數(shù)據(jù)進行多次的比較
- 需要在排序完的結果集上進行額外的過濾操作
1、分析函數(shù)和聚合函數(shù)的不同之處是什么？
普通的聚合函數(shù)用group by分組，每個分組返回一個統(tǒng)計值，而分析函數(shù)采用partition by分組，并且每組每行都可以返回一個統(tǒng)計值。
2、分析函數(shù)的形式
分析函數(shù)帶有一個開窗函數(shù)over()，包含三個分析子句:分組(partition by), 排序(order by), 窗口(rows) ，他們的使用形式如下：
function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);
- function_name()：函數(shù)名稱
- argument：參數(shù)
- over( )：開窗函數(shù)
- partition_Clause：分區(qū)子句，數(shù)據(jù)記錄集分組，group by...
- order by_Clause：排序子句，數(shù)據(jù)記錄集排序，order by...
- windowing_Clause：開窗子句，定義分析函數(shù)在操作行的集合，三種開窗方式：rows、range、Specifying
注：使用開窗子句時一定要有排序子句！?。?/p>
3、OVER解析
OVER解析作用是告訴SQL引擎：按區(qū)域對數(shù)據(jù)進行分區(qū)，然后累積每個區(qū)域每個客戶的訂單總額(sum(sum(o.tot_sales)))。
①Over函數(shù)指明在那些字段上做分析，其內跟Partition by表示對數(shù)據(jù)進行分組。注意Partition by可以有多個字段。
②Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，還有諸如Rank，Dense_rank等。
4、Oracle分析函數(shù)簡單實例：
-- 按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額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;
二、分析函數(shù)：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函數(shù)為每條記錄產(chǎn)生一個從1開始至n的自然數(shù)，n的值可能小于等于記錄的總數(shù)。這3個函數(shù)的唯一區(qū)別在于當碰到相同數(shù)據(jù)時的排名策略。
- row_number： 返回一個唯一的值，當碰到相同數(shù)據(jù)時，排名按照記錄集中記錄的順序依次遞增。
- dense_rank： 返回一個唯一的值，當碰到相同數(shù)據(jù)時，此時所有相同數(shù)據(jù)的排名都是一樣的。first、last ：從DENSE_RANK返回的集合中取出排在最后面的一個值的行
- rank： 返回一個唯一的值，當碰到相同的數(shù)據(jù)時，此時所有相同數(shù)據(jù)的排名是一樣的，同時會在最后一條相同記錄和下一條不同記錄的排名之間空出排名。
①ROW_NUMBER：12345
②DENSE_RANK：12223
③RANK：12225
-- ①對所有客戶按訂單總額進行排名
-- ②按區(qū)域和客戶訂單總額進行排名
-- ③找出訂單總額排名前13位的客戶
-- ④找出訂單總額最高、最低的客戶
-- ⑤找出訂單總額排名前25%的客戶
-- 篩選排名前12位的客戶, table : user_order
-- 1.對所有客戶按訂單總額進行排名, 使用rownum , rownum = 13,14 的數(shù)據(jù)跟 12 的數(shù)據(jù)一樣, 但是被漏掉了
select rownum, tmptb.* from (select * from user_order order by CUSTOMER_sales desc) tmptb where rownum <= 12;
-- 2.按區(qū)域和客戶訂單總額進行排名 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_number from user_order group by region_id, customer_id;
三、分析函數(shù)：Top/Bottom N、First/Last、NTile
-- ①對所有客戶按訂單總額進行排名
-- ②按區(qū)域和客戶訂單總額進行排名
-- ③找出訂單總額排名前13位的客戶
-- ④找出訂單總額最高、最低的客戶
-- ⑤找出訂單總額排名前25%的客戶
-- 此處 null 被排到第一位 , 可以加 nulls last 把null的數(shù)據(jù)放到最后
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 */) rank from user_order group 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;-- 找出每個區(qū)域訂單總額排名前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;四、匯總
- 匯總
- 滾動匯總
- 分區(qū)滾動匯總
- 當前記錄和后一條記錄
- 分區(qū)匯總
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 函數(shù)的作用是用于當存在多個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) last from user_order group by customer_id;
-- 出訂單總額排名前1/5的客戶 ntile
-- 1.將數(shù)據(jù)分成5塊
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;
-- 2.提取 tile=1 的數(shù)據(jù)
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_month from 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_month from 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函數(shù)可以在一次查詢中取出同一字段的前n行的數(shù)據(jù)和后n行的值。這種操作可以使用對相同表的表連接來實現(xiàn)，不過使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一個參數(shù)是列名，
第二個參數(shù)是偏移的offset，
第三個參數(shù)是超出記錄窗口時的默認值。
-- ①列出每月的訂單總額以及全年的訂單總額
-- ②列出每月的訂單總額以及截至到當前月的訂單總額
-- ③列出上個月、當月、下一月的訂單總額以及全年的訂單總額
-- ④列出每天的營業(yè)額及一周來的總營業(yè)額
-- ⑤列出每天的營業(yè)額及一周來每天的平均營業(yè)額
-- ①通過指定一批記錄：例如從當前記錄開始直至某個部分的最后一條記錄結束
-- ②通過指定一個時間間隔：例如在交易日之前的前30天
-- ③通過指定一個范圍值：例如所有占到當前交易量總額5%的記錄
-- 列出每月的訂單總額以及全年的訂單總額
1.實現(xiàn)方法1
select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over (order by month rows between unbounded preceding and unbounded following) total_sales from orders group by month;
2.實現(xiàn)方法2
select month, sum(tot_sales) month_sales, sum(sum(tot_sales)) over(/*order by month*/) all_sales -- 加上Order by month , 則數(shù)逐條記錄遞增 from orders group by month;
-- 列出每月的訂單總額以及截至到當前月的訂單總額
1.實現(xiàn)方法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_sales from orders group by month;
2.實現(xiàn)方法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;
-- 有時可能是針對全年的數(shù)據(jù)求平均值，有時會是針對截至到當前的所有數(shù)據(jù)求平均值。很簡單，只需要將：
-- sum(sum(tot_sales))換成avg(sum(tot_sales))即可。
-- 統(tǒng)計當天銷售額和五天內的平均銷售額 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_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個月的銷售平均值
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_tmp where year = 2001 and region_id = 6 group 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_sales from orders_tmp where year = 2001 and region_id = 6 group 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的值必須為數(shù)值或數(shù)值型字段。
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_ratio from orders where year = 2001 group by region_id, salesperson_id order by region_id, salesperson_id;
到此這篇關于Oracle分析函數(shù)的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持本站。
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信