Often in PostgreSQL you may want to use the LAG function to calculate the difference between rows in a table within groups.
You can use the following basic syntax to do so:
SELECT *,
points - lag(points) over (PARTITION BY team ORDER BY game) AS p_diff
FROM athletes
ORDER BY team, game;
This particular example calculates the difference between consecutive rows in the points column of the athletes table (ordered by the values in the game column), but only for rows that share the same value in the team column.
These differences between rows are saved in a new column named p_diff.
The following example shows how to use this syntax in practice.
Example: How to Use LAG Within Groups in PostgreSQL
Suppose that we create the following table named games that contains information about points scored by two different basketball teams during four consecutive games:
-- create table named 'athletes'
CREATE TABLE athletes (
team TEXT,
game INTEGER,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES ('Celtics', 1, 99);
INSERT INTO athletes VALUES ('Celtics', 2, 94);
INSERT INTO athletes VALUES ('Celtics', 3, 100);
INSERT INTO athletes VALUES ('Celtics', 4, 114);
INSERT INTO athletes VALUES ('Lakers', 1, 100);
INSERT INTO athletes VALUES ('Lakers', 2, 124);
INSERT INTO athletes VALUES ('Lakers', 3, 127);
INSERT INTO athletes VALUES ('Lakers', 4, 110);
-- view all rows in table
SELECT * FROM athletes;
team | game | points
---------+------+--------
Celtics | 1 | 99
Celtics | 2 | 94
Celtics | 3 | 100
Celtics | 4 | 114
Lakers | 1 | 100
Lakers | 2 | 124
Lakers | 3 | 127
Lakers | 4 | 110
Suppose that we would like to calculate the difference in points between consecutive games for each team.
We can use the following syntax to do so:
SELECT *,
points - lag(points) over (PARTITION BY team ORDER BY game) AS p_diff
FROM athletes
ORDER BY team, game;
team | game | points | p_diff
---------+------+--------+--------
Celtics | 1 | 99 |
Celtics | 2 | 94 | -5
Celtics | 3 | 100 | 6
Celtics | 4 | 114 | 14
Lakers | 1 | 100 |
Lakers | 2 | 124 | 24
Lakers | 3 | 127 | 3
Lakers | 4 | 110 | -17
The resulting table contains a new column named p_diff that shows the difference in points values between consecutive games for each team.
Feel free to use similar syntax to calculate lagged values within groups in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to SELECT DISTINCT on Multiple Columns
