Mega Code Archive

 

Categories / Oracle PLSQL / Analytical Functions

 

PARTITION BY

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> --PARTITION BY: divide the groups into subgroups. SQL> SQL> SQL> SELECT   2   prd_type_id, month, SUM(amount),   3   RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank   4  FROM all_sales   5  where amount IS NOT NULL   6  GROUP BY prd_type_id, month   7  ORDER BY prd_type_id, month; PRD_TYPE_ID      MONTH SUM(AMOUNT)       RANK ----------- ---------- ----------- ----------           1          1    16034.84          2           1          2     28289.3          1           1          4    25026.45          2           1          5    17212.66          1           1          6    31128.92          1           1          7    15644.65          2           1          8    16434.82          2           1          9    19654.57          1           1         10    21764.19          1           1         11    26053.46          1           1         12    10032.64          2           2          1    42669.68          1           2          3    20167.83          1           2          4    25056.45          1           2          7    62654.82          1           2          8    26434.82          1           2          9    15644.65          2           2         10    21264.19          2           2         12    10034.64          1 19 rows selected. SQL> SQL> drop table all_sales; Table dropped. SQL> SQL>