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

標籤：wm_concat   rowsbetween   加1連乘減1   佔比函數   第一個值最後一個值   前1後1   

/* 目錄：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 排序 指定Function Compute的順序，比如取排名，比如累加   --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 20150531group by 日期, 客戶號;-----一段時間算累累計收益率  select  客戶號,                         power(10,SUM(Log10(1 + NVL(日收益率, 0)))-1 累加收益率 from 客戶收益表where 日期 between 20150101 and 20150531group by 客戶號;

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