选择先前不同的PostgreSQL值

Question

我有一个表:

<表类> id 日期 价值 tbody><<tr>12022-01-01112022-01-02112022-01-03212022-01-04212022-01-05312022-01-063

我很确定你必须做一个空白和岛屿组;您的更改。

可能有一种更简洁的方法来得到你想要的结果，但这是我将如何解决这个问题:

with changes as ( -- mark the changes and lag values
select id, date, value, 
coalesce((value != lag(value) over w)::int, 1) as changed_flag,
lag(value) over w as last_value
from a_table
window w as (partition by id order by date)
), groupnums as ( -- number the groups, carrying the lag values forward 
select id, date, value, 
sum(changed_flag) over (partition by id order by date) as group_num,
last_value
from changes
window w as (partition by id order by date)
) -- final query that uses group numbering to return the correct lag value
select id, date, value, 
first_value(last_value) over (partition by id, group_num
order by date) as diff
from groupnums;

db<此处小提琴>