Analytic functions versus aggregate functions

Like aggregate functions, analytic functions return aggregate results, but analytics do not group the result set.

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.

Analytic queries generally run faster and use fewer resources than aggregate queries.

Aggregate functions Analytic functions
Return a single summary value. Return the same number of rows as the input.
Define the groups of rows on which they operate through the SQL GROUP BY clause. Define the groups of rows on which they operate through window partition and window frame clauses.

Examples

The examples below contrast the aggregate function COUNT with its analytic counterpart COUNT. The examples use the employees table as defined below:

CREATE TABLE employees(emp_no INT, dept_no INT);
INSERT INTO employees VALUES(1, 10);
INSERT INTO employees VALUES(2, 30);
INSERT INTO employees VALUES(3, 30);
INSERT INTO employees VALUES(4, 10);
INSERT INTO employees VALUES(5, 30);
INSERT INTO employees VALUES(6, 20);
INSERT INTO employees VALUES(7, 20);
INSERT INTO employees VALUES(8, 20);
INSERT INTO employees VALUES(9, 20);
INSERT INTO employees VALUES(10, 20);
INSERT INTO employees VALUES(11, 20);
COMMIT;

When you query this table, the following result set returns:

=> SELECT * FROM employees ORDER BY emp_no;
 emp_no | dept_no
--------+---------
      1 |      10
      2 |      30
      3 |      30
      4 |      10
      5 |      30
      6 |      20
      7 |      20
      8 |      20
      9 |      20
     10 |      20
     11 |      20
(11 rows)

Below, two queries use the COUNT function to count the number of employees in each department. The query on the left uses aggregate function COUNT; the query on the right uses analytic function COUNT:

Aggregate COUNT Analytics COUNT
=> SELECT dept_no, COUNT(*) AS emp_count
    FROM employees
    GROUP BY dept_no ORDER BY dept_no;

=> SELECT emp_no, dept_no, COUNT(*) OVER(
     PARTITION BY dept_no
     ORDER BY emp_no) AS emp_count
     FROM employees;

 dept_no | emp_count
---------+-----------
      10 |         2
      20 |         6
      30 |         3
(3 rows)

 emp_no | dept_no | emp_count
--------+---------+-----------
      1 |      10 |         1
      4 |      10 |         2
------------------------------
      6 |      20 |         1
      7 |      20 |         2
      8 |      20 |         3
      9 |      20 |         4
     10 |      20 |         5
     11 |      20 |         6
------------------------------
      2 |      30 |         1
      3 |      30 |         2
      5 |      30 |         3
(11 rows)
Aggregate function COUNT returns one row per department for the number of employees in that department. Within each dept_no partition analytic function COUNT returns a cumulative count of employees. The count is ordered by emp_no, as specified by the ORDER BY clause.

See also