PostgreSQL Window Functions

Summary: in this tutorial, you’ll learn about PostgreSQL window functions and how to use them effectively to construct powerful queries.

Getting Started with PostgreSQL Window Functions #

In PostgreSQL, a window function allows you to calculate across a set of rows related to the current row.

If it sounds confusing to you, let’s take an example to make the concept more clear.

Suppose you want to retrieve the product name, price, and highest price from the products table:

If you use the MAX aggregate function, you’ll get a single row:

SELECT
  MAX(price)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   max
---------
 2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To get the product and price for each row together with the max price, you can use the MAX window function:

SELECT
  product_name,
  price,
  MAX(price) OVER ()
FROM
  products
ORDER BY
  price DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        |  price  |   max
----------------------------+---------+---------
 Samsung QN900C Neo QLED    | 2999.99 | 2999.99
 LG G3 OLED                 | 2499.99 | 2999.99
 Sony Bravia XR A95K        | 2499.99 | 2999.99
 LG OLED TV C3              | 1999.99 | 2999.99
 Samsung Galaxy Z Fold 5    | 1799.99 | 2999.99
 Lenovo ThinkPad X1 Carbon  | 1599.99 | 2999.99
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the OVER clause makes the MAX function a window function.

The MAX window function returns the maximum price for each row in the products table.

Unlike the MAX aggregate function, the MAX window function does not group rows but retains individual rows.

In practice, you’ll find the window functions helpful for queries like calculating running total, ranking, and moving averages.

Note that analytic function is another term often used to refer to window function.

PostgreSQL Window Function Syntax #

Here’s the basic syntax for a window function:

function_name (expression) OVER (
    [PARTITION BY expression_list]
    [ORDER BY expression_list]
    [frame_clause]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• function_name: The window function name such as MAX.
• expression: The column or expression you want the window function to calculate.
• PARTITION BY: This optional clause groups the rows into partitions where the window function applies.
• ORDER BY: This clause defines the order of rows within each partition. Note that it differs from the ORDER BY clause in the SELECT statement.
• frame_clause: This clause defines the subset of rows within the partition for the window function to consider.

PostgreSQL Window Function List #

There are three kinds of window function in PostgreSQL: