Oracle PL/SQL/Analytical Functions/CUME DIST — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 12:55, 26 мая 2010
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>