• Oracle中的分析函數匯總

     更新時間:2022年05月05日 11:38:03   作者:springsnow  
    本文詳細講解了Oracle中的分析函數,文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下

    一、概述

    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;

    image

    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;

    image

    二、分析函數: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個函數的唯一區別在于當碰到相同數據時的排名策略。

    1. row_number: 返回一個唯一的值,當碰到相同數據時,排名按照記錄集中記錄的順序依次遞增。
    2. dense_rank: 返回一個唯一的值,當碰到相同數據時,此時所有相同數據的排名都是一樣的。first、last :從DENSE_RANK返回的集合中取出排在最后面的一個值的行
    3. 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

    -- ①對所有客戶按訂單總額進行排名 
    -- ②按區域和客戶訂單總額進行排名 
    -- ③找出訂單總額排名前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 */) 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分析函數的文章就介紹到這了。希望對大家的學習有所幫助,也希望大家多多支持腳本之家。

    相關文章

    • 生產環境Oracle undo表空間管理實踐

      生產環境Oracle undo表空間管理實踐

      這篇文章主要介紹了生產環境Oracle undo表空間管理實踐,Oracle 數據庫有一種維護信息的方法,用于回滾或撤消對數據庫的更改,下面文章分享更多的相關資料需要的小伙伴可以參考一下
      2022-03-03
    • Oracle 批處理自動備份bat腳本語句的步驟詳解

      Oracle 批處理自動備份bat腳本語句的步驟詳解

      這篇文章主要介紹了Oracle 批處理自動備份bat腳本語句的步驟詳解,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
      2021-01-01
    • Oracle基礎:程序中調用sqlplus的方式

      Oracle基礎:程序中調用sqlplus的方式

      今天小編就為大家分享一篇關于Oracle基礎:程序中調用sqlplus的方式,小編覺得內容挺不錯的,現在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
      2018-12-12
    • ORA-00947:Not enough values (沒有足夠的值)的深入分析

      ORA-00947:Not enough values (沒有足夠的值)的深入分析

      本篇文章是對ORA-00947:Not enough values (沒有足夠的值)的解決方法進行了詳細的分析介紹,需要的朋友參考下
      2013-05-05
    • Oracle通過遞歸查詢父子兄弟節點方法示例

      Oracle通過遞歸查詢父子兄弟節點方法示例

      這篇文章主要給大家介紹了關于Oracle如何通過遞歸查詢父子兄弟節點的相關資料,遞歸查詢對各位程序員來說應該都不陌生,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考借鑒,下面隨著小編來一起學習學習吧。
      2018-01-01
    • Oracle查看和修改連接數(進程/會話/并發等等)

      Oracle查看和修改連接數(進程/會話/并發等等)

      查詢數據庫當前進程的連接數及會話的連接數、并發連接數以及會話情況等等,感興趣的你可以參考下哈,希望可以幫助到你
      2013-03-03
    • ORACLE中的的HINT詳解

      ORACLE中的的HINT詳解

      本篇文章主要介紹了ORACLE中的的HINT詳解,小編覺得挺不錯的,現在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧
      2016-12-12
    • oracle sql 去重復記錄不用distinct如何實現

      oracle sql 去重復記錄不用distinct如何實現

      本文將詳細介紹oracle sql 去重復記錄不用distinct如何實現,需要了解的朋友可以參考下
      2012-11-11
    • Linux下Oracle刪除用戶和表空間的方法

      Linux下Oracle刪除用戶和表空間的方法

      這篇文章主要介紹了Linux下Oracle刪除用戶和表空間的方法,涉及Oracle數據庫用戶和表操作的相關技巧,具有一定參考借鑒價值,需要的朋友可以參考下
      2015-12-12
    • oracle中的decode的使用介紹

      oracle中的decode的使用介紹

      這篇文章主要介紹了oracle中的decode函數的一些使用小技巧,需要的朋友可以參考下
      2013-09-09

    最新評論

    美丽人妻被按摩中出中文字幕