Analytic Functions in Oracle

SELECT AVG(sal) FROM   emp;

SELECT deptno, AVG(sal) FROM   emp

GROUP BY deptno

ORDER BY deptno;

SELECT empno, deptno, sal,

       AVG(sal) OVER () AS avg_sal

FROM   emp;

SELECT empno, deptno, sal,

       AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal

FROM   emp;

SELECT empno, deptno, sal, 

       FIRST_VALUE(sal IGNORE NULLS) OVER (PARTITION BY deptno) AS first_sal_in_dept

FROM   emp;

-- How many days after the first hire of each department were the next

-- employees hired?

SELECT empno, deptno, hiredate - FIRST_VALUE(hiredate)

OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP

FROM emp

WHERE deptno IN (20, 30)

ORDER BY deptno, DAY_GAP;

     EMPNO     DEPTNO    DAY_GAP

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

      7369         20          0

      7566         20        106

      7902         20        351

      7788         20        722

      7876         20        756

SELECT empno,deptno,sal,

       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"

FROM   emp;

SELECT empno,       deptno,       sal,

       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"

FROM   emp;

The FIRST and LAST functions can be used to return the first or last value from an ordered sequence

SELECT empno,       deptno,       sal,

       MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",

       MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"

FROM   emp

ORDER BY deptno, sal;

The FIRST_VALUE analytic function is similar to the FIRST analytic function, allowing you to return the first result from an ordered set.

SELECT empno,       deptno,       sal,

       FIRST_VALUE(sal) IGNORE NULLS 

         OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept

FROM   emp;

The LAST_VALUE analytic function is similar to the LAST analytic function, allowing you to return the last result from an ordered set.

SELECT empno, deptno, sal, LAST_VALUE(sal) IGNORE NULLS

         OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept

FROM   emp;

SELECT empno,       deptno,       sal,

       LAST_VALUE(sal) IGNORE NULLS

         OVER (PARTITION BY deptno ORDER BY sal RANGE BETWEEN

           UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept

FROM   emp;

The LAG function is used to access data from a previous row

The LEAD function is used to return data from the next row

SELECT empno,       ename,       job,       sal,

       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev

FROM   emp;

SELECT empno,       ename,       job,       sal,

       Lead(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev

FROM   emp;

LISTAGG allows us to order the elements in the concatenated list.

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno;