oracle 分析函數總結--僅供參考

/* 
目錄：
1.語法
2.常見函數及用法
  2.1 排序函數
  2.2 wm_concat（）能實現不同行同字段的合并 ！！！！生產環境慎用
  2.3 開窗用法 
  2.4 占比函數ratio_to_report( ) over(partition by  )
  2.5 前一個lag 后一個 lead
  2.6 第一個first_value 最后一個last_value
  2.7 加1連乘再減1   
*/  
  
--1.分析函數的語法 ：函數名（） over (分區 排序 滑動窗口/開窗)
   --1.1.函數指定要在over圈定的數據上做什么操作，   
      --可以做sum(),max()等常見的聚合函數，   
      --也可以用row_number(),first_value()等功能強大的分析函數
   --1.2 分區 限定前面的函數操作的數據集是什么，類似于group by，但是功能比它強大
         用group by 時不做聚合的列就必須參與分組，不能單獨顯示出來。 
   --1.3 排序 指定函數計算的順序，比如取排名，比如累加
   --1.4 開窗 partition by 的基礎上指定參與計算的行
--2.常見的函數及用法
  --2.1 排序函數   
     row_number() 只返回一個結果  123456      
     rank()是跳躍排序，有兩個第二名時接下來就是第四名122456
     dense_rank() 是連續排序，有兩個第二名時仍然跟著第三名122345    
    【比如想獲取每位員工在所在部門的排序】   
    select row_number() over (partition by deptno order by sal desc) no,  
           ename,   
           sal,  
           deptno from emp; 
        NO ENAME             SAL     DEPTNO
       ---------- ---------- ---------- ----------
        1 KING             5000         10
        2 CLARK            2450         10
        3 MILLER           1300         10
 
        1 SCOTT            3000         20
        2 FORD             3000         20
        3 JONES            2975         20
        4 ADAMS            1100         20
        5 SMITH             800         20
 
        1 BLAKE            2850         30
        2 ALLEN            1600         30
        3 TURNER           1500         30
        4 MARTIN           1250         30
        5 WARD             1250         30
        6 JAMES             950         30
   /*分區partition by 限定是按照部門作為一個數據集，這樣就每個部門一個數據集
     row_number() 指明要在數據集合上做排序
     order by 指定按照sal排序 */

     --指定要每個部門的前3
      select *   
          from ( select t.deptno,  
                        t.ename,
                        t.sal,
                        row_number() over (partition by deptno 
                                           order by sal desc) top_no
                   from emp t
                 ) a
         where a.top_no<3;  
 
  --2.2 wm_concat（）能實現不同行同字段的合并 ！！！！生產環境慎用 
     /*場景：2.1已經能統計出每個部門的前3
     現在想將每個部門的前三按照：名字工資，名字工資，名字工資的格式合并為一列。*/
      select a.deptno,wm_concat(ename||sal)  
           ---切記雖然格式上有逗號，但是vm_concat會自動加逗號隔開
      from (
             select t.deptno,t.ename,t.sal,
                    row_number() over (partition by deptno 
                                       order by sal desc) top_no
             from emp t
           ) a
      where a.top_no<3      

    
  --2.3 開窗用法  向前preceding，向前5行 5 preceding ，向前所有unbounded preceding  
                  當前CURRENT ROW，  
                  向后following，向后5行 5 following，向后所有unbounded following   
     /*場景 stock_market表中記錄 
    （d_date 日期，v_symbol 股票代碼，vc_exchange 市場,f_price_close 收盤價） 
     現在要取每個股票的信息及3天均值 前后5天最高值 */ 
 SELECT t.d_date,
        t.vc_symbol,
        t.vc_exchange,
        t.f_price_close,
        --按照股票代碼和市場進行分組 日期進行排序，當前行及向前2行進行avg 
        avg(t.f_price_close) over(PARTITION BY t.vc_symbol, t.vc_exchange      
                                  ORDER BY t.d_date   
                                  ROWS between 2 preceding and CURRENT ROW) avg_price,
        max(t.f_price_close) over(PARTITION BY t.vc_symbol, t.vc_exchange  
                                  ORDER BY t.d_date   
                                  ROWS between 5 preceding and 5  following) max_price
 FROM stock_market t 
 WHERE t.d_date between 20161123-5 and 20161123+5

 --2.4 占比函數ratio_to_report( ) over(partition by  )
  --計算員工工資占所在部門工資總和的百分之幾。
   select ename,deptno,sal,ratio_to_report(sal) over(partition by deptno) from emp;
  --計算員工工資占所有工資總和的百分之幾。
   select ename,deptno,sal,ratio_to_report(sal) over( ) from emp;


--2.5前1 lag 后1  lead
    select t.deptno,t.ename,t.sal,
           lag(sal) over(partition by deptno order by sal desc) d_pre,
           lead(sal) over(partition by deptno order by sal desc) d_flow
    from emp t
     
--2.6 第一個first_value 最后一個last_value
    select t.deptno, 
          t.ename, 
          t.sal,
          first_value(sal) over(partition by deptno   
                                 order by sal desc  
                                 rows between unbounded preceding and unbounded following                                )  as d_first,
          last_value(sal) over(partition by deptno  
                               order by sal desc  
                               rows between unbounded preceding and unbounded following                                 ) as d_last
     from emp t;
 
--2.7日常工作中在計算一段時間累加收益時，將每天的收益+1，然后進行連乘，最后再-1
 --每天算累加收益率      
select 日期,
       客戶號,                 
       power(10,SUM(Sum(Log(10, (1 + NVL(日收益率, 0)))))
       OVER(partation by 客戶號  
            ORDER BY t2.d_date  
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) - 1   累加收益率 
from 客戶收益表
where 日期 between 20150101 and 20150531
group by 日期, 客戶號;


-----一段時間算累累計收益率
  select  客戶號,                 
        power(10,SUM(Log10(1 + NVL(日收益率, 0)))-1 累加收益率
 from 客戶收益表
where 日期 between 20150101 and 20150531
group by 客戶號;