Is DENSE_RANK an Analytic function or an Aggregate Function?

Is DENSE_RANK an Analytic function or an Aggregate Function?

 

'Analytic Function' was an instant answer whenever this question had been thrown at me. But the answer is DENSE_RANK is both an Analytic and an aggregate function since Oracle 10G. 

DENSE_RANK as an aggregate function,

•   returns the dense rank of the hypothetical row identified by the arguments of the function in an order group of rows


•   The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group


•   the number of arguments must be the same as number of expressions in the order_by_clause and types of the arguments must also be compatible.

The following query returns dense rank of salary 2000 in each department,

  SELECT dept,
                DENSE_RANK (2000) within group (order by salary DESC) DENSE_RNK
    FROM employees
  GROUP BY dept;

 

Notably, the dense_rank will be identified and returned even if such salary does not exist in the department.

For instance when the above query runs against employees table with below set of records dense_rank of salary 2000 in dept 4 will be returned as 4 even though there are no employees earning the salary of 2000

EMPLOYEE_ID	LAST_NAME	SALARY	DEPT
100	Employee100	1000	10
101	Employee101	2000	10
104	Employee104	2000	10
105	Employee105	4000	10
110	Employee110	4000	40
111	Employee111	8000	40
114	Employee114	8000	40
115	Employee115	16000	40

The above query will return, 

 

DEPT    DENSE_RNK 10      2 40      4