PostgreSQL NTH_VALUE Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL NTH_VALUE() window function to access the n^th row in a window frame.

Getting Started with the PostgreSQL NTH_VALUE Window Function #

The NTH_VALUE() is a window function that allows you to access the n^th row within a window frame.

Here’s the syntax of the NTH_VALUE() window function:

NTH_VALUE(value, n) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

value: This is a column or expression to retrieve the value from the n^th row.
n: Specifies the row number within the window frame from which the NTH_VALUE function retrieves the value.
OVER: Defines the window over which the NTH_VALUE function applies.
PARTITION BY: Divides the result set into partitions by the partition_expression. The partition_expression can be one or more columns to group rows into partitions. If you omit the PARTITION BY, the function treats the entire result set as a single partition.
ORDER BY: Sorts the rows within each partition.
frame_clause: Defines the window frame for the function.

The NTH_VALUE() function returns the value from the nth row of a window frame. If the n^th row does not exist, the NTH_VALUE() function returns NULL.

The NTH_VALUE function is helpful for comparing a specific row with other rows within the same window frame.

PostgreSQL NTH_VALUE Window Function Examples #

Let’s explore some examples of using the NTH_VALUE function with the sales table and sales_by_years view:

The sales Table

SELECT * FROM sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The sales_by_years view:

SELECT * FROM sales_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Using PostgreSQL NTH_VALUE Window Function to Compare Yearly Sales #

The following SELECT statement uses the NTH_VALUE function to compare the sales amount of each year with the sales amount of the first year:

SELECT
  year,
  amount,
  NTH_VALUE(amount, 1) OVER (ORDER BY year) AS first_year_amount
FROM
  sales_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year |  amount   | first_year_amount
------+-----------+-------------------
 2019 | 513700.00 |         513700.00
 2020 | 490000.00 |         513700.00
 2021 | 512000.00 |         513700.00
 2022 | 483200.00 |         513700.00
 2023 | 491000.00 |         513700.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

The ORDER BY clause sorts the rows in the sales_by_years view by the year in ascending order.
The NTH_VALUE(amount, 1) retrieves the amount value of the first row.

The query returns the sales amount for each year alongside the sales amount from the first year, making it easy to compare.

Calculating Sales Change Percentage vs. First Year #

The following SELECT statement uses the NTH_VALUE function to calculate the percentage change in sales compared to the first year:

SELECT
  year,
  amount,
  NTH_VALUE(amount, 1) OVER (ORDER BY year) AS first_year_amount,
  ROUND(
    (
      amount - NTH_VALUE(amount, 1) OVER (ORDER BY year)
    ) / NTH_VALUE(amount, 1) OVER (ORDER BY year) 
    * 100,
    2
  ) AS sales_change_percentage
FROM
  sales_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year |  amount   | first_year_amount | sales_change_percentage
------+-----------+-------------------+-------------------------
 2019 | 513700.00 |         513700.00 |                    0.00
 2020 | 490000.00 |         513700.00 |                   -4.61
 2021 | 512000.00 |         513700.00 |                   -0.33
 2022 | 483200.00 |         513700.00 |                   -5.94
 2023 | 491000.00 |         513700.00 |                   -4.42

How it works:

The NTH_VALUE function returns the amount of the first row (year).
The expression (amount - NTH_VALUE(amount, 1) OVER (ORDER BY year)) / NTH_VALUE(amount, 1) OVER (ORDER BY year) * 100 calculates the percentage change in sales compared to the first year.
The ROUND() function rounds the percentage change to two decimal places.

Comparing Sales by Products #

The following SELECT statement uses the NTH_VALUE() window function to compare the sales of different products year by year with the first year’s sales for each product:

SELECT 
    year,
    product,
    amount,
    NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year) AS first_year_amount
FROM sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year | product |  amount   | first_year_amount
------+---------+-----------+-------------------
 2019 | Galaxy  | 296000.00 |         296000.00
 2020 | Galaxy  | 273000.00 |         296000.00
 2021 | Galaxy  | 272000.00 |         296000.00
 2022 | Galaxy  | 258200.00 |         296000.00
 2023 | Galaxy  | 260000.00 |         296000.00
 2019 | iPhone  | 217700.00 |         217700.00
 2020 | iPhone  | 217000.00 |         217700.00
 2021 | iPhone  | 240000.00 |         217700.00
 2022 | iPhone  | 225000.00 |         217700.00
 2023 | iPhone  | 231000.00 |         217700.00

How it works:

PARTITION BY: Divides the result set into partitions based on the product.
ORDER BY year: Sorts the rows within each partition by year.
NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year): Returns the amount from the first year for the same partition (or product).

Comparing Sales vs. First Year by Product in Percentage #

The following SELECT statement uses the NTH_VALUE() window function to calculate the percentage change in sales for each product compared to the first year:

SELECT 
    year,
    product,
    amount,
    NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year) AS first_year_amount,
    ROUND((amount - NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year)) / NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year) * 100, 2) AS sales_change_percentage
FROM 
    sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year | product |  amount   | first_year_amount | sales_change_percentage
------+---------+-----------+-------------------+-------------------------
 2019 | Galaxy  | 296000.00 |         296000.00 |                    0.00
 2020 | Galaxy  | 273000.00 |         296000.00 |                   -7.77
 2021 | Galaxy  | 272000.00 |         296000.00 |                   -8.11
 2022 | Galaxy  | 258200.00 |         296000.00 |                  -12.77
 2023 | Galaxy  | 260000.00 |         296000.00 |                  -12.16
 2019 | iPhone  | 217700.00 |         217700.00 |                    0.00
 2020 | iPhone  | 217000.00 |         217700.00 |                   -0.32
 2021 | iPhone  | 240000.00 |         217700.00 |                   10.24
 2022 | iPhone  | 225000.00 |         217700.00 |                    3.35
 2023 | iPhone  | 231000.00 |         217700.00 |                    6.11

How it works:

PARTITION BY: Divides the result set into partitions based on the product.
ORDER BY: Sorts the rows within each partition by year in ascending order.
NTH_VALUE: Returns the amount from the first year for the same product.
The expression (amount - NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year)) / NTH_VALUE(amount, 1) OVER (PARTITION BY product ORDER BY year) * 100 computes the percentage change in sales compared to the first year for the same product.
ROUND: Rounds the percentage change to two decimal places.

Summary #

Use the NTH_VALUE window function to access a specific value within the same partition.

Quiz #

Was this tutorial helpful ?