Oracle PL/SQL/Analytical Functions/CUME DIST

Материал из SQL эксперт
Перейти к: навигация, поиск

CUME_DIST(): calculate the position of a specified value relative to a group of values

   <source lang="sql">

SQL> CREATE TABLE all_sales (

 2    year INTEGER,
 3    month INTEGER,
 4    prd_type_id INTEGER,
 5    emp_id INTEGER ,
 6    amount NUMBER(8, 2)
 7  );

Table created. SQL> SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,1    ,1          ,21    ,16034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,2    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,3    ,2          ,21    ,20167.83);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,4    ,2          ,21    ,25056.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,5    ,2          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,6    ,1          ,21    ,15564.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,7    ,1          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,8    ,1          ,21    ,16434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,9    ,1          ,21    ,19654.57);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,10   ,1          ,21    ,21764.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2006,12   ,2          ,21    ,10034.64);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,22    ,16634.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,1    ,2          ,21    ,26034.84);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,2    ,1          ,21    ,12644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,3    ,1          ,21    ,NULL);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,4    ,1          ,21    ,25026.45);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,5    ,1          ,21    ,17212.66);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,6    ,1          ,21    ,15564.26);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,7    ,2          ,21    ,62654.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,8    ,2          ,21    ,26434.82);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,9    ,2          ,21    ,15644.65);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,10   ,2          ,21    ,21264.19);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,11   ,1          ,21    ,13026.73);

1 row created. SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)

 2                  values(2005,12   ,1          ,21    ,10032.64);

1 row created. SQL> SQL> select * from all_sales;

     YEAR      MONTH PRD_TYPE_ID     EMP_ID     AMOUNT

---------- ----------- ---------- ----------
     2006          1           1         21   16034.84
     2006          2           1         21   15644.65
     2006          3           2         21   20167.83
     2006          4           2         21   25056.45
     2006          5           2         21
     2006          6           1         21   15564.66
     2006          7           1         21   15644.65
     2006          8           1         21   16434.82
     2006          9           1         21   19654.57
     2006         10           1         21   21764.19
     2006         11           1         21   13026.73
     2006         12           2         21   10034.64
     2005          1           2         22   16634.84
     2005          1           2         21   26034.84
     2005          2           1         21   12644.65
     2005          3           1         21
     2005          4           1         21   25026.45
     2005          5           1         21   17212.66
     2005          6           1         21   15564.26
     2005          7           2         21   62654.82
     2005          8           2         21   26434.82
     2005          9           2         21   15644.65
     2005         10           2         21   21264.19
     2005         11           1         21   13026.73
     2005         12           1         21   10032.64

25 rows selected. SQL> SQL> --CUME_DIST(): calculate the position of a specified value relative to a group of values; SQL> --CUME_DIST() is short for cumulative distribution. SQL> SQL> SELECT

 2   prd_type_id, SUM(amount),
 3   CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS cume_dist
 4  FROM all_sales
 5  GROUP BY prd_type_id
 6  ORDER BY prd_type_id;

PRD_TYPE_ID SUM(AMOUNT) CUME_DIST


----------- ----------
         1    227276.5         .5
         2   223927.08          1

SQL> SQL> drop table all_sales; Table dropped. SQL> SQL>


 </source>
   
  


cume_dist over

   <source lang="sql">
   

SQL> CREATE TABLE sales(

 2    product_id            NUMBER(6),
 3    cid           NUMBER,
 4    time_id               DATE,
 5    sold         NUMBER(3),
 6    amount                NUMBER(10,2),
 7    cost                  NUMBER(10,2)
 8  );

Table created. SQL> SQL> select product_id,sum(sold),cume_dist () over (order by sum(sold) asc) as cume_dist

 2  from sales
 3  where to_char(time_id, "yyyy-mm") = "2001-06"
 4  group by product_id
 5  order by sum(sold) desc;

no rows selected SQL> SQL> drop table sales; Table dropped.



 </source>
   
  


Rank, Percent_Rank, and Cume_Dist

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    empno              Number(3)  NOT NULL, -- Employee ID
 3    ename              VARCHAR2(10 BYTE),   -- Employee Name
 4    hireDate          DATE,                -- Date Employee Hired
 5    orig_salary        Number(8,2),         -- Orignal Salary
 6    curr_salary        Number(8,2),         -- Current Salary
 7    region             VARCHAR2(1 BYTE)     -- Region where employeed
 8  )
 9  /

Table created. SQL> SQL> SQL> -- prepare data for employee table SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(122,"Alison",to_date("19960321","YYYYMMDD"), 45000,       NULL,       "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(123, "James",to_date("19781212","YYYYMMDD"), 23000,       32000,       "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(104,"Celia",to_date("19821024","YYYYMMDD"), NULL,       58000,        "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(105,"Robert",to_date("19840115","YYYYMMDD"), 31000,      NULL,        "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(116,"Linda", to_date("19870730","YYYYMMDD"), NULL,       53000,       "E")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(117,"David", to_date("19901231","YYYYMMDD"), 78000,       NULL,       "W")
 3  /

1 row created. SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)

 2                values(108,"Jode",  to_date("19960917","YYYYMMDD"), 21000,       29000,       "E")
 3  /

1 row created. SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /
    EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R

---------- --------- ----------- ----------- -
      122 Alison     21-MAR-96       45000             E
      123 James      12-DEC-78       23000       32000 W
      104 Celia      24-OCT-82                   58000 E
      105 Robert     15-JAN-84       31000             W
      116 Linda      30-JUL-87                   53000 E
      117 David      31-DEC-90       78000             W
      108 Jode       17-SEP-96       21000       29000 E

7 rows selected. SQL> SQL> SQL> SQL> --The use of NTILE with a small amount of data like we have done here is poor statistics, but a reasonable database demonstration. To truly deal with NTILE in a statistical sense, we"d have to use a lot more data. SQL> SQL> --What about nulls with the NTILE function? Here is an example using the same query on our Employee table with nulls (Empwnulls): SQL> SQL> SELECT ename, curr_salary sal,

 2    ntile(2) OVER(ORDER BY curr_salary desc) n2,
 3    ntile(3) OVER(ORDER BY curr_salary desc) n3,
 4    ntile(4) OVER(ORDER BY curr_salary desc) n4,
 5    ntile(5) OVER(ORDER BY curr_salary desc) n5,
 6    ntile(6) OVER(ORDER BY curr_salary desc) n6,
 7    ntile(8) OVER(ORDER BY curr_salary desc) n8
 8  FROM employee;

ENAME SAL N2 N3 N4 N5 N6 N8


---------- ---------- ---------- ---------- ---------- ---------- ----------

Alison 1 1 1 1 1 1 Robert 1 1 1 1 1 2 David 1 1 2 2 2 3 Celia 58000 1 2 2 2 3 4 Linda 53000 2 2 3 3 4 5 James 32000 2 3 3 4 5 6 Jode 29000 2 3 4 5 6 7 7 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee; Table dropped. SQL> SQL>


 </source>