So I'm essentially adding an event to a changelog. Presuming I have an initial changelog that looks like this.

changelog table:

I also have an event table that looks like this:

I'm trying to add the rows from the event table to the changelog table so that the end table looks like this

The difficulty I'm having is on the row I'm adding in I need to fill the state with the state from the row above or row with the most recent timestamp before the timestamp of the row I'm adding. I can't simply use for example the current state.

Is this possible to achieve using SQL?

At the minute I'm just leaving the state null for the added event which isn't what I want.

SELECT 
  *,
  null as event
FROM changelog
UNION
SELECT
  *,
  null as state
FROM event

I'm using PostgreSQL

CodePudding user response：

One option is to merge the two tables using a UNION statement. Then you can translate the null values (with the COALESCE function) in the "state" column with the last "state" value using the LAG window function, which will order on the timestamp for each different id.

WITH cte AS (
    SELECT id, timestamp, state, null AS event FROM changelog_
    UNION
    SELECT id, timestamp, null AS state, event FROM events_
)
SELECT id,
       timestamp,
       COALESCE(state, 
                LAG(state) OVER(PARTITION BY id
                                ORDER     BY timestamp)) AS state,
       event
FROM cte

Try it here.