Compute a Difference (Delta) Between Two Columns on Different Rows in postgreSQL

In PostgreSQL the modern relational database system, two types of queries are supported: SQL for the traditional relational databases and JSON for the non-relational databases. It is free and anybody can use it. The difference (delta) computation between the values of two columns of PostgreSQL at different rows can be done with various techniques.

In data manipulation and data analysis tasks, the numerical values on different rows in two columns are often compared. The difference (delta) is computed to do such a comparison. This article explores various techniques to achieve this task efficiently and effectively.

Computing a Difference (Delta) Between Two Columns on Different Rows

Three distinct methods will be explored in detail:

Using Window Functions

Window functions in PostgreSQL offer a robust mechanism for performing calculations across a set of rows relative to the current row. Leveraging these functions, we can easily compute the delta between values in two columns on different rows.

Using Subqueries

Another approach involves utilizing subqueries to retrieve the values of the previous row and then calculating the delta. This method provides flexibility and is particularly useful in scenarios where window functions may not be applicable.

Using Common Table Expressions (CTEs)

Common Table Expressions (CTEs) allow for defining temporary result sets that can be referenced within a query. By leveraging CTEs, we can efficiently compute the delta between values in two columns on different rows while maintaining readability and manageability.

First, we’ll create a table named example_table with two columns: id, and value.

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    value INTEGER
);

Now, let’s insert some sample data into the table:

INSERT INTO example_table (value) VALUES (10), (20), (30), (40), (50);

Now our table is ready, and we have some sample data. Here are some examples of computing the difference (or delta) between the value columns of different rows.

Example Table

1. Using Window Functions

Window functions in PostgreSQL allow for performing calculations across a set of rows related to the current row. By utilizing window functions, you can compute the delta between values in two columns on different rows.

SELECT 
    value,
    value - LAG(value) OVER (ORDER BY id) AS delta
FROM 
    example_table;

Explanation:

• SELECT value, value – LAG(value) OVER (ORDER BY id) AS delta: This part of the query selects the value column from the example_table and calculates the difference (delta) between the current value and the previous value using the LAG window function.
• value: This selects the value column from the example_table.
• LAG(value) OVER (ORDER BY id): This part calculates the value of the value column from the previous row, ordered by the id column. The LAG function retrieves the value from the previous row based on the specified ordering. If there’s no previous row, it returns NULL.
• value – LAG(value) OVER (ORDER BY id) AS delta: This computes the difference (delta) between the current value and the previous value, and aliases the result as delta.
• FROM example_table: This specifies the table from which the value column is selected.

Output:

Using Window Function

2. Using Subqueries

Another approach is to use subqueries to retrieve the values of the previous row and then calculate the delta.

SELECT 
    t1.value,
    t1.value - t2.value AS delta
FROM 
    example_table t1
JOIN 
    example_table t2 ON t1.id = t2.id + 1;

Explanation:

• SELECT t1.value, t1.value – t2.value AS delta: This part of the query selects the table’s first occurrence of example_table (also called t1) value column and then formulates the difference between the current row value (t1.value) and previous row value (t2.value) row id one less than current row through calculating.
• t1.value: Selects the value column from the first occurrence of example_table.
• t1.value – t2.value AS delta: Computes the delta (which is the difference between the value column of the current row (t1.value) and the value column of the row with id one less than the current row (t2.value)), and aliases it as delta.
• FROM example_table t1: This part of the query shows the first occurrence of example_table and aliases it as t1.
• JOIN example_table t2 ON t1.id = t2.id + 1: This part of the query does the inner join of t1 and t2 in both the table. It combines rows where the id of t1 is the same as the id of t2 incremented by 1.

Output:

Using Subqueries

3. Using Common Table Expressions (CTEs)

Common Table Expressions provide a way to define temporary result sets that can be referenced within a query.

WITH lagged_values AS (
    SELECT 
        id,
        value,
        LAG(value) OVER (ORDER BY id) AS lagged_value
    FROM 
        example_table
)
SELECT 
    id,
    value,
    value - lagged_value AS delta
FROM 
    lagged_values;

Explanation:

• WITH lagged_values AS (…): This part of the query defines a Common Table Expression (CTE) named lagged_values. CTEs allow you to create temporary result sets that can be referenced within the same query.
• SELECT id, value, LAG(value) OVER (ORDER BY id) AS lagged_value FROM example_table: Inside the CTE, this query selects three columns (id, value, and the lagged value of value) from the example_table. The LAG function retrieves the value of value from the previous row based on the ordering specified by id.
• SELECT id, value, value – lagged_value AS delta FROM lagged_values: This part of the query selects columns id and value from the lagged_values CTE, and calculates the difference (delta) between the current value and the lagged value, and aliases the result as delta.

Output:

Using CTE

Conclusion

In PostgreSQL, one can see the difference between any column in the first and second rows of the same columns using window functions, subqueries, and CTEs. Each technique is an advantage when it comes to the certain needs of the person. Then you will be able to implement these approaches for fast delta computations in your PostgreSQL queries.