How to Use PostgreSQL LEAD Function

Written by Data Pilot
September 03, 2019
Have a Database Problem? Speak with an Expert for Free
Get Started >>

What is PostgreSQL LEAD() Function

Let’s jump straight into the PostgreSQL LEAD function. The LEAD() is one of the PostgreSQL function allows us to access the row that comes after the present row at a defined physical offset. In other words, from the present row the LEAD() function is capable to access data of the succeeding row, the row comes after the next row, and so on. The LEAD() function is beneficial for contrasting the values from present row and succeeding row.

Below is the syntax for the LEAD() function.

1
2
3
4
5
LEAD(expression [,offset [,default_value]])
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Let’s take the above syntax and explain it part by part.

  1. The expression will be processed and compared against the subsequent row, based on a specific offset from the present set of data or row. The expression can be any of the following:

    1. Column
    2. Expression
    3. Subquery

The above must weigh to an individual or single value.

  1. The offset option is a positive number that indicate the quantity of rows moving forward from the present row from that to access the needed data. If we did not specify a value to offset it will default to one (1).

  2. The default_value will be the value returned once the offset overboard the partition’s scope. It will also defaults to NULL once omitted in the statement.

  3. Using PARTITION BY will divide the rows into different partitions to where we apply the LEAD() function. As the default the entire set of results will be considered a single or individual partition once we take out this clause.

  4. ORDER BY will specify and control on how the rows will be presented in every partition to where PostgreSQL LEAD() function is specified.

How to Use PostgreSQL LEAD() function in Examples

Before we proceed with the demonstration let us create a table for this purpose.

Let’s use the below query to create a table named team_stats.

1
2
3
4
5
6
7
CREATE TABLE team_stats(
    name VARCHAR(140) NOT NULL,
    team_id INT NOT NULL,
    YEAR INT NOT NULL CHECK (YEAR > 0),
    points DECIMAL(10,1) CHECK (points >= 0),
    PRIMARY KEY (name,YEAR)
);

After creating the above team_stats table, we will now insert some data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
INSERT INTO
    team_stats(name, team_id, YEAR, points)
VALUES
    ('Luke SkyWalker',1,2018,180000),
    ('Mike SkyScraper',2,2018,170000),
    ('Jack Sparrow',3,2018,140000),
    ('Jackie Chan',4,2018,300000),
    ('Jet li',5,2018,300000),
    ('Luke SkyWalker',1,2019,160000),
    ('Mike SkyScraper',2,2019,140000),
    ('Jack Sparrow',3,2019,150000),
    ('Jackie Chan',4,2019,28000),
    ('Jet Li',5,2019,250000);
    ('Luke SkyWalker',1,2020,190000),
    ('Mike SkyScraper',2,2020,170000),
    ('Jack Sparrow',3,2020,180000),
    ('Jackie Chan',4,2020,290000),
    ('Jet Li',5,2020,270000),
    ('Luke SkyWalker',1,2020,190000),
    ('Mike SkyScraper',2,2020,170000),
    ('Jack Sparrow',3,2020,180000),
    ('Jackie Chan',4,2020,290000),
    ('Jet Li',5,2020,270000);

Examples of PostgreSQL LEAD() function: Result Set

In this example we will return the total points of the made by year. To do this use the following statement.

1
2
3
4
5
6
SELECT
   YEAR,
   SUM(points)
FROM team_stats
GROUP BY YEAR
ORDER BY YEAR;

The result should look something like the following.

1
2
3
4
5
6
 year |    sum
------+------------
 2018 | 1090000.0
 2019 |  728000.0
 2020 | 1100000.0
(3 rows)

In this example we will use LEAD() function to retrieve total points made on the present year and succeeding year the, use the following syntax to do this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH cte AS (
   SELECT
      YEAR,
      SUM(points) points
   FROM team_stats
   GROUP BY YEAR
   ORDER BY YEAR
)
SELECT
   YEAR,
   points,
   LEAD(points,1) OVER (
      ORDER BY YEAR
   ) next_year_points
FROM
   cte;

The result look something like the following.

1
2
3
4
5
6
 year |   points  | next_year_points
------+-----------+-----------------
 2018 | 1090000.0 |        728000.0
 2019 |  728000.0 |       1100000.0
 2020 | 1100000.0 |
(3 rows)

The above syntax uses CTE to get the total points sum up by year. While the query (SELECT) make use of the LAG() function to retrieve the points of the succeeding year for every row.

Since our data end with year 2020 the next_year_points for that will be null as there’s no data to be processed for next year which is 2021.

Example of the PostgreSQL LEAD() function: In Partition

In this example we use the LEAD() function to be able compare the points of the present year against the points of succeeding year for each team. To do this use the following syntax.

1
2
3
4
5
6
7
8
9
10
SELECT
   YEAR,
   points,
   team_id,
   LEAD(points,1) OVER (
      PARTITION BY team_id
      ORDER BY YEAR
   ) next_year_points
FROM
   team_stats;

Let’s discuss the above statement part by part.

  1. PARTITION BY will apportion every rows into partitions as specified by the team_id.
  2. ORDER By will arrange every rows in the partition in an ascending order of the field year.
  3. Finally we use LEAD() function to be able retrieve the points of the succeeding year from the present year of every partitions.

The result should resemble something like the following.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 year |  points   | team_id | next_year_points
------+-----------+---------+------------------
 2018 | 180000.0 |       1 |        160000.0
 2019 | 160000.0 |       1 |        190000.0
 2020 | 190000.0 |       1 |
 2018 | 170000.0 |       2 |        140000.0
 2019 | 140000.0 |       2 |        170000.0
 2020 | 170000.0 |       2 |
 2018 | 140000.0 |       3 |        150000.0
 2019 | 150000.0 |       3 |        180000.0
 2020 | 180000.0 |       3 |
 2018 | 300000.0 |       4 |         28000.0
 2019 |  28000.0 |       4 |        290000.0
 2020 | 290000.0 |       4 |
 2018 | 300000.0 |       5 |        250000.0
 2019 | 250000.0 |       5 |        270000.0
 2020 | 270000.0 |       5 |
(15 rows)

Conclusion

In this article we learn how to use the PostgreSQL LEAD() function.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.