Oracle : Aggregate cum Analytical Functions

 

I always remembered Max, Min, Avg, Sum, Count as aggregate functions. But actually these are also analytical functions

analytical functions will have following syntax

{analytical function} (arguments) OVER (analytical clause)

Aggregate functions with OVER clause will become analytical functions.. could become more useful thank they already are.

for example AVG could be used as

SELECT AVG(salary) "Average"

  FROM employees;

also as

SELECT manager_id, last_name, hire_date, salary,

       AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date

  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg

  FROM employees

  ORDER BY manager_id, hire_date, salary;

Some of the analytical functions used in my experience are

1. LEAD  - provides access to a row at a given physical offset beyond that position.. for example, we can use this function to display current row value and next row value of same field in single row


2. LAG -- Similar to LEAD, but gives access to previous row information


3. ROW_NUMBER


4. RANK


5. DENSE_RANK