PostgreSQL Window Functions with practical examples

In this article we will try to elaborate on the usage of window functions in 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.

To sum this up we can say that:

• Define a set of rows per row within the result-set.
• Perform an operation on those rows
• Return the result of this operation as the value of each row.

Which rows will be in the window is defined by the window definition -which is also the main syntactic distinction is that window function- defined by the OVER clause. The operation which we want to apply will be defined alongside the OVER clause.

Overall when we see an OVER clause then the function is a window function else it will simply be an aggregation or scalar function.

Syntax – F.O.P.O.

In terms of syntax for window functions, when you are getting started with them F.O.P.O. is a nice way to remember the components you need.

• F: The function component, essentially how we want to treat our data. (AVG(), RANK() etc).
• O: The OVER() clause, the way to invoke the functions
• P: The PARTITION BY() clause, the way we want to create subgroups – essentially our GROUP BY for the window functions.
• O: ORDER BY()ascending or descending order of data based on the desired column(s).

func() over (PARTITION BY column ORDER BY column)

There are several window function provided in PostgreSQL. The complete list can be found here.

Dataset

We will be using the Northwind Database for our example for which the ERD you can found below.

Examples

Let’s start working with them to get familiarized. Assume that we want to get some data from the product (name, and unit price e.g) and also we want to calculate the average unit price for the category of the particular product. Most likely we would create a CTE where we would calculate the the average unit price per category and then join it with the rest of the results. Or perhaps we could do a complex group by as well.

Utilizing window function that would be something like:

 SELECT product_id,
       product_name,
       unit_price,
       category_id,
       AVG(unit_price) OVER(PARTITION BY category_id) AS average_unit_price_in_category
FROM products
WHERE not discontinued::bool;

And this will produce the below outcome:

This SELECT statement may be syntactically confusing initially so let us try to break it down:

• As in any SELECT statement we have the columns we want to display in the output.
• Then we have the AVG() function called with the unit price.
• This is what we want to calculate over a subset of rows (per category essentially). The PARTITION does this for us. So as the name implies it will partition the results over category_id.
• The aggregate function (AVG(unit_price)) will now be calculated for each partition/group.

Let us perform one more similar example. Assume now that we want to produce the count of orders for each customer, along with the count of orders for the country of the customer. Again this can happen with a CTE easily and join with the outer query to get the desired outcomes. This can also happen with window functions.

SELECT
    DISTINCT
    c.customer_id,
    c.company_name,
    c.country,
    COUNT(order_id) OVER (PARTITION BY c.customer_id) as customer_orders,
    COUNT(order_id) OVER (PARTITION BY country) as country_orders
FROM orders
JOIN customers c on orders.customer_id = c.customer_id;

The logic is similar to the above but this time we are using the COUNT() function. In the first occurrence we partition over customers and in the second over countries. Note the DISTINCT in this case. Since we do not have an actual GROUP BY() clause this will produce one row per order per customer if we do not use DISTINCT.

The outcome will be:

RANK()

The previously examples were mostly to demonstrate the syntax and utility of window functions. Let us now move on more practical examples and functionality.
Going back to our first example (average unit price per category), assume that you want to know how each product’s price ranks against the rest of the product prices within the same category. Also let’s say that we want to assign the highest ranks to the most expensive product. We can do this utilizing the RANK() function.

SELECT product_id,
       product_name,
       unit_price,
       category_id,
       AVG(unit_price) OVER(PARTITION BY category_id) AS average_unit_price_in_category,
       RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as product_price_ranked_in_category
FROM products
WHERE not discontinued::bool;

This is the same as earlier with the addition of the latest line in selector. What we define there, is that we want to give a rank (RANK()) to each record/product of the same category (PARTITION BY category_id) based on their unit price (ORDER BY unit_price) and that the most expensive product should be ranked as first (DESC).

This is the outcome:

Notice how rows with the same value in unit price within the same category have the same rank and how the order is skipped to the actual rank in the next row (When you have for example two records with rank 2 the next one will have rank 4 and so on so forth). For each category the RANK() creates rankings and also allows us to keep track of duplicates. This is extremely powerful considering also how easy this construct is.

DENSE_RANK()

In the case when we need to keep the ranking complete and not skip any ranks when we have same ranks within the partition we can use DENSE_RANK(). This is the same as RANK() except the skipping ranks part. Let ‘s check them side by side.

SELECT product_id,
       product_name,
       unit_price,
       category_id,
       RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as product_price_ranked_in_category,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as dense_product_price_ranked_in_category
FROM products
WHERE not discontinued::bool;

Output:

By checking the last two columns we can see that the difference lies on the ranking.

ROW_NUMBER()

When we simple need a row ordering within the partition we can use ROW_NUMBER(). This will simple follow the ordering and provide a “ranking”.

SELECT product_id,
       product_name,
       unit_price,
       category_id,
       RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as product_price_ranked_in_category,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as dense_product_price_ranked_in_category,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as row_number_in_category
FROM products
WHERE not discontinued::bool;

Check the last three columns to see how RANK(), DENSE_RANK(), and ROW_NUMBER() behave over the same partitioning.

PERCENT_RANK()

The PERCENT_RANK() function returns a percentile ranking number which ranges from zero to one and calculates the percentile ranking of rows in a result set.

Syntax is similar, let’s still use the same example with unit price and categories.

SELECT product_id,
       product_name,
       unit_price,
       category_id,
       RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as product_price_ranked_in_category,
       PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as percent_rank
FROM products
WHERE not discontinued::bool;

This will produce the following:

Let’s examine category 1 for example. What this tells us is that the item with ID=38 is cheapest than 0% of the items in this category. The item with ID=75 is cheapest than 100% of the items in this category. The items with IDS 34 and 67 are cheapest that 75% of the items in this category and so on so forth.

CUME_DIST()

CUME_DIST() is a window function which calculates the cumulative distribution of a row value within the partition. The output of this function is a number which represents the number of rows with values less than or equal to the current row’s value divided by the total number of rows.

Assume the example below where in the CTE we simple calculate the order per country, where country here is the customer’s country.

WITH country_orders (country, num_of_orders) AS (
    SELECT country,
           count(order_id)
    FROM orders
    JOIN customers c on orders.customer_id = c.customer_id
    GROUP BY country
)
SELECT country,
       num_of_orders,
       CUME_DIST() OVER (
           ORDER BY num_of_orders
           )
FROM country_orders;

Note: We are skipping PARTITION_BY here since we already have created the subgroups in CTE. We could of course do the same with PARTITION_BY and a DISTINCT without the CTE.

The output is:

Norway has 6 orders and is the only country with 6 orders (1 record). The total number of rows in result set is 21. The CUME_DIST()will perform: 1 / 21 = 0.04 …
Poland has 7 orders. The countries that have equal or less orders than 7 are 2 (Norway and Poland). Thus the calculation will be : 2 / 21 = 0.09….

Same logic applies to the rest of the rows in the result set.

NTILE()

This function allows as to do some bucketing/binning in the result-set. The number of buckets is provided as argument. It will attempt to break the results/or partition of the results into a specified number of buckets, which will have approximately the same number of rows.

Let us go back to the unit price example.

SELECT product_id,
       product_name,
       unit_price,
       category_id,
       NTILE(4) OVER (PARTITION BY category_id ORDER BY unit_price DESC ) as price_group
FROM products
WHERE not discontinued::bool;

For each category we want here to create subgroups (4 in our case) which will be specified from the ordering of unit price.

Checking the price_group column we can see SQL attempts to make 4 groups of similar size for each category.

LAG()

Very often used in interview questions, LAG()allows us access the previous row of the current row within a working set. Simply putted we can access data from the previous row. This is extremely powerful when we want to find differences between current and previous rows.

It can accept an offset where we can define the number of previous rows from which to access data, defaults to 1 if skipped, and it can also accept a default value which is useful for the records that previous rows does not exist (first row in the working set), if skipped it will return NULL. We should be careful with the default since this will affect the calculation which we want to perform later.

Let us attempt to calculate how much time is passing on average for each customer to make an order.

Note: We are making the assumption, that the company opened at 01-01-1995 and all the customers were registered on this day. That means that for every client’s first order we should calculate from 01-01-1995 the passed days.

SELECT
    customer_id,
    order_date,
    LAG(order_date, 1 ,'1995-01-01') OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order,
    order_date - LAG(order_date, 1 ,'1995-01-01') OVER (PARTITION BY customer_id ORDER BY order_date) as difference_in_days
FROM orders
ORDER BY customer_id;

The second and third column are not needed for our example. We display them for demo purposes and to have LAG() a bit more readable.

We are getting the order_date and we deduct the previous (LAG() with 1 offset) order_date of the same customer (PARTITION_BY) based on the order of order_date. In the case we do not have a previous record we will assign 01-01-1995 as the default value. The output of the above is:

From this point it is pretty straight forward to get the average day span for each customer to perform an order. We can use either some CTE either sub-queries. We will also add the days of the latest and earliest orders, and the LAG() calculation without default value to demonstrate how this may affect the output.

Sub-query:

SELECT customer_id,
       max(order_date) as latest_order,
       min(order_date) as earliest_order,
       avg(difference_in_days_with_default) as avg_difference_in_days_with_default,
       avg(difference_in_days) as avg_difference_in_days
FROM (
        SELECT
            customer_id,
            order_date,
            order_date - LAG(order_date, 1 ,'1995-01-01') OVER (PARTITION BY customer_id ORDER BY order_date) as difference_in_days_with_default,
            order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) as difference_in_days
        FROM orders
        ORDER BY customer_id
     ) t
GROUP BY customer_id;

CTE:

WITH customer_order_stats (customer_id, order_date, difference_in_days_with_default, difference_in_days) AS (
    SELECT
        customer_id,
        order_date,
        order_date - LAG(order_date, 1 ,'1995-01-01') OVER (PARTITION BY customer_id ORDER BY order_date) as difference_in_days_with_default,
        order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) as difference_in_days
    FROM orders
    ORDER BY customer_id
)
SELECT customer_id,
       max(order_date) as latest_order,
       min(order_date) as earliest_order,
       avg(difference_in_days_with_default) as avg_difference_in_days_with_default,
       avg(difference_in_days) as avg_difference_in_days
FROM customer_order_stats
GROUP BY customer_id;

Both of which produce: