CODEWITHSUNDEEP
sqlpostgresql
rovyko
rovyko

Reputation: 4577

Calculate value using consecutive pairs of rows, including a value for the last row

Given an example table:

CREATE TABLE test_table AS (
SELECT
generate_series(1,20) id,
POW(generate_series(1,20),2) val
);

How can I calculate the difference, for each row, between the row's val and the next row's val, but have the last row take on the difference of the second last row?

Currently I have:

WITH calc AS (
SELECT
    cur.id,
    nex.val - cur.val diff
FROM test_table cur
JOIN test_table nex
    ON (cur.id+1 = nex.id)
)
SELECT * FROM calc
UNION
SELECT
(SELECT MAX(id) FROM test_table),
(SELECT diff FROM calc ORDER BY id DESC LIMIT 1)
ORDER BY id

Where the last row is unioned to the rest of the calculation. I'm wondering if there's a more elegant way to do this, perhaps with window functions?

Upvotes: 2

Views: 50

Answers (1)

klin
klin

Reputation: 121604

Use the window functions lead(), lag() and coalesce():

select id, 
    coalesce(
        lead(val) over w- val,
        val- lag(val) over w) as difference
from test_table
window w as (order by id)
order by id

Live demo in rextester.

Upvotes: 2

Related Questions