Window Functions (PostgreSQL)

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result. 1

PARTITION BY and WINDOW clauses

  SELECT
    category,
    count(*) OVER (PARTITION BY category) as category_count,
    count(*) OVER () as total_count
  FROM posts
  ORDER BY category;

will output both the count for each category, and the total amount of rows: 

   category | category_count | total_count
  ----------+----------------+------------
          1 |              2 |          3
          1 |              2 |          3
          3 |              1 |          3

you can also define aliases for the window frames. 

  SELECT
    category,
    count(*) OVER (w1) as category_count,
    count(*) OVER (w2) as total_count
  FROM posts
  WINDOW
    w1 as (PARTITION BY category),
    w2 as ()
  ORDER BY category;

Introducing some useful functions

ROW_NUMBER

  SELECT
    category,
    row_number() OVER (PARTITION BY category) as category_count,
    count(*) OVER () as total_count
  FROM posts
  ORDER BY category;

LEAD

The LEAD() function is used to retrieve data from the next row.

LAG

The LAG() function is used to retrieve data from the previous row.

ORDER BY

FIRST_VALUE() and LAST_VALUE()

RANK

DENSE_RANK

CUME_DIST

The CUME_DIST function computes the fraction of partition rows that are less than or equal to the current row and its peers. (Ferrari and Pirozzi 2023, 165) 

  SELECT
    x,
    CUME_DIST() OVER w 
  FROM (SELECT generate_series(1,10) AS x)
  WINDOW w AS (ORDER BY x);

  -- Outputs:
   x  | cume_dist 
  ----+-----------
    1 |       0.1
    2 |       0.2
    3 |       0.3
    4 |       0.4
    5 |       0.5
    6 |       0.6
    7 |       0.7
    8 |       0.8
    9 |       0.9
   10 |         1

NTILE

Using Advanced Statement Window Functions

Value Functions

References:

Ferrari, Luca, and Enrico Pirozzi. 2023. Learn Postgresql: Use, Manage, and Build Secure and Scalable Databases with Postgresql 16. 2nd ed. Packt Publishing Ltd.

Footnotes:

1

Source: PostgreSQL Docs