Oracle分析函數用法詳解

2022-05-27 14:04:15

一、概述

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) tmptb
where 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_number
from user_order
group by region_id, customer_id;

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

-- 此處 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 */) 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;

-- 找出每個區域訂單總額排名前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) last
from user_order
group 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) tile
from user_order
group 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_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函數可以在一次查詢中取出同一欄位的前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_sales
from 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;

-- 列出每月的訂單總額以及截至到當前月的訂單總額
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_sales
from 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_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的值必須為數值或數值型欄位。

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分析函數的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。