Analytic Functions Vs. Aggregate Functions

What is the Difference Between Analytical Functions and Aggregate Functions?

Like aggregate functions, analytic functions return aggregate results, but analytics do not group the result set. Instead, they return the group value multiple times with each record, allowing further analysis. Let’s understand this in detail.

 Analytical Functions:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the Analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Aggregate Functions:

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

Analytic functions	Aggregate functions
There is no row collapsing while processing rows. Therefore, Return the same number of rows as the input.	Aggregates take "many rows, collapse them into one". Therefore, Return a single summary value.
Analytic queries also generally run faster and use fewer resources.	Slower than Analytical functions.
The groups of rows on which an analytic function operates are defined by window partitioning and window frame clauses	The groups of rows on which an aggregate function operates are defined by the SQL GROUP BY clause.

Let’s understand this with the help of below table:

SQL> SELECT * From EMP;

        ID NAME          DEPT_NO
---------- ---------- ----------
       101 Ravi               10
       102 Rajan              30
       103 Pritesh            30
       104 Manu               10
       105 Gaurav             30
       106 Subho              20
       107 Akhil              20
       108 Neeraj             40
       109 Shiva              20
       110 Aman               10
       111 Nik                20
       112 Sri                40
       113 Shaan              30

13 rows selected.

Now find number of Employees in each Department.

Using Aggregate Function

SQL> SELECT DEPT_NO, Count(*) EmployeeCount
  2  From EMP
  3  Group By DEPT_NO Order By 1;

   DEPT_NO EMPLOYEECOUNT
---------- -------------
        10             3
        20             4
        30             4
        40             2

Using Analytical Function

SQL> SELECT ID, NAME, DEPT_NO,
  2  Count(*) Over (Partition By DEPT_NO ORDER BY ID) As EmployeeCount
  3  FROM EMP;

        ID NAME          DEPT_NO EMPLOYEECOUNT
---------- ---------- ---------- -------------
       101 Ravi               10             1
       104 Manu               10             2
       110 Aman               10             3
       106 Subho              20             1
       107 Akhil              20             2
       109 Shiva              20             3
       111 Nik                20             4
       102 Rajan              30             1
       103 Pritesh            30             2
       105 Gaurav             30             3
       113 Shaan              30             4
       108 Neeraj             40             1
       112 Sri                40             2

13 rows selected.

Now if we Try to do this with Aggregate Function then count will return the same value unless there are duplicate values.

SQL> SELECT ID, NAME, DEPT_NO, Count(*) EmployeeCount
  2  From EMP Group By ID,NAME,DEPT_NO
  3  Order By DEPT_NO;

        ID NAME          DEPT_NO EMPLOYEECOUNT
---------- ---------- ---------- -------------
       110 Aman               10             1
       104 Manu               10             1
       101 Ravi               10             1
       107 Akhil              20             1
       111 Nik                20             1
       109 Shiva              20             1
       106 Subho              20             1
       105 Gaurav             30             1
       103 Pritesh            30             1
       102 Rajan              30             1
       113 Shaan              30             1
       108 Neeraj             40             1
       112 Sri                40             1

13 rows selected.

Conclusion- 

Aggregate function COUNT() returns one row per Department for the number of Employees in that Department (Summary View).

The Analytic function COUNT() returns a count of the number of Employees in each department, as well as which Employee is in each department. Within each partition, the results are sorted on the Employee ID column, which is specified in the OVER order by clause.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.