Chapter 10. Window (Analytical) Functions

According to the SQL specification, window functions (also known as analytical functions) are a kind of aggregation, but one that does not filter the result set of a query. The rows of aggregated data are mixed with the query result set.

The window functions are used with the OVER clause. They may appear only in the SELECT list or the ORDER BY clause of a query.

Besides the OVER clause, Firebird window functions may be partitioned and ordered.

Syntax 

   |<window-function> ::=
   |  <window-function-name> ([<expr> [, <expr> ...]]) OVER <window-specification>
   | 
   |<window-function-name> ::=
   |    <aggregate-function>
   |  | <ranking-function>
   |  | <navigational-function>
   | 
   |<ranking-function> ::=
   |  RANK | DENSE_RANK | ROW_NUMBER
   | 
   |<navigational-function>
   |  LEAD | LAG | FIRST_VALUE | LAST_VALUE | NTH_VALUE
   | 
   |<window-specification> ::=
   |  ( [ <window-partition> ] [ <window-order> ] )
   | 
   |<window-partition> ::=
   |  [PARTITION BY <expr> [, <expr> ...]]
   | 
   |<window-order> ::=
   |  [ORDER BY
   |    <expr> [<direction>] [<nulls placement>]
   |    [, <expr> [<direction>] [<nulls placement>] ...]
   | 
   |<direction> ::= {ASC | DESC}
   | 
   |<nulls placement> ::= NULLS {FIRST | LAST}

Table 10.1. Window Function Arguments
ArgumentDescription

expr

Expression. May contain a table column, constant, variable, expression, scalar or aggregate function. Window functions are not allowed as an expression.

aggregate_function

An aggregate function used as a window function

10.1. Aggregate Functions as Window Functions

All aggregate functions can be used as window functions, by adding the OVER clause.

Imagine a table EMPLOYEE with columns ID, NAME and SALARY, and the need to show each employee with his respective salary and the percentage of his salary over the payroll.

A normal query could achieve this, as follows:

  |select
  |    id,
  |    department,
  |    salary,
  |    salary / (select sum(salary) from employee) portion
  |  from employee
  |  order by id;

Results 

  |id  department  salary  portion
  |--  ----------  ------  ----------
  |1   R & D        10.00      0.2040
  |2   SALES        12.00      0.2448
  |3   SALES         8.00      0.1632
  |4   R & D         9.00      0.1836
  |5   R & D        10.00      0.2040

The query is repetitive and lengthy to run, especially if EMPLOYEE happens to be a complex view.

The same query could be specified in a much faster and more elegant way using a window function:

  |select
  |    id,
  |    department,
  |    salary,
  |    salary / sum(salary) OVER () portion
  |  from employee
  |  order by id;

Here, sum(salary) over () is computed with the sum of all SALARY from the query (the EMPLOYEE table).