Oracle 用秩分析函数计算差值百分比

 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 DOCID        NOT NULL NUMBER(19)
 REVISIONID       NOT NULL NUMBER(19)
 PACPERUNIT         NUMBER(19,4)
 WACLASTUPDATE         VARCHAR2(50)
 WEEK          VARCHAR2(100)
 MINPROFITPERCENT        NUMBER(19,4)
 MODIFIEDBY         VARCHAR2(4000 CHAR)
 DATECREATED         TIMESTAMP(6)
 MINIMUMPRICE         NUMBER(19,4)
 CASHFILLQUANTITY        NUMBER(19,4)
 AWPLASTUPDATE         VARCHAR2(50)
 PACLASTUPDATE         VARCHAR2(50)
 COST          NUMBER(19,4)
 FILLINGFEE         NUMBER(19,4)
 DRUGCODE         VARCHAR2(100)
 COSTLASTUPDATE         VARCHAR2(50)
 DATEMODIFIED         TIMESTAMP(6)
 CALENDARMONTH         VARCHAR2(100)
 PRICINGQUANTITY        NUMBER(19,4)
 CREATEDBY         VARCHAR2(4000 CHAR)
 WAC          NUMBER(19,4)
 AWPPERUNIT         NUMBER(19,4)
 ROUNDING         NUMBER(19,4)

with cc as 
(SELECT ((c.currentcost - p.previouscost) / c.currentcost) * 100 changeper,
         c.drugcodecs,
         c.costlastupdatec,
         p.costlastupdatep,
         c.currentcost,
         p.previouscost
    FROM (SELECT costlastupdate costlastupdatec,
                 cost currentcost,
                 drugcode drugcodecs,
                 rank() over(partition BY drugcode ORDER BY to_date(costlastupdate, 'mm/dd/yyyy') DESC, rownum) rnk
            FROM DRUGPRICINGATTRIBUTES
           where drugcode in
                 (select code from drugmaster where isanchor = 'Y')) c
    JOIN (SELECT costlastupdate costlastupdatep,
                cost previouscost,
                drugcode,
                rank() over(partition BY drugcode ORDER BY to_date(costlastupdate, 'mm/dd/yyyy') DESC, rownum) rnk
           FROM DRUGPRICINGATTRIBUTES
          where 1 = 1
               --drugcode in (select code from drugmaster where isanchor='Y')
            and (drugcode, to_date(costlastupdate, 'mm/dd/yyyy')) NOT IN
                (SELECT drugcode, max(to_date(costlastupdate, 'mm/dd/yyyy'))
                   FROM DRUGPRICINGATTRIBUTES
                  where drugcode in
                        (select code from drugmaster where isanchor = 'Y')
                  GROUP BY drugcode)) p
      ON (c.rnk = 1 and p.rnk = 1 and c.drugcodecs = p.drugcode))
   select * from cc;

 CHANGEPER DRUGCODECS  COSTLASTUPDATEC COSTLASTUPDATEP CURRENTCOST PREVIOUSCOST
---------- ----------- -------------- --------------   ----------- ------------
38.6728811 00074455290 01/05/2015     08/26/2014      .8153       .5
4.89982061 00078035934 01/12/2015     12/16/2014     4.6267      4.4
66.6363636 00378232101 01/05/2015     09/30/2014        .11    .0367
7.51288976 68382035406 12/20/2014     07/14/2014      .4073    .3767

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
 140  consistent gets
   0  physical reads
   0  redo size
       1213  bytes sent via SQL*Net to client
 524  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   5  sorts (memory)
   0  sorts (disk)
   4  rows processed

SQL> drop table DRUGPRICINGATTRIBUTES purge;

Table dropped.

SQL>
SQL> create table DRUGPRICINGATTRIBUTES (
  2    costlastupdate date,
  3    cost int,
  4    drugcode varchar2(10)
  5  );

Table created.

SQL>
SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate,10,'A');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-1,11,'A');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-2,13,'A');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-3,17,'A');

1 row created.

SQL>
SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate,14,'B');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-10,17,'B');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-20,12,'B');

1 row created.

SQL> insert into DRUGPRICINGATTRIBUTES values ( sysdate-30,22,'B');

1 row created.

SQL>
SQL> select * from DRUGPRICINGATTRIBUTES order by drugcode, costlastupdate desc;

COSTLASTU       COST DRUGCODE
--------- ---------- ----------
06-APR-16         10 A
05-APR-16         11 A
04-APR-16         13 A
03-APR-16         17 A
06-APR-16         14 B
27-MAR-16         17 B
17-MAR-16         12 B
07-MAR-16         22 B

8 rows selected.

SQL>
SQL> select *
  2  from (
  3  select d.*,
  4         first_value(cost) over ( partition BY drugcode ORDER BY costlastupdate DESC) n1,
  5         row_number() over ( partition BY drugcode ORDER BY costlastupdate DESC ) as r
  6  from DRUGPRICINGATTRIBUTES d
  7  )
  8  where r = 2;

COSTLASTU       COST DRUGCODE           N1          R
--------- ---------- ---------- ---------- ----------
05-APR-16         11 A                  10          2
27-MAR-16         17 B                  14          2

2 rows selected.

SQL>
SQL>