Oracle is providing Analytical functions, which are being quite used for daily analysis or for reporting purpose.
In the following example, first 2 columns namely "SEQ","SEQ_DEPT" are created using
ROW_NUMBER( ) function SEQ is for entire table, SEQ_DEPT is for department number i.e. sequence starts for every department,
1) Analytical functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
2) Analytic functions are performed at last, usually query processing starts with
"where clause, join, group by, having, order by then Analytic functions".
3) Analytical functions aggregates the rows based on groups of rows these are differ from aggregates functions, where aggregates functions returns a single row for each group, Analytical functions returns more than a single row for each group i.e. number of rows returns by Analytical functions are as same as number rows in input.
4) Data is split into groups which are called as window and is defined by the analytical_clause.
over partition by columns : here data split into groups based on the columns which are present in the partition clause.
There are several Analytical Functions but here I am specifying a few & which are being used very often.
1) Row_Number()
2) Rank
3) Dense_Rank()
4) Max, Min, Avg, Sum, Count
5) Lag, Lead
6) Range Between, Rows Between
1) Row_Number()
2) Rank
3) Dense_Rank()
4) Max, Min, Avg, Sum, Count
5) Lag, Lead
6) Range Between, Rows Between
1) We use ROW_NUMBER( ) function to generate sequence of natural numbers.
In the following example, first 2 columns namely "SEQ","SEQ_DEPT" are created using
ROW_NUMBER( ) function SEQ is for entire table, SEQ_DEPT is for department number i.e. sequence starts for every department,
----------------------------
2) RANK & DENSE_RANK
Rank function does not gives the sequence numbers when same value is repeated.
EX: Marks 100,99,99,98 Ranks: 1,2,2,4
Where as Dense_Rank function gives the sequence numbers when same value is repeated.
EX: Marks 100,99,99,98 Dense_Ranks: 1,2,2,3
For more details, click on the below link.
---
--------------
3) Max, Min, Avg Functions :
We can find out Max, Min, Avg Salaries w.r.t. Table, Department No. using the following SQL Queries.
4) Lag, Lead :
We can get Previous, Next cell values using LAG, LEAD analytical functions.
Lag : Previous Cell value
Lead : Next Cell Value
Examples of using ANALYTICAL FUNCTIONS
----
-------------------- --------------- ----------------- -------------------------
Lead : Next Cell Value
4) ROWS BETWEEN :
We can use ROWS BETWEEN () function to get the CUMULATIVE (RUNNING TOTALS).
Examples of using ANALYTICAL FUNCTIONS
HOW TO GET ACTUAL VALUES FROM CUMULATIVE VALUES
----
Dropping table & Creating the Same table then inserting data into Table, Table name is "EMP_CUSTOM_TABLE"
--------------------------------------
Inserting data into table using insert statements.
---------------
-------------
-------------
------
PARTITION BY SPLITS THE DATA INTO GROUPS, IN THIS CASE DATA IS SPLITTED INTO GROUPS USING DEPTNO.
---
---
LAG(X, 1,0) --- PREVIOUS VALUE, IF IT DOES NOT EXIST THEN 0.
LEAD(X, 1,0) --- NEXT VALUE, IF IT DOES NOT EXIST THEN 0.
FIRST ROW DOES NOT HAVE PREVIOUS VALUE SO QUERY GENERATES '0' IN THE SAME FOR LAST ROWS TERE IS NO NEXT ROW SO SHOWING '0'.
----
----
WE CAN GET MAX, MIN, AVG, STD, VAR OF SALARY DEPT WISE OR ENTIRE TABLE. HERE WE ARE GETTING SAMPLE OF TWO FUNCTIONS i.e. MAX, MIN.
WE ARE GETTING TOTAL SALARY IN THE TABLE BY USING ANALYTICAL FUNCTION WITHOUT PARTITION.
---------------------
HERE WE ARE GETTING DEPT TOTAL SALARY & RUNNUNG TOTAL OF SALRY WITH IN DEPT.
IF WE SPECIFY ORDER BY THEN IT FOLLOWS RUNNING SUM ELSE TOTAL OF DEPT.
FINALLY AT THE END OF THE DEPT WE ARE GETTING SAME TOTAL SALRY OF THE DEPT. ( It is highlighted in the below screen shot.)
--------------------------------
HIGHLIGHTED ROWS WERE SUMMED UP SINCE BOTH ARE HAVING THE SAME SALARY AND BELONGING TO THE SAME DEPT.
WE ARE APPLYING PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC; IT MEANS SALARIES ARE SAME & BELONGONG TO THE SAME DEPT SO VALUES ARE BEING SUMMED UP.
HERE WE ARE SPECIFYING ENAME IN THE ORDER BY CLAUSE SO VALUES ARE BEING SUMMED UP ACCORDINGLY, IT MEANS DEPT IS SAME BUT ENAMES ARE DIFFERENT.
WE USE LOG(), LEAD() FUNCTIONS TO GET PREVIOUS & NEXT VALUES ACCORDINGLY. HERE WE ARE PARTITION BY 'DEPT', SO WHENEVER WE ARE ENTRING INTO NEW DEPT THEN IRST_VALUE()
LAST_VALUE()
---------------------
----
------ removing duplicates in the select statement ----------------
we can get directly it by using distinct but here want to show how to USE AGGREGATE FUNCTIONS.
-- 1)
describe EMP_CUSTOM_TABLE;
-- -------------------------------------------------------------------------------------------------------------
-- 2)
SELECT * FROM EMP_CUSTOM_TABLE ;
select count(*) as Numer_of_Records FROM EMP_CUSTOM_TABLE ;
-- -------------------------------------------------------------------------------------------------------------
-- 3)
SELECT EMPNO,ENAME, SAL as Salary, DEPTNO,
count(*) OVER () Numer_of_Records,
SUM(SAL) OVER () TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME
DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
-- 4)
SELECT EMPNO,ENAME, SAL, DEPTNO,
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL,
SAL + LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_NEXT_SAL,
SAL + LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_PREV_SAL ,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) +
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_PLUS_PREV_SAL
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
--5)
SELECT ENAME, SAL, CUMLATIVE_SAL,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL
FROM
(SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL ASC );
-- -------------------------------------------------------------------------------------------------------------
-- 6)
SELECT EMPNO,ENAME, DEPTNO,SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 7)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM --- CREATING CUMULATIVE SALARIES AT TSBLE, DEPT LEVEL
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 8)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LEAD,
MIN(SAL) OVER ( PARTITION BY DEPTNO ) MIN_SAL_DEPT,
MAX(SAL) OVER ( PARTITION BY DEPTNO ) MAX_SAL_DEPT,
ROUND(AVG(SAL) OVER ( PARTITION BY DEPTNO ),3) AS AVG_SAL_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 9)
SELECT DEPTNO,
MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL) DEPT_MIN_SAL,
MAX(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL) DEPT_MAX_SAL
, MAX(SAL) MAXSAL
, MIN(SAL) MINSAL
FROM EMP_CUSTOM_TABLE GROUP BY DEPTNO ORDER BY DEPTNO
;
-- -------------------------------------------------------------------------------------------------------------
-- 10)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) RANKS,
RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPT_RANKS,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 11)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LAG
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
-- -------------------------------------------------------------------------------------------------------------
-- 12)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_DEPT,
SUM(SAL) OVER ( ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_TABLE
FROM (SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
-- -------------------------------------------------------------------------------------------------------------
-- 13)
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
-- -------------------------------------------------------------------------------------------------------------
-- 14)
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC) DENSE_RANKS_DEPT
FROM (SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
-- -------------------------------------------------------------------------------------------------------------
-- 15)
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
--16)
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
-- 17)
SELECT EMPNO,ENAME, SAL, DEPTNO,
SUM(SAL) OVER () TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC;
-- -------------------------------------------------------------------------------------------------------------
-- 18)
SELECT EMPNO,ENAME, SAL, DEPTNO,
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL,
SAL + LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_NEXT_SAL,
SAL + LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) CURRENT_PLUS_PREV_SAL ,
LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) +
LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_PLUS_PREV_SAL
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC ;
---------------------------------------------------------------------------------------------------------------
--19)
SELECT ENAME, SAL, CUMLATIVE_SAL,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL
FROM
(SELECT ENAME, SAL, SUM (SAL) OVER (ORDER BY SAL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL ASC );
---------------------------------------------------------------------------------------------------------------
--20)
SELECT EMPNO,ENAME, DEPTNO,SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ;
-- -------------------------------------------------------------------------------------------------------------
-- 21)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL, CUMLATIVE_SAL,CUMLATIVE_SAL_DEPT,
COALESCE ((CUMLATIVE_SAL - LAG (CUMLATIVE_SAL, 1) OVER (ORDER BY CUMLATIVE_SAL)), CUMLATIVE_SAL) AS GETTING_ACTUAL_TABLE,
COALESCE ((CUMLATIVE_SAL_DEPT - LAG (CUMLATIVE_SAL_DEPT, 1) OVER (PARTITION BY
DEPTNO ORDER BY CUMLATIVE_SAL_DEPT)), CUMLATIVE_SAL_DEPT) AS GETTING_ACTUAL_DEPTNO
FROM --- CREATING CUMULATIVE SALARIES AT TABLE, DEPT LEVEL
(SELECT EMPNO,ENAME, DEPTNO,SAL,
SUM (SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL,
SUM (SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMLATIVE_SAL_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
) ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ;
-- -------------------------------------------------------------------------------------------------------------
-- 22)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL, 1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LEAD,
MIN(SAL) OVER ( PARTITION BY DEPTNO ) MIN_SAL_DEPT,
MAX(SAL) OVER ( PARTITION BY DEPTNO ) MAX_SAL_DEPT,
ROUND(AVG(SAL) OVER ( PARTITION BY DEPTNO ),3) AS AVG_SAL_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 23)
SELECT DEPTNO,
MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL) DEPT_MIN_SAL,
MAX(SAL) KEEP (DENSE_RANK LAST ORDER BY SAL) DEPT_MAX_SAL
, MAX(SAL) MAXSAL
, MIN(SAL) MINSAL
FROM EMP_CUSTOM_TABLE GROUP BY DEPTNO ORDER BY DEPTNO
;
-- -------------------------------------------------------------------------------------------------------------
-- 24)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) RANKS,
RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPT_RANKS,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC
;
-- -------------------------------------------------------------------------------------------------------------
-- 25)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
LAG(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) PREV_SAL_DEPT_LAG,
LEAD(SAL,1,0) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC) NEXT_SAL_DEPT_LAG
FROM ( SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
-- -------------------------------------------------------------------------------------------------------------
-- 26)
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_DEPT,
SUM(SAL) OVER ( ORDER BY DEPTNO ASC, SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMULATIVE_TABLE
FROM (SELECT * FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC)
;
-- -------------------------------------------------------------------------------------------------------------
-- 27)
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DENSE_RANKS_DEPT
FROM ( SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
-- -------------------------------------------------------------------------------------------------------------
-- 27)
SELECT * FROM (
SELECT ROWNUM SEQ ,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC ) SEQ_DEPT,
EMPNO,ENAME, DEPTNO,SAL ACTUAL_SAL,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL ASC) DENSE_RANKS_DEPT
FROM (SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC, ENAME ASC))
WHERE DENSE_RANKS_DEPT<=2
;
-- -------------------------------------------------------------------------------------------------------------
--28)
SELECT ENAME,DEPTNO, SAL,
DENSE_RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) DENSE_RANKS,
RANK() OVER ( ORDER BY DEPTNO ASC, SAL DESC ) RANKS
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC, SAL DESC
;
-- -------------------------------------------------------------------------------------------------------------
-- 29)
SELECT ENAME,JOB,DEPTNO, SAL
, SUM(SAL) OVER() AS TOTAL_SAL_TABLE
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC ) AS TOTAL_SAL_DEPT
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC, ENAME ASC ) AS TOTAL_SAL_DEPT_EMP
, SUM(SAL) OVER(PARTITION BY JOB ORDER BY JOB ASC,SAL DESC) AS TOTAL_SAL_JOB
, SUM(SAL) OVER(PARTITION BY JOB ORDER BY JOB ASC,SAL DESC, ENAME ASC) AS TOTAL_SAL_JOB_ENAME
FROM EMP_CUSTOM_TABLE ORDER BY JOB ASC, SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
-- 30)
SELECT * FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO, JOB , SAL DESC ;
---------------------------------------------------------------------------------------------------------------
--31)
SELECT ENAME,JOB,DEPTNO, SAL FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO, JOB , SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
-- 32)
SELECT ENAME,JOB,DEPTNO, SAL
, SUM(SAL) OVER() TOTAL_SAL_TABLE
, SUM(SAL) OVER(PARTITION BY DEPTNO ) AS TOTAL_SAL_DEPT
, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC ) AS RUNNING_TOTAL_SAL_DEPT
--, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO ASC,SAL DESC, ENAME ASC ) AS TOTAL_SAL_DEPT_ENAME
FROM EMP_CUSTOM_TABLE ORDER BY DEPTNO ASC,SAL DESC ;
-- -------------------------------------------------------------------------------------------------------------
-- 33)
SELECT EMPNO,ENAME, SAL, DEPTNO,
SUM(SAL) OVER () TOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO) DEPTTOTALSAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) DEPTTOTALSAL2,
SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC ,ENAME
DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) CUM_SAL_DEPT,
SUM(SAL) OVER (ORDER BY DEPTNO ASC, SAL DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMSAL_TABLE
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC
;
-- -------------------------------------------------------------------------------------------------------------
-- 34)
SELECT EMPNO,ENAME, SAL, DEPTNO
, LAG(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) PREVIOUS_SAL_WITH_IN_DEPT
, LEAD(SAL, 1,0) OVER (PARTITION BY DEPTNO ORDER BY DEPTNO ASC, SAL DESC) NEXT_SAL_WITH_IN_DEPT
FROM EMP_CUSTOM_TABLE
ORDER BY DEPTNO ASC, SAL DESC;
-- ========================================================
-- ***** END - WINDOW FUNCTIONS (ANALYTICAL FUNCTION) *****
-- ========================================================
No comments:
Post a Comment