sql - 使用连续的行对计算值，包括最后一行的值

Question

给定一个示例表:

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

如何为每一行计算该行的 val 和下一行的 val 之间的差异，但让最后一行取第二行的差异最后一行？

目前我有:

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

最后一行与其余计算合并的位置。我想知道是否有更优雅的方法来做到这一点，也许是使用窗口函数？

Answer 1

使用窗口函数 lead()、lag() 和 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

现场演示 rextester.