FirebirdSQL logo

Named Windows

The WINDOW clause can be used to explicitly name a window, for example to avoid repetitive or confusing expressions.

A named window can be used

  1. in the OVER clause to reference a window definition, e.g. OVER window_name

  2. as a base window of another named or inline (OVER) window, if it is not a window with a frame (ROWS or RANGE clauses)

    Note

    A window with a base windows cannot have PARTITION BY, nor override the ordering (ORDER BY) of a base window.

Ranking Functions

The ranking functions compute the ordinal rank of a row within the window partition.

These functions can be used with or without partitioning and ordering.However, using them without ordering almost never makes sense.

The ranking functions can be used to create different type of counters.Consider SUM(1) OVER (ORDER BY SALARY) as an example of what they can do, each of them differently.Following is an example query, also comparing with the SUM behavior.

select
    id,
    salary,
    dense_rank() over (order by salary),
    rank() over (order by salary),
    row_number() over (order by salary),
    sum(1) over (order by salary)
  from employee
  order by salary;
Results
id  salary  dense_rank  rank  row_number  sum
--  ------  ----------  ----  ----------  ---
 3    8.00           1     1           1    1
 4    9.00           2     2           2    2
 1   10.00           3     3           3    4
 5   10.00           3     3           4    4
 2   12.00           4     5           5    5

The difference between DENSE_RANK and RANK is that there is a gap related to duplicate rows (relative to the window ordering) only in RANK.DENSE_RANK continues assigning sequential numbers after the duplicate salary.On the other hand, ROW_NUMBER always assigns sequential numbers, even when there are duplicate values.

CUME_DIST()

Relative rank (or, cumulative distribution) of a row within a window partition

Result type

DOUBLE PRECISION

Syntax
CUME_DIST () OVER <window_name_or_spec>

CUME_DIST is calculated as the number of rows preceding or peer of the current row divided by the number of rows in the partition.

In other words, CUME_DIST() OVER <window_name_or_spec> is equivalent to COUNT(*) OVER <window_name_or_spec> / COUNT(*) OVER()

CUME_DIST Examples

select
  id,
  salary,
  cume_dist() over (order by salary)
from employee
order by salary;
Result
id salary cume_dist
-- ------ ---------
 3   8.00       0.2
 4   9.00       0.4
 1  10.00       0.8
 5  10.00       0.8
 2  12.00         1