Oracle Analytic functions Summary-for reference only

Tag:wm_concat   rowsbetween    plus 1 1    ratio function     First value last value     before 1 1   

/*  Table of Contents: 1. Syntax 2. Common functions and usages   2.1  sort functions   2.2 wm_concat () enables merging of different rows and fields  !!!! Production environment with caution   2.3  window usage    2.4  ratio function ratio_to_report ( )  over (partition  by  )   2.5  previous lag   lead  2.6  first first_value  The last last_value  2.7  plus 1 multiply and subtract 1   */    --1. Syntax &nbsp for analytic functions;: function name ()  over  (Division   Sort   sliding window/open window)    --1.1. function specifies what to do on the over-delineated data,          --can do common aggregation functions such as SUM (), Max (),          --can also use powerful analytic functions such as row_number (), First_value (), and partitioning   defining the data set of the previous function operation, similar to group  by, but the function is stronger than it           columns that do not aggregate when group by  is used must participate in grouping, cannot be displayed separately.     --1.3  sort   Specify the order of function calculations, such as ranking, such as accumulation    --1.4  window  partition On the basis of  by , specify the line that participates in the calculation--2. Common functions and usage   --2.1  sorting Functions         row_number ()   Returns only one result   123456           rank () is a jump sort, There were two second names, and then there was fourth place. 122456     dense_rank ()   is a sequential sort, with two second names still following the third place 122345          "Like to get the sort of each employee in your department"        select  Row_number ()  over  (PARTITION&NBSP;BY&NBSP;DEPTNO&NBSP;ORDER&NBSP;BY&NBSP;SAL&NBSP;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   /* partitioning partition by  is defined as a data set by department, so that one dataset per department      row_number ()   Indicates the sort to be done on the data collection      order by  Specify to sort by Sal  */     --Specify the front 3      select *    for each department           from  ( select t.deptno,                            t.ename,                         t.sal,                          row_number ()  over  (partition by deptno                                               order by&nbSP;SAL&NBSP;DESC)  top_no                    from emp t                  )  a          Where a.top_no<3;     --2.2 wm_concat () enables merging of different rows and fields  !!!! Production environment with caution       /* scenario: 2.1 has been able to count the former 3      of each department Now I want to merge the first three of each department into one column according to the form of the first name salary, the name salary and the name salary. */      select a.deptno,wm_concat (ename| | SAL)              ---Remember that although there is a comma in the format, vm_ Concat will automatically be separated by commas       from  (              select t.deptno,t.ename,t.sal,                &nbSp;    row_number ()  over  (partition by deptno                                           ORDER&NBSP;BY&NBSP;SAL&NBSP;DESC)  top_no              from emp t           )  a      where a.top_no<3             --2.3  Open Window Usage    forward preceding, forward 5 lines  5 preceding  Forward all unbounded preceding                      Current current row,                      backwards following, 5 lines backwards  5 following, backwards all unbounded following         /* scene  stock_market table Records       (d_date   Date,v_symbol  Stock Code,vc_exchange  Market,f_price_close  Close)        Now to get information about each stock and the 3-day mean   5 days before and after the highest value  */  SELECT t.d_date,         t.vc_symbol,        t.vc_exchange,         t.f_price_close,        --GROUP by stock code and market   The date is sorted, the current line and the forward 2 rows are 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 function Ratio_to_ The report ( )  over   --calculates the percentage of the employee's salary as the sum of the wages in the department.    select ename,deptno,sal,ratio_to_report (SAL)  over (Partition by deptno)  from emp;  --calculates the percentage of employees ' wages as a sum of all wages.    select ename,deptno,sal,ratio_to_report (SAL)  over ( )  from emp;-- 2.5 Front 1 lag  after 1  lead    select t.deptno,t.ename,t.sal,            lag (SAL)  over (partition by deptno  ORDER&NBSP;BY&NBSP;SAL&NBSP;DESC)  d_pre,            lead (SAL)  over (PARTITION&NBSP;BY&NBSP;DEPTNO&NBSP;ORDER&NBSP;BY&NBSP;SAL&NBSP;DESC)  d_The first first_value  of flow    from emp t     --2.6  One last 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 In the daily work in the calculation of cumulative gains in a period of time, will be a daily income of +1, and then to connect, and finally -1 --per day to calculate the cumulative rate of return       select  date,         Customer number,                         power (10,SUM (SUM (Log (10),   (1&NBSP;+&NBSP;NVL (daily rate of return,  0))))        over (partation by   Customer number               order by  t2.d_date              ROWS  Between unbounded preceding and current row))  - 1    cumulative rate of return  from  Customer Income Statement where  Date  between 20150101 and 20150531group by  date,   customer number;-----a period of time to calculate the cumulative rate of return   select   Customer number,                          power (10,sum (Log10 (1&NBSP;+&NBSP;NVL (daily rate of return,  0) ) -1  Cumulative yield  from  Customer Income statement where  Date  between 20150101 and 20150531group  by  customer number;

Summary of Oracle Analytic functions-for reference only